MERGE Statement in SQL server 2008

The MERGE statement performs insert, update, or delete operations on a target
table based on the results of a join to a source table.

For example, you can synchronize two tables by inserting, updating, or deleting
all of the rows in one table based on differences in the other table.


Here is an example of MERGE code:
MERGE MyDatabase.Inventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM mySales.Orders AS o
JOIN mySales.mySalesOrderHeader AS soh
ON o.mySalesOrderID = soh.mySalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate,
Deleted.ProductID,
Deleted.Quantity, Deleted.ModifiedDate;

SQL Server Service Check Utility

SQL Server Service Check Utility

Here is how this script could help you

Determine the status of SQL server service(s) - running or not

Determine if the service is installed or not

I have executed it on the following servers

32 bit and 64 bit

SQL 2005 with SP3 - Stand alone and on multiple instances

SQL 2008 with SP1 and SP2 - Stand alone and on multiple instances

SQL 2008 R2 latest patches

/* ------------------------------------------ Inital Setup -----------------------------------------------------*/

CREATE TABLE #RegResult

(ResultValue NVARCHAR(4))

CREATE TABLE #ServicesServiceStatus /*Create temp tables*/

(RowID INT IDENTITY(1,1),ServerName NVARCHAR(128),ServiceName NVARCHAR(128),ServiceStatus varchar(128)

,StatusDateTime DATETIME DEFAULT (GETDATE()),PhysicalSrverName NVARCHAR(128))

DECLARE

@ChkInstanceName nvarchar(128) /*Stores SQL Instance Name*/

,@ChkSrvName nvarchar(128) /*Stores Server Name*/

,@TrueSrvName nvarchar(128) /*Stores where code name needed */

,@SQLSrv NVARCHAR(128) /*Stores server name*/

,@PhysicalSrvName NVARCHAR(128) /*Stores physical name*/

,@FTS nvarchar(128) /*Stores Full Text Search Service name*/

,@RS nvarchar(128) /*Stores Reporting Service name*/

,@SQLAgent NVARCHAR(128) /*Stores SQL Agent Service name*/

,@OLAP nvarchar(128) /*Stores Analysis Service name*/

,@REGKEY NVARCHAR(128) /*Stores Registry Key information*/

SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128))

SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))

SET @ChkInstanceName = @@serverName

IF @ChkSrvName IS NULL /*Detect default or named instance*/

BEGIN

SET @TrueSrvName = 'MSQLSERVER'

SELECT @OLAP = 'MSSQLServerOLAPService' /*Setting up proper service name*/

SELECT @FTS = 'MSFTESQL'

SELECT @RS = 'ReportServer'

SELECT @SQLAgent = 'SQLSERVERAGENT'

SELECT @SQLSrv = 'MSSQLSERVER'

END

ELSE

BEGIN

SET @TrueSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))

SET @SQLSrv = '$'+@ChkSrvName

SELECT @OLAP = 'MSOLAP' + @SQLSrv /*Setting up proper service name*/

SELECT @FTS = 'MSFTESQL' + @SQLSrv

SELECT @RS = 'ReportServer' + @SQLSrv

SELECT @SQLAgent = 'SQLAgent' + @SQLSrv

SELECT @SQLSrv = 'MSSQL' + @SQLSrv

END

/* ---------------------------------- SQL Server Service Section ----------------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLSrv

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1

BEGIN

INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Sever service*/

EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrv

UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

ELSE

BEGIN

INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')

UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

/* ---------------------------------- SQL Server Agent Service Section -----------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLAgent

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1

BEGIN

INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Agent service*/

EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgent

UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

ELSE

BEGIN

INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')

UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

/* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\SQLBrowser'

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1

BEGIN

INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Browser Service*/

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'

UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

ELSE

BEGIN

INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')

UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

/* ---------------------------------- Integration Service Section ----------------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\MsDtsServer'

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1

BEGIN

INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Intergration Service*/

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer'

UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

ELSE

BEGIN

INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')

UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

/* ---------------------------------- Reporting Service Section ------------------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\'+@RS

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1

BEGIN

INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Reporting service*/

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@RS

UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

ELSE

