Database Mirroring - keeping logins/jobs etc. up to date

Question

Database Mirroring - keeping logins/jobs etc. up to date

julia.nicholas

Mr or Mrs. 500

Points: 542
More actions
April 1, 2008 at 5:19 am

#128400

As part of my studies for the MCTS exam I've been trying out database mirroring. I understand that logins, jobs etc. need to be transferred to the mirror server as these are server level objects and therefore not mirrored in database mirroring.
OK, I thought, that makes sense, so using SSIS I made a very simple package that does a Transfer Login for the database being mirrored. Great... until you have mirroring in action, then, because the mirror database is in recovery mode you can't transfer the logins.
Does that mean in a High Availability environment, your Principal and Mirror swap roles on failure but then you have to run a job to transfer the logins and jobs across? What if the Principal is totally dead and you can't get to the objects to transfer them?
How are you folks out there who are using HA mirroring in production environments where users and jobs are in a constant state of flux manage this?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Andy sql SSCrazy Eights Points: 9454 More actions · Answer 1

For Logins, my advice is to stick with Windows Authentication only. That way, all logins are via domain users/groups - no logins need to be transferred.

Doesn't answer the main part of your question though.... My plan is to use SSIS to save the various agent jobs, etc, as a package. When a failover occurs, run the SSIS package on the new Principal (ex-Mirror). You could schedule this job to run as soon as the Mirror Database changes to become the Principal.

Andy

K9998 SSCommitted Points: 1817 More actions · Answer 2

I can address the login but not the jobs. Look for this article. 'Login failures connecting to new principal after failover using Database Mirroring'. It was published on http://www.mssqltips.com in January 2007.

Basically what you have to do is to set up your SQL Server user ids on the principal. The article has a script to script your users including their sids and passwords. Cause the principal database to failover and run your user script on the new principal.

julia.nicholas Mr or Mrs. 500 Points: 542 More actions · Answer 3

AndyD (4/2/2008)
For Logins, my advice is to stick with Windows Authentication only. That way, all logins are via domain users/groups - no logins need to be transferred.

True, but you still need to map the new users created to the database in question which you can't do when the db is in recovery. Plus in our environment we have applications that are service-based so we have no choice but to use SQL Authentication in some cases.

The more I look at this, the more it doesn't seem like such a "high availability" option! or at least not the hands-off automatic failover tool it's punted as... unless you are working in a static environment where users and jobs etc. don't get changed after the mirror is set up of course, then it's great!

Andy sql SSCrazy Eights Points: 9454 More actions · Answer 4

Typically, in a production environment, logins, Agent jobs, etc do not change much (for the specific mirrored database in question). Other databases might be added, with their own logins, etc, but that won't affect the mirrored database itself.

But I do agree with you, Database Mirroring is not the perfect high-availability solution. It is, however, very, very cheap compared to setting up a Microsoft Cluster.

bbr SSChasing Mays Points: 613 More actions · Answer 5

AndyD (4/3/2008)
...Database Mirroring is not the perfect high-availability solution. It is, however, very, very cheap compared to setting up a Microsoft Cluster.

I don't agree. Database Mirroring is not a cheap alternative to Clustering. They both provide very different feature sets and serve different purposes.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

julia.nicholas (4/3/2008)
AndyD (4/2/2008)
For Logins, my advice is to stick with Windows Authentication only. That way, all logins are via domain users/groups - no logins need to be transferred.
True, but you still need to map the new users created to the database in question which you can't do when the db is in recovery.

The users will be transfered as part of the mirroring (since they're DB objects)

If you create the logins on the mirror server with the same SID as on the principal, then the logins and users will automatically match and you won't have to do any fixing.

The CREATE LOGIN command allows you to specify a (hashed) password and a SID

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bbr SSChasing Mays Points: 613 More actions · Answer 7

Our practice for mirrored databases:

1. Create new server logins on primary

2. Transfer to mirror using SQL script

3. Add db users to principal database. (Mirroring activity will move db user to mirror database)

Andy sql SSCrazy Eights Points: 9454 More actions · Answer 8

bbr (4/4/2008)
AndyD (4/3/2008)
...Database Mirroring is not the perfect high-availability solution. It is, however, very, very cheap compared to setting up a Microsoft Cluster.
I don't agree. Database Mirroring is not a cheap alternative to Clustering. They both provide very different feature sets and serve different purposes.

I am genuinely interested in your views here. If you ignore the hardware setup, OS setup and costs (eg. MSCS requires Windows Server 2003 Enterprise or Datacenter editions, very exacting standards for hardware, etc), where are the differences?

Yes they are based on different technologies (transaction logging/shipping vs. virtual resources), and yes a Mirror is per database not per Instance. But if you are talking about a high-availability solution, with automatic failover, for a database, what are the differences?

The limitation of using "SQL Native Client" DSN is a factor (or the application being mirror-aware). But what else?

Andy

bbr SSChasing Mays Points: 613 More actions · Answer 9

AndyD,

It's always informative to get views and opinions from different people. Over the years, many of the practices I follow started out as somebody else's opinion.

I do agree that there is a lot of similarity between mirroring and clustering. You could even throw log shipping into the mix, in some sense. The differences come in the details in what they do and do not protect against. I find it useful to sometimes combine methods. For example, you might build a cluster but also log ship the database to an off-site system.

Clustering will only protect against the failure of a duplicated component. A typical entry-level cluster (2 servers + shared array) will not protect you against an array failure. I had this happen at a site. There was a problem with the backplane of their disk array. Unfortunately, one other "cost-saving" decision they made was not to get the 24*7 hardware service agreement. It took 3 days to get replacement parts. (and yes this was a top tier vendor) There are also the other components like switches and cables that people tend to not duplicate in order to cut budget. Clustering will also not protect against outside issues like a building fire.

Another thing is not to look at these features in a vacuum. You can install the most expensive clustered/mirrored system, but if your cheap network switch fails, your users still can't work.

I'm off to deal with billable work...😎

Andy sql SSCrazy Eights Points: 9454 More actions · Answer 10

Sounds like you haven't had the best luck with MS Cluster. Absolutely agree that the network infrastructure is just as important. If nothing else, you'll get frequent unnecessary failovers with a shoddy network.

My main problem at the moment is performance with my Mirror plus Witness. I did ask a question a while back:

http://qa.sqlservercentral.com/Forums/Topic467633-360-1.aspx

but got no responses. Ah well.

Thanks for you thoughts!

Andy

bbr SSChasing Mays Points: 613 More actions · Answer 11

Situations that would push me toward clustering might be where I have multiple databases that are dependent on each other or other services running that that interact with the databases.

bbr SSChasing Mays Points: 613 More actions · Answer 12

I don't have a lot of mirroring out there. After all it is new technology. One site I work at a lot we are mirroring to a server in another building about 1/2 mile away. We are using full-safety sycronous and were running without the mirror for some time. Since mirroring we are seeing no noticable performance drop.

Andy sql SSCrazy Eights Points: 9454 More actions · Answer 13

Yep, good point; inter-dependent databases are not something I would rely on Database Mirroring to handle nicely.

julia.nicholas Mr or Mrs. 500 Points: 542 More actions · Answer 14

GilaMonster (4/4/2008)The users will be transfered as part of the mirroring (since they're DB objects)

Of course, duh! I didn't think of that. So basically as long as when changes are made on the Principal server in terms of adding new jobs and SQL Logins, jobs etc. you just need to do it twice over. More a case of disciplined procedures to keep this good. I'll have a play...

Thanks.