SQL DBA learning curve

Great news !!! SQL Server 2014 CTP1 is out and the wait is over. Drop everything(Except the databases !) and download your fresh copy from here.

Here is the official announcement with some high level details.

In this post we will do a quick step by step walk through of the whole install process and see if there are any changes from the prior installs.

* Note  – The below point from the download page should be noted

Microsoft SQL Server 2014 CTP1 is pre-release software and should be installed only on a clean machine. It does not support upgrade from or side-by-side installations with any previous version of SQL Server, including SQL Server 2012.

Below is the step by step install process –

[Please click on the images for a better resolution]

Step 1 –  

We will run the setup.exe to get the regular install screen.

Step…

Well, it can’t be done. You cannot move a table to a particular file in database.

What you can do is move the table to a particular file group. A FILEGROUP contains one or more data files. If the FILEGROUP contains more than one data file, SQL Server uses a proportional fill algorithm to make use of all data files in the FILEGROUP. On the other hand, if the FILEGROUP contains a single data file, then you can say that you have moved the table to a particular file without any doubts.

To move a table to a particular file file group, you need to re-create the clustered index on the table. Recreating an index effectively moves the table to the new FILEGROUP. The example below demonstrates the same.

Let’s create a test database to work with:

USE   [master]

GO

CREATEDATABASE [TestDB]

One of the most exciting news which got announced yesterday was related to Windows Azure. When Scott Guthrie mentioned that billing model for Windows Azure is going to be per minute basis from now on, then there was a great cheer from the crowd.

This is indeed a game changing announcement. I definitely foresee great amount of Windows Azure usage in the future, and this announcement is a great deal for the cloud adopters.

Earlier if I had used my cloud service for 20 minutes,and then turned that off I was charged still for the full hour. Lot many cloud providers still operate with this policy.

Going forward I will be charged for what I use. I will be charged for just 20 minutes, no questions asked.

Another super news which was really exciting for me was related to no charge for stopped VMs.

Think about a situation –

Your Dev/Test folks works from…

Statistics are used by the SQL Server optimizer to choose the efficient plan.  When we don’t have up to date statistics
it may end with SQL server optimizer choosing inefficient query plan. We can use below query to identify when SQL Server
statistics were last updated.

Introduction
Change data capture or (CDC) is very helpful feature in SQL Server 2008
Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational ‘change table” The SQL Server DBA can then easily monitor the activity for the logged table using these new audit tables .(Really i love this feature very nice microsoft)
Enabling Change Data Capture
CDC Enabling have two steps one on Database level and one on Tables level before Enabling CDC you must be Check CDC is enabled in any Database so Run the Query to check whether it is enabled for any database.

USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
GO

After you run this script you will see your all database name and id and you will know  from this column  (IS_CDC_ENABLED) if CDC  feature is enabled…