Why I prefer Log shipping over Mirroring

• I have more than one standby server (a local standby server within the same data centre and one in another country). Mirroring only allows for one standby server.
• I can re-use the standby server for reporting by setting the databases to restore with STANDBY. (You have to delay restoring the logs while the standby is accessed for reports otherwise the restore will fail). One way to do this is to allow reports to run during working hours say 09:00 – 17:00 and schedule the restore jobs to run after out of hours (17:00 – 09:00).
• In my case (15-30 minutes) downtime is acceptable failover time. If you are looking for less downtime, then you need to look at a combination Mirroring, Clustering and Log shipping.
• I don’t have reliable bandwidth to our DR site. This means when the link is down, the log files in the principle server will grow until the link is restored which is an issue as my databases generate huge transaction logs. In my case, all my transaction log backups are compressed as I use a 3rd party tool. I can even move them to another server if they get too many until the link to the DR site is restored.
• I have around 30 databases. If you have more than 10 databases, you should consider what impact Mirroring will have on performance as each database requires two threads. This is not the case with log shipping.
• I can decide when to apply the logs onto the standby server for instance I apply the logs after 20 minutes to catch any accidental/ malicious change to propagate to the standby). In the mirroring scenario, the change is instantly reflected in the mirrored database.
• I like keeping my databases in Bulk-Recovery mode to reduce the amount of data that goes into the transaction log for the minimally-logged operations. If you are looking at mirroring, you databases must be in Full recovery mode.
• I am limited to Synchronous mirroring as I don’t have the Enterprise Edition of Sql server. This is an issue for me as performance matters in my case (mainly due to the latency between the two sites).
• Also, to populate my data warehouse I extract from the standby server (so my ETL has virtually no impact on my production databases!). During the ETL process I don’t restore logs for 1 hour.
• Our application is legacy asp code that cannot automatically re-connect to the mirror in the case of failure (.net code can automatically re-connect in the case of mirroring failover). We still have to do manual changes for the failover to happen.

sp_executesql may cause slow perfomance

I stumbled across an interesting issue today when a simple query was taking too long to execute. The query was fired by a clinet using sp_executesql to one of my sql servers running Sql server 2000 SP4 Enterprise edition. Here is the query: exec sp_executesql N'SELECT Max(Id) FROM ExternalLinks WHERE ExtHandle=@ExtHandle',N'@ExtHandle nvarchar(14)', @ExtHandle = N'AT51988635-052'

and the IO stats were:

--Table 'ExternalLinks'. Scan count 2, logical reads 4804, physical reads 0, read-ahead reads 0. The query was doing 2 scans and 4804 logical reads. When i tried to exeute the query using query analyser without the sp_executesql,

SELECT Max(Id) FROM ExternalLinks WHERE ExtHandle='AT51988635-052' i got : --Table 'ExternalLinks'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0. After investigating, i figured out that the client was using sp_executesql which was passing a parameter as Nvarchar as oppesed to Varchar (the datatype of the table is varchar). This was causing the query engine to convert the data before comparing. This may not be noticable on a small table, but this will make a big impact on large tables. The execution plan for the query that was using sp_executesql is shown below which uses a clustered Scan rather than a seek as it needs to convert all the values before it can compare the values.

The argument shows the convert function.

The second query (without the sp_executesql) uses an index seek which is much more ifficient.

Just to clarify that this is not a shortcoming of sp_executesql, it is rather the client that decided to use Nvarchar for the field rather than varchar. I get the same results if i use the correct data type with sp_executesql.

** If you are using Nhibernate mapping file, you can use type="AnsiString" to force enhibernate to use varchar data type.

Rebuild system databases (master, model, msdb and tempdb) in sql 2008

Recently I have build a two node Sql Server 2008 cluster to be used for one of our production servers and during testing we noticed that the tempdb and the other system databases on existing production server (SQL 2000) has a different collation that that the tempdb on the new cluster. SO, I decided to rebuild the master database.

The method of rebuilding master and other system databases in Sql server 2008 has changed. It does not use rebuildm.exe as it was the case in Sql Sever 2000

To rebuild the master database in SQL 2008, you need to use setup.exe. There is no need to use the installation DVD or the installation files. All the required files should be available on the server itself.

On a normal installation, the setup.exe can be found at
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release

Note; before rebuilding master, it is recommended to take a backup of the existing system databases in case your rebuild is not what you wanted so that you can go back to the previous configuration. Also, before you start rebuilding the master, you need to;

- Detach all the user databases so that they can be re-attached after the rebuild
- Script all the logins
- Script all the jobs

There is a good blog with sample scripts on how to do the above in http://www.mssqltips.com/tip.asp?tip=1531
and
http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx


If you are rebuilding the master in a clustered environment,

Make sure that the node that you are rebuilding the master from is the owner of the instance.

Open a command prompt and go to the directory where the setup.exe is located; which typically is in

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release

Then type the following substituting the parameters to your environment.

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=MyDomain\adminUser /SAPWD=9Cazpop1Z123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Make sure there are no spaces between the “/” and the parameter otherwise you will get the following error.

“The syntax of the argument “//” is incorrect”

I was running two instances of sql server on the cluster (active-active) so I had to rebuild the master twice. Once for each instance as I wanted to change the collation of the master database on both instances.

You will have to take the sql server resource offline in a cluster other wise you will get the following error.

“If this clustered instance is owned by the current node, the sql server resource must be offline or failed.”

If the Sql server instance that you are trying to rebuild the master for is not owned by the node that you are running the serup.exe command from, you will get the following error:
“The Sql Server resource group must be owned by the current cluster node. Setup requires access to shared cluster resources.”

To fix this, you need to move the cluster resource to the node that you are running the setup.exe from



To take the Sql Serve resource offline, open Failover cluster manager, click on the Sql Server instance (or group as it used to be called) then under Server name on the right hand pane, tight-click and select take resource offline as show in the snapshot below.

After the setup.exe completes, bring the Sql resource online again and start attaching your databases, re-create logins, jobs..etc.

After completing the rebuild on the first node, I had to repeat the same process on the other node substituting the instance name in the command below.

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=SNAMESSQL01 /SQLSYSADMINACCOUNTS=MyDomain\adminUser /SAPWD=9Cazpop1Z1234 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS




Rebuilding the master, recreated the tempdb in the default location. If you are keeping tempdb in a different location perhaps on another set of disks then you need to move it after you start the sql server resource,


In order to move tempdb, use sp_helpfile to find the name of the tempdb files

Then

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\tempdblog.ldf')



In order to verify that the collation has changed for the master databases, use

sp_helpdb
or
SELECT DATABASEPROPERTYEX('tempdb', 'Collation')

Copying backup files to remote server

A very useful tool for any dba to copy database backup files is ROBOCOPY which can be freely downloaded. the tool allow you to copy a list of files from a source server to the destination and it can mirror the files in both location so that if you delete a file in the source, it will automatically be deleted in the destination server. Also, in case of network issues during the copy, it is capable of restarting from where it stopped rather than copying from scratch.

This is very useful as you dont have to worry about deleting old backup files from the second server as long as your backup job on the primary takes care of deleting old backup files, they will automatically be deleted by ROBOCOPY from the secondary server.

as an example, the following command will copy the contents of the c:\backups to another server called \\backupserver and the /MIR switch will make sure that the contents of both folders remain in sync.

The /z switch will restart the copy process in case of a network issue.

you can easily create a windows scheduled task to run overnight and execute the following line to copy your backup files to another server offline.

robocopy C:\foo \\backupserver\bar /MIR /Z

For more details http://en.wikipedia.org/wiki/Robocopy