Cannot Start Mirroring

Mirroring is dead.  Long live mirroring.  While it will no longer be included in future versions of SQL Server, I still come across instances I have to support and thus am making some notes.  Occasionally, when setting up mirroring, you may get the following error.

The server network address “TCP://SQLServer:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

I have found some common scenarios include:

  • Insufficient permission on both nodes.  Make sure the service account for each SQL Server instance has rights to the partner instance.  Seems simple enough, but this was an issue for me just last month.
  • The log backup has not been applied to the partner/mirrored instance.  While sometimes it might let you set up without taking a transaction log backup and applying it on the partner database, more often than not it requires it.

Check out Robert Davis’s post for more helpful information if you are stuck.

GETUTCDATE Function

I came across a database that stores the execution date in UTC time.  I needed to run a report with a sliding window to report errors during the day.  Because this server was in Virginia, it is subject to daylight savings so I needed to be able to calculate the local time based off the UTC time and this could be 4 or 5 hours depending on the season.

I found the function GETUTCDATE as an easy way to calculate the UTC date.  It should be noted that it uses the server time to calculate this so if you have multiple servers in different time zones there may be different logic needed for each one.  In my case, I just needed to find the hour difference between my timezone and UTC–in this case I use the DATEDIFF function.

Now that I know how far away from UTC I am, I can apply local time logic to get the data I want.  This example uses AdventureWorks table.

[sourcecode language=”SQL”]DECLARE @HourOffset INT

SET @HourOffset = DATEDIFF(hh,GETUTCDATE(),GETDATE())
–Select @HourOffset

SELECT DATEADD(hh,@HourOffset,ErrorTime) AS ExecutionTime
FROM [dbo].[ErrorLog][/sourcecode]

Moving A Database With Attach and Detach

I found myself doing several database migrations this weekend and because these databases were generally small (Less than 10 GB), I choose the route to detach the database, copy the files to the new location and re-attach.  To remove the databases, I detach them from the current instance.  I am not worried about backup history with this as the server will be decommissioned.

[sourcecode language=”sql”]EXEC master.dbo.sp_detach_db @dbname = N’MyDatabase’
GO [/sourcecode]

If you refresh your database list, you will no longer see the database listed; however, the files are still on the server.  You can now copy those to the new location and attach them. You need only pass in the name of the database and the location of the files and SQL Server will add the database and make it available to you.

[sourcecode language=”sql”]EXEC sp_attach_db @dbname = N’MyDatabase’,
@filename1 = N’E:MSSQLMyDatabase.mdf’,
@filename2 = N’F:MSSQLMyDatabase_log.ldf’; [/sourcecode]

Don’t forget to add your security and any jobs on the old server you need to copy over for this database.