Enable CDC with Database Mirroring environment

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 :

http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

To explain & simplify the situation in our environment I’m taking an example here :

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>_capture
cdc.<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.

Cheers,                                                                                                                                                         Gaurav

Advertisements

SQL Server 2012 RTM (Code Name Denali) Launched today.

Hi SQL Server Folks,

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:

http://www.microsoft.com/Presspass/press/2012/mar12/03-06SQLServer12PR.mspx

Microsoft Releases SQL Server 2012, Drives Advancements in Big Data – Server and Tools Business News

Also SQL Server 2012 is capable to handle big data.

Let’s use and rock it !!!

New Cumulative Updates For SQL Server 2008 R2

Hi SQL Server Folks,

On Feb 22, 2012, Microsoft released SQL Server 2008 R2 SP1 Cumulative Update 5, which is Build 10.50.2806. This CU has 16 fixes listed. Below is the Lnik to download this-

http://support.microsoft.com/kb/2659694 

 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.

http://support.microsoft.com/lifecycle/search/?sort=PN&alpha=SQL

As always, keep in mind that these Cumulative Updates are only for SQL Server 2008 R2, not for SQL Server 2008.

Cheers,

Gaurav !!!

How to Un-Install SQL Server 2005 from Registry

Hi SQL Server Folks,

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.

  1.  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).
  2. Next take Transaction Log backup of the User Databases i.e. in .TRN format.
  3. 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.
  4. Take a full backup of Registry key. Details at http://support.microsoft.com/kb/322756
  5. Please make a copy of DATA folder under %ProgramFiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data into a different drive or location or server.
  6. Script out of the Logins for SQL 2005? We can follow below KB Articles – 918992 or 246133. If you already have the logins script then it’s really good.

 How to transfer the logins and the passwords between instances of SQL Server 2005

http://support.microsoft.com/kb/918992

 How to transfer logins and passwords between instances of SQL Server

http://support.microsoft.com/kb/246133

 Script out all the jobs in MSDB database? I found that you can script out all jobs from Management Studio. If you already have the Job scripts then it’s really good. 

  1. Open the server in the Object Explorer.
  2. Open the “Object Explorer Details” window.
  3. In the “Object Explorer Details” window, drill down to “SQL Server Agent”
  4. Drill down to “Jobs”
  5. Select the jobs you want to script.
  6. Right click and select “Script Job as”, then “Create To”, and select where you want them scripted to.

 Links –   Script out Jobs in SQL Serverhttp://blogs.msdn.com/jenss/archive/2009/01/27/script-out-jobs-in-sql-server.aspx

Job.Script Method – http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.script(SQL.90).aspx

 Just for ready reference:

KB915845 – SQL Server Agent jobs fail when the jobs contain job steps that use tokens after you install SQL Server 2005 Service Pack 1

http://support.microsoft.com/kb/915845

           Steps that we need to perform during Uninstallation of SQL 2005 SP3.

 Use the following KB Article 909967 to uninstall SQL 2005 Manually.

How to uninstall an instance of SQL Server 2005 manually

http://support.microsoft.com/kb/909967

OR  How to: Uninstall an Existing Instance of SQL Server 2005 (Setup)

 http://technet.microsoft.com/en-us/library/ms143412(SQL.90).aspx

 Use ‘Windows Installer Cleanup Utility’ and ensure that there is SQL components on the server. If any just select the component and remove them.

Windows Installer Cleanup Utility

http://support.microsoft.com/kb/290301

 I advice please don’t use this Utility, I created so many problems in your new SQL Server install and more of that it’s not supported by Microsoft now.

The steps that I would manually (For un-installation of uninstall SQL 2005) do during the call would be as shown below.

 Step-1

I perform the steps mentioned in Below is the steps that we need to do before uninstalling SQL 2005 SP3.

 Step-2

Go to Add/Remove Programs – Remove all the components of SQL Server 2005 as shown below.

Microsoft SQL Server 2005

Microsoft SQL Server 2005 Backward Compatibility

Microsoft SQL Server 2005 Best Practices Analyzer

Microsoft SQL Server 2005 Books Online

Microsoft SQL Server Native Client

Microsoft SQL Server 2005 Setup Support Files

Microsoft SQL Server VSS Writer

MSXML 6 Service Pack 2 (KB954459)

SQLXML4

Use ‘Windows Installer Cleanup Utility’ and ensure that there is SQL components on the server. If any just select the component and remove them.

Windows Installer Cleanup Utility

http://support.microsoft.com/kb/290301

 Step-3

Delete  following registry keys (If they exist):

 i) For Default Instance : HKLM\Microsoft\Microsoft SQL Server\

Or,

For Named Instance : HKLM\Microsoft\Microsoft SQL Server\<Instance_Name>

 Step-3

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.

 ii) For Other services of Default Instance:

 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLFDLauncher

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServerOLAPService

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ReportServer

 

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Services\SQLSERVERAGENT

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Services\MSSQLSERVER

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Services\MSSQLFDLauncher

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Services\MSSQLServerOLAPService

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Services\ReportServer

 

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Services\SQLSERVERAGENT

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Services\MSSQLSERVER

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Services\MSSQLFDLauncher

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Services\MSSQLServerOLAPService

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Services\ReportServer

 

iii) For Other services of Named Instance:

 

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLAgent$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLFDLauncher$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSOLAP$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ReportServer$<Instance_Name>

 

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Services\SQLAgent$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Services\MSSQL$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Services\MSSQLFDLauncher$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Services\MSOLAP$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet001\Services\ReportServer$<Instance_Name>

 

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Services\SQLAgent$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Services\MSSQL$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Services\MSSQLFDLauncher$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Services\MSOLAP$<Instance_Name>

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet002\Services\ReportServer$<Instance_Name>

 

Step-4

Remove the following services from command prompt :

 i) For Integration Services:

 C:\>sc.exe delete MsDtsServer

[SC] DeleteService SUCCESS

 ii) For Other services of Default Instance:

 Microsoft Windows [Version 5.2.3790]

(C) Copyright 1985-2003 Microsoft Corp.

 C:\>sc.exe delete SQLSERVERAGENT

[SC] DeleteService SUCCESS

 C:\>sc.exe delete MSSQLSERVER

[SC] DeleteService SUCCESS

 C:\>sc.exe delete MSSQLFDLauncher

[SC] DeleteService SUCCESS

 C:\>sc.exe delete MSSQLServerOLAPService

[SC] DeleteService SUCCESS

 C:\>sc.exe delete ReportServer

[SC] DeleteService SUCCESS

 Or,

 For Other services of Named Instance:

 Microsoft Windows [Version 5.2.3790]

(C) Copyright 1985-2003 Microsoft Corp.

 C:\>sc.exe delete SQLAgent$<Instance_Name>

[SC] DeleteService SUCCESS

 C:\>sc.exe delete MSSQL$<Instance_Name>

[SC] DeleteService SUCCESS

 C:\>sc.exe delete MSSQLFDLauncher$<Instance_Name>

[SC] DeleteService SUCCESS

 C:\>sc.exe delete MSOLAP$<Instance_Name>

[SC] DeleteService SUCCESS

 C:\>sc.exe delete ReportServer$<Instance_Name>

[SC] DeleteService SUCCESS

 

Step-5

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

 For Default Instance:

%ProgramFiles(x86)% \Microsoft SQL Server\MSSQL.1

%ProgramFiles(x86)% \Microsoft SQL Server\MSAS.x

%ProgramFiles(x86)% \Microsoft SQL Server\MSRS.x

 Step-6

Reboot the Windows Server after above steps.

 Now you should be able to re-install the cleaned up instance.

 How to: Restore a Database Backup (SQL Server Management Studio)

http://msdn.microsoft.com/en-us/library/ms177429(SQL.90).aspx

How to: Restore a Transaction Log Backup (SQL Server Management Studio)

http://msdn.microsoft.com/en-us/library/ms177446(SQL.90).aspx

  • Restore the Logins and Jobs by running the Logins & Jobs scripts.

 

Hope this Post will help some one and will save time for them.

Thanks for reading, Stay glue for more upcoming stuff’s

CU # 4 Has been relased for SQL Server 2008 R2 SP1

Hi Guys,

Cumulative Update # 4 has been released for SQL Server 2008 R2 SP1, For more details and to download it please check the below links-

1. Cumulative Update # 4 for SQL Server 2008 R2 SP1 – http://support.microsoft.com/kb/2633146

2. An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems- http://support.microsoft.com/kb/935897

3. SQL Server Support Information: http://support.microsoft.com/ph/2855

[Part 2] Cannot connect to Server A network-related or instance-specific error occurred while establishing a connection to SQL Server