BEGIN

INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')

UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

/* ---------------------------------- Analysis Service Section -------------------------------------------------*/

IF @ChkSrvName IS NULL /*Detect default or named instance*/

BEGIN

SET @OLAP = 'MSSQLServerOLAPService'

END

ELSE

BEGIN

SET @OLAP = 'MSOLAP'+'$'+@ChkSrvName

SET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAP

END

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1

BEGIN

INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Analysis service*/

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP

UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

ELSE

BEGIN

INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')

UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

/* ---------------------------------- Full Text Search Service Section -----------------------------------------*/

SET @REGKEY = 'System\CurrentControlSet\Services\'+@FTS

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1

BEGIN

INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Full Text Search service*/

EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@FTS

UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

ELSE

BEGIN

INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')

UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity

UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity

UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity

TRUNCATE TABLE #RegResult

END

/* -------------------------------------------------------------------------------------------------------------*/

SELECT PhysicalSrverName AS 'Physical Server Name' /*Display finding*/

,ServerName AS 'SQL Instance Name'

,ServiceName AS 'SQL Server Services'

,ServiceStatus AS 'Current Service Service Status'

,StatusDateTime AS 'Date/Time Service Status Checked'

FROM #ServicesServiceStatus

/* -------------------------------------------------------------------------------------------------------------*/

DROP TABLE #ServicesServiceStatus /*Perform cleanup*/

DROP TABLE #RegResult

Kill all connection to database

use master

go

if exists(select * from master.dbo.sysobjects where name = 'proc_kill_dbconnections' and type = 'P')

    drop procedure dbo.proc_kill_dbconnections

go

create procedure proc_kill_dbconnections

/*****************************************************************************************************

*

* Author Rafal Skotak

* Purpose Procedure tries to kill all connections to the specified database

*

* Date 2011.02.18

*

******************************************************************************************************/

    @dbname    sysname

with encryption

as

begin

    set nocount on

    ------- prepare temporary tables

    -------------------------------------------

    create table #temp_processes_table

    (

        spid             int primary key,

        ecid             int,

        status             sysname,

        loginame         sysname null,

        hostname         sysname null,

        blk             int,

        dbname             sysname null,

        cmd             sysname null,

        request_id        int null,

        mod             int not null default 0

    );

    -- get processes list

    --------------------------

    insert into #temp_processes_table (spid, ecid, status, loginame, hostname, blk, dbname, cmd, request_id) exec sp_who

    declare @count        int

    declare @process_id    int

    declare @cmd        varchar(8000)

    select @count = count(*) from #temp_processes_table where mod = 0

    while @count > 0

    begin

        set @process_id = NULL

        select top 1 @process_id = spid from #temp_processes_table where mod = 0 and dbname = @dbname

        if @process_id is NULL

            break

        print @process_id

        set @cmd = 'kill ' + cast(@process_id as varchar)

        exec (@cmd)

        update #temp_processes_table set mod = 1 where spid = @process_id

        select @count = count(*) from #temp_processes_table where mod = 0 and dbname = @dbname

    end

    -- cleanup - drop temporary tables

    ---------------------------------------

    drop table #temp_processes_table

end

go

-- example

-- exec dbo.proc_kill_dbconnections 'Northwind'

Database Mirroring in SQL Server 2008

What is Database Mirroring?

Database mirroring is the feature in SQL Server 2005 and SQL Server 2008 that provides a high availability solution for Databases. This feature can be enabled and used only on a database with Full recovery models. The database can be mirrored from one SQL Server instance to another SQL Server instance. The source instance is called Principal server; the target instance is called Mirrored server. We could have one more server called Witness server--we will talk about that in later part of this article series.

How does database mirroring work?

The principle server sends the active transaction log record to the mirrored server. The mirrored server applies the transaction log record one by one in sequence.

Modes of Database Mirroring

Database mirroring can be configured in two different modes, High-Safety mode also known as synchronous mode and High-Performance mode also known as asynchronously. The term synchronous and asynchronous says it all.

In the synchronous mode, the principal server sends the transaction and waits until the transaction is committed on the mirrored server. Then the transaction is committed on the principal server.

In Asynchronous mode, the principal server sends the transaction to the mirrored server and does not wait for the transaction on the mirrored server to commit.

We will discuss transaction safety in detail in a future installment of this series.

Now let's setup database mirroring between the SQL Server instance PowerPC\SQL2008 [our principal server] and PowerPC\SQL2k8 [our mirrored server].

What are the Pre-Requisites of database mirroring?

The following are the pre-requisites for database mirroring.

• Edition of SQL Server should be Standard, Enterprise or Developer edition
• Principal Database involved in database mirroring should be in full recovery mode
• Before configuring database mirroring, take a full backup, transactional log backup on the principal server and restored it on the mirrored server with NORECOVERY option.

Now let's create a database DB1 on the principal server, PowerPC\SQL2008, using the following transact SQL statement. In this part of article series, we are going to discuss database mirroring with synchronous mode and with no witness server.

USE [master] GO  /****** Object:  Database [DB1]    Script Date: 06/20/2009 21:10:33 ******/ IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1') DROP DATABASE [DB1] GO   USE [master] GO  /****** Object:  Database [DB1]    Script Date: 06/20/2009 21:10:13 ******/ CREATE DATABASE [DB1] ON  PRIMARY  ( NAME = N'DB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DB1.mdf' , \  SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  LOG ON  ( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DB1_log.LDF' ,   SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO 

Now let's create a database DB1 on the mirrored server, PowerPC\SQL2K8, using the following transact SQL statement.

USE [master] GO  /****** Object:  Database [DB1]    Script Date: 06/20/2009 21:10:33 ******/ IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1') DROP DATABASE [DB1] GO   USE [master] GO  /****** Object:  Database [DB1]    Script Date: 06/20/2009 21:10:13 ******/ CREATE DATABASE [DB1] ON  PRIMARY  ( NAME = N'DB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf' ,   SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  LOG ON  ( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_log.LDF' ,   SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO 

If the target server does not have the database with same name, you will get the following error when configuring database mirroring. [Refer Fig 1.0]

Fig 1.0

Note: Instead of creating the DB1 database on the mirrored server, you could restore the database backup and tranlog backup using the with replace option to create and restore at the same time.

Now let's backup the database and transaction on the principal server using the following transact SQL statement.

use master go Backup database DB1 to disk ='C:\Backups\DB1.Bak' with init go Backup log DB1 to disk ='C:\Backups\DB1.trn' with init go 

Restore the database on the target server using the following transact SQL statement.

use master go restore database DB1 from disk ='C:\Backups\DB1.Bak' with norecovery,  replace, move 'DB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf', move 'DB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_log.ldf' go restore log DB1 from disk ='C:\Backups\DB1.trn' with norecovery, replace, move 'DB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1.mdf', move 'DB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8\MSSQL\DATA\DB1_log.ldf' go 

On the target server, if the database is not in restore mode you will get the following error. [Refer Fig 1.2]

Fig 1.2

Configure the database DB1 on the principal server for database mirroring. Using SQL Server management studio, expand the databases and click on the Database DB1. Right click on the database DB1 and select properties. In the properties window select the "Mirroring" option as shown below. [Refer Fig 1.3]

Fig 1.3

Now click on the "Configure Security" button and you will see the following screen. Since we are not going to setup the witness server, select the option "No" and click next. [Refer Fig 1.4]

Fig 1.4

Select the default port and the endpoint name chosen by the SQL server management studio and click Next. [Refer Fig 1.5] If you are choosing some other port, then make sure that port is open and available.

Fig 1.5

Now select the mirrored server name, click on the "Connect" button and make sure you can connect to the mirrored server. Select the default port and the endpoint name chosen by the SQL server management studio and click Next. [Refer Fig 1.6] If you are choosing some other port, then make sure that port is open and available.

Fig 1.6

Type the appropriate service account you want to use for the database mirroring. [Refer Fig 1.7]

Fig 1.7

Double check the summary details and click finish. This will configure database mirroring. [Refer Fig 1.8, 1.9, 1.10]

Fig 1.8

\
Fig 1.9

\
Fig 1.10

On the next screen, click on the button "Start Mirroring". [Refer Fig 1.11]

