Complete Guide to SQL 2005 mirroring using the GUI
Well it has occurred to me that there are no real guides to setting up mirroring that deal with what errors you could, or more like you will run into whilst setting up a mirror.
Ok for this example I am going to setup a ‘High safety with automatic failover’ this particular mirror uses a Principle server, failover server and a witness server, so for this example you will need 3 servers (you could get a way with 2 as the witness could be the failover, but best to have 3)
So first thing is first the prerequisites.
3 SQL 2005 server’s
Service Packs installed
at least SP1 applied, if this is not applied you will get an error saying that this feature is not enabled, you can enable this feature, however I recommend rather then playing with the startup values you patch your MSSQL server to the current SP for SQL.
MSSQL Service
You must have all three SQL servers using an account that has network access, by default most of us will have used the local system account, which in this case is not going to work, you will get this obscure error message when trying to start the mirror.
All databases in FULL recovery Mode
both the mirror and the failover database both need to be in a Full recovery mode, if not you will get the below error
‘the database cannot be mirrored because it does not use the full recovery model’
If you get this, right click on your database and go to ‘properties’ then to ‘options’ you can change the mode their.
OK, Now your ready to start getting the mirror together
first thing is first, take a full backup of your database that you want to start mirroring, move this backup to a share or somewhere that your failover server can restore the database from.
Now using the same name of the database on the Principle server, create a database, then restore the database backup, make sure when restoring the database you are using the NORECOVERY method
You could use this TSQL to create and complete your database restore:
restore databasename from disk backup location and name with replace, NORECOVERY
example:
restore mydatabase from disk d:\SQL_backups\mydatabase.bak with replace, NORECOVERY
Ok now you should have a database on your failover server and it should be in a ‘restoring’ state, if you are not seeing this then refresh your databases, if you still don’t see then then you probably missed the NORECOVERY part, go back and do this again with NORECOVERY applied.
we are halfway there, all you need to do now is again create another backup on the principle server, but this time it must be a transactional backup. So once again create a new transactional backup, place the backup in a place where the fail over server can restore the database from, and then perform your restore again using the NORECOVERY method.
or do this from TSQL
restore mydatabase from disk d:\SQL_backups\mydatabaseTRANSACTIONAL.TRN with NORECOVERY
Once again you will have a database that should be still in a restoring state.
If this step is missed or you restore an older TRN log file, then you can expect to get the following error when trying to create the mirror :
‘the mirror database …., has insufficient transaction log data to preserver log backup chain of the principle database.
Now we are ready to start the mirroring!
right click on the principle database mirror, choose tasks followed by Mirror
You will be prompted with the following:
Click on security in the top right side
Yes we want to include a witness server, (in this case we are doing the high availability so we need a witness server for it to work)
Make sure all three are ticked
This is where we get to set the port that the mirroring is going to be using to connect to each other, and the endpoint name. I have accepted defaults, but before excepting default ports ensure that all of your servers can talk to each other throughout that port best way to test would be
telnet servername 5022
do this for all combinations so from:
principle to witness
principle to fail over
failover to principle
failover to witness
witness to Failover
witness to principle
If you can make successful connections to each then you are good to go.
Once you have run through the 3 database servers you will be prompted with the below screen:
Here if they are all on the same network and using integrated (AD) authentication which recommenced) then you may skip this, otherwise you will be required to enter in valid credentials
You should come to this screen after the review screen, if you have failed on one of the servers, then read the error and correct the problem (these errors are generally helpful ones )
Now start you mirror!
Other problems you may run into:
If you tried to start your mirror, and you get an error ‘one or more of the servers network addresses lacks a fully qualified domain name’ that’s an easy one, you must edit the server so it has a FQDN like server.domain.com if your DNS is being painful add it to the host file of the server you are setting the mirror up on.
That should be enough to get your mirror up and runn
ing
Stay tuned more on mirroring coming soon
as promised a new more in depth look auto- failover of SQL Mirroring, it can be found here, SQL 2005 Mirroring – Automatic Failover
July 29th, 2008 at 11:28 pm
[...] July 29, 2008 SQL 2005 Mirroring – Automatic Failover Posted by bradmarsh under Uncategorized This post is a follow on from a previous post ‘Complete Guide to SQL 2005 mirroring using the GUI‘ [...]
January 20th, 2009 at 8:29 am
[...] by bradmarsh under Microsoft, SQL Server, Software, Techie This post has been moved to: http://bradmarsh.net/index.php/2008/07/09/complete-guide-to-sql-2005-mirroring-using-the-gui/ [...]
April 2nd, 2009 at 11:03 am
screenshot links are broken… it doesn’t show up..
June 15th, 2010 at 10:40 pm
Great information! I’ve been looking for something like this for a while now. Thanks!
August 10th, 2011 at 4:22 pm
[...] have successfully created a SQl failover with 3 servers following the guide Complete Guide to SQL 2005 mirroring using the GUI | Brad Marsh's Weblog Someone told me that I need to actually have Cluster Admin setup first. I'm rather confused about [...]
August 10th, 2011 at 4:48 pm
[...] Cluster Admin for SQL failover No Comments I have successfully created a SQl failover with 3 servers following the guide Complete Guide to SQL 2005 mirroring using the GUI | Brad Marsh’s Weblog [...]
December 30th, 2020 at 7:41 pm
Loyce Stare…
Well it has occurred to me that there are no real guides to setting up mirroring that deal with what errors you could, or more like you will run into whilst…
May 12th, 2021 at 6:11 am
Private Proxies Free Trial…
Well it has occurred to me that there are no real guides to setting up mirroring that deal with what errors you could, or more like you will run into whilst…
August 6th, 2021 at 4:16 am
Buy Best Private Proxy…
Well it has occurred to me that there are no real guides to setting up mirroring that deal with what errors you could, or more like you will run into whilst…
January 8th, 2022 at 12:26 pm
3determinate…
…