Saturday 14 November 2009

Sql Server 2008 Database Mirroring - Cannot Create Endpoint

We have been testing SQL Server 2008 for a while now. This weekend was the date set to move our main database to SQL Server 2008 and also create a mirror on original SQL Server. We did a side by side install of SQL Server 2008 on the original server, which had SQL server 2005 on it.

The move to SQL Server 2008 went fine. I did a database copy form SQL 2005 to SQL 2008 on both the principal and the mirror instances. I then made a backup of the database from the principal instance and restored it to the mirror in recovery mode. Then I did a log backup on the principal server and restored it to the mirror database and still left it in recovery mode.

Finally it came to setting up the mirror. I used the wizard and got all the way through. Between it cam to starting the mirror an error showed up stating that the end point on the original server couldn't be contacted.

Firstly I thought that there was a firewall issue. However on checking I found that Windows firewall was switched off on both server.

Then I realised the issue was to do with the fact that mirroring had originally been enabled on the mirror servers SQL 2005 instance. I had gone through all mirrored databases on SQL 2005 instance and removed all mirroring but some where SQL 2005 was still keeping hold of the default 5022 endpoint.

After a bit of Googling and "Books On Line (ing)" I found two SQL statements that showed the problem when ran on the master database.

FROM sys.tcp_endpoints t

FROM sys.database_mirroring_endpoints e

The two statements show mirroring endpoints for an instance.

I had to delete the lines related to mirroring on the SQL Server 2005 instance. Once this was done I was able to successfully start mirroring on SQL Server 2008 instance for our databases.

As with anything like this it is advisable to take a backup of your system before manually changing the master database

No comments: