SQL2K5
SP1
I am testing out Synchronous Mirroring and have a question about failover.
It seems the only way I can failover to the Mirror box is to log onto the
Principal box and hit the Failover button? Isn't the whole point of
mirroring to be able to handle the Principal not being available? Is there
another way? To clarify, I have no witness and am attempting a manual
failover.
TIA, ChrisRHi Chris,
I believe the option you are looking for is as follows from bol:
1.. Connect to the mirror server.
2.. Issue the following statement:
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
where <database_name> is the mirrored database.
The mirror server immediately transitions to principal server, and
mirroring is suspended.
Regards
Jamie
"ChrisR" <NotAChance@.ms.com> wrote in message
news:O9tTWmHyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> SQL2K5
> SP1
> I am testing out Synchronous Mirroring and have a question about failover.
> It seems the only way I can failover to the Mirror box is to log onto the
> Principal box and hit the Failover button? Isn't the whole point of
> mirroring to be able to handle the Principal not being available? Is there
> another way? To clarify, I have no witness and am attempting a manual
> failover.
> TIA, ChrisR
>
Showing posts with label sql2k5. Show all posts
Showing posts with label sql2k5. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
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:
>
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
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
Subscribe to:
Posts (Atom)