How to remove/uninstall SQL Server 2008

I installed SQL Server 2008 R2 in my Windows 7 to configure SharePoint 2010 as a Farm.  After trying many options, I am still having problem to install SharePoint 2010 in Windows 7 as a farm using SQL Server 2008 instead of SQL Server 2008 Express Edition. 
It’s time to remove SQL Server 2008 from Windows 7 so that I can use SQL Server 2008 Express to install SharePoint as stand alone installation.
To Remove SQL Server 2008 from Windows 7, do the following steps
1. Go to Control Panel | Program | Uninstall Programs |Microsoft SQL Server 2008 R2 (64-bit) | and click on Uninstall


2. You will get option to Add/Repair and Remove SQL Server 2008 . Click Remove

3.  Once Setup Support Rules completed, click OK  


4. Select Instance to remove
Select the instance of SQL Server to remove. To remove Management Tools and Shared Features only, Select
Remove Shared Features only and then click next.


5. Select the features to remove
The SQL Server Features on this computer are shown below. To remove a feature, select the checkbox next to the feature name

6.  Click next to Removal Rules and your system is ready to Remove SQL Server 2008

7. Removal of SQL Server 2008 R2 from Windows 7 computer


8.  Complete: Your SQL Server 2008 R2 Removal Completed Successfully.

How to Troubleshooting SQL Server Cluster Installations

On a Windows Cluster it is possible to install a SQL Server Cluster, which will be a separate entity with disctinct Virtual Server Name, IP address, and pipe name. The nodes of the SQL Cluster must be able to communicate with one another. This communication may fail if the TCP port on which SQL Server is listening is not excepted from firewalls on the machines. This port can be found by using the Configuration Manager utility by viewing the properties of the TCP protocol or by checking the SQL Server errorlog, typically located at “Program FilesMicrosoft SQL ServerMSSQL.xMSSQLLOG”. Here MSSQL.x is the instance ID of the clustered instance in question.

If the Cluster instance being installed is a named instance, a service called SQL Browser is used in order to figure out the port or pipe SQL Server is listening on. For SQL Server Cluster installations, this service is running by default. If this service is not running, it will not be possible to communicate with the remote nodes. To manipulate this service, open services.msc, find SQL Browser, and make sure its state is Automatic and Running. SQL Browser listens on a UDP port, which must also be excepted from firewalls on the machines. Even if SQL Browser is running, in case any of the nodes has firewall ON, it is necessary to add exceptions for UDP port 1434.
These issues with SQL Browser (service not running or TCP and UDP port not excepted from firewall) can also block remote connectivity to non-clustered instances.

Understanding Kerberos and NTLM authentication in SQL Server Connections

In this post, I focus on how NTLM and Kerberos are applied when connecting to SQL Server 2005 and try to explain the design behavor behind several common issues that customers frequently hit.
On this page:
Kerberos VS NTLM.
Requirements for Kerberos and NTLM in SQL Connections.
When are Kerberos and NTLM are applied when connecting to SQL Server 2005.
Common issues and workaround.
Troubleshooting Tips checklist.

I. Kerberos VS NTLM
NTLM Authentication: Challenge- Response mechanism.
In the NTLM protocol, the client sends the user name to the server; the server generates and sends a challenge to the client; the client encrypts that challenge using the user’s password; and the client sends a response to the server.If it is a local user account, server validate user’s response by looking into the Security Account Manager; if domain user account, server forward the response to domain controller for validating and retrive group policy of the user account, then construct an access token and establish a session for the use.
Kerberos authentication: Trust-Third-Party Scheme.
Kerberos authentication provides a mechanism for mutual authentication between a client and a server on an open network.The three heads of Kerberos comprise the Key Distribution Center (KDC), the client user and the server with the desired service to access. The KDC is installed as part of the domain controller and performs two service functions: the Authentication Service (AS) and the Ticket-Granting Service (TGS). When the client user log on to the network, it request a Ticket Grant Ticket(TGT) from the AS in the user’s domain; then when client want to access the network resources, it presents the TGT, an authenticator and Server Principal Name(SPN) of the target server, contact the TGS in the service account domain to retrive a session ticket for future communication w/ the network service, once the target server validate the authenticator, it create an access token for the client user.
II. Requirements for Kerberos and NTLM authentication
Kerberos, several aspects needed:
1) Client and Server must join a domain, and the trusted third party exists; if client and server are in different domain, these two domains must be configured as two-way trust.
2) Registered SPN. Service Principal Name(SPNs) are unique identifiers for services running on servers. Each service  that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. It is registered in Active Directory under either a computer account or a user account.
   Service Principal Name
   An SPN for SQL Server is composed of the following elements:    
   • ServiceClass: This identifies the general class of service. This is always MSSQLSvc for SQL Server.
   • Host: This is the fully qualified domain name DNS of the computer that is running SQL Server.
   • Port: This is the port number that the service is listening on. 
     eg:  MSSQLSvc/myserver.corp.mycomany.com:1433
NTLM
NTLM requires user’s password to formulate a challenge-response and the client are able to prove its identities without sending the password to server. Thus you can tell if your client running under System Context w/o credential, what might happen?
NTLM fallback
NT LAN Manager is the authentication protocol used in Windows NT and in Windows 2000 work group environments. Windows Server 2003, Windows XP, and Windows 2000 use an algorithm called Negotiate (SPNEGO) to negotiate which authentication protocol is used. Although the Kerberos protocol is the default, if the default fails, Negotiate will try NTLM.
III. When are Kerbers and NTLM applied when connect to SQL Server 2005.
Under condition that you are using Integrated Security or trusted connection which use windows authentication.
1) Kerberos is used when making remote connection over TCP/IP if SPN presents.
2) Kerberos is used when making local tcp connection on XP if SPN presents.
3) NTLM is used when making local connection on WIN 2K3.
4) NTLM is used over NP connection.
5) NTLM is used over TCP connection if not found SPN.
To undersand these scenarios, first you need to know hwo to verify your SQL Server SPN exists:
download the SetSpn.exe from http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&DisplayLang=en
At a command prompt, type:
setspn -L <Account>
Account could be either <machinename> or <domainusername>
a. If your SQL Server running under LocalSystem or NetworkService account, you should be able to
see SPN by:
setspn -L <hostserver that your sql installed>
b. If your SQL Server running under a domain user account, you should be able to see SPN by:
setspn -L <mydomain><username>
c.If the domain user is non-admin, you can ask your domain administrator to register the SPN under
your account if you must use Kerberos authentication.
setspn -A <mydomain><username>
d. If your sql server is running under a local machine admin account, you can either ask your
domain administrator or run setspn under your domain credential to add the SPN.
Summary, SQL Server would automatically register SPN during start up if:
a. Your sql server running under LocalSystem/Network Service/Domain admin user account.
b. TCP/IP protocol is enabled.
Otherwise, you need to manually register SPN if forcing Kerberos authentication.
Normally, if you are making TCP connection, SQL driver on the client tries to resolve the fully qulified DNS name of the server that is running SQL, and then format the SQL specific SPN, present it to SPNEGO, later SPNEGO would choose NTLM/Kerberos depends on whether it can validate the SPN in KDC, the behavior is different from OS to OS, in most case, if SPN was not found, Kerberos authentication failed, it fallback to NTLM, but there is exception like in above case 2), if Kerberos authentication failed, it would not fallback. If you are making NP connection, SQL driver generate blank SPN and force NTLM authentication.
IV. Common issues and Workaround.
[1]  “Login Failed for user ‘NT AuthorityANONYMOUS’ LOGON”
In this scenario, client make tcp connection, and it is most likely running under LocalSystem account, and there is no SPN registered for SQL instance, hence, NTLM is used, however, LocalSystem account inherits from System Context instead of a true user-based context, thus, failed as ‘ANONYMOUS LOGON’. See http://support.microsoft.com/kb/132679.
The workaround here is
a. ask your domain administrator to manually register SPN if your SQL Server running under a domain user account.
b. use NP connection.
c. change your sql server to run under either localsystem account or networkservice account.
Here, a is recommended.
[2] “Login Failed for user ‘ ‘, the user is not associated with a trusted SQL Server connection”.
In this scenario, client may make tcp connetion, plus, running under local admin or non-admin machine account, no matter SPN is registered or not, the client credential is obviously not recognized by SQL Server.
The workaround here is:
Create the same account as the one on the client machine with same password on the target SQL Server machine, and grant appropriate permission to the account.
Let’s explain in more detail:
When you create the same NT account (let’s call it usr1) on both
workstations, you essentially connect and impersonate the local account of
the connecting station. I.e when you connect from station1 to station2,
you’re being authenticated via the station2’s account. So, if you set the
startup account for SQL Server (let’s assume it’s running on station2) to be
station2’s usr1, when you connect to SQL from station1 with station1’s usr1
login, SQL will authenticate you as station2’s usr1.
Now, within SQL, you can definitely access station1’s resources. Though, how
much access will depend on station1’s usr1 permission.
So far, SQL only deal with an user who is part of the sysadmin role within
SQL Server. To allow other users (non-sysamdin) access to network resources,
you will have to set the proxy account. Take a look at the article for
additional info.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
[3] “Could not open a connection to SQL Server[1326]”
The same root cause as [2], just is making np connection.
[4] “Login failed for user ‘<domain><machinename>$’ “
In this scenario, you client probably running under LocalSystem account or NetworkService account, so, just need to grant login to the account “domainmachinename$” in SQL Server.
[5] “Login failed for user ‘NT AuthorityNetworkService'”
This is a typical authorization failed case, and it probably when client running ASP.NET application and use ASPNET account or network service account.
workaround, see  http://support.microsoft.com/kb/316989/
[6] Can not generate SSPI Context.
This is typical Kerberos authentication failure, there are various situations that can trigger this error. see blog: http://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx
http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx
The major reason is due to the Credential Cache( is used by Kerberos to store authentication information, namely the TGT and session ticked is cached so that can be used during their lifetime.)
The most general workaround is: clean up credential cache by using “klist.exe -purge” or kerbtray.exe or just reboot machine.
See more detail about various cause and solution in http://support.microsoft.com/kb/811889.
 Differenciate Authentication failed and Authorization failed.
