Brad Marsh’s Weblog

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

Twitter This

Usuario: Password: