Mirror database, what about logins?

I have setup SQL Server 2005 SP1 with mirror of a database to another
server.
How am I supposed to duplicate the logins between the two servers and map
the logins defined at the server level to the database level?
OlavSSIS has a task that allows you to transfer logins. You have to carefully
plan the two servers to ensure that logins created on the principal(s) are
also created on the mirror.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Olav" <x@.y.com> wrote in message
news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>I have setup SQL Server 2005 SP1 with mirror of a database to another
>server.
> How am I supposed to duplicate the logins between the two servers and map
> the logins defined at the server level to the database level?
> Olav
>|||Yes, I understand that.
But, even if the logins are transferred they will not be mapped correctly to
the database after the failover from the Principal to the Mirror server.
It looks like I have to issue a command like this for every login in the
database:
USE Commerce;
GO
sp_change_users_login @.Action='update_one',
@.UserNamePattern='CommerceWebUser', @.LoginName='CommerceWebUser';
GO
How are you supposed to do an automatic failover when you have to issue
those kind of commands to get the Logins to work?
Olav
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
> SSIS has a task that allows you to transfer logins. You have to carefully
> plan the two servers to ensure that logins created on the principal(s) are
> also created on the mirror.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Olav" <x@.y.com> wrote in message
> news:eoeJ7GlaGHA.4564@.TK2MSFTNGP03.phx.gbl...
>|||You have to make that part of the process when it fails over. To do it in
an automated way, you are going to need to create a job that checks the
state and executes a script to remap the logins in the event of a failure.
Database Mirroring does not handle logins or do anything at all to
synchronize them. This is by design. You can mirror multiple databases all
into the same SQL Server instance and if it automatically moved logins, it
could create major security problems. So, anything external to the database
is entirely up to you which means you have to handle the logins, linked
servers, remote servers, SSIS packages, custom error messages, jobs, and any
other server level objects.
Mirroring also does not handle multi-database issues. For example say you
had the following databases: customers, inventory, orders. When a customer
places an order you need to debit their remaining credit line, decrease
inventory, and insert into order + order detail table. From your
application's perspective, this is a single, atomic transaction spanning 3
databases. To SQL Server this is 3 completely independent transactions
since a transaction can not span databases. So, you can create issues
during a failover where the data does not match up from a business
perspective (credit line debited and inventory debited, but the order
doesn't exist because it was rolled back during the failover), but is
perfectly valid within each of the databases.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Olav" <x@.y.com> wrote in message
news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Yes, I understand that.
> But, even if the logins are transferred they will not be mapped correctly
> to the database after the failover from the Principal to the Mirror
> server.
> It looks like I have to issue a command like this for every login in the
> database:
> USE Commerce;
> GO
> sp_change_users_login @.Action='update_one',
> @.UserNamePattern='CommerceWebUser', @.LoginName='CommerceWebUser';
> GO
> How are you supposed to do an automatic failover when you have to issue
> those kind of commands to get the Logins to work?
> Olav
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>|||How about using sp_help_revlogin instead?
http://support.microsoft.com/kb/246133/en-us
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav" <x@.y.com> wrote in message news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...en">
> Yes, I understand that.
> But, even if the logins are transferred they will not be mapped correctly
to the database after
> the failover from the Principal to the Mirror server.
> It looks like I have to issue a command like this for every login in the d
atabase:
> USE Commerce;
> GO
> sp_change_users_login @.Action='update_one', @.UserNamePattern='CommerceWebU
ser',
> @.LoginName='CommerceWebUser';
> GO
> How are you supposed to do an automatic failover when you have to issue th
ose kind of commands to
> get the Logins to work?
> Olav
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:%23CC%23tXnaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>|||Where can I find a sample of such script?
It's hard to belive that this is not provided out of the box.
Olav
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uQ57htoaGHA.3376@.TK2MSFTNGP05.phx.gbl...
> You have to make that part of the process when it fails over. To do it in
> an automated way, you are going to need to create a job that checks the
> state and executes a script to remap the logins in the event of a failure.
> Database Mirroring does not handle logins or do anything at all to
> synchronize them. This is by design. You can mirror multiple databases
> all into the same SQL Server instance and if it automatically moved
> logins, it could create major security problems. So, anything external to
> the database is entirely up to you which means you have to handle the
> logins, linked servers, remote servers, SSIS packages, custom error
> messages, jobs, and any other server level objects.
> Mirroring also does not handle multi-database issues. For example say you
> had the following databases: customers, inventory, orders. When a
> customer places an order you need to debit their remaining credit line,
> decrease inventory, and insert into order + order detail table. From your
> application's perspective, this is a single, atomic transaction spanning 3
> databases. To SQL Server this is 3 completely independent transactions
> since a transaction can not span databases. So, you can create issues
> during a failover where the data does not match up from a business
> perspective (credit line debited and inventory debited, but the order
> doesn't exist because it was rolled back during the failover), but is
> perfectly valid within each of the databases.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Olav" <x@.y.com> wrote in message
> news:ufeqTQoaGHA.1192@.TK2MSFTNGP03.phx.gbl...
>|||I don't know of a sample for the script. You launch SSIS, select the
transfer logins task, save the package. You can also build your own using
sp_help_revlogin.
There is a reason that it is not provided and this is not done
automatically. That is because it CAN'T be done automatically. I can have
Inst1, Inst2, and Inst3 in my environment. DB1 on Inst1 is mirrored to
Inst3. DB2 on Inst2 is mirrored to Inst3. Inst1 has a SQL Server login
called mylogin that has access to DB1. Inst2 has a login called mylogin
that has access to DB2. The user accessing DB1 does not have the authority
to access DB2 and vice versa. BUT, I am mirroring both of them to Inst3.
Just how is mirroring supposed to resolve this? (Should you have that type
of configuration, no. But, it is possible and is just one of the dozens of
things that prevent this stuff from being done automatically.) And before
someone says, "I don't have that problem", they aren't designing software
for just a single system, this has to work reliably on every system that is
out there. So, unfortunately, everything is designed very conservatively,
especially when it comes to security implications.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Olav" <x@.y.com> wrote in message
news:ul7DEevaGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Where can I find a sample of such script?
> It's hard to belive that this is not provided out of the box.
> Olav
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:uQ57htoaGHA.3376@.TK2MSFTNGP05.phx.gbl...
>|||Database mirroring is obviously designed to be an automated failover
solution, right?
I'm not asking for how to replicate the logins, I have already done that.
What I'm asking for is how to automate the failover process. I don't sit
besides my server and watch it until it failover in order to fix the logins
manually.
How do you design a job to run after the failover is complete so the logins
can be fixed? Can a job be started automatically right after the failover of
the database has completed?
Olav
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u9FPEryaGHA.508@.TK2MSFTNGP02.phx.gbl...
>I don't know of a sample for the script. You launch SSIS, select the
>transfer logins task, save the package. You can also build your own using
>sp_help_revlogin.
> There is a reason that it is not provided and this is not done
> automatically. That is because it CAN'T be done automatically. I can
> have Inst1, Inst2, and Inst3 in my environment. DB1 on Inst1 is mirrored
> to Inst3. DB2 on Inst2 is mirrored to Inst3. Inst1 has a SQL Server
> login called mylogin that has access to DB1. Inst2 has a login called
> mylogin that has access to DB2. The user accessing DB1 does not have the
> authority to access DB2 and vice versa. BUT, I am mirroring both of them
> to Inst3. Just how is mirroring supposed to resolve this? (Should you
> have that type of configuration, no. But, it is possible and is just one
> of the dozens of things that prevent this stuff from being done
> automatically.) And before someone says, "I don't have that problem",
> they aren't designing software for just a single system, this has to work
> reliably on every system that is out there. So, unfortunately, everything
> is designed very conservatively, especially when it comes to security
> implications.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Olav" <x@.y.com> wrote in message
> news:ul7DEevaGHA.1196@.TK2MSFTNGP03.phx.gbl...
>sql