DB Mirror Failover Script

I had to manually failover about 25 principals to their mirror partners. After about 20 mouse clicks I thought "There HAS to be a better way" and began to Google. Behold:

ALTER DATABASE [Database Name] SET PARTNER FAILOVER

Be mindful - this can ONLY be done from the principal, not the mirror. If the principal is unavailable, the mirror can be forced to start up, but there is a possibility of data loss:

ALTER DATABASE [Database Name] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Credit where credit is due: http://www.sqlservercentral.com/Forums/Topic829941-1549-1.aspx

Stored Procedure to get File Info from the Master DB

I just realized that I had not published this before. Here is the sp I use to get all the info I need regarding files from available DBs on a server:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: K Griffith
-- Create date: 04 Feb 2011
-- Description: Returns the space consumed on the DB Server or generic info about each
-- DB on the server depending on the parameter passed. Only
-- returns DBs NOT participating in mirroring.
-- =============================================
CREATE PROCEDURE [dbo].[sp_FileSizes]

@TYPE NVARCHAR(10)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @ID INT
DECLARE @MAX INT
DECLARE @DB_ID INT
DECLARE @DB_NAME NVARCHAR(300)
DECLARE @SQL NVARCHAR(500)
DECLARE @SQL1 NVARCHAR(500)

SET @ID = 5
SET @MAX = (SELECT MAX(database_id) FROM sys.databases)

CREATE TABLE #temp(DBName nvarchar(300), Name nvarchar(300), Available decimal(18,2), Used decimal (18,2),
Type nvarchar(5), FileID int)

WHILE @ID <= @MAX 
 BEGIN 
  SET @DB_ID = (SELECT D.database_id 
                FROM sys.database_mirroring M 
                INNER JOIN sys.databases D 
                  ON M.database_id = D.database_id 
                WHERE M.mirroring_role_desc IS NULL 
                  AND D.database_id = @ID) 
  IF @DB_ID IS NOT NULL 
    BEGIN 
      SET @DB_NAME = (SELECT NAME 
                      FROM sys.databases
                      WHERE database_id = @ID)


      SET @SQL = 'USE [' + @DB_NAME + ']' 
      SET @SQL1 = ' INSERT INTO #temp (DBName, Name, Available, Used, Type,  
                     FileID) 
                  SELECT ''' + @DB_Name + ''' AS DBName, Name, 
                   size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'')  
                   AS int)/128.0 AS AVAILABLE, 
                   CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0  
                   AS USED, 
                     type = CASE Type 
                              WHEN 1 THEN ''Log'' 
                              ELSE ''Data''  
                             END, 
                   file_id 
                 FROM sys.database_files' 
              
      EXEC (@SQL + @SQL1) 
    END 
    SET @ID = @ID + 1 
   END 
     IF @TYPE = 'SPACE' 
       BEGIN 
         SELECT SUM(USED)/1024 AS [Total Space in GB] 
         FROM #temp 
       END 
     ELSE 
         SELECT * 
         FROM #temp 
         WHERE Type = @TYPE 
         ORDER BY Type ASC, Available DESC 


     DROP TABLE #temp 
  END

Retrieving a list of DBs that are NOT participating in mirroring

I have a stored procedure that I use on all my servers that queries the master table and retrieves all the data I need regarding each DB siting on the server. I can then decide which files need shrinking, which DB needs more space, etc. I was recently tasked with mirroring. My sp started to fail because a database on the mirrored server acting as a mirror is not available. I changed my query to only grab the IDs of DBs NOT participating in mirroring an viola! My sp worked again. Here's the query:

SELECT D.database_id
FROM sys.database_mirroring M
INNER JOIN sys.databases D
ON M.database_id = D.database_id
WHERE M.mirroring_role_desc IS NULL

I wish I was smart enough to have come up w/ this on my own, but such is not the case. Here's the "credit where credit is due": http://social.msdn.microsoft.com/Forums/en/sqldatabasemirroring/thread/ded375ce-1f09-4070-b588-3d3cb91ed41a

Guide to Mirroring Databases on SQL Server 2008 Enterprise

Guide to Mirroring Databases on SQL Server 2008 Enterprise

  One of the first things to make sure of is that the drive structure is the same on the principal server and the mirror server. For example, if data files are stored at S:\SqlData and log files are kept at L:\SqlLogs on the principal server, this exact same drive structure must be mimicked on the mirror server.
  Additionally, mirroring was attempted between a Windows 2003 Enterprise Server with SQL Server 2008 Enterprise as the principal and a Windows 2003 Enterprise Server with SQL Server 2008 Enterprise R2 as the mirror. The initial mirroring could be established and the failover could occur from the principal (SQL Server 2008 Enterprise) to the mirror (SQL Server 2008 R2 Enterprise). However, when a failover back to the principal from the mirror was attempted, the mirroring broke and could not be reestablished. The take away here is to make sure that both servers are running the exact same version of SQL Server (SQL Server 2008 Enterprise, for example).
  Lastly, keep in mind that mirroring is done at the database level as opposed to something like clustering which is done at the server level. Therefore, each database must be set up for mirroring individually.

1. Select a database for mirroring on the principal server. Right click on the database. Select ‘Tasks’ then ‘Back Up…’ Select the location to where you would like to back up the database. The backup will need to end up on the mirroring server and eventually be restored there.
2. On the mirror server in SSMS, right click on Databases underneath the server name and select ‘Restore Backup’. In the Restore Database window that opens, after you have selected a database name and the backup to be restored, click on ‘Options’ in the top left hand corner of the window. Make sure that the MDF and LDF files are named the same as they are on the principal. Also, check the ‘Leave the database non-operational… (RESTORE WITH NORECOVERY)’ option.
3. Go back to the principal server, select the same database, pick ‘Tasks’ and then ‘Back Up…’ again. On the Backup type dropdown, select ‘Transaction Log’. Make sure that the transaction log backup is in a location that can be accessed from the mirror so that it can be restored to the mirror.
4. Right click on the database on the mirror. The database should show as ‘(Restoring…)’ Select ‘Tasks’ -> ‘Restore’ -> ‘Transaction Log…’ Select the transaction log backup that you created from the principal. Select ‘Options’ in the ‘Restore Transaction Log’ window and click the radio button that states ‘Leave the database non-operational… (RESTORE WITH NORECOVERY)’. Then click the ‘OK’ button’
5. Go back to the principal server and right click on the database that you have selected for mirroring. Click ‘Tasks’ -> ‘Mirror…’ When the Database Properties window opens, click the ‘Configure Security’ button and go through the wizard.
6. If you wish to include a witness server, select the ‘Yes’ radio button in the second screen. Continue to go through the wizard. The databases that are being mirrored in this instance are all on servers on a private network behind a firewall, so DO NOT check the box for ‘Encrypt data sent through this endpoint’. Additionally, data encryption can cause failures if things are not set up properly. Unless it is absolutely necessary, do not encrypt the data.
7. The wizard will build endpoints on the principal, mirror, and witness servers. The default port for mirroring is 5022. On a Windows 2003 Server, if the Windows Firewall is not in use, no additional configuration should be necessary. However, if the Windows Firewall is in use on the server, the port may have to be specifically opened. By default on a Windows 2008 Server, all ports are locked down so 5022 will have to be specifically opened on a 2008 Servers.
8. If all goes well, there should be success messages beside each of the servers in the mirroring scheme if the endpoints get configured correctly. When the wizard concludes, select ‘Start Mirroring’ to finalize the mirroring set up. If no error messages appear, look at both the principal and mirrored databases in SSMS. The principal database should display a message stating ‘(Principal, Synchronized)’ next to the database name. The mirrored database should display a message that states ‘(Mirror, Synchronized / Restoring…)’ next to the database.

Additional Information
  If the mirrored databases are going to be used in an ASP.NET application, this information will need to be placed in the web.config file when the connection string is defined. The parameter is called 'Failover Partner' and it follows the Data Source parameter:


<add name="SqlCS" connectionString="Data Source=SqlPrincipal; Failover Partner=SqlMirror; Initial Catalog=master; Persist Security Info=False; User ID=user; Password=password123" providerName="System.Data.SqlClient" />


  Once again, Database Mirroring occurs at the database level, NOT the server level. This is important because the logins are created at the server level. Additionally, SQL Server uses a GUID (like 0xB7898239D38FA34D84F30B7404F5C67B) called an SID to identify a login, NOT the login name. So if we create the login ‘user123’ on both the principal and mirror servers and assign the same permissions to the mirrored database, when the database fails the login ‘user123’ will not work because the SIDs don't match on the servers. The following article presents a method to resolve this issue:
http://support.microsoft.com/kb/918992
On the principal server, open a new query window in SSMS and run the following query:

----------------------------------------- BEGIN QUERY ----------------------------------------------
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR


SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

----------------------------------------- END QUERY -------------------------------------------------

This script will create 2 stored procedures in the master database: sp_hexidecimal and sp_help_revlogin. After the script has executed, run the following statement in a new query window:

EXEC sp_help_revlogin

The output will give you the ability to create logins with identical SIDs on the mirrored database. Here is a sample output from the script:

---- Login: User123
CREATE LOGIN [User123] WITH PASSWORD = 0x0100F6FF96F04564026C472234A73D7FD9B3CE467E6B9E066BBC
HASHED, SID = 0xCC1C8D1C6BF52640844476D7FFADB862, DEFAULT_DATABASE = [master],
CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Run the above script on the mirror server and assign the same permissions to the user ID to the database that exist on the principal server and everything should work correctly.

Filtering by Time in a Query


SELECT CONVERT(VARCHAR(5),[fldDateTime], 108) AS Time, [fldDateTime]
FROM tblDateTimes
WHERE ((CONVERT(VARCHAR(5),[fldDateTime], 108) <= '06:00') OR (CONVERT(VARCHAR(5),[fldDateTime], 108) >= '18:00'))


Finding Duplicates with T-SQL


SELECT UserID, COUNT(UserID) AS Total
FROM tblUsers
GROUP BY UserID
HAVING COUNT(UserID) > 1