Mirroring Failover/Failback

I have been using Mirroring now for a while and put the system into
production now after applying SQL sp 1.
We have a setup with a witness, principal and mirror with certificates (the
witness is on a webserver in a different domain, so we can't use integrated
security).
Automatic failover works fine if we take down the principal. But when the
principal is up and running again, no automatic failback occur. If this by
design, or is there something I've missed out in the configuration?
Per SchjetneThe roles for mirroring are soft roles. So, you cannot define a preferred no
de (like you can for a
cluster). If you want to fail back, you have to ask for it (ALTER DATABASE).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into product
ion now after applying
>SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates (th
e witness is on a
> webserver in a different domain, so we can't use integrated security).
> Automatic failover works fine if we take down the principal. But when the
principal is up and
> running again, no automatic failback occur. If this by design, or is there
something I've missed
> out in the configuration?
> Per Schjetne
>|||"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates
> (the witness is on a webserver in a different domain, so we can't use
> integrated security).
> Automatic failover works fine if we take down the principal. But when the
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?
Sorry, there isn't an option for automatic failback. You will have to do
that manually.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp|||If you think about it, failing back will cause all client connections to be
dropped again so you probably don't want SQL Server to decide when is a good
time to fail back. It's probably much better if you pick a time when the
change won't affect too many users.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates
> (the witness is on a webserver in a different domain, so we can't use
> integrated security).
> Automatic failover works fine if we take down the principal. But when the
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?
> Per Schjetne
>|||I agree on that. Is there a way to query any system tables telling me which
database is acting as principal vs mirror? Is the witness aware of this, or
is it just monitoring which server is replying, being able to do a failover?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
> If you think about it, failing back will cause all client connections to
> be dropped again so you probably don't want SQL Server to decide when is a
> good time to fail back. It's probably much better if you pick a time when
> the change won't affect too many users.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>|||There's an event that you can monitor to be notified when the state changes.
The sys.dm_database_mirroring view can be used to determine the mirroring
state of all the databases of an instance. The
sys.dm_database_mirroring_witnesses can be used from the witness to see
which server is the primary.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:OLn03SiaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>I agree on that. Is there a way to query any system tables telling me which
>database is acting as principal vs mirror? Is the witness aware of this, or
>is it just monitoring which server is replying, being able to do a
>failover?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>|||That's not exactly correct.
The reason it did not failback, is because that database is no longer the
principal. Failover causes a role reversal. When it fails over to the
mirror, that database is promoted to a principal and begins serving the
database. When the other database comes online, its role is automatically
demoted to mirror and the transactions start flowing back to it.
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.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDV9lIeaGHA.4936@.TK2MSFTNGP05.phx.gbl...
> The roles for mirroring are soft roles. So, you cannot define a preferred
> node (like you can for a cluster). If you want to fail back, you have to
> ask for it (ALTER DATABASE).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>|||Thanks! Just what I needed. By the way, the correct tablename is:
sys.database_mirroring_witnesses
Per :-)
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23bsLDGkaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> There's an event that you can monitor to be notified when the state
> changes.
> The sys.dm_database_mirroring view can be used to determine the mirroring
> state of all the databases of an instance. The
> sys.dm_database_mirroring_witnesses can be used from the witness to see
> which server is the primary.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:OLn03SiaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>

Mirroring Failover/Failback

I have been using Mirroring now for a while and put the system into
production now after applying SQL sp 1.
We have a setup with a witness, principal and mirror with certificates (the
witness is on a webserver in a different domain, so we can't use integrated
security).
Automatic failover works fine if we take down the principal. But when the
principal is up and running again, no automatic failback occur. If this by
design, or is there something I've missed out in the configuration?
Per SchjetneThe roles for mirroring are soft roles. So, you cannot define a preferred node (like you can for a
cluster). If you want to fail back, you have to ask for it (ALTER DATABASE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into production now after applying
>SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates (the witness is on a
> webserver in a different domain, so we can't use integrated security).
> Automatic failover works fine if we take down the principal. But when the principal is up and
> running again, no automatic failback occur. If this by design, or is there something I've missed
> out in the configuration?
> Per Schjetne
>|||"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates
> (the witness is on a webserver in a different domain, so we can't use
> integrated security).
> Automatic failover works fine if we take down the principal. But when the
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?
Sorry, there isn't an option for automatic failback. You will have to do
that manually.
--
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp|||If you think about it, failing back will cause all client connections to be
dropped again so you probably don't want SQL Server to decide when is a good
time to fail back. It's probably much better if you pick a time when the
change won't affect too many users.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates
> (the witness is on a webserver in a different domain, so we can't use
> integrated security).
> Automatic failover works fine if we take down the principal. But when the
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?
> Per Schjetne
>|||I agree on that. Is there a way to query any system tables telling me which
database is acting as principal vs mirror? Is the witness aware of this, or
is it just monitoring which server is replying, being able to do a failover?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
> If you think about it, failing back will cause all client connections to
> be dropped again so you probably don't want SQL Server to decide when is a
> good time to fail back. It's probably much better if you pick a time when
> the change won't affect too many users.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when the
>> principal is up and running again, no automatic failback occur. If this
>> by design, or is there something I've missed out in the configuration?
>> Per Schjetne
>|||There's an event that you can monitor to be notified when the state changes.
The sys.dm_database_mirroring view can be used to determine the mirroring
state of all the databases of an instance. The
sys.dm_database_mirroring_witnesses can be used from the witness to see
which server is the primary.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:OLn03SiaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>I agree on that. Is there a way to query any system tables telling me which
>database is acting as principal vs mirror? Is the witness aware of this, or
>is it just monitoring which server is replying, being able to do a
>failover?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> If you think about it, failing back will cause all client connections to
>> be dropped again so you probably don't want SQL Server to decide when is
>> a good time to fail back. It's probably much better if you pick a time
>> when the change won't affect too many users.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
>> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when
>> the principal is up and running again, no automatic failback occur. If
>> this by design, or is there something I've missed out in the
>> configuration?
>> Per Schjetne
>>
>|||That's not exactly correct.
The reason it did not failback, is because that database is no longer the
principal. Failover causes a role reversal. When it fails over to the
mirror, that database is promoted to a principal and begins serving the
database. When the other database comes online, its role is automatically
demoted to mirror and the transactions start flowing back to it.
--
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.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDV9lIeaGHA.4936@.TK2MSFTNGP05.phx.gbl...
> The roles for mirroring are soft roles. So, you cannot define a preferred
> node (like you can for a cluster). If you want to fail back, you have to
> ask for it (ALTER DATABASE).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when the
>> principal is up and running again, no automatic failback occur. If this
>> by design, or is there something I've missed out in the configuration?
>> Per Schjetne
>|||Thanks! Just what I needed. By the way, the correct tablename is:
sys.database_mirroring_witnesses
Per :-)
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23bsLDGkaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> There's an event that you can monitor to be notified when the state
> changes.
> The sys.dm_database_mirroring view can be used to determine the mirroring
> state of all the databases of an instance. The
> sys.dm_database_mirroring_witnesses can be used from the witness to see
> which server is the primary.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:OLn03SiaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>>I agree on that. Is there a way to query any system tables telling me
>>which database is acting as principal vs mirror? Is the witness aware of
>>this, or is it just monitoring which server is replying, being able to do
>>a failover?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> If you think about it, failing back will cause all client connections to
>> be dropped again so you probably don't want SQL Server to decide when is
>> a good time to fail back. It's probably much better if you pick a time
>> when the change won't affect too many users.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
>> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when
>> the principal is up and running again, no automatic failback occur. If
>> this by design, or is there something I've missed out in the
>> configuration?
>> Per Schjetne
>>
>>
>

Mirroring + remoting

Hi,

I have a 3-tier app that is currently running on a simple single server setup. However, I have to adapt it to run on the following setup:

1) Client .NET app on client systems with remoting connection to...

2) Host .NET app with a DAL connecting to ...

3) SQL Server 2005 mirrored

Using mirroring, if the principle SQL server goes down it switches automatically to the mirror server.

My question relates to my connections. What do I need to do to make sure the Client and Host apps follow SQL?

There are two setups I have in mind:

1) Host app + SQL on same box

2) Host app on its own server + SQL on its server (+mirror)

With these scenarios:

Setup 1 - SQL fails but server continues - Host app has to connect to mirror - clients continue to connect to Host on principle server.

Setup 1 - Whole server fails - Host on mirror server connects to mirror SQL and clints now have to locate Host on mirror.

Setup 2 - SQL's server fails so mirror switches in. Only Host app has to re-direct. Clients connect to same Host app.

