The sequence to install service packs or hotfixes on an instance of SQL Server that is part of Log Shipping/Database Mirroring/Replication/Failover Clustering environment

    When an instance of SQL Server is configured as part of Log Shipping, Database Mirroring, Replication, or Failover Clustering environment, it is important to install service packs or hotfixes in a correct sequence otherwise we may get unexpected issues. This blog post will give you a guild line of sequences in such as environment.

    Log Shipping

    There is no required sequence to apply a service pack or hotfix for Primary, Secondary and Monitor servers in a Log Shipping environment. The following is my preferable to apply service pack or hotfix:

    1. Apply the service pack or hotfix on the Monitor server.
    2. Apply the service pack or hotfix on the all Secondary servers.
    3. Apply the service pack or hotfix on the Primary server.

   Database Mirroring

    If you install service packs or hotfixes on servers in a database mirroring environment, you need to determine the role of the servers. If there are many mirroring sessions configured on the server, you need to determine all possible roles that could be. For instance, if the server is acting as a mirror server for any database mirroring session, update the server as the mirror role for all mirroring sessions. To do this, follow these steps:

    1. If a witness server is configured in the database mirroring session, disable the automatic failover during the update process. To do this, remove the witness server from the mirroring session. For more information, see How to: Remove the Witness from a Database Mirroring Session.
    2. If the safety level of the database mirroring session is OFF (asynchronous mode), change the safety level to FULL (this is required in step 3).
    3. Make sure all the database mirroring sessions to be in Synchronous mode and synchronized.
    4. Pause the database mirroring sessions that are present on the server. This step prevents changes to the principal database. For more information, see: How to: Pause a Database Mirroring Session.
    5. Install the service pack or hotfix on the mirror server.
    6. Resume the database mirroring sessions. For more information, see: How to: Resume a Database Mirroring Session.
    7. Perform manual failover (all the mirroring sessions on this principal server) to the mirror server so that mirroring server assumes the principal role.
    8. Pause the database mirroring sessions as step 4.
    9. Install the service pack or hotfix on the new mirror server (previous principal server).
    10. Resume the database mirroring sessions.
    11. If you changed the safety level in step 2, change the safety level back to OFF.
    12. If the database mirroring session has a witness server, undo the changes made in step 1. For more information, see: How to: Add or Replace a Database Mirroring Witness.


    In a replication environment, there is no preferable sequence to apply service pack or hotfix for non-bidirectional replication typology. However, for bi-directional replication typology such as merge typology or transactional replication with updateable subscriptions, you must upgrade Distributor, Publisher, and Subscribers in the following order:

    1. Apply the service pack or hotfix on the Distributor server.
    2. Apply the service pack or hotfix on the Publisher server.
    3. Apply the service pack or hotfix on the Subscriber server.

   Failover Clustering

    In SQL Server 2005, if you want to install a service pack or hotfix, you must install the setup on the Active node (node that currently runs SQL Server services). When running the setup which will launch simultaneously “remote silence” on all passive nodes.

    However, in SQL Server 2008 and SQL Server 2008 R2, the service pack or hotfix deployment is changed to reduce the downtime. Now, you must install the service pack or hotfix on the passive node first. To do this, following these steps:

    1. Apply the service pack or hotfix on the passive node (or all passive node if you have more that one).
    2. Reboot the passive node.
    3. Failover the SQL Server failover cluster to the passive node (and the passive node becomes active now).
    4. Apply the service pack or hotfix on the new passive node (previous active node).
    5. Reboot the passive node.
Posted in Setup & Upgrade | Tagged , | 2 Comments

Fix: Reply from Destination host unreachable

In my test Hyper-V environment, I created several virtual machines (VMs). One of them is a domain controller, ADSrv which is also a DSN server. I have two other VMs, which are successfully added to the domain. However, I encountered the problem to add a third server, TestSrv to join the domain. All these servers are added the same Virtual Network created using Virtual Network Manager in Hyper-V. I cannot ping ADSrv or TestSrv from each other, and get the following error message from Ping IPAddress command:

Figure 1: Ping TestSrv from ADSrv

Figure 2: Ping ADSrv from TestSrv

The first step I would like to check the network configuration for each server, which looks correct as fellows:

Figure 3: ADSrv (Domain Controller, DNS server)

Figure 4: TestSrv

The second step I would think this issue should be due to that the ICMP traffic was blocked by Windows Firewall therefor I created an exception for ICMPv4 protocol on both ADSrv and TestSrv for ICMP protocol. However, I still cannot ping each them from each other server, which frustrated me a lot. (P.S. This is usually the cause when the server cannot be Pinged.)

Finally, after some diggings, I noticed the following different virtual LAN Identification configurations as fellow:

Figure 5: Network Adapter configuration for ADSrv

Figure 6: Network Adapter configuration for TestSrv

