How to apply service packs or hotfixes on an Active/Passive SQL Server cluster?

As per Microsoft, you need to apply the latest service pack or hotfixes to resolve a bug that was discovered by a SQL Server Agent job failure or stumbled on by your development team. How can you prepare and how should you apply service packs or hotfixes on the Active/Passive SQL Server cluster?

Preparation steps for the production server after you had already tested the service pack or hotfixes on a development server:

1. Request a scheduled maintenance window of 1 hour or more. Usually in the late evenings or weekends, depending on your business.
2. Once approved, notify the users or required teams of the scheduled maintenance window.
3. Download the service pack or hotfixes to a shared drive or to a local drive.
4. Backup all databases.
5. Script out all SQL Server Agent jobs.
6. Script out all the logins and permissions for the logins.

If your System Administration team has third party software to take snapshot of the servers, ask them nicely to do so.

Applying the service pack or hotfixes on the Active/Passive SQL Server cluster:

1. On the passive node (Node2), apply the service pack or hotfixes.
2. Reboot the passive node (Node2).
3. On the active node (Node1), failover the SQL resource. The passive node (Node2) that you had already patched will become the active node.
4. On the passive node (Node1), apply the service pack or hotfixes.
5. Reboot the passive node (Node1).

You can verify the current service pack and version build number by running the following query:

1	-- Querying the SQL Server Instance level info

2

SELECT

3	SERVERPROPERTY('ServerName') AS [SQLServer]

4	,SERVERPROPERTY('ProductVersion') AS [VersionBuild]

5	,SERVERPROPERTY ('Edition') AS [Edition]

6	,SERVERPROPERTY('ProductLevel') AS [ProductLevel]

7	,SERVERPROPERTY('IsIntegratedSecurityOnly') AS[IsWindowsAuthOnly]

8	,SERVERPROPERTY('IsClustered') AS [IsClustered]

9	,SERVERPROPERTY('Collation') AS [Collation]

10	,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS[CurrentNodeName]

Questions

1. Explain SQL Server Failover Clustering?
2. How do we check index Fragmentation?
3. TSQL syntax for Index rebuild and reorg. Difference between the two. How users are effected?
4. How do we rebuild large indexes?
5. Detect Locks in SQL Server.
6. Simulate a deadlock situation. Explain how SQL Server handles and comes out of the situation.
7. List out some commonly used DMVs.
8. Function to retrieve file level space usage.
9. List out and Explain some useful DBCC commands.
10. How do we store datafiles in a network drive.
11. Faster way of counting rows in a table.
12. What do we do when tempdb is full.
13. Whats the role of MSDTC.
14. Expalin two phase commit.
15. Steps before and after SQL Server upgrade. And steps to perform to enhance performance or prevent performance problems as a result of upgrade.
16. Isolation levels in SQL Server.
17. Restore a database from suspect mode without using Backup.
18. Frequestly used stored procedures and functions.
19. TLOG for tempdb is full and is not shrinking with shrink command. How do we take care of this issue?
20. What all activities happen during a checkpoint?
21. Events that can trigger a checkpoint.
22. How to change checkpoint interval? and whats the default interval?
23. Activities happening while a SQL Server instance starts.
24. What is 3GB switch?
25. Find memory usage of three instances respectively in a Server from Task manager.
26. How can a database go into suspect mode?
27. Different modes for a database in SQL Server.
28. Find tables that has no indexes.
29. Move indexes to a separate file group.
30. Will log-shipping work for bulk logged recovery model?
31. Different modes of Database mirroring?
32. Role of Cluster Quorum disk.
33. Two communication methods between the nodes of cluster.

Part 2

1. Maximum Number of instances possible in a SQL Server?
   1. 50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster when using a shared cluster disk as the stored option for you cluster installation SQL Server supports 50 instances on a failover cluster if you choose SMB file shares as the storage option for your cluster installation.
2. Frequency at which the merge agent checks for changes at publisher and subscriber in a merge replication.
   1. By default 60. Right click merge agent and look for pol interval inside agent profile from management studio.
3. Main differences in installation steps for 2005 and 2008 failover cluster.
4. Best recommended replication possible for sales person who do not have access to Office internal LAN. Keep in mind that they can connect only through internet without vpn.
   1. Enable web synchronization. Refer http://msdn.microsoft.com/en-us/library/ms151810.aspx and http://msdn.microsoft.com/en-us/library/ms345214.aspx
5. What happens to a table if it doesn’t have a clustered index? Ans: It becomes a heap.

Answers and Clues

1.
a. Group of independent servers that work together to increase the availability of applications and services, protecting against software and hardware failiures by failing over resources from one server to another as required.
b. Failover cluster requires one or more clustered servers (called nodes),  configuration of shared cluster disks, two networks for communication  (atleast one public and one private.)

---

2. use dmv

sys.dm_db_index_physical_stats(
 'database_id',
 'object_id',
 'index_id',
 'partition_number',
 'mode')

. Refer MSDN for more details.

---

