How to restore a database when you have a SQL Server backup file?

Oftentimes, when I get a database backup file, I usually perform a quick database restore without any hesitation. This usually works fine, but sometimes I get an annoying error message without any clue. Actually, SQL Server provides some commands that can do some basic check against the backup file before restoring. Here are some steps I usually do and it helps a lot, so I would like to share with you all.

1. First of all, I would like to check whether it is a valid backup via running RESTORE VERIFYONLY command.

RESTORE VERIFYONLY
FROM DISK= N’D:\Workspace\Wordpress\2011-11-22\AdventureWorks2008R2_Full.bak';
GO
If the verify succeeds, it will show “The backup set on file 1 is valid.” which indicates the backup file is valid and we can perform the actual database restore later. If any error generated, it may states it is not a actual SQL Server database backup file or the file was corrupted and we should not go any further.

2. After verify check process and it is a valid backup file.  Now, I would like to get the header of the backup file via running RESTORE HEADERONLY T-SQL command. Don’t take a file with .bak extension as database backup and .trn extension as transactional-log backup for granted. Actually, file extension is really no matter, we can use any file extension for SQL Server backups, or even any file on Windows OS. Therefore, I highly recommend check it use RESTORE HEADERONLY:

RESTORE HEADERONLY
FROM DISK= N’D:\Workspace\Wordpress\2011-11-22\AdventureWorks2008R2_Full.bak';
GO

There are lots of useful backup/database properties in the result, and here are some interesting columns

RESTOREHEADERONLY

  • BackupType: indicates what kind of backup in the backup file. 1 = FULL database backup, 2 = T-Log backup, 5 = differential backup.
  • Compressed: indicates whether the backup is compressed or not when performing backup, 1 = Compressed, 0 = Not compressed.
  • Database Version: indicates the SQL Server version where the original database exists and backed up. 661 = SQL Server 2008 R2, 655 = SQL Server 2008, 611/612 = SQL Server 2005, 539 = SQL Server 2000, 515 = SQL Server 7.0.
  • Compatibility Level: indicates the database compatibility when the backup was performed. The compatibility has no indirect relation with database version, a SQL Server 2008 R2 (version 661) can has 100, 90, 80 as compatibility level.
  • SoftwareVersionMajor.SoftwareVersionMinor.SoftwareVersionBuild: indicates the build # of the SQL Server instance where original database exists. For example, 10.50.2500 is the build # of SQL Server 2008 R2 SP1. Please check a very detailed reference at here.
  • IsCopyOnly: indicates whether CopyOnly option used during the database backup. Use the CopyOnly won’t interrupt the existing backup/restore sequences. It is recommended to use this option if you want to get a test copy of the database.
  • RecoveryModel: indicates the recovery model of the original database when the backup was performed, will be one of SIMPLE, BULK-LOGGED, FULL.

3. If the above 1 and 2 are passed, I am confident to know that I can perform the actual database restore. The restore type depends on the backup type displayed in the step 2. At this point, I would like to get the file list contained in the backup using RESTORE FILELISTONLY command and then perform the last restore process.

RESTORE FILELISTONLY
FROM DISK= N’D:\Workspace\Wordpress\2011-11-22\AdventureWorks2008R2_Full.bak';
GO

In this test, I get the following:

RESTOREFILELISTONLY

So, I can see how may files in the backup, and when I restore the database I can use appropriate MOVE clause to move each logical file to existing folder. Although, the MOVE clause is optimal when full database restore, the corresponding to its original file location display in the above screenshot. However, if the corresponding file location is not exists, or the permission is not properly configured, you may get errors. Therefore, I would recommend using MOVE clause when do this. For more information about restore locaton, you may check another blog post Where the database data and log files will be restored?.

That’s it, hope it helps.

How to attach a database without log file (.ldf)?

Earily this morning, I was asked about how to attach a database without the lof file (.ldf). This may happen if someone drop the (.ldf) file incorrectly. For SQL Server 2005 or later, this can be eaisly done via both T-SQL or SQL Server Management Studio (SSMS).

T-SQL

USE master;
GO
-- Create a dummy database for testing
IF (DB_ID('TestDB')) IS NOT NULL
	DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB ON PRIMARY
(
	NAME = 'TestDB',
	FILENAME = 'E:\Workspace\Data\TestDB.mdf'
)
LOG ON
(
	NAME = 'TestDB_Log',
	FILENAME = 'E:\Workspace\Data\TestDB_log.ldf'
);
GO

-- Create a dummy table and populate some test data
USE TestDB;
GO
SELECT * INTO dbo.dbs FROM master.sys.databases
-- Check the table data
SELECT * FROM dbo.dbs

-- Set the database offline and delete the ldf file manually from the OS
USE master;
ALTER DATABASE TestDB SET OFFLINE;
GO

-- In a real world, we may start from here (only mdf file left)
IF (DB_ID('TestDB')) IS NOT NULL
	DROP DATABASE TestDB;
GO
-- This is how it works - use CREATE DATABASE ... FOR ATTACH or ATTACH_REBUILD_LOG
-- For more information, check: http://msdn.microsoft.com/en-us/library/ms176061.aspx
CREATE DATABASE TestDB ON
(FILENAME = N'E:\Workspace\Data\TestDB.mdf')
FOR ATTACH
GO
-- OR
--CREATE DATABASE TestDB ON
--(FILENAME = N'E:\Workspace\Data\TestDB.mdf')
--FOR ATTACH_REBUILD_LOG
--GO

-- After attached, check the table data again
USE TestDB;
SELECT * FROM dbo.dbs;

-- Drop the dummy database
USE master;
DROP DATABASE TestDB;
GO

SSMS

image

Update: As a side note, if the database is not clean shundown, and the ldf file is lost, the Database Engine may not be able to re-create the log file in this case. In that case, there is a undocument option may work (ATTACH_FORCE_REBUILD_LOG). However, you may have data lost after the database created.

Hope it helps.

SQL Server database CHECKSUM page varify option

In SQL Server 2005, there is a new page varify option, CHECKSUM, which can detect the page corruption between time a database page is written to disk and read from subquently by SQL Server. This option (CHECKSUM) is more restricter than TORN_PAGE_DETECTION, and you can have either one of then eabled. Therefore, it is recommended to use CHECKSUM as page varify option. You may has some performance concerns about this option, however, Linch Shea (Blog) bloged that there almost non/mimnum performance impact on the SQL Server based on his benchmarking tests (check here).

You can use the following script to get all the user databases that are not using CHCEKSUM page varify option, update them accordingly.

USE master;
GO

-- Check before update
SELECT	name,
		page_verify_option /* 0 = NONE, 1 = TORN_PAGE_DETECTION, 2 = CHECKSUM */
FROM	sys.databases
WHERE	database_id > 4 AND /* Exclude the system databases */
		name NOT IN ('ReportServer', 'ReportServerTempDB') AND /* Exclude the reporting services databases or any other*/
		page_verify_option <> 2;
		
		
-- Update the databases

SELECT	name,
		page_verify_option /* 0 = NONE, 1 = TORN_PAGE_DETECTION, 2 = CHECKSUM */
INTO	#temp
FROM	sys.databases
WHERE	database_id > 4 AND /* Exclude the system databases */
		name NOT IN ('ReportServer', 'ReportServerTempDB') AND /* Exclude the reporting services databases or any other*/
		page_verify_option <> 2;
		

DECLARE @Command NVARCHAR(MAX);
DECLARE @DatabaseName SYSNAME;

WHILE EXISTS(SELECT * FROM #temp WHERE page_verify_option <> 2)
BEGIN
	SELECT TOP(1) @DatabaseName = name FROM #temp WHERE page_verify_option <> 2;
	SET @Command = 'ALTER DATABASE ' + QUOTENAME(@DatabaseName) + ' SET PAGE_VERIFY CHECKSUM WITH NO_WAIT';
	
	EXECUTE(@Command);
	
	UPDATE #temp SET page_verify_option = 2 WHERE name = @DatabaseName;
END;

DROP TABLE #temp;
GO


-- Check after update
SELECT	name,
		page_verify_option /* 0 = NONE, 1 = TORN_PAGE_DETECTION, 2 = CHECKSUM */
FROM	sys.databases
WHERE	database_id > 4 AND /* Exclude the system databases */
		name NOT IN ('ReportServer', 'ReportServerTempDB') /* Exclude the reporting services databases or any other */ AND
		page_verify_option <> 2;

Tips: Avoid single-use cached plan with ‘optimize for ad hoc workloads’ in the SQL Server 2008 or later

In SQL Server 2008, there is a new server configuration option introduced, called ‘optimize for ad hoc workloads’. This option can improve the efficiency of the plan cache for single use ad-hoc queries (or called dynamic SQL). When this option is on (set to 1, and 0 by default), SQL Server only stores a small compiled plan stub in the plan cache instead of the full compiled plan. When the same query executs next time, the database engine knows that this ad hoc query has compiled before based on the compiled plan stub in the plan cache, and then removes the compiled plan stub from the plan cache and stores the full compiled plan in the plan cache this time so that the plan can be reused in the following executions. For more informaiton, refer to BOL: http://msdn.microsoft.com/en-us/library/cc645587.aspx.

Here is a script to check the ‘optimize for ad hoc workloads’ option, and update it to 1 if required.

-- Check the total server memory currently used by buffer pool only
SELECT cntr_value / 1024 AS [Total Server Memory (MB)]
FROM	sys.dm_os_performance_counters 
WHERE object_name = 'SQLServer:Memory Manager' 
AND counter_name = 'Total Server Memory (KB)'

-- Cached plan usage statistics  (from kimberly)
SELECT objtype AS [CacheType]
        , COUNT_BIG(*)AS [Total Plans]
        , SUM(CAST(size_in_bytes AS DECIMAL(18,2)))/1024/1024 AS [Total MBs]
        , AVG(usecounts) AS [AVG USE COUNT]
        , SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE COUNT 1]
        , SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE COUNT 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE COUNT 1] DESC
GO 

-- Check the size of the total cached plans
SELECT SUM(CAST(size_in_bytes AS DECIMAL(18,2)))/1024/1024 AS [Total MBs]
FROM sys.dm_exec_cached_plans

-- Check the size of total cached plans that were used only once
SELECT SUM(CAST(size_in_bytes AS DECIMAL(18,2)))/1024/1024 AS [Total MBs of Single-user Plans]
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1

-- You can clean the whole plan cache since 'optimize for ad hoc workloads' 
-- only works for following queries
/*
DBCC FREEPROCCACHE;
GO
*/

-- Check the optimize for ad hoc workloads option, should be 1
SELECT	*
FROM sys.configurations
WHERE	name = 'optimize for ad hoc workloads';
GO

-- Update optimize for ad hoc workloads to 1 if it is 0
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE;
GO

/*
	References:

http://msdn.microsoft.com/en-us/library/cc645587.aspx


http://msdn.microsoft.com/en-us/library/ms187404.aspx


http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx


http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx


http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/04/did-you-know-sp2-does-not-limit-the-amount-of-plan-cache-you-can-have.aspx

*/

FAQs: Database backup and restore for a mirrored database

Recently, I was asked several regarding database backup and restore for a mirrored database; therefore I think I need to write a blog post about them which may help others who has the similar doubts.

Q: Can I do database backups for a mirrored database?

A: Yes, it is allowed to do database backups in the Principal database. However, it is not allowed on the Mirror server since which is in the Restoring state.

Q: Can I do transactional log or differential database backups on mirror database after mirroring session fails over?

A: Yes. Someone may think full database can be performed on the mirror database, but not think so for transactional log (t-log) or differential database backups. Actually, we can t-log or differential database backups as well as long as there was already one full database backed up before in either server and both database has been synced up once after that..

Q: Can I restore the database with backup files which are backed up from differential server?

A: Yes, but these backup file must be in a valid Recovery Path. For more information, see: Working with Restore Sequences for SQL Server Databases.

Demo:

Make sure you have already setup a mirroring environment before following the demo steps below. If not, you can set it up following a previous blog post: http://ifcsong.wordpress.com/2011/10/11/setup-database-mirroring-using-windows-authentication-t-sql/.

:SETVAR principal_server "PRINCIPAL"
:SETVAR mirror_server "MIRROR"
:SETVAR restore_test "TEST"

-- Step 1: Perform some changes on the principal server
:CONNECT $(principal_server)
USE DBMirroringTest;
GO
CREATE TABLE dbo.backuptest
(
	Id int not null identity(1,1) primary key,
	Operation nvarchar(100)
);
GO
INSERT INTO dbo.backuptest(Operation) VALUES ('1st row inserted on the principal server');
GO

-- Step 2: Perform the 1st full database backup on the principal server
:CONNECT $(principal_server)
USE master;
GO
BACKUP DATABASE DBMirroringTest
TO DISK = 'E:\Workspace\Backups\1stfullbackup.bak'
WITH INIT, FORMAT, STATS = 10;
GO

-- Step 3: Insert some more records in the table on the principal server
:CONNECT $(principal_server)
USE DBMirroringTest
INSERT INTO dbo.backuptest(Operation) VALUES ('2st row inserted on the principal server after full backup');
GO

-- Step 4: Perform the 1st log backup on the principal server
:CONNECT $(principal_server)
USE master;
GO
BACKUP LOG DBMirroringTest
TO DISK = 'E:\Workspace\Backups\1stlogbackup.trn'
WITH INIT, FORMAT, STATS = 10;
GO

-- Step 5: Failover the database mirroring from principal server
:CONNECT $(principal_server)
USE master;
GO
ALTER DATABASE DBMirroringTest SET PARTNER FAILOVER;
GO

-- Step 6: Insert some more records in the table on the new principal (oringal mirror) server
:CONNECT $(mirror_server)
USE DBMirroringTest;
GO
INSERT INTO dbo.backuptest(Operation) VALUES ('3st row inserted on the new principal (original mirror) server after 1st log backup');
GO

-- Step 7: Perform the 1st differential backup on the new principal server (original mirror)
:CONNECT $(mirror_server)
USE master;
GO
BACKUP DATABASE DBMirroringTest
TO DISK = 'E:\Workspace\Backups\1stdiffbackup.bak'
WITH DIFFERENTIAL, INIT, FORMAT, STATS = 10;
GO


-- Step 8: Perform the 2nd log backup on the new principal server (original mirror)
:CONNECT $(mirror_server)
USE master;
GO
BACKUP LOG DBMirroringTest
TO DISK = 'E:\Workspace\Backups\2stlogbackup.trn'
WITH INIT, FORMAT, STATS = 10;
GO


:CONNECT $(restore_test)
-- Test 1:
-- Restore the 1st full backup and 1st log backup from the principal server and 2nd log backup
-- from the new principal server (original mirror)
USE master;
GO
RESTORE DATABASE DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\1stfullbackup.bak'
WITH 
	MOVE 'DBMirroringTest'
	TO 'E:\Workspace\Databases\MONITOR\DBMirroringTest_data.mdf',
	MOVE 'DBMirroringTest_log'
	TO 'E:\Workspace\Databases\MONITOR\DBMirroringTest_log.ldf',
REPLACE, NORECOVERY;
GO
RESTORE LOG DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\1stlogbackup.trn'
WITH NORECOVERY, STATS = 10;
GO
RESTORE LOG DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\2stlogbackup.trn'
WITH NORECOVERY, STATS = 10;
GO
RESTORE DATABASE DBMirroringTest WITH RECOVERY;
GO

-- Check the result
USE DBMirroringTest;
GO 
SELECT * FROM dbo.backuptest;
GO

-- Drop the database
USE master;
GO
DROP DATABASE DBMirroringTest;
GO

-- Test 2:
-- Restore the 1st full backup and 1st differential backup  and 2nd log backup
-- from the new principal server (original mirror)
USE master;
GO
RESTORE DATABASE DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\1stfullbackup.bak'
WITH 
	MOVE 'DBMirroringTest'
	TO 'E:\Workspace\Databases\MONITOR\DBMirroringTest_data.mdf',
	MOVE 'DBMirroringTest_log'
	TO 'E:\Workspace\Databases\MONITOR\DBMirroringTest_log.ldf',
REPLACE, NORECOVERY;
GO
RESTORE DATABASE DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\1stdiffbackup.trn'
WITH NORECOVERY, STATS = 10;
GO
RESTORE LOG DBMirroringTest
FROM DISK = 'E:\Workspace\Backups\2stlogbackup.trn'
WITH NORECOVERY, STATS = 10;
GO
RESTORE DATABASE DBMirroringTest WITH RECOVERY;
GO

-- Check the result
USE DBMirroringTest;
GO 
SELECT * FROM dbo.backuptest;
GO

-- Drop the database
USE master;
GO
DROP DATABASE DBMirroringTest;
GO

Setup Database Mirroring using Windows Authentication (T-SQL)

Generally, database mirroring involves two SQL Server instances, synchronously or asynchronously replicating transaction logs generated on the first server, known as Principal Server, to the second server, known as Mirror server and replay them on the Mirror server; also we can configure a third server, known as Witness server use which mirroring can automatically failover from Principal to Mirror server. For more information, see Database Mirroring Overview in the SQL Server Books Online.

The following blog post will work you through the steps how to setup the a database mirroring session using Windows Authentication (T-SQL) (without Witness configured in this example).

1. Prepare the Principal and Mirror database via running the following T-SQL script.

Note: You need to enable SQLCMD mode in the SQL Server Management Studio (SSMS).

	-- Run the following query under SQLCMD mode
	:SETVAR principal_server "PRINCIPAL"
	:SETVAR mirror_server "MIRROR"   -- On the principal server
	-- Create the principal database
	-- Change the database recovery mode to FULL (required for database mirroring)
	-- Perform the full and log backup of the principal database
	:CONNECT $(principal_server)
	USE master;
	GO
	CREATE DATABASE DBMirroringTest
	ON PRIMARY (
			NAME = 'DBMirroringTest',
			FILENAME = 'E:\Workspace\Databases\DBMirroringTest_data.mdf',
			SIZE = 100MB,
			MAXSIZE = UNLIMITED,
			FILEGROWTH = 100MB
		)
	LOG ON (
			NAME = 'DBMirroringTest_log',
			FILENAME = 'E:\Workspace\Databases\DBMirroringTest_log.mdf',
			SIZE = 100MB,
			MAXSIZE = UNLIMITED,
			FILEGROWTH = 100MB
		);
	GO
	ALTER DATABASE DBMirroringTest SET RECOVERY FULL;
	GO   BACKUP DATABASE DBMirroringTest
	TO DISK = 'E:\Workspace\Backups\DBMirroringTest_full.bak'
	WITH INIT, FORMAT, STATS = 10;
	GO
	BACKUP LOG DBMirroringTest
	TO DISK = 'E:\Workspace\Backups\DBMirroringTest_log.bak'
	WITH INIT, FORMAT, STATS = 10;
	GO   -- On the mirror server
	-- Restore the full and log backups of the principal database
	:CONNECT $(mirror_server)
	USE master;
	GO
	RESTORE DATABASE DBMirroringTest
	FROM DISK = 'E:\Workspace\Backups\DBMirroringTest_full.bak'
	WITH 
		MOVE 'DBMirroringTest'
		TO 'E:\Workspace\Databases\SQL08R2\DBMirroringTest_data.mdf',
		MOVE 'DBMirroringTest_log'
		TO 'E:\Workspace\Databases\SQL08R2\DBMirroringTest_log.ldf',
	REPLACE, NORECOVERY;
	GO   RESTORE LOG DBMirroringTest
	FROM DISK = 'E:\Workspace\Backups\DBMirroringTest_log.bak'
	WITH NORECOVERY, STATS = 10;
	GO

2. Create mirroring endpoint on both Principal and Mirror. Please note that we only need to configure one mirroring endpoint for one single server instance which will server all database mirroring sessions on the server. If you have already had one mirroring endpoint in place, then you do no need to create that again.

	-- Run the following query under SQLCMD mode
	:SETVAR principal_server "PRINCIPAL"
	:SETVAR mirror_server "MIRROR"   -- On the principal server, create an Endpoint that supports all roles
	-- using port 7022
	:CONNECT $(principal_server)
	 CREATE ENDPOINT EndPoint_Mirroring
		STATE = STARTED
		AS TCP (LISTENER_PORT = 7022)
		FOR DATABASE_MIRRORING ( ROLE = ALL);
	GO   :CONNECT $(mirror_server)
	-- On the mirror server, create an Endpoint that supports all roles
	-- using port 7023
	 CREATE ENDPOINT EndPoint_Mirroring
		STATE = STARTED
		AS TCP (LISTENER_PORT = 7023)
		FOR DATABASE_MIRRORING ( ROLE = ALL);
	GO   -- On the mirror server, set the principal server as partner
	:CONNECT $(mirror_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest
		SET PARTNER = 'TCP://PRINCIPAL:7022';
	GO   -- On the principal server, set the principal server as partner
	:CONNECT $(principal_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest
		SET PARTNER = 'TCP://PRINCIPAL:7023';
	GO

3. Now, after step 2, the database mirroring should be set up, you can check the mirroring database in the Object Explorer of SSMS; or running the following T-SQL script in either server:

	SELECT 
		d.name,
		dm.mirroring_state_desc,
		dm.mirroring_role_desc,
		dm.mirroring_partner_instance,
		dm.mirroring_witness_state_desc
	FROM 
		sys.database_mirroring dm 
		JOIN sys.databases d 
		ON (dm.database_id=d.database_id) 
	WHERE 
		mirroring_guid IS NOT NULL

4. Manually failover test via the following T-SQL script:

	-- Run the following under SQLCMD model   :SETVAR mirror_server "MIRROR"   -- Connect to principal server
	:CONNECT $(mirror_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest SET PARTNER FAILOVER;
	GO   :SETVAR principal_server "PRINCIPAL"   -- Connect to principal server
	:CONNECT $(principal_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest SET PARTNER FAILOVER;
	GO

5. Remove the mirroring session via the following T-SQL script:

	-- Run the following query under SQLCMD mode
	:SETVAR principal_server "PRINCIPAL"
	:SETVAR mirror_server "MIRROR"   -- Connec to either server, in this case using the principal server
	:CONNECT $(principal_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest SET PARTNER OFF;
	GO
	DROP DATABASE DBMirroringTest;
	GO
	DROP ENDPOINT EndPoint_Mirroring;
	GO   -- Connect to the mirror server
	:CONNECT $(mirror_server)
	USE master;
	GO
	ALTER DATABASE DBMirroringTest WITH RECOVERY;
	GO
	DROP DATABASE DBMirroringTest;
	GO
	DROP ENDPOINT EndPoint_Mirroring;
	GO

Update: I forgot to mention that we need to make sure that the each server’s service account must be a login in other’s as a login, and have CONNECT permission on the mirroring endpoint. If the SQL Server is running under the Local System or Network Service built-in Windows account, please add the Computer account (Domain\MYCOMPUTER$) of each server instead. For more information, see my another blog post: http://ifcsong.wordpress.com/2011/05/23/configuring-sql-server-service-account-security-for-network-resources-access/.

I will talk about how to setup the database mirroring session using Certificates (T-SQL) in a future blog post.

Hope this helps :-)

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.

   Replication

    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.

Fix: Reply from xxx.xxx.xxx.xxx: 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:

clip_image001
Figure 1: Ping TestSrv from ADSrv

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

clip_image003
Figure 3: ADSrv (Domain Controller, DNS server)

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

clip_image005
Figure 5: Network Adapter configuration for ADSrv

clip_image006
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.

References:

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
SELECT 
	o.name AS OBJECT_NAME,
	CASE 
		WHEN o.principal_id IS NULL THEN dp2.name
	ELSE  dp.name END AS Object_Owner
FROM 
	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.

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:

image

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.
    image
  3. In Management Studio, open the project again and you can see that those items are sorted alphabetically.
    image

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.

HTH :-)