I think you have already noticed the differences, the both servers ADSrv (Domain Controller as VM name)and TestSrv (SAN as VM name) are configured with the same virtual network, called Domain Network in this case, and have “Enable virtual LAN Identification” checked, but with different VLAN ID – which is the root cause in this case. Since the two servers with different VLAN ID, therefore they cannot access each other as all traffic for the managed operating systems that goes through the network adapter will be tagged with the VLAN ID you set.

To resolve this issue, we have two solutions:

  1. Update the VLAN ID to the same value (I updated the VLAN ID to 5 for TestSrv server).
  2. Uncheck the “Enable virtual LAN identification”.

P.S. If you have any other servers also use this network (such as Domain Network in this case), make sure all of these servers using the network either have “Enable virtual LAN identification” checked with the same VLAN ID or with it disabled.


Posted in Hyper-V | Tagged , , | 1 Comment

How to detect the owner of the database objects

Prior to SQL Server 2005, if you create objects in a schema, the owner of the object is the user who created it. However, this behavior was change in SQL Server 2005 and later versions, by default, if you create objects in a schema, the schema owner will be the owner of the objects, not you (who created the objects). In both cases, we can change the owner of the objects using ALTER AUTHORIZATION statement.

So, here comes another question, how can we know the owner of the objects in SQL Server 2005 and later versions. Here are two rules:

  1. If the object owners were not changed after creation, then schema owner will be the owner of the objects.
  2. If the object owners were changed after creation, then we need to find out that database user who is the new owner of the objects.

Fortunately, in the sys.objects system catalog view, the principal_id column will indicate this. If the principal_is is NULL, the object is owned by the schema owner. If not NULL, this principal is the owner of the object. Here is a script that we can detect the owner of a specific object:

-- If the principal_id is NULL, which means the object owner is not changed 
-- after creation so that the owner will be the schema owner. Otherwise, 
-- the object owner will be the user whose principal_id is 
-- equal principal_id in sys.objects table
		WHEN o.principal_id IS NULL THEN
	ELSE END AS Object_Owner
	sys.objects AS o
	LEFT JOIN sys.schemas AS s 
	ON o.schema_id = s.schema_id
	LEFT JOIN sys.database_principals AS dp
	ON o.principal_id = dp.principal_id
	INNER JOIN sys.database_principals AS dp2
	ON s.principal_id = dp2.principal_id

WHERE o.object_id = OBJECT_ID(N'input_object_name_here')

Hope this helps.
Posted in Uncategorized | Leave a comment

Workaround: Items are not sorted alphabetically in the SQL Server Management Studio Solution Explorer

In SQL Server Management Studio, when you added more items (such as queries) in your project, they are always in the order of latest to oldest from up to down, but not sorted alphabetically. It can be problematic if you have do dozens, or more items, it is hard to find the one your need. Here is a screenshot:


The workaround to let them sorted by alphabet is to change the “Sorted” property from “true” to “false”. Here are steps in detail:

  1. Save and close your solution from Management Studio.
  2. Find the project in the file system, locate the project configuration file (with ssmssqlproj extension), update “Sorted” property from “true” to “false” and save it.
  3. In Management Studio, open the project again and you can see that those items are sorted alphabetically.

However, if you have new items added in your project, the “Sort” property will be changed to “true” again and your new items are not sorted alphabetically either. You need to perform the above steps again to workaround this issue as I said it is really a workaround not a solution.


Posted in Tools, Uncategorized | Tagged , | 1 Comment

How to detect database version of SQL Server backups

Oftentimes, we have many different SQL Server (2000, 2005, 2008, etc.) database backups files, and it is hard to remember database version in each backup file. This could cause problems, such as restoring a SQL Server 2008 database backup on a SQL Server 2005 instance would be failed. So, it is necessary to know the database version in the backup file before restoring a database. The following shows one way get it by using RESTORE HEADREONLY.

Firstly, run the following T-SQL command against the database backup file:

FROM DISK = 'D:\Workspace\SQLServer\Backups\AdventureWorks.bak';

If successfully executed, you would get a result record in below, I cut it into several screenshots due to its length.







Here are some useful columns :

  • BackupType: Indicates the backup type of the backup file (1 = full, 2 = transaction log, 5 = differential)
  • Compressed: 1 for compressed, 0 for non-compressed
  • DatabaseName: The name of the database when taking backup
  • DatabaseVersion: The version of the database from which the backup was taken
  • SoftwareVersionMajor/SoftwareVersionMinor/SoftwareVersionBuild: Indicates the version info of SQL Server from which the backup was taken
  • RecoveryModel: The database recovery model when the backup was taken

For a complete description of each column, please refer to RESTORE HEADREONLY in Books Online.