Hi SQL Server Folks,

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:-

  1.  “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.

Hope It will Help Someone !! Thanks for your time to read this.

[Part 1] Cannot connect to Server A network-related or instance-specific error occurred while establishing a connection to SQL Server

Hi SQL Server Folks,

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.

+++++++++++++++         SQL Server Ports      +++++++++++++

netsh advfirewall firewall add rule name=”SQLServer” dir=in action=allow protocol=TCP localport=1433

netsh advfirewall firewall add rule name=”SQL Admin Connection” dir=in action=allow protocol=TCP localport=1434

netsh advfirewall firewall add rule name=”SQL Service Broker” dir=in action=allow protocol=TCP localport=4022

netsh advfirewall firewall add rule name=”SQL Debugger/RPC” dir=in action=allow protocol=TCP localport=135 

 

+++++++++++++++   Analysis Services Ports     +++++++++++++   

netsh advfirewall firewall add rule name=”Analysis Services” dir=in action=allow protocol=TCP localport=2383

netsh advfirewall firewall add rule name=”SQL Browser” dir=in action=allow protocol=TCP localport=2382

 

+++++++++++++++     Misc. Applications   +++++++++++++++ 

netsh advfirewall firewall add rule name=”HTTP” dir=in action=allow protocol=TCP localport=80

netsh advfirewall firewall add rule name=”SSL” dir=in action=allow protocol=TCP localport=443

netsh advfirewall firewall add rule name=”SQLServerBrowser” dir=in  action=allow protocol=udp localport=1434

netsh advfirewall firewall add rule name=”multicastbroadcastresponse” dir=in  action=enable

 

+++++++++++++++       Integration services     +++++++++++++++ 

netsh advfirewall firewall add rule name=”RPC (TCP/135) for SQL SSIS” dir=in action=allow protocol=TCP localport=135

 

+++++++++++++++       Programs         +++++++++++++++ 

netsh advfirewall firewall add rule name=”RPC (TCP/135) for SQL SSIS” dir=in program=”C:\Program Files\100\DTS\Binn\MsDtsSrvr.exe” action=allow

netsh advfirewall firewall add rule name=”SQLServer” dir=in Program=”F:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe” action =Allow

For all Outbound ports use the same cmds with “dir=out ” in place of dir=in

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

Method 2: If above solution is not working for you then you can use below method also

To create the script, follow these steps:

1. Start Notepad.

2. Copy and paste the following code into Notepad:

 @echo =========  SQL Server Ports  ===================

@echo Enabling SQLServer default instance port 1433

netsh firewall set portopening TCP 1433 “SQLServer”

@echo Enabling Dedicated Admin Connection port 1434

netsh firewall set portopening TCP 1434 “SQL Admin Connection”

@echo Enabling conventional SQL Server Service Broker port 4022 

netsh firewall set portopening TCP 4022 “SQL Service Broker”

@echo Enabling Transact-SQL Debugger/RPC port 135

netsh firewall set portopening TCP 135 “SQL Debugger/RPC”

@echo =========  Analysis Services Ports  ==============

@echo Enabling SSAS Default Instance port 2383

netsh firewall set portopening TCP 2383 “Analysis Services”

@echo Enabling SQL Server Browser Service port 2382

netsh firewall set portopening TCP 2382 “SQL Browser”

@echo =========  Misc Applications  ==============

@echo Enabling HTTP port 80

netsh firewall set portopening TCP 80 “HTTP”

@echo Enabling SSL port 443

netsh firewall set portopening TCP 443 “SSL”

@echo Enabling port for SQL Server Browser Service’s ‘Browse’ Button

netsh firewall set portopening UDP 1434 “SQL Browser”

@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)

netsh firewall set multicastbroadcastresponse ENABLE

3. Save the file as a .txt file by using the following name: OpenSqlServerPort.txt

4. Rename the OpenSqlServerPort.txt file to the following: OpenSqlServerPort.bat

Before you run the OpenSqlServerPort.bat script, you must copy the script to the computer which has the firewall and then run the script on that computer. To run the script, follow these steps:

1. Click Start, click Run, type cmd, and then click OK.

2. At the command prompt, use the cd command to navigate to the folder in which you saved the OpenSqlServerPort.bat file.

3. To run the OpenSqlServerPort.bat script, type OpenSqlServerPort.bat at the command prompt, and then press ENTER.

Hope this post will help someone. Please stay glue for more SQL Stuff’s.  🙂