This is new to me, so if anyone can advise, or direct me to the right info.

Hi,

I assume your solution employs the Presentation Layer <-> Business Layer <-> Data Layer architecture. Since you want to maximise transparency to the client application (pres layer), in my opinion, it's better to go with option 2 and put the host app on a separate server from the database to decrease the probability of multiple failures (if the machine with host + sql goes down - both will be inaccessible).

If you do this and code the host app to use ADO.NET to connect to the database, you could take advantage of the ability of the SqlConnection object to automatically re-connect to the failover partner in a mirrored configuration. This MSDN article explains how this works - http://msdn2.microsoft.com/en-us/library/5h52hef8.aspx.

In addition, if you want complete transparency to the client application, you will still have to make it handle the case where the host fails.

-- Kaloian.

|||

Thanks Kaloian,

That leaves me with the problem of having the clinet apps fail over to another server when the host app's server fails. I guess I'm asking is there a remoting option to fail over, similar to the Failover Partner attribute in the sql connection string? Or am I going to have to trap the failure and code the switch?

|||

As far as I know there's no such mechanism, but you may want to post this question to the .NET Remoting newsgroup - http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=41&SiteID=1

- Kaloian.

Mirroring + remoting

Hi,

I have a 3-tier app that is currently running on a simple single server setup. However, I have to adapt it to run on the following setup:

1) Client .NET app on client systems with remoting connection to...

2) Host .NET app with a DAL connecting to ...

3) SQL Server 2005 mirrored

Using mirroring, if the principle SQL server goes down it switches automatically to the mirror server.

My question relates to my connections. What do I need to do to make sure the Client and Host apps follow SQL?

There are two setups I have in mind:

1) Host app + SQL on same box

2) Host app on its own server + SQL on its server (+mirror)

With these scenarios:

Setup 1 - SQL fails but server continues - Host app has to connect to mirror - clients continue to connect to Host on principle server.

Setup 1 - Whole server fails - Host on mirror server connects to mirror SQL and clints now have to locate Host on mirror.

Setup 2 - SQL's server fails so mirror switches in. Only Host app has to re-direct. Clients connect to same Host app.

This is new to me, so if anyone can advise, or direct me to the right info.

Hi,

I assume your solution employs the Presentation Layer <-> Business Layer <-> Data Layer architecture. Since you want to maximise transparency to the client application (pres layer), in my opinion, it's better to go with option 2 and put the host app on a separate server from the database to decrease the probability of multiple failures (if the machine with host + sql goes down - both will be inaccessible).

If you do this and code the host app to use ADO.NET to connect to the database, you could take advantage of the ability of the SqlConnection object to automatically re-connect to the failover partner in a mirrored configuration. This MSDN article explains how this works - http://msdn2.microsoft.com/en-us/library/5h52hef8.aspx.

In addition, if you want complete transparency to the client application, you will still have to make it handle the case where the host fails.

-- Kaloian.

|||

Thanks Kaloian,

That leaves me with the problem of having the clinet apps fail over to another server when the host app's server fails. I guess I'm asking is there a remoting option to fail over, similar to the Failover Partner attribute in the sql connection string? Or am I going to have to trap the failure and code the switch?

|||

As far as I know there's no such mechanism, but you may want to post this question to the .NET Remoting newsgroup - http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=41&SiteID=1

- Kaloian.

Mirroring