Fig 1.11

On the next screen, click on the "Yes" button. [Refer Fig 1.12]

\
Fig 1.12

The following screen shows that database mirroring is configured and running. [Refer Figure 1.13]

Fig 1.13

Click OK and refresh the databases. You can see the caption of the DB1 database has changed in both principal and mirrored server. [Refer Fig 1.14]

\
Fig 4

SQL Server 2008 LOG Shipping

Log shipping technology is one of the methods for implementing High Availability solution. Log shipping is easy to set up, standby database can be available for reporting purpose, easy to maintain and troubleshoot, and multiple standby databases can be configured for log shipping in the same server or instance. One of the main issues or concern with log shipping is adding overhead to the disk subsystem because of frequent transaction log backup. More frequent the transaction log backup taken, the more overhead will be added to the server disk subsystem, and this leads system slow down.

In SQL Server, transaction log provides an ability to recover a database to a point in time. Log shipping is a process of copying the transaction log back up from a source server to a destination server and restore on the destination server. The main objective of implementing log shipping is to maintain two SQL Server database synchronized in separate locations for high availability.

Basic Components of Log shipping are:

1. Primary server

2. Secondary Server

3. Monitor Server

Primary Server:

· Server where the primary database resides.

· It is the main database server and the primary database will be accessible for the applications.

· Database in this server should be either in full recovery model or bulk-logged recovery model for the transaction log shipping.

· Full backup of the database is taken on this server and copied over to stand by server.

· Transaction log backup are taken on periodic basis and copied over to the secondary server.

Secondary Server:

· This is another server used in the log shipping technology.

· This server will have a back up copy of the primary database used for log shipping.

· Secondary database server can be one or more than one as per the need of the business.

· Secondary database can be in Standby Mode or No Recovery mode.

· Secondary database is not accessible during the transaction log shipping process.

· Always, NO RECOVERY mode should be used so that transaction logs can be restored on the secondary database since in Standby mode users can use SELECT command and logs will not be restored if the users are connected to the database which is in restoring state.

Monitor Server:

· This is another database engine server that tracks the log shipping process.

· It contains set of jobs and sends alert if the transaction log shipping sessions is perceived to be out of sync.

· This server is an optional component of log shipping.

Log Shipping Demo:

In this example, I have used two instances for log shipping.

Primary Server Instance: ABI-PC

Secondary Server Instance: ABI-PC\SQLINSTANCE

Monitor Server Instance: ABI-PC\SQLINSTANCE

1. Go to the Primary Instance Management Studio.

2. Point to the UserDB database, Right Click on UserDB and Click on Tasks and then click on Ship Transaction Logs….

3. In Database Properties Window, Click on Enable this as a Primary database in a log shipping configuration, as shown below.

4. Click on Backup Settings…. Here you can schedule the transaction log back up in the primary server.

5. Provide the backup folder where you can store the Transaction Log Backup of UserDB database on the primary server (in my case this is just one server but have different instance for Primary and Secondary database and have different drive for the backups). You can either keep in a Local Drive or Network Path. It is shown below.

6. Click on Schedule tab to schedule the transaction Log Back up on the Primary Server. I have scheduled this backup to run every 15 minutes as shown in the figure below.

7. Few things to note in Job Schedule Properties Window and they are:

· Name: This is a job name which is LSBackupSchedule_ABI-PC1

· Schedule Type: Recurring

· Frequency: Runs daily that runs every 15 minutes.

8. Click OK.

9. Click OK on Transaction Log Backup Settings window.

Adding Secondary Database Server in Mirroring Session:

10. In the Database Properties Window, Click on Add button as shown below.

11. When you click Add, the Secondary Database Settings window comes where the secondary database instance comes, as shown below

12. Click Connect button on this Connect to Server Window and will be connected to the Secondary Instance.

13. There are three options on the Secondary Database Settings window as shown in the figure below.

In Initialize Secondary Database, restore of the primary database on the secondary database is initializing. In my case, I have generated full database backup on the Primary Server UserDB database and apply that backup in the Secondary Instance. In this option, if the secondary database does not exist, it will create a new UserDB database.