When you saw error ” Login failed for user ‘ ‘ ….” or ” Login failed for user ‘(null)’ ” or ” ANONMOUS LOGON”, these are authentication failure.
When you saw error like ” Login failed for user ‘<username>’ “, these are authorization failure, which is related to your SQL server security settings.
The final part gives troubleshootin tips checklist for authentication fail which is the focus of this blog.
V. Troubleshooting Tips checklist
[1] Verify computer settings
http://technet2.microsoft.com/WindowsServer/en/library/e1c3f70d-f8b3-4642-93c6-61421fd1292e1033.mspx?mfr=true
[2] Verify DNS name resolution
The key factor that makes Kerberos authentication successful is the valid DNS functionality on the network.
ping <remoteserver> , ipaddress should return
ping -a <ipaddress> , FQDN should return
nslookup, type the ipaddress, should get FQDN, or type FQDN should return ipaddress.
[3] Verify NTLM works.
try command:
“net view \server”, or “net view \ipaddress”.
[4] Verify SPN set
See which account SQL Server is running under, if SQL Server fails to register SPN, there is errorinfo in ERRORLOG, but you should doublecheck whether expected SPN was manually registered by other people.
[5] Clean up your client credential cache and retry see whether the problem persists.
[6] Then go to part III, to see your scenario falls into which case listed, and analyze whether the problem is included in the Common issues part IV, and applied the solution. Again, be careful to differenciate authentication error and authorization error. If you face authorization error, recommend post your question to the security forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1
[7] Make sure your SQL Server Protocol setting is correct for NTLM and Kerberos before go to step [8].
a. You are using windows authentication.
b. You already grant proper permission to the windows account,
c. Your server has SPN registered or not as you expected, also the port in SPN is the one that sql server is listening.
d. If making remote connection, you enabled “File and Printer Sharing” in the firewall on your remote server.
e. TCP/IP or NP is enabled.
f. Your client connection string specify the correct target server name and sql instance name.
[8] If you find it is pure Kerberos or NTLM issue, you need to check system log and  security log or even do netmon to gather Kerberos or NTLM error code for further debugging.
See “Diagnose Tool” secion in this: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx#E2HAC
Summary:
If you face problem that did not list out in this post, please provide following info w/ your problem:
1) Which account your client is running under?
2) Which account your SQL Server is running under?
3) Is SPN registered for your SQL Server?
4) Does your client and server join the domain? Are they in the same domain?
5) Which OS your client and server is on?
6) What is the error message?
7) What error info in your SQL Server ERRORLOG?
8) What is your connection string?
9) Local connection or remote?
10) Linked server involved?
11) Any Kerberos delegation involved? double-hop or single-hop?
If your scenario invovle linked server and kerberos delegation, please check blog:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

Authentication

It means that the username and password you specified in the connection is valid. If you are using Windows authentication and SQL Server server running with any of two mode “Mixed Mode” or “Windows-only” authentication, SQL Server will request Local Security Authority Subsystem Service (LSASS) to verify the credentials of the specified user. Again if the windows account is a local machine account, it is verified against local system security database and if it is a domain account, LSASS then requests Active Directory to authenticate the user.
Once this user is authenticated, a token is then passed to SQL Server that authentication is successfull. There are again two things in this authentication, they are NTLM or KERBEROS. KERBEROS needs SQL Server Service Principal Name to be registered in Active Directory agains the SQL Server node name or the SQL Server Virtual Server Name. If SPN’s are not registered, then connection to failback to NTLM depending on your environment settings. For more info about NTLM & Kerberos, refer this article 

If the login is a SQL Server login, then SQL Server takes care of authenticating the user because SQL Server stores the username and password. If target SQL Server instance is running in Windows-Only authentication mode then though you are specifying correct password for the SQL login, the connection will fail because SQL Server will not allow Non Windows login. You can change this to make SQL Server to allow Mixed Mode Authentication (Both SQL logins and Windows logins) from SSMS -> Instance -> Properties -> Security -> Server Authentication. Please note that you need to restart SQL Server if you make this change.

Authorization:


Once authentication succeeds, authorization phase starts whereby SQL Server checks the permission for accessing the target database based on the TokenPerm cache built and if yes, the login succeeds and if not the connection will fail with State 16.
Once authorization completes, you can submit your queries to execute in the SQL Server instance.  

Troubleshoot Connectivity/Login failures (18456 State x) with SQL Server

We know that there are lot of articles over the internet world with this topic… But this article is from my perspective which has detailed troubleshooting steps…
The main problem with troubleshooting connectivity issues with SQL Server is because the error message returned to client is almost generic and incase of login failures, the state is always 1
So let me give you step-by-step approach in troubleshooting connectivity issues with SQL Server.
Here is a flow of how connection to SQL Server is made:
1. Protocol used for making connections to SQL Server
Connecting is made using a protocol based on the “Client Protocols” order specified in your local box.

In the case of screenshot given above, the connection made to any SQL Server from this client machine will use “Shared Memory” protocol first and if that connection fails, it will try to use “TCP/IP” protocol and if that fails, connection request will be made using “Named Pipes” protocol. This is because I have all three protocols “Enabled” and I have specified the order in this way.
Shared Memory protocol can be used only for local server connections whereby SQL Server should be running in the same box where you are trying connect. This protocol will not help you to connect to a Clustered SQL Server instance because the IP address of SQL Server is different from your local node.
You can also force to use specific protocol using syntax:
TCP:SQLSRVRNAME\INSTANCE for forcing connections to use TCP/IP protocol
NP:SQLSRVNAME\INSTANCE for forcing connections to use Named Pipe protocol instead you can also use \\.\pipe\instancename\sql\query
LPC:SQLSRVNAME\INSTANCE for forcing connections to use Shared Memory protocol. LPC stands for Local Procedure Call.
If you force these protocols, then connectivity will happen only using specific protocol and if that fails, connection will fail without trying with further protocols. If you are connecting using a SQL Server alias created in the local client, then the protocol specified in the alias will only be used.
2. Connecitivity flow when using TCP/IP Protocol
While making connections using TCP/IP protocol, the client driver will check whether the instance is DEFAULT instance (MSSQLSERVER is the instance name for default instance) and if yes, the connection is made directly to port 1433 using TCP protocol to the target SQL Server Instance.
For ex: If SQLMOSS is the instance name and if I connect from SQL Server Management Studio to SQLMOSS, a connection request is sent to TCP port 1433 for the IP Address returned by DNS to the hostname SQLMOSS.
If this connection fails, a request is sent to port 1434 over UDP protocol and this is the port and protocol in which SQL Server Browser will be listening for incoming requests in the target SQL Server instance SQLMOSS. Now SQL Server Browser would have already read the port in which requested  SQL Server Instance SQLMOSS\MSSQLSERVER is listening from the registry. So SQL Server Browser knows the TCP port is say 1488, it will return this information back to the requested client that SQLMOSS instance is listening on port 1488.
Now the client will reconnect to SQLMOSS instance using the TCP port 1488 and provided there is not firewall blocking, this connection will succeed.
There are other things like authentication and authorization to complete a login successfully.
3. Authentication:
It means that the username and password you specified in the connection is valid. If you are using Windows authentication and SQL Server server running with any of two mode “Mixed Mode” or “Windows-only” authentication, SQL Server will request Local Security Authority Subsystem Service (LSASS) to verify the credentials of the specified user. Again if the windows account is a local machine account, it is verified against local system security database and if it is a domain account, LSASS then requests Active Directory to authenticate the user.
Once this user is authenticated, a token is then passed to SQL Server that authentication is successfull. There are again two things in this authentication, they are NTLM or KERBEROS. KERBEROS needs SQL Server Service Principal Name to be registered in Active Directory agains the SQL Server node name or the SQL Server Virtual Server Name. If SPN’s are not registered, then connection to failback to NTLM depending on your environment settings. For more info about NTLM & Kerberos, refer this article
If the login is a SQL Server login, then SQL Server takes care of authenticating the user because SQL Server stores the username and password. If target SQL Server instance is running in Windows-Only authentication mode then though you are specifying correct password for the SQL login, the connection will fail because SQL Server will not allow Non Windows login. You can change this to make SQL Server to allow Mixed Mode Authentication (Both SQL logins and Windows logins) from SSMS -> Instance -> Properties -> Security -> Server Authentication. Please note that you need to restart SQL Server if you make this change.
4. Authorization:
Once authentication succeeds, authorization phase starts whereby SQL Server checks the permission for accessing the target database based on the TokenPerm cache built and if yes, the login succeeds and if not the connection will fail with State 16.
Once authorization completes, you can submit your queries to execute in the SQL Server instance.
So by now, hope you have the complete flow of how connectivity happens from a client to SQL Server.
Here are some Troubleshooting tips:
1. To make sure SQL Server Browser is able to start (Port 1434 is available for SQL Server Browser to grab), you can try start SQL Server Browser from command line :
C:\Program Files\Microsoft SQL Server\90\Shared>sqlbrowser.exe -c
You should see something like:
 SQLBrowser: starting up in console mode
SQLBrowser: starting up SSRP redirection service
SQLBrowser is successfully listening on ::[1434]
SQLBrowser is successfully listening on 0.0.0.0[1434]
[9088]: Waiting for next request…
This step will ensure that SQL Browser works fine. If you are able to start SQL Server Browser from command line and not as a service, check the service account configured for SQL Server Browser. To shutdown SQL Browser started from command line, you have to press CTRL + C.
2. If SQL Server Browser failed to start, run TCPView from http://technet.microsoft.com/en-us/sysinternals/bb897437 to make sure that no other process is already listening on 1434 UDP. For example if you have SQL Server 2000 already installed on the same node where SQL Server Browser fails to come online, it could be very well because SQL 2000 SSRP service already listening on 1434 and TCPView will help you to troubleshoot this.
3. If connectivity to SQL Server instance fails, first thing to check is whether it is a Clustered SQL Server instance or a Standalone.
If it is a clustered, try connecting from Active node of SQL Server to the SQL Server Instance. If this works then it could be a firewall blocking connections from passive node and other clients.

Daily using SQL Scripts

SQL Scripts

1 ) Find the Size of Database & Log File

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'tempdb'
GO

 2) Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?

SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

3 Say some DBCC Commands?

DBCC OPENTRAN   - What are the transactions are going
DBCC CHECKDB     - Consistency Check & Allocation Errors 
DBCC FREEPROCCACHE - Clears the procedure cache removing all execution
Plans, all procedures are recompiled 
DBCC DROPCLEANBUFFERS -Drops all the data that was cached in memory
DBCC SHOWCONTIG - Shows fragmentation within tables / indexes 
DBCC DBREINDEX    - Performs a complete reorganization of the index.
DBCC SQLPERF (LOGSPACE)
DBCC IND
DBCC PAGE
DBCC CHECKTABLE
DBCC CHECKCATALOG
DBCC CHECKALLOC
DBCC SHRINKDATABASE
DBCC SHRINKFILE 
DBCC LOGINFO
DBCC LOGTRUNCATEONLY
DBCC INPUTBUFFER
DBCC CHECKCONSTRAINTS - Runs after repair allow data loss to find referential integrity issues
DBCC MEMMORYSTATUS – It will show the VM Committed,reserved,AWE allocated etc.
DBCC OUTPUTBUFFER - Returns the current output buffer in hexadecimal and ASCII format
DBCC CHECKDB WITH ESTIMATE – It will show the estimated Tempdb space required for checktable and checkalloc.

4)  How to identify blocking in SQL Server 

i)  select * from sys.sysprocesses where blocked<>0

ii) USE Master
    GO
    EXEC sp_who2
    GO

iii) Using DMVS

USE Master
GO
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
GO

5) How to take the databas online & Offline


Bring the database online using below query

alter database  TEST set online with rollback immediate

Take the database offline using below query

alter database  TEST set offline with rollback immediate


6) SQL server info query


SELECT @@servername as [SQL Instance],SERVERPROPERTY('productversion') as [SQL Versiaon], SERVERPROPERTY('productlevel') As [Service Pack] , SERVERPROPERTY('edition') as [SQL Server Edition]

Unable to Drop Windows Login from SQL Server due to ENDPOINT Permission

We encountered Error 15173 while we tried to drop one of our Windows Login from SQL Server, part of our house-keeping task, and SQL Server prevent us from dropping the Windows Login even though we have unmapped all the database mapped and owned by the login. It works fine for us before this.

Msg 15173, Level 16, State 1, Line 2
Login 'DOMAIN\User_Login' has granted one or more permission(s). Revoke the permission(s) before dropping the log

After some research, we found a solution for our issue from the SQLServerCentral’s forum with regards to drop the Windows Login from SQL Server due to ENDPOINT permission. But our SQL Server was not configured with mirroring.
We try to execute the following queries and we realized that we obtain the same output as the one shown in the threat.

1	SELECT * from sys.server_permissions

WHEN grantor_principal_id = (Select principal_id

FROM sys.server_principals where name = N'DOMAIN\User_Login');

To obtain the mirroring endpoint name.

1

SELECT * FROM sys.[database_mirroring_endpoints];

Solution:

Drop the mirroring ENDPOINT. The ENDPOINT name retrieved from earlier query.

1	DROP ENDPOINT <ENDPOINT_NAME>;

Next, drop the user again, and it succeeded.

1	IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DOMAIN\User_Login')

2	DROP LOGIN [DOMAIN\User_Login];

Database Mirroring counters

For the principal server in the mirror, you should monitor the following counters:

• Log Bytes Sent/sec: Number of bytes of the log sent to the mirror per second.
• Log Send Queue KB: Total kilobytes of the log that have not yet been sent to the mirror server.
• Transaction Delay: Delay (in milliseconds) in waiting for commit acknowledgement from the mirror. This counters reports the total delay for all the transactions in process at that time. To determine the average delay per transaction, divide this counter by the Transactions/sec counter. When running asynchronous mirroring this counter will always be 0.
• Transactions/sec: The transaction throughput of the database. This counter is in the Databases performance object.
• Log Bytes Flushed/sec: The rate at which log records are written to the disk. This is the log generation rate of the application. It plays a very important role in determining database mirroring performance. This counter is in the Databases performance object.
• Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks.

For mirror server

• Redo Bytes/sec: Number of bytes of the transaction log applied on the mirror database per second.
• Redo Queue KB: Total kilobytes of hardened log that remain to be applied to the mirror database to roll it forward.
• Disk Write Bytes/sec: The rate at which the disk is written to. This counter is in the Logical Disk performance object and represents. Monitor this counter for the data as well as the log disks on the mirror.

Database Mirroring Concepts

Database mirroring maintains a hot standby database (known as the mirror database) that can quickly assume client connections in the event of a principal database outage. Database mirroring involves two copies of a single database that reside on different computers. At any given time, only one copy of the database is available to clients. This copy is known as the principal database. Unlike log shipping which works by applying full transaction log backups to a warm standby database, database mirroring works by transferring and applying a stream of database log records from the principal database to the other copy of the database (the mirror database). Database mirroring applies every database modification that is made on the principal database to the mirror database. This includes data changes as well as changes to the physical and logical structures in the database, such as database files, tables, and indexes.

Principal: In a database mirroring configuration, there are two copies of a single database, but only one copy is accessible to the clients at any given time. The copy of the database that the applications connect to is called the principal database. The server that hosts the principal database is known as the principal server.
Mirror: The mirror is the copy of the principal database. The mirror is always in a restoring state; it is not accessible to the applications. To keep this database up-to-date, the log records are transferred from the principal and applied on the mirror database. The server that hosts the mirror database is known as the mirror server.
Witness: The optional witness is an SQL Server instance in a database mirroring configuration. It is separate from the principal and mirror instances. When database mirroring is used in synchronous mode, the witness provides a mechanism for automatic failover.
Send Queue: While sending the log records from the principal to the mirror, if the log records can’t be sent at the rate at which they are generated, a queue builds up at the principal. This is known as the send queue. The send queue does not use extra storage or memory. It exists entirely in the transaction log of the principal. It refers to the part of the log that has not yet been sent to the mirror.
Redo Queue: While applying log records on the mirror, if the log records can’t be applied at the rate at which they are received, a queue builds up at the mirror. This is known as the redo queue. Like the send queue, the redo queue does not use extra storage or memory. It exists entirely in the transaction log of the mirror. It refers to the part of the hardened log that remains to be applied to the mirror database to roll it forward. 
Endpoint: An endpoint is a SQL Server object that enables SQL Server to communicate over the network. It encapsulates a transport protocol and a port number.
Failover: When the principal database (or the server hosting it) fails, database mirroring provides a mechanism to fail over to the mirror database.

Some important aspects of database mirroring are:

• The granularity of database mirroring is a database. Mirroring is configured for one database at a time. The whole instance is not mirrored.
  
• Two copies of the same database are involved in database mirroring, but only one database is accessible to the applications at any given time. You can create a snapshot on the mirror and use it for read-only purposes (a good solution for reporting requirements). However, you cannot directly access the mirror database or back up the mirror database.
  
• You cannot mirror the master, msdb, temp, or model databases.
  
• Database mirroring requires that the database use the full recovery model. You can’t use the simple or bulk-logged recovery models.
  
• SQL Server 2005 allows only one mirror database for each principal database.
  
• One instance can serve as the principal for one database, the mirror for another database, and the witness for yet another database.
  
• Multiple databases in an instance can be mirrored.
  
• Applications that connect to a database with ADO.NET or the SQL Native Client (SNAC) can automatically redirect connections when the database fails over to the mirror.
  
• Data between the principal and the mirror is transferred encrypted by default.
  
• A database which is mirrored to another server can also be the source database for a log-shipping scenario.
  

Database Mirroring

One of the most important requirements of business-critical applications is the availability of the database. Maximizing the availability of the database is on the top priority list of many database administrators. Database mirroring, a new feature in Microsoft SQL Server™ 2005, adds another alternative to the SQL Server availability arsenal.
To maximize database availability, unplanned as well as planned downtime needs to be minimized. Unplanned downtime is primarily caused by hardware failure (computer failure and storage failure), disk corruption, power outages, communication failures, natural disasters, terrorism, human error, and other factors that cause the primary production database, the production server, and/or the production data center to be unavailable. Planned downtime is primarily due to changes that are applied to the production system. These might be hardware upgrades, software upgrades, and database storage and configuration changes, which cause the primary database or server to be unavailable for a short period of time.
Database mirroring helps minimize both planned and unplanned downtime by:

• Maintaining a mirror database that is kept up-to-date with the production database either synchronously or asynchronously.
  
• Providing ways to perform automatic as well as manual failover.
  
• Allowing the mirror database to be in a remote data center, thereby providing a foundation for disaster recovery

SQL Cluster 1

Public and Private Networks

Each node of a cluster must have at least two network cards to be a fully supported installation. One network card is connected to the public network, and the other network card will be connected to a private cluster network. (Read Part 1 of this two-part article here.)

• The public network is the network to which the client applications connect. This is how they communicate to a clustered SQL Server instance using the clustered IP address and clustered SQL Server name. It is recommended to have two teamed network cards for the public network for redundancy and to improve availability.
• The private network is used solely for communications between the clustered nodes. It is used mainly for the heartbeat communication. Two forms of communications are executed:
  • LooksAlive: Verifies that the SQL Server service runs on the online node every 5 seconds by default
  • IsAlive: Verifies that SQL Server accepts connections by executing sp_server_diagnostics.

This health detection logic determines if a node is down and the passive node then takes over the production workload.

The SQL Server Instance

Surprisingly, SQL Server client applications don’t need to know how to switch communicating from a failed cluster node to the new active node or anything else about specific cluster nodes (such as the NETBIOS name or IP address of individual cluster nodes). This is because each clustered SQL Server instance is assigned a Network name and IP address, which client applications use to connect to the clustered SQL Server. In other words, client applications don’t connect to a node’s specific name or IP address but instead to the cluster SQL network name or cluster SQL IP address that stays consistent and fails over. Each clustered SQL Server will belong to a Failover Cluster Resource Group that contains the following resources that will fail together:

• SQL Server Network Name
• IP Address
• One or more shared disks
• SQL Server Database Engine service
• SQL Server Agent
• SQL Server Analysis Services, if installed in the same group
• One file share resource, if the FILESTREAM feature is installed

How a Failover Works

Assume that a single SQL Server 2012 instance runs on the active node of a cluster and that a passive node is available to take over when needed. At this time, the active node communicates with both the database and the quorum on the shared disk array. Because only a single node at a time can access the shared disk array, the passive node does not access the database or the quorum. In addition, the active node sends out heartbeat signals over the private network, and the passive node monitors them, so it can take over if a failover occurs. Clients are also interacting with the active node via the clustered SQL Server name and IP address while running production workloads.

Now assume that the active node stops working because of a power failure. The passive node, which is monitoring the heartbeats from the active node, notices that the heartbeats stopped. After a predetermined delay, the passive node assumes that the active node has failed and initiates a failover. As part of the failover process, the passive node (now the active node) takes over control of the shared disk array and reads the quorum, looking for any unsynchronized configuration changes. It also takes over control of the clustered SQL Server name and IP address. In addition, as the node takes over the databases, it has to perform a SQL Server startup and recover the databases.

The time this takes depends on many factors, including the performance of the hardware and the number of transactions that might have to be rolled forward or back during the database recovery process. When the recovery process is complete, the new active node announces itself on the network with the clustered SQL Server name and IP address, which enables the client applications to reconnect and begin using the SQL Server 2012 instance after this minimal interruption.

SQL Cluster

In this section, you consider active and passive nodes, the shared disk array, the quorum, public and private networks, and the cluster server. Then, you learn how a failover works.

Active Nodes Versus Passive Nodes

A Windows Failover Cluster can support up to sixteen nodes; however, most clustering deployment is only two nodes. A single SQL Server 2012 instance can run on only a single node at a time; and should a failover occur, the failed instance can failover to another node. Clusters of three or more physical nodes should be considered when you need to cluster many SQL Server instances.

In a two-node Windows Failover Cluster with SQL Server, one of the physical nodes is considered the active node, and the second one is the passive node for that single SQL Server instance. It doesn’t matter which of the physical servers in the cluster is designated as active or passive, but you should specifically assign one node as the active and the other as the passive. This way, there is no confusion about which physical server is performing which role at the current time.

When referring to an active node, this particular node is currently running a SQL Server instance accessing that instance’s databases, which are located on a shared disk array.

When referring to a passive node, this particular node is not currently running the SQL Server. When a node is passive, it is not running the production databases, but it is in a state of readiness. If the active node fails and a failover occurs, the passive node automatically runs production databases and begins serving user requests. In this case, the passive node has become active, and the formerly active node becomes the passive node (or the failed node, if a failure occurs that prevents it from operating).

Shared Disk Array

Standalone SQL Server instances usually store their databases on local disk storage or nonshared disk storage; clustered SQL Server instances store data on a shared disk array. Shared means that all nodes of the Windows Failover Cluster are physically connected to the shared disk array, but only the active node can access that instance’s databases. To ensure the integrity of the databases, both nodes of a cluster never access the shared disk at the same time.

Generally speaking, a shared disk array can be an iSCSI, a fiber-channel, SAS connected, a RAID 1, a RAID 5, or a RAID 10 disk array housed in a standalone unit, or a SAN. This shared disk array must have at least two logical disk partitions. One partition is used for storing the clustered instance’s SQL Server databases, and the other is used for the quorum drive, if a quorum drive is used. Additionally, you need a third logical partition if you choose to cluster MSDTC.

The Quorum

When both cluster nodes are up and running and participating in their respective active and passive roles, they communicate with each other over the network. For example, if you change a configuration setting on the active node, this configuration is propagated automatically, and quickly, to the passive node, thereby ensuring synchronization.

As you might imagine, though, you can make a change on the active node and have it fail before the change is sent over the network and made on the passive node. In this scenario, the change is never applied to the passive node. Depending on the nature of the change, this could cause problems, even causing both nodes of the cluster to fail.

To prevent this change from happening, a Windows Failover Cluster employs a quorum. A quorum is essentially a log file, similar in concept to database logs. Its purpose is to record any change made on the active node. This way, should any recorded change not get to the passive node because the active node has failed and cannot send the change to the passive node over the network, the passive node, when it becomes the active node, can read the quorum log file to find out what the change was. The passive node can then make the change before it becomes the new active node. If the state of this drive is compromised, your cluster may become inoperable.

In effect, each cluster quorum can cast one “vote,” where the majority of total votes (based on the number of these cluster quorums that are online) determine whether the cluster continues running on the cluster node. This prevents more than one cluster node attempting to take ownership of the same SQL Server instance. The voting quorums are cluster nodes or, in some cases, a disk witness or file share witness. Each voting cluster quorum (with the exception of a file share witness) contains a copy of the cluster configuration. The cluster service works to keep all copies synchronized at all times.

Following are the four supported Windows Failover Cluster quorum modes:

• Node Majority: Each node that is available and in communication can vote. The cluster functions only with a majority of the votes.
• Node and Disk Majority: Each node plus a designated disk in the cluster storage (the “disk witness”) can vote, whenever they are available and in communication. The cluster functions only with a majority of the votes.
• Node and File Share Majority: Each node plus a designated file share created by the administrator (the “file share witness”) can vote, whenever they are available and in communication. The cluster functions only with a majority of the votes.
• No Majority: Disk Only: The cluster has a quorum if one node is available and in communication with a specific disk in the cluster storage. Only the nodes that are also in communication with that disk can join the cluster. The disk is the single point of failure, so use highly reliable storage. A quorum drive is a logical drive on the shared disk array dedicated to storing the quorum and as a best practice should be around 1GB of fault tolerant disk storage.

With two-node clusters Disk only is the most often used quorum configuration, commonly known as the quorum disk. The quorum configuration can be switched after the cluster has been deployed based on the number of clustered nodes and user requirements. While in clusters with greater than two nodes, the other three quorum modes are more commonly used.