We are moving our asp application from SQL2k to SQL2k5. I implemented
database mirroring for my critical databases, which is setup & running. I
modified the connection string in the Global.asa to point to my new servers.
When both SQL servers are up & running I connect to the primary database.
However, when the server hosting the primary database is stopped, I get an
error regarding the connection string.
Is this an application error that needs to be resolved? What do I tell the
developers to do?
Is this a problem with the SQL connection string? Does anyone have working
examples?
Also, I am using a witness server.
Does anyone have any test procedures/scripts/scenarios to insure mirroring
is working as it should. I thought I could stop SQL on a server run a test,
restart SQL & then check the databases.
I'm ok at server & database admin but poor at database programming. And
brand new to SQL2k5 mirroring.
Thanks"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> We are moving our asp application from SQL2k to SQL2k5. I implemented
> database mirroring for my critical databases, which is setup & running. I
> modified the connection string in the Global.asa to point to my new
> servers.
> When both SQL servers are up & running I connect to the primary database.
> However, when the server hosting the primary database is stopped, I get an
> error regarding the connection string.
> Is this an application error that needs to be resolved? What do I tell the
> developers to do?
> Is this a problem with the SQL connection string? Does anyone have working
> examples?
> Also, I am using a witness server.
> Does anyone have any test procedures/scripts/scenarios to insure mirroring
> is working as it should. I thought I could stop SQL on a server run a
> test,
> restart SQL & then check the databases.
> I'm ok at server & database admin but poor at database programming. And
> brand new to SQL2k5 mirroring.
>
Does your connection string specify both the principal and the mirror? If
not, failover will work, but startup when the principal is down will not.
See:
System.Data.SqlConnection.ConnectionString Property
Failover Partner
N/A
The name of the failover partner server where database mirroring is
configured.
The Failover Partner keyword is not supported by .NET Framework version 1.0
or 1.1.
http://msdn2.microsoft.com/en-us/li...tionstring.aspx
David|||"David Browne" wrote:

>
> "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> Does your connection string specify both the principal and the mirror? If
> not, failover will work, but startup when the principal is down will not.
> See:
> System.Data.SqlConnection.ConnectionString Property
> Failover Partner
> N/A
> The name of the failover partner server where database mirroring is
> configured.
> The Failover Partner keyword is not supported by .NET Framework version 1.
0
> or 1.1.
> http://msdn2.microsoft.com/en-us/li...tionstring.aspx
>
> David
> This is the connection string set in the Global.asa:
Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
Also, we're not using asp.net - just plain old ASP.|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:E89D67CA-4BA6-4A87-B053-F3F10BC1D3AC@.microsoft.com...
>
> "David Browne" wrote:
>
> Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
> Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
> Also, we're not using asp.net - just plain old ASP.
>
The OleDb provider you're using isn't mirroring-aware. The SQLNCLI the new
SqlServer Native Client provider, if it works correctly with your
application. See:
Using ADO with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130978.aspx
If you stick with SQLOLEDB, then you can use DNS or BigIP to manually switch
the traffic between servers, or just change your config and boune the site.
David|||Thanks for your reply.
Yesterday & was able to determine that without mirroring I could connect to
each server with the SQL login. However, with mirroring enabled I could only
connect to SQL1 with the SQL login. I began to suspect the SID & found this
article: http://support.microsoft.com/default.aspx/kb/918992/
"Magas" wrote:
[vbcol=seagreen]
> If you use SQL Server login to connect to the mirror database - make sure
> logins on both mirror servers have the same SID. The easiest way to do it
-
> copy login from one server to another using SSIS package.
>
> "BigSam" wrote:
>

Mirroring