14. In the Second Tab, Copy files options will enable to copy the Transaction Log backup from the primary server to the secondary server. In my case, I have the same sever but kept another drive for this purpose. This will be a destination folder for the copied files, and usually this folder will be in the secondary server. Copy job will run to copy the transaction log backup to copy the files to the secondary server and it scheduled to run in every 15 minutes. Figure is shown below:

15. Restore transaction Log tab will have the transaction log backup details. There are two modes: No Recovery Mode and Standby Mode. Always, NO RECOVERY mode should be selected because in standby mode, SELECT statement can be used to retrieve some data from the UserDB database tables, therefore the restore will fail since the database will be in use. When the restore mode is in NO RECOVERY MODE users will not be able to connect to the database and transaction log restore will not fail. Restore of transaction log is scheduled to run every 15 minutes.

To add Monitor Server Instance:

16. In the database properties window, add the Monitor Server Instance, Check the Use Monitor Server Instance Check Box and then click on Settings as shown in the figure below. I have used my Secondary Server Instance as the Monitor Server Instance (ABI-PC\SQLINSTANCE). Monitor server is an optional server instance.

17. After you click Settings, Log Shipping Monitor Settings Window will come. In this Window, Click on Connect button, Monitor Server Instance comes, in my case, ABI-PC\SQLINSTANCE, is the monitor server instance, as shown in the figure below.

18. Backup, copy and restore jobs are connected on this server instance. Alert job alerts to the users or database administrators if the log shipping fails. Connection used in monitor server is by Impersonating the Proxy Account of the job. Alert job starts automatically when the SQL Server Agent starts.

19. Click OK on the Monitor Server Instance Settings.

20. Finally, Click OK on Database Properties window to initiate Log Shipping session, final window will look like the following:-

Finally, log shipping is set up and it should up and running now.

How to Check Log Shipping Status:

MSDB database tables on secondary store the information. SQL Server 2005 has a capability to provide a report on log shipping status on both primary and secondary servers. Following approach is used to check the log shipping status:

· Connect to the Secondary Server/Instance from Management Studio

· Right click on the server name (Primary/Secondary) in the object explorer

· Navigate to Reports and then to Standard Reports

· Click on “Transaction Log Shipping Status”

Fail Over to Secondary Server:

In the event of any hardware or Operating system failure in the primary server, following steps will be implemented to bring the secondary server database online. This process will be implemented in real disaster recovery scenario. Follow the following steps:

· Restore UserDB on secondary database using the following command in the query analyzer:

RESTORE DATABASE [UserDB] WITH RECOVERY

· Disable the Log shipping jobs on the secondary server by right clicking each job and click disable on Copy, Alert, and Restore job.

Conclusion:

Log shipping technology is one of the methods for implementing High Availability solution. Log shipping is easy to set up, standby database can be available for reporting purpose, easy to maintain and troubleshoot, and multiple standby databases can be configured for log shipping in the same server or instance. One of the main issues or concern with log shipping is adding overhead to the disk subsystem because of frequent transaction log backup. More frequent the transaction log backup taken, the more overhead will be added to the server disk subsystem, and this leads system slow down.

List of the bugs that are fixed in SQL Server 2005 Service Pack 4

or more information about the bugs that are fixed in SQL Server 2005 Service Pack 4, click the following article numbers to view the articles in the Microsoft Knowledge Base:

VSTS bug number	KB article number	Description
294192	2445326	FIX: 0xC02020A1, 0xC020902A, 0xC0202092, and 0xC0047038 errors may occur at the same time when you try to run an SSIS 2005 package if you use multi-character column delimiters in a Flat File source
396531	980671	Correcting the lack of cryptographic salt variation on SQL Server sa login hash
308002	968749	SQL Server 2005 setup fails when MSXML Core Services 6.0 Service Pack 2 has already been installed

Back to the top

Additional issues that are fixed in this service pack

Resolutions to the following issues are also included in SQL Server 2005 Service Pack 4:

VSTS bug number	Description
297747	The multimedia timer frequency does not increase when you use Windows Internal Database.
555409	An access violation error may occur during the execution of Extended Stored Procedures under high stress conditions.

Back to the top