3.
Reorganize: This defragments the indexes by moving contents across pages making the data contiguous. Highly fragmented tabled should not be reorganized. Instead we should go with index rebuild. Index reorg is an online process and it can be stopped or cancelled anytime. The work done so far will not be rolled back as compared to index rebuild.
Rebuild: Existing Indexes are dropped and rebuilt from scratch. Enterprise Edition supports online index rebuild. If the index rebuild process is killed in the middle, entire work done on that index will be rolled back.
Refer http://technet.microsoft.com/en-us/library/ms188388.aspx for Syntax and Samples.
To automatically rebuild or reorg indexes, refer my post http://www.sherbaz.com/2011/12/automatically-rebuild-or-reorg-index-based-on-fragmentation/
 Helpfull Links:
http://www.sqldbadiaries.com/2010/09/05/mr-dba-what-is-the-status-of-rebuild-index/
http://www.sql-server-performance.com/2011/index-maintenance-performance/

---

16. Isolation Levels in SQL Server
Command: “SET TRANSACTION <Isolation Level>”
Read Uncommitted
- Lowest
- Higher concurrency
- All concurrency problems: Dirty reads, lost updates,
Nonrepeatable reads(Inconsistent analysis) and phantom reads.
Read Committed
- Eliminates dirty-reads
- Other concurrency problems exists.
- Default Isolation level of SQL Server
Repeatable Read
- Eliminates all concurrency problems except Phantom reads.
- Does not release the shared lock once the record is read and keeps till the transaction
is over.
Serializable
- Highest Isolation level.
- Avoids all concurrency related problems.
- Its just like Repeatable read with one additional feature. Obtains key range locks based on the filters that have been used. It locks not only current records that stratify the filter, but new records that fall into same filter.
Snapshot Isolation Level
- Works on Row Versioning Technology.
- When a transaction is gonna modify something, SQL server will first store the consistence version of the record in tempdb so that when another transaction running on same isolation level requires same record, it can be taken from the version store(in Tempdb).
- Prevents all concurrency problems. And also it allows in multiple updates for same resource by different transactions cuncurrently.
Read commited snapshot
- New implementation of Read commited.
- Has to be applied at database level and not session or transaction level.
- Read committed Vs Read Commited snapshot : Pessimistic Vs Optimistic.
- Snapshot Vs Read commited Snapshot : Unlike snapshot, It always returns latest consistence version and no conflicts are detected.
- All concurrency problems will happen except dirty reads.
Above points were summarized from http://www.sql-server-performance.com/2007/isolation-levels-2005/3/

---

 17. Recover a DB from suspect mode without backup.

Truncate Mirrored Database Log File

Truncate Mirrored Database Log File

If you are running asynchronous database mirroring, then there could be a backlog of transaction log records that have not been sent from the principal to the mirror (called the database mirroring SEND queue). 

The transaction log records cannot be freed until they have been successfully sent. With a high rate of transaction log record generation and limited bandwidth on the network (or other hardware issues), the backlog can grow quite large and cause the transaction log to grow.

On the mirrored database, you cannot backup the log file with TRUNCATE_ONLY. Here the steps to shrink the log file for a database participating in mirroring

1. Backup the log file to a location

BACKUP Log YourDatabaseName 

TO DISK ='D:\BACKUP\DBNAME_20090201.TRN'

2. Check if there is enough free space on perform the shrink operation

SELECT name ,

size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed') ASint)/128.0 AS AvailableSpaceInMB 

FROM sys.database_files;

DBCC SQLPERF(LOGSPACE);

If there is no sufficient free space then the shrink operation cannot reduce file size.

3. Check if all the transactions are written into the disk

DBCC LOGINFO('DatabaseName')

The status of the last transaction should be 0. If not, then backup the transaction log once again.

4. Shrink the log file

DBCC SHRINKFILE(logfilename , target_size)

If the transaction lof file does not shrink after performing the above steps then backup the log file again to make more of the virtual log files inactive.

Also check the column LOG_REUSE_WAIT_DESC in thesys.databases catalog view to check if the reuse of the transaction log space is waiting on anything. 

Check this link to find the factors that can delay log truncation

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

Mirroring – Role of Witness Server and Quorum

Mirroring – Role of Witness Server and Quorum

When a witness server is set, a mirroring session (high safety mode with automatic failover mode) needs quorum to keep the database service. A quorum is the minimal relationship among all connected servers required for synchronous database mirroring session.

Now the next question that comes in mind is about the single point of failure for witness. It is not a single point of failure because if witness fails, principal and mirror will still continue to form a quorum.

Various types of quorum are possible.

Say for example

A = Principal
B = Mirror
C = Witness

Full Quorum – Both partners and witness are included - A∩B∩C

Quorum of partners – Only the two partners are included - A∩B

Quorum with witness and partner – Witness and one of the partners are included C∩(AUB)

Quorum loses sessions

If all the servers are disconnected then the session loses quorum

Now that we know all possible types of quorum, let’s see how each one affects the database and application.

If witness is disconnected when either partner goes down, the database is unavailable since quorum cannot be formed. If the session loses quorum, then the database will not be available until the quorum is re-established.