We are moving our asp application from SQL2k to SQL2k5. I implemented
database mirroring for my critical databases, which is setup & running. I
modified the connection string in the Global.asa to point to my new servers.
When both SQL servers are up & running I connect to the primary database.
However, when the server hosting the primary database is stopped, I get an
error regarding the connection string.
Is this an application error that needs to be resolved? What do I tell the
developers to do?
Is this a problem with the SQL connection string? Does anyone have working
examples?
Also, I am using a witness server.
Does anyone have any test procedures/scripts/scenarios to insure mirroring
is working as it should. I thought I could stop SQL on a server run a test,
restart SQL & then check the databases.
I'm ok at server & database admin but poor at database programming. And
brand new to SQL2k5 mirroring.
Thanks
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> We are moving our asp application from SQL2k to SQL2k5. I implemented
> database mirroring for my critical databases, which is setup & running. I
> modified the connection string in the Global.asa to point to my new
> servers.
> When both SQL servers are up & running I connect to the primary database.
> However, when the server hosting the primary database is stopped, I get an
> error regarding the connection string.
> Is this an application error that needs to be resolved? What do I tell the
> developers to do?
> Is this a problem with the SQL connection string? Does anyone have working
> examples?
> Also, I am using a witness server.
> Does anyone have any test procedures/scripts/scenarios to insure mirroring
> is working as it should. I thought I could stop SQL on a server run a
> test,
> restart SQL & then check the databases.
> I'm ok at server & database admin but poor at database programming. And
> brand new to SQL2k5 mirroring.
>
Does your connection string specify both the principal and the mirror? If
not, failover will work, but startup when the principal is down will not.
See:
System.Data.SqlConnection.ConnectionString Property
Failover Partner
N/A
The name of the failover partner server where database mirroring is
configured.
The Failover Partner keyword is not supported by .NET Framework version 1.0
or 1.1.
[url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstri ng.aspx[/url]
David
|||"David Browne" wrote:

>
> "BigSam" <BigSam@.discussions.microsoft.com> wrote in message
> news:26E0585C-23F3-4C84-B466-B8EFB44504B4@.microsoft.com...
> Does your connection string specify both the principal and the mirror? If
> not, failover will work, but startup when the principal is down will not.
> See:
> System.Data.SqlConnection.ConnectionString Property
> Failover Partner
> N/A
> The name of the failover partner server where database mirroring is
> configured.
> The Failover Partner keyword is not supported by .NET Framework version 1.0
> or 1.1.
> [url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstri ng.aspx[/url]
>
> David
> This is the connection string set in the Global.asa:
Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
Also, we're not using asp.net - just plain old ASP.
|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:E89D67CA-4BA6-4A87-B053-F3F10BC1D3AC@.microsoft.com...
>
> "David Browne" wrote:
> Application("ConnectionString") = "Provider=sqloledb;Server=SQL1;Failover
> Partner=SQL2;Initial Catalog=MainDB;UID=TestUser;Pwd=Test99"
> Also, we're not using asp.net - just plain old ASP.
>
The OleDb provider you're using isn't mirroring-aware. The SQLNCLI the new
SqlServer Native Client provider, if it works correctly with your
application. See:
Using ADO with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130978.aspx
If you stick with SQLOLEDB, then you can use DNS or BigIP to manually switch
the traffic between servers, or just change your config and boune the site.
David
|||Thanks for your reply.
Yesterday & was able to determine that without mirroring I could connect to
each server with the SQL login. However, with mirroring enabled I could only
connect to SQL1 with the SQL login. I began to suspect the SID & found this
article: http://support.microsoft.com/default.aspx/kb/918992/
"Magas" wrote:
[vbcol=seagreen]
> If you use SQL Server login to connect to the mirror database - make sure
> logins on both mirror servers have the same SID. The easiest way to do it -
> copy login from one server to another using SSIS package.
>
> "BigSam" wrote:
sql

Mirrored linked server failover.

We have a mirrored db setup, with a third server that needs to be linked to this mirrored setup as a linked server. The link server setup only allows you to specify the primary server. How will the linked server setup know what the secondary server is? Thanks

It's not clear which way you are creating the linked server:

If it is from the primary->third server then you will need to create the linked server on both the principal and mirror since it does not automatically failover (it's defined at the instance level and not the database level).

If it is from the third server->primary then ensure you set the failover partner property in the linked server and that the default database is specified (catalog in provider speak). For example

EXEC master.dbo.sp_addlinkedserver

@.server = N'matthol11',

@.srvproduct=N'',

@.provider=N'SQLNCLI',

@.catalog=N'test',

@.provstr=N'Server=matthol11;FailoverPartner=matthol6;'

select*from matthol11.test.dbo.test

Regards,

Matt

Mirrored linked server failover.

We have a mirror setup, with a third server that needs to be linked to this mirrored setup. The link server setup only allows you to specify the primary server. How will the linked server setup know what the secondary server is? Thanks

Andre,

You can specify the partner name in the linked server server properties and the linked server will automatically failover from the principal to the mirror if the principal is failed over.

Regards,

Matt Hollingswort

Sr. Program Manager

Microsoft SQL Server

sql

Mirrored linked server failover.

We have a mirrored db setup, with a third server that needs to be linked to this mirrored setup as a linked server. The link server setup only allows you to specify the primary server. How will the linked server setup know what the secondary server is? Thanks

It's not clear which way you are creating the linked server:

If it is from the primary->third server then you will need to create the linked server on both the principal and mirror since it does not automatically failover (it's defined at the instance level and not the database level).

If it is from the third server->primary then ensure you set the failover partner property in the linked server and that the default database is specified (catalog in provider speak). For example

EXEC master.dbo.sp_addlinkedserver

@.server = N'matthol11',

@.srvproduct=N'',

@.provider=N'SQLNCLI',

@.catalog=N'test',

@.provstr=N'Server=matthol11;FailoverPartner=matthol6;'

select * from matthol11.test.dbo.test

Regards,

Matt

Mirrored linked server failover.

We have a mirror setup, with a third server that needs to be linked to this mirrored setup. The link server setup only allows you to specify the primary server. How will the linked server setup know what the secondary server is? Thanks

Andre,

You can specify the partner name in the linked server server properties and the linked server will automatically failover from the principal to the mirror if the principal is failed over.

Regards,

Matt Hollingswort

Sr. Program Manager

Microsoft SQL Server

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" 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.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" 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.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola

Mirror Setup Problems

We are having problems getting the two databases to connect with each other for mirroring. We have the following setup:

- Doing Principal and Mirror (no witness)

- Using Management Studio

- Both servers are SQL Server 2005 SP1 Standard Edition

- Same domain account, same user id

- We can telnet to both and connect to both from Stuio.

We were originally getting error 1418, but now we're getting:

"Neither the partner nor the witness server instance for database "tmsng" is available. Reissue the command when at least one of the instances becomes available. (Microsoft SQL Server, Error: 1431)"

This is what the log file looks like:

2006-11-06 13:01:59.62 spid24s Error: 1443, Severity: 16, State: 2.

2006-11-06 13:01:59.62 spid24s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 13:03:32.98 spid17s Error: 1474, Severity: 16, State: 1.

2006-11-06 13:03:32.98 spid17s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

2006-11-06 13:53:18.14 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:20.15 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:20.15 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 13:53:29.79 spid73 The Database Mirroring protocol transport has stopped listening for connections.

2006-11-06 13:53:31.79 spid73 Server is listening on [ 'any' <ipv4> 5022].

2006-11-06 13:53:31.79 spid73 The Database Mirroring protocol transport is now listening for connections.

2006-11-06 14:21:43.48 spid24s Error: 9642, Severity: 16, State: 3.

2006-11-06 14:21:43.48 spid24s An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')

2006-11-06 14:47:38.79 spid22s Error: 1443, Severity: 16, State: 2.

2006-11-06 14:47:38.79 spid22s Database mirroring has been terminated for database 'tmsng'. This is an informational message only. No user action is required.

2006-11-06 14:49:23.06 spid22s Error: 1474, Severity: 16, State: 1.

2006-11-06 14:49:23.06 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MWSM3D02:5022'.

Here’s an update:

I tried doing mirroring via the command line.

Creating endpoints went OK, backup and restore went OK.

Setting partner went OK from the mirror to principal, but blew up for principal to mirror:

ALTER DATABASE test1

SET PARTNER = ‘tcp://srvr1.xxx.net.com:5022’;

Msg 1418, Level 16, State 1, Server SRVR1, Line 1

The server network address "tcp://srvr1.xxx.net.com:5022" 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.

Name is right, ports are listening, endpoints are started. Why would it go from A to B but not B to A?

Thanks.

update again - problem more specific, starting a new thread.

|||I had the same problem, I have noticed that principal tries to connect to the mirror using 'NT AUTHORITY\ANONIMOUS ACCESS' (look at the errorlog file in C:\Programmi\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), so I just added that user with sysadmin role and everything works fine|||

I solved the problem by changing the way that SQL server services (SQL server and Agent) start, instead of local system account i used domain user account that had rights on both servers !

Best regards
Nikola

sql

Mirror Setup

Hello,

I'm trying to setup database mirroring between 3 servers and the details are as follows:

1. All 3 machines reside in the same domain.

2. Server1 consists of SQL 2005 Ent Edition without SSAS, Server 2 consists of SQL 2005 Ent Ed without SSAS & SSRS, Server 3 consists of SQL 2005 Express Ed.

3. All the 3 machines are having their individual service domain accounts.

4. Server 1 consists of 5 databases which needs to be mirrored to Server 2. I took full backups and tlog backups and restored the full backups in NO Recovery mode on Server 2 and then applied the tlog backups to sync them with Server 1.

5. I created the logins on Server 1 (Server 2 & 3), Server 2 (Server 1 & 3) and Server 3 (Server 1 & 2) and gave "sa" permissions respectively.

6. I kept the first database on Server 1 to full recovery mode, created an endpoint with the default port and granted connection to Server 2 & 3 logins.

7. I created an endpoint on Server 2 with the default port number and granted connection to Server 1 & 3 logins.

8. I created an endpoint on Server 3 with the default port number and granted connection to Server 1 & 2 logins.

9. On Server 2 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 1>:<Port No>'

10. On Server 1 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 2>:<Port No>'

ALTER DATABASE <dbname>

SET WITNESS = 'TCP://<Server Network Address of Server 3>:<Port No>'

On Step 9 & 10, I'm getting an error message "Msg 1416, Severity 16, State 2 Database <dbname> is not configured for mirroring" in the query window but when I see the error logs individually on Server 1 & 2 I'm seeing "Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'dbname'. This is an informational message only. No user action is required."

I would appreciate if any one can look into the problem I'm facing. I've googled and could not come up with a solution.

Thanks

I just have one question, did you restore the tran logs from principal to mirror using with norecovery option?.......coz you need to restore atleast 1tran log in mirror with norecovery option prior to configuring mirroring......|||

Hello Deepak,

I have restored 2 sets of tlog backups from principal to mirror -- first with norecovery mode and the second one with recovery mode. Still I'm getting the same errors.

Query Window

===========

Msg 1416, Level 16, State 31, Line 3

Database " " is not configured for database mirroring.

Error Log

=======

Error: 1443, Severity: 16, State: 2.

Database mirroring has been terminated for database ' '. This is an informational message only. No user action is required.

Do you have any other workaround for this problem?

Thanks

|||

Hello Deepak,

I have even tried with a sample database on the same set of servers and encountering the same set of errors in query window and error log. Is it something to do with any sort of configuration which I need to do before implementing database mirroring?

Thanks

|||

here is the mistake,

1. you should restore the full backup in the mirror using with norecovery clause

2.you need to restore one tran log in mirror using with norecovery clause

the idea behind restoring the full backups and tran log backups using with norecovery is that you can apply further transaction logs

since you have applied one with norecovey and other with recovery option further tran logs cannot be applied in the mirror...that seems to be the error.........perform the 2 steps as above and then start mirroring.....

|||

Hello Deepak,

I took 1 full backup and 2 tlog backups from the principal server.

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode

These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

Thanks

|||

abybody out there who can troubleshoot my problem?

TIA

|||

well as i said earlier you need to restore the full and subsequent tran log backups in mirror using with norecovery option.

this step has to be performed prior to configuring mirroring....further adding to your problem the error says, that the "database is not configured for mirroring".....which is a clear indication that either your mirror is not restored with norecovery or it is not in Full Recovery i believe.....this is what i know.....

|||

Hello Deepak,

It seems you haven't gone through my earlier messages and here it is

I took 1 full backup and 2 tlog backups from the principal server.

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode

These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

Thanks

|||

refer this,

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode > this has to be in norecovery mode but u have restored it in recover mode other steps are fine

|||

Hello Deepak,

Thanks for pointing the problem and I could setup mirroring successfully..

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...
>>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
>|||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...
>> 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...
>>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
>>
>|||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...
> 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...
>>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
>>
>|||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...
>> 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...
>>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
>>
>>
>|||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...
>> 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...
>> 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...
>>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
>>
>>
>>
>|||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...
>> 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...
>> 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...
>>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
>>
>>
>>
>>
>

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

Mirror database is not accessible when Mirroring is stopped.

Hi,

I've a very basic doubt about database mirroring. I did setup a database mirroring session with the help of SQL Server Management Studio between the server A (db1) and B(db1). When is stop database mirroring by using the command button "Stop Mirroring" available in the mirroring page of SQL Server Management Studio , the mirror database{ B(db1) } goes to state "Restoring...". After stopping the mirroring i'm not able to access the mirror database.

Can you please tell me how to bring mirror database B(db1) to operation mode so that we can start working with that database?

Regards,

Gopi

Hi Gopi. Hitting the 'Stop Mirroring' Button will in effect remove the database mirroring session for that particular database, not cause a failover...not sure if that's what you are trying to do, or if you really do mean to stop mirroring alltogether.

To simply bring the mirror copy into an operational state at the point you are at now, simply run something like the following on the mirror instance:

restore database <mirror database name> with recovery

This is very similar to as if you were restoring t-log backups to a particular database and wanted to recover it after applying the last one.

However, bear in mind that following the procedure you followed above is not what you'd want to do if you are in fact trying to 'failover' to the mirror copy of the database. For information on what you'd want to do there, click the 'Help' button from the mirroring dialog you were on above and see the topic titled 'Failover'.

HTH,