Secondary, using the result of RESTORE HEADERONLY command, we can get the database version of the backup via the following two method.

  1. Inferring the database version through DatabaseVersion column. You can refer to the following for mappings between SQL Server and number of DatabaseVersion:
    • 515 <=> SQL Server 7.0
    • 539 <=> SQL Server 2000
    • 611/612 <=> SQL Server 2005
    • 655 <=> SQL Server 2008
    • 661 <=> SQL Server 2008 R2
  2. Inferring the database version through SoftwareVersionMajor, SoftwareVersionMinor, and SoftwareVersionBuild columns. Replace SoftwareVersionMajor.SoftwareVersionMinor.SoftwareVersionBuild with actually values, and refer to the following article to get exactly SQL Server version info from which the backup was taken.

So, in my example above, I can learn that the this backup was taken from SQL Server 2008 R2 (661) through method, and from SQL Server 2008 R2 CU7 (10.50.1777) through method 2.

Hope that helps.

Posted in Uncategorized | Tagged | Leave a comment

Taking tail-log backup what if the database is inaccessible

A tail-log backup is that a log backup that backs up the tail of the transaction log that have not yet backed up. In a event of database disaster, data loss cannot be prevented if tail-log backup cannot be taken. However, if we can back up the tail of the log, we may suffer minimal data or even no data loss. For more information about tail-log backup, see “Tail-Log Backups” ( ) in Books Online.

To be able to take tail-log backup, the following must be true:

  1. The database is in FULL or BULK-LOGGED recovery model, and at least one full backup was taken.
  2. The log file is not damaged
  3. If data files are damaged, make sure
  4. If in BULK-LOGGED recovery model, there is no minimally logged operations after the last log backup.

To take a tail log backup using the T-SQL statement, like below:

-- If the database is online and begin to restore the database after backup
BACKUP LOG [database_name] TO [backup_device] WITH NORECOVERY
-- If the database is offline, and log file may be damaged
BACKUP LOG [database_name] TO [backup_device] WITH CONTINUE_AFTER_ERROR

Real-world scenario:

In a event of database disaster, our database is inaccessible now, data files (not log file) are damaged so that if we could take tail-log backup we should prevent no data loss in this case. I will walk you through on how to do it.

  1. Create a sample database to paly with, set the recovery model to full and take the 1st full backup:
    	USE master;
    	CREATE DATABASE TailLogBackupDemo;
    	ALTER DATABASE TailLogBackupDemo SET recovery FULL;
    	GO&nbsp;&nbsp; USE TailLogBackupDemo;
    	CREATE TABLE dbo.TestTable
    		FirstName NVARCHAR(20) NOT NULL,
    		LastName NVARCHAR(20) NOT NULL
    	BACKUP DATABASE TailLogBackupDemo
    	TO DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_Full.bak';
  2. Insert some new records in our sample table and take a transaction log backup.
    	-- Insert 5 records in our table and then peform our 1st tranasction log backup
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong01', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong02', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong03', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong04', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong05', 'Feng');
    	BACKUP LOG TailLogBackupDemo
    	TO DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_Log_1st.trn';
  3. Insert another new records in our sample table.
    	-- Insert anotehr 5 records in our table and then peform our 1st tranasction log backup
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong06', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong07', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong08', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong09', 'Feng');
    	INSERT INTO dbo.TestTable(FirstName, LastName) VALUES('Chunsong10', 'Feng');
  4. Now, database disaster happens.
      1. Detach the database and delete all data files
      2. Copy log file to somewhere else
  5. Steps to take tail-log backup in this scenario.
    	USE master;
    	-- Create a new dummy database used to backup our tail log
    	CREATE DATABASE NewTailLogBackupDemo;
    	-- This is required since we need to take log backup
    	ALTER DATABASE NewTailLogBackupDemo SET recovery FULL;
    	-- Set the database offline, and then delete all data and log files
    	-- And then copy original log file (in step4) rename the same name as
    	-- NewTailLogBackupDemo log file
    	ALTER DATABASE NewTailLogBackupDemo SET offline;
    	-- Bring the database online (Although error happens here, it does not matter)
    	-- Take the tail-log backup
    	BACKUP LOG NewTailLogBackupDemo
    	TO DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_tail_log.trn' 
    	USE master;
    	-- Drop dummy database, make sure the physical files are dropped
    	DROP DATABASE NewTailLogBackupDemo;
    	-- With tail-log backup, we can start to restore the database
    	RESTORE DATABASE TailLogBackupDemo
    	FROM DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_Full.bak'
    	RESTORE DATABASE TailLogBackupDemo
    	FROM DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_Log_1st.trn'
    	RESTORE DATABASE TailLogBackupDemo
    	FROM DISK = N'D:\Workspace\SQLServer\Backups\TailLogBackupDemo_tail_log.trn'
    	-- Check if all data are recovered
    	USE TailLogBackupDemo;
    	SELECT * FROM dbo.TestTable;

Now, we should see all data are recovered. Please always try our best to take tail-log backup in any disaster to avoid data loss as much as possible.

Posted in Uncategorized | Tagged , | Leave a comment