Archives

Hi SQL Server Folks – Recently I got a situation where database mirroring was setup in my production environment, but due to other needs I need to enable CDC (Change Data Capture) feature also at my SQL Server 2008 R2 instance. If you wants to know about CDC and its usage, you can follow below link :

1. We had a Server A and Server B. Database Mirroring on a database name as “Test” was setup from Server A to Server B, here Server A was principal and Server B was acting as Mirror.

2. We enabled the CDC feature at Server A @ database “Test” at “Emp” table. As soon as you enable the CDC it will create two jobs at Server A with name as below : cdc.<db_name>_capturecdc.<db_name>_cleanup

Also it will create a system table name as cdc_jobs under msdb system database that will keep the information about above 2 jobs.

3. But here the problem is, it will not create those above jobs and system table at Server B. So if you fail over the database “Test” from Server A to Server B, changes that you made on emp table will not capture in CDC related tables at server B. So to make CDC effective on both the servers we need to create above 2 CDC jobs at Server B also. To create the above 2 jobs at Server B follow the below below process :

a) Stop and disable both CDC jobs at Server A.

b) Fail over the database mirroring for “Test” from Server A to B.

c) Connect to Server B now since here db “Test” is Principal now and

fire the below commands :

Use Test go

EXEC sys.sp_cdc_add_job @job_type = N’capture’;

GO

EXEC sys.sp_cdc_add_job

@job_type = N’cleanup’,

@start_job = 0,

@retention = 5760;

d) After executing above statements It will create 2 CDC jobs (CDC Capture & CDC cleanup) and a system table name as cdc_jobs in msdb database at Server B. So now if you will make any transaction at Server B at emp table under “Test” db, it will also capture under CDC related tables and will mirror to mirrored Server A.

Hope this article will help, Thanks for reading and stay tune for upcoming posts.

SQL Server 2012 RTM (Code Name Denali) Launched today. It can be available on 1st April in most countries. It’s having some really cool features like File Table, ColumnStore Index and a good reporting environment PowerView. Please find More details at below links:

I think now you should be making plans to get off of the RTM branch, and get on to the SP1 branch for SQL Server 2008 R2, especially since a number of useful new DMVs were added to SP1. Another reason to get on SP1 is that Microsoft will eventually retire the RTM branch, leaving you on an “unsupported service pack” when that happens.

From the below link you can find the supoort cycle for MS SQL Server and their HotFixes for different versions.

I had come across situation multiple times when i need to install SQL Server 2008 but before that need to un-install SQL Server 2005. So many times it happens with me that if i am remvoving SQL Server 2005 from Add\Remove programs then it’s failing. In that case you can follow below steps that describes how to uninstall SQL Server 2005 by deleteing resistry keys.

Taking full Database backups of the System Databases and User Databases i.e. in .BAK format. (Also if we have RS – take backup of the Encryption Key).

Next take Transaction Log backup of the User Databases i.e. in .TRN format.

Take the backup of the registry – Start à Run à Type – regedit à Highlight – My computer – right click and choose Export and save it in desktop or disk drive.

Delete/Rename following Registry keys for the services and also make a note of the service name :

i) For Integration Services:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MsDtsServer – Please remember Integration Services is a Shared component, so removing this might affect other instances which are in working condition.

Delete/Rename any folders on the hard drive related to that instance. e.g. If default installation path is chosen for SQL Installation dir and SQL Data dir then you would like to delete the following folders :

Before doing the Delete please make a copy of DATA folder under %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data

For 32-bit SQL on 32-bit machine OR 64-bit SQL on 64-bit machine, delete/rename the following folders :

For Default Instance:

%ProgramFiles%\Microsoft SQL Server\MSSQL.1

%ProgramFiles%\Microsoft SQL Server\MSAS.x

%ProgramFiles%\Microsoft SQL Server\MSRS.x

In WOW mode (For 32-bit SQL on 64-bit machine), delete/rename the following folders

Before doing the Delete please make a copy of DATA folder under %ProgramFiles(x86)% \Microsoft SQL Server\MSSQL.1\MSSQL\Data

In the Part-1 of this section I told you that how to open the default SQL Server applications ports of SQL Server 2008 R2 on Windows Server 2008 R2. But what happen if SQL Server applications is not using default ports which happened most probably. So to open the ports that SQL Server apps is using first we need to find out the port numbers. To find out the port numbers that is using by different SQL Server applications like SQL Server, Analysis Service and SQL Server Browser Service, Open the command prompt as Administrator and type the following command:-

“netstat –abn >>C:\Port.txt”

2. This command will capture all the ports and their respective PID (Process ID) i.e. which PID is using which port in your system. All this info you can find in a file name as Port.txt under C: drive that we just created.

3. To find out the PID for various SQL applications go in to start –> Programs –>Microsoft SQL Server 2008 R2 –> Configuration Tools –> SQL Server Configuration Manager then you will find window like below:-

4. On this window on the left hand side you can see “SQL Server Services”, Just click on that and you will see a window like below:-

In this window as you can see in highlighted section that there are different SQL Applications are there under column “Name” and they are using different “Process ID”.

5. Now you open the file “Port.txt” and search the PID for which you want to know the Port No. For ex. If you want to find out that What is the port using by Analysis Services then note down the PID for this Analysis Service that is 4260 in this case, it might be different in your case. Now Search that PID 4260 in the ports.txt file you will get a info like below:-

AS highlighted this PID using 10.86.35.155:2383; Here 10.86.35.155 is the IP which is used by Analysis Service and 2383 is the port number where Analysis Service is listening the client requests.

Like that you can find out other port numbers used by different applications.

On Windows Server 2008 R2 if you install SQL Server 2008 R2 then if you try to connect that SQL Server from outside of that server then you won’t able to connect it . What it does mean let me explian this. Suppose on Node1 (With OS Windows Server 2008 R2) you installed default instance of SQL Server 2008 R2. After this you did not opened the ports for this SQL instance. Now from Node2 If you try to connect this SQL Server instance then you will get a below error:-

This error is coming because of Port issue. By default for all the applications there is a firewall enable on windows 2008 R2 servers. So in this environment to access SQL Server from remote server we have to open the ports for SQL Server. Below are the commands to open the SQL Server ports for different features of it. Before opening the ports of SQL Servers you have to know the port numbers. In below I am specifying only default ports.

Below are the two methods to open the Default Ports of SQL Server apps.

Method 1 :- Open the command prompt as administartor and run the following commands.