Share your mirroring experiences...

Ok, so in a week or so I will be setting up a database mirroring scenario in order to facilitate a server move (i.e. physical move to new location) with low downtime amounts. We've got our plan pretty much down, very abridged version is:

  • Backup primary
  • Restore to secondary
  • Set up mirroring from primary to secondary, and wait for SYNCHRONIZED
  • Move server
  • Catch up the secondary
  • Move production traffic to the secondary
So - any gotchas that you've experienced in the past? Just want to make sure we don't need our GTH plan :)
more ▼

asked May 17, 2011 at 12:44 PM in Default

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

how far is the server moving? No chance to virtualise and transfer image? or backup/restore?
May 17, 2011 at 03:55 PM Fatherjack ♦♦
It's about 25 miles... So - virtualise and transfer image = impact on running system... And both those would have the impact of either a problem to do with catching up on operations performed 'in the mean time' or downtime - which we're trying to minimise...
May 18, 2011 at 01:09 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

5 answers: sort newest

You may want to add a step after backup primary to backup primary tlog, then a step after restore to secondary for restoring tlog to secondary. For some reason there still seems to be a glitch where you have to also restore a log. I have only tested in 2005 and 2008. 2008R2 may have fixed this.

The only other gotcha would be anything hard coded to the primary host name. If you use DNS aliases or virtual ip addresses you should be fine. Does the primary server have any ETL processes or shared folders?

What is your plan for migrating your users? Several processes exist for a one time move but I am not aware of anything that will keep the SID's in sync other than a drop and create of the user object.

I almost forgot, you asked about experiences. I used mirroring a lot when migrating to a new data center and just last week for a DR test. The only gotcha's I have encountered are user access. Keeping the bloody accounts in sync. Fortunately the majority of the accounts I deal with are server type SQL accounts where the password doesn't change and active directory groups. However there are those systems where we have local SQL accounts where the user changes their password. Those have been a pain to work with.
more ▼

answered May 17, 2011 at 01:01 PM

Tim gravatar image

36.4k 38 41 139

When you say 'migrating your users' I presume you mean users in the context of sys.database_principals and not end-users of our system? There are not a lot of database users - so making sure that the database users map to their respective logins after activating the secondary isn't a huge task... But - definitely a good thing to look out for - thank you...
May 17, 2011 at 01:40 PM Matt Whitfield ♦♦
I was referring to the database users. Where I work we have a lot of older crappy systems that require SQL authentication so hundreds of database users that we have to worry with. Sounds like you are rather lucky in that respect. It is ironic that you posted this question today because last night and this morning I just setup mirroring on a 250 GB database for a migration that is happening next week. That is 6 hours I don't have to spend working during the migration.
May 17, 2011 at 01:45 PM Tim
Hahah good timing then. Would you say it's easier if all your users are Windows Auth users then? Because, long term, we were tossing around the idea of taking the SQL Server out of the domain entirely... I'm not convinced that we see a lot of benefit from Windows Auth, right at the moment...
May 17, 2011 at 02:13 PM Matt Whitfield ♦♦
For me adding an Active Directory group to a SQL server and granting permissions to the group is much easier than managing hundreds of local SQL accounts or worse local Windows Accounts. With AD Groups you can add the group on the primary and secondary server grant permissions and be done with it. If users authenticate to the app, then the app has a service level account to SQL then you really don't have to be in the domain. Remove it and remove that risk.
May 17, 2011 at 02:17 PM Tim
Yeah, it's the users authenticate to the app scenario that I'm in. I do wish that SQL Server supported that a bit better... :) Thanks for all your comments...
May 17, 2011 at 03:08 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
* can you please explain how to make sure that security settings for account have proper authentication, I created a new user and login for mirroring ...is there any steps that I can follow ???
more ▼

answered Jun 06, 2011 at 01:06 AM

coorsktm gravatar image

21 2 2 3

(comments are locked)
10|1200 characters needed characters left

Ok, I have some points to share now:

When you set up mirroring, and get this error:

The server network address TCP://..com: 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)

You might, maybe, be tempted to believe that the network address was wrong, or that one side or other couldn't connect to the port. However, it can also mean

  • That when you restored the backup from the primary to do the initial seed of the mirroring, you did not restore it WITH NORECOVERY and therefore mirroring couldn't keep restoring the logs from the primary
  • That the security settings for the account under which SQL Server is running are causing it to have authentication problems when talking to the other server
So yeah, they had me bummed out for a while... :)
more ▼

answered May 27, 2011 at 08:08 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

We mirror between Head Office and our DR centre. The biggest issue we have is the time taken to get the backup(s) over to the DR centre. Our network isnt too fast and it is hours in the process, then the log backup needs to be done and copied and etc etc. We usually schedule this for a weekend so that the transfer doesnt affect normal business traffic on the network and there are fewer updates during the copy and the log backup is then much smaller.

My advice - practice it at least once. Even if its on AdventureWorks, just so as you know what the steps are and what does, doesnt fit with the BOL version.

Its a rock solid tech though, we get very few issues and are pleased we changed to it from replication. From SQLBits Klaus Aschenbrenner said that its actually service broker under the covers, I guess he should know!

We dont use auto failover as we dont want false positives and the secondary at the DR centre becoming live. the backup solution would then have to be swapped over too and all sorts of extra work would be required. We dont have a witness either, mostly as we are not wanting auto-failover.
more ▼

answered May 17, 2011 at 03:48 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

We don't use auto failover either. I don't have that much confidence in our network. No issues with it either in my shop other than occasionally a mirrored db will get in a paused state, not sure what has caused that on two occasions in 2 years of using mirroring. A simple monitor job checks for that and alerts me though. The only impact other than not being in sync is the transaction log keeps growing until the mirror is started back. I have yet to have to a situation where I had to redo mirroring like I used to have to do with logshipping.
May 17, 2011 at 03:53 PM Tim
We wouldn't be going for auto-failover either - so that's really useful, thank you sir!
May 17, 2011 at 11:32 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

Firewalls and authentication are two that come to mind.

Other than that it works pretty smooth.
more ▼

answered May 17, 2011 at 01:02 PM

Blackhawk-17 gravatar image

11.9k 28 31 37

I vote for a world ban on Firewalls ;-)
May 17, 2011 at 01:24 PM sp_lock
Awesome - thank you sir...
May 17, 2011 at 01:41 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 17, 2011 at 12:44 PM

Seen: 1771 times

Last Updated: May 17, 2011 at 12:44 PM