Database Mirroring. Asp application (IIS 6.0) does''t forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Client error '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

|||

Been banging my head against this for a few days, have applied Hotfix for Microsoft.Net Framework 2.0 (KB916002) see http://support.microsoft.com/kb/912151 this is meant to fix default timeout in dB driver - didn't help. Just tried your suggestion about connection pooling, added 'Pooling=False;' to connect string - didn't help.

Is it sensible to turn off connection pooling? my understanding was this provides a big boost to site performance so would MS make us sacrifice performance for resilience? Has anyone got this failover working?

sql

Database Mirroring. Asp application (IIS 6.0) does't forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Client error '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

Database Mirroring. Asp application (IIS 6.0) does't forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Clienterror '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

|||

Been banging my head against this for a few days, have applied Hotfix for Microsoft.Net Framework 2.0 (KB916002) see http://support.microsoft.com/kb/912151 this is meant to fix default timeout in dB driver - didn't help. Just tried your suggestion about connection pooling, added 'Pooling=False;' to connect string - didn't help.

Is it sensible to turn off connection pooling? my understanding was this provides a big boost to site performance so would MS make us sacrifice performance for resilience? Has anyone got this failover working?

Database Mirroring with SQLEXPRESS as witness

I am trying to setup SQLServer 2005 to use Database Mirroring.
The principal and mirror servers are running on Windows2003 64bit edition.
The witness server is running Windows2003 32bit.
I have configured endpoints for both the principal and mirror but when I try
to setup SQLEXPRESS as the witness using...
create endpoint MirroringEndPoint
state=started
as tcp (listener_port=10111)
for database_mirroring (role=all)
I get..
Msg 7878, Level 16, State 4, Line 4
This "ROLE=PARTNER/ALL ENDPOINT" statement is not supported on this edition
of SQL Server.
SQLEXPRESS is SP2. I have tried the initial SQLEXPRESS release but then it
complains about mirroring not being supported in a production environment
which is why I have upgraded to SP2.
Best regards
MarkSorry, I misread. Specify role=witness, which is the only role supported for
Express Edition.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark Baldwin" <sWozzi3@.community.nospam> wrote in message
news:uBslxtVmHHA.3736@.TK2MSFTNGP03.phx.gbl...
>I am trying to setup SQLServer 2005 to use Database Mirroring.
> The principal and mirror servers are running on Windows2003 64bit edition.
The witness server is
> running Windows2003 32bit.
> I have configured endpoints for both the principal and mirror but when I t
ry to setup SQLEXPRESS
> as the witness using...
> create endpoint MirroringEndPoint
> state=started
> as tcp (listener_port=10111)
> for database_mirroring (role=all)
> I get..
> Msg 7878, Level 16, State 4, Line 4
> This "ROLE=PARTNER/ALL ENDPOINT" statement is not supported on this editio
n of SQL Server.
> SQLEXPRESS is SP2. I have tried the initial SQLEXPRESS release but then it
complains about
> mirroring not being supported in a production environment which is why I h
ave upgraded to SP2.
> --
> Best regards
> Mark
>|||You need standard or enterprise edition for mirroring. By the name of your i
nstance to judge, it is
express edition.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark Baldwin" <sWozzi3@.community.nospam> wrote in message
news:uBslxtVmHHA.3736@.TK2MSFTNGP03.phx.gbl...
>I am trying to setup SQLServer 2005 to use Database Mirroring.
> The principal and mirror servers are running on Windows2003 64bit edition.
The witness server is
> running Windows2003 32bit.
> I have configured endpoints for both the principal and mirror but when I t
ry to setup SQLEXPRESS
> as the witness using...
> create endpoint MirroringEndPoint
> state=started
> as tcp (listener_port=10111)
> for database_mirroring (role=all)
> I get..
> Msg 7878, Level 16, State 4, Line 4
> This "ROLE=PARTNER/ALL ENDPOINT" statement is not supported on this editio
n of SQL Server.
> SQLEXPRESS is SP2. I have tried the initial SQLEXPRESS release but then it
complains about
> mirroring not being supported in a production environment which is why I h
ave upgraded to SP2.
> --
> Best regards
> Mark
>|||Thanks, that seems to have sorted the problem out but I have another issue
with security now - still I shall leave that to another thread
Best regards
Mark
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzTxF%23VmHHA.4768@.TK2MSFTNGP05.phx.gbl...
> Sorry, I misread. Specify role=witness, which is the only role supported
> for Express Edition.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Mark Baldwin" <sWozzi3@.community.nospam> wrote in message
> news:uBslxtVmHHA.3736@.TK2MSFTNGP03.phx.gbl...
>

Database Mirroring with SQLEXPRESS as witness

I am trying to setup SQLServer 2005 to use Database Mirroring.
The principal and mirror servers are running on Windows2003 64bit edition.
The witness server is running Windows2003 32bit.
I have configured endpoints for both the principal and mirror but when I try
to setup SQLEXPRESS as the witness using...
create endpoint MirroringEndPoint
state=started
as tcp (listener_port=10111)
for database_mirroring (role=all)
I get..
Msg 7878, Level 16, State 4, Line 4
This "ROLE=PARTNER/ALL ENDPOINT" statement is not supported on this edition
of SQL Server.
SQLEXPRESS is SP2. I have tried the initial SQLEXPRESS release but then it
complains about mirroring not being supported in a production environment
which is why I have upgraded to SP2.
--
Best regards
MarkSorry, I misread. Specify role=witness, which is the only role supported for Express Edition.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark Baldwin" <sWozzi3@.community.nospam> wrote in message
news:uBslxtVmHHA.3736@.TK2MSFTNGP03.phx.gbl...
>I am trying to setup SQLServer 2005 to use Database Mirroring.
> The principal and mirror servers are running on Windows2003 64bit edition. The witness server is
> running Windows2003 32bit.
> I have configured endpoints for both the principal and mirror but when I try to setup SQLEXPRESS
> as the witness using...
> create endpoint MirroringEndPoint
> state=started
> as tcp (listener_port=10111)
> for database_mirroring (role=all)
> I get..
> Msg 7878, Level 16, State 4, Line 4
> This "ROLE=PARTNER/ALL ENDPOINT" statement is not supported on this edition of SQL Server.
> SQLEXPRESS is SP2. I have tried the initial SQLEXPRESS release but then it complains about
> mirroring not being supported in a production environment which is why I have upgraded to SP2.
> --
> Best regards
> Mark
>|||You need standard or enterprise edition for mirroring. By the name of your instance to judge, it is
express edition.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark Baldwin" <sWozzi3@.community.nospam> wrote in message
news:uBslxtVmHHA.3736@.TK2MSFTNGP03.phx.gbl...
>I am trying to setup SQLServer 2005 to use Database Mirroring.
> The principal and mirror servers are running on Windows2003 64bit edition. The witness server is
> running Windows2003 32bit.
> I have configured endpoints for both the principal and mirror but when I try to setup SQLEXPRESS
> as the witness using...
> create endpoint MirroringEndPoint
> state=started
> as tcp (listener_port=10111)
> for database_mirroring (role=all)
> I get..
> Msg 7878, Level 16, State 4, Line 4
> This "ROLE=PARTNER/ALL ENDPOINT" statement is not supported on this edition of SQL Server.
> SQLEXPRESS is SP2. I have tried the initial SQLEXPRESS release but then it complains about
> mirroring not being supported in a production environment which is why I have upgraded to SP2.
> --
> Best regards
> Mark
>|||Thanks, that seems to have sorted the problem out but I have another issue
with security now - still I shall leave that to another thread
--
Best regards
Mark
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzTxF%23VmHHA.4768@.TK2MSFTNGP05.phx.gbl...
> Sorry, I misread. Specify role=witness, which is the only role supported
> for Express Edition.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Mark Baldwin" <sWozzi3@.community.nospam> wrote in message
> news:uBslxtVmHHA.3736@.TK2MSFTNGP03.phx.gbl...
>>I am trying to setup SQLServer 2005 to use Database Mirroring.
>> The principal and mirror servers are running on Windows2003 64bit
>> edition. The witness server is running Windows2003 32bit.
>> I have configured endpoints for both the principal and mirror but when I
>> try to setup SQLEXPRESS as the witness using...
>> create endpoint MirroringEndPoint
>> state=started
>> as tcp (listener_port=10111)
>> for database_mirroring (role=all)
>> I get..
>> Msg 7878, Level 16, State 4, Line 4
>> This "ROLE=PARTNER/ALL ENDPOINT" statement is not supported on this
>> edition of SQL Server.
>> SQLEXPRESS is SP2. I have tried the initial SQLEXPRESS release but then
>> it complains about mirroring not being supported in a production
>> environment which is why I have upgraded to SP2.
>> --
>> Best regards
>> Mark
>

Database mirroring scenario

We are looking to establish a mirror server with witness. We have some
databases that have interdependencies that concern me. If one of the db's
goes offline on the primary for any reason and only that db fails over to
the mirror then we have an issue when the other db's on the primary continue
operating and are not available on the mirror.
Is there some way to monitor the failure and force the other dependent db's
to go offline forcing them to failover as well?
We had a cpu go out on our primary server last week which caused some
hiccups while the bios was evaluating the issue. Now I'm tasked with
getting this environment tested quickly...
Any help is greatly appreciated!!!
Thanks.
You can use the dmv for this:
SELECT mirroring_state_desc FROM sys.database_mirroring WHERE database_id =
DB_ID ('MirroredDatabase');
GO
Then when your database fails over you can issue the following
alter database othermirroreddatabases set partner failover
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:euW%23ZPwTIHA.748@.TK2MSFTNGP04.phx.gbl...
> We are looking to establish a mirror server with witness. We have some
> databases that have interdependencies that concern me. If one of the db's
> goes offline on the primary for any reason and only that db fails over to
> the mirror then we have an issue when the other db's on the primary
> continue operating and are not available on the mirror.
> Is there some way to monitor the failure and force the other dependent
> db's to go offline forcing them to failover as well?
> We had a cpu go out on our primary server last week which caused some
> hiccups while the bios was evaluating the issue. Now I'm tasked with
> getting this environment tested quickly...
> Any help is greatly appreciated!!!
> Thanks.
>
sql

Database Mirroring is not working at all in RTM

I tried all the scenarios .

1- three CLEAN machines in the same domain each has a default instance
2- 1 machine having principal and witness and another one having mirror
3- 1 machine has all

the service for all of them is running under 1 account which is the domain admin.

but still i always getting the same error 1418 "the nework address '...' can not be reached or does not exist ..." when i try to add the mirror server as a partner for the principal server from the principal server .

i tried using TSQL and management studio

this error is driving me crazy , i search on google even for it and in forums and BOL and everwhere .
i followed the exact steps .

You know that the mirroring is available only under trace flag in RTM version? You need to use TF-1400

Thanks

|||

This is Books Online info on this flag (1400):

Enables database mirroring. This trace flag is allowed only when using the -T trace# startup option during start up of the server instance. Therefore, enabling mirroring requires a restart of the server instance. For more information about using Setup options, see Using the SQL Server Service Startup Options.

Important:
Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.

After starting up a server instance using this trace flag, you can set up and evaluate database mirroring. For more information, see Setting Up Database Mirroring.

Trace flag 1400 is a temporary flag. This flag will be removed when database mirroring is made available for general production use.

|||Hi getting exactly the same problem, how and where exactly do you put the -T1400 ..? Ive put it what i think is the correct place, but still get the same error as you and the three operating modes under the mirroe tab are still grayed out..?

chris|||Basically, you need to modify the service that startups and add the flag to the end of the file path.|||

Open configuration tools, then choose SQL Server Configuration Manager. Double click on SQL Server; choose the advanced tab then startup parameters.
This is what mine looks like:

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf; -T1400

|||

i tried of course the Trace -T1400

but still the same

|||I was experiencing the same problem until I realised from the example above that there is a ; after the file path and before the trace switch. It's all working nicely now.|||

Hey all,

I am having the same problem. But the funny thing is that I had set up Mirroring on the three servers (Principal, Mirror and Witness) and tested it and everything was working fine unitil I needed to change the Server's computer name. I Stopped Mirroring, renamed my servers, dropped all the objects created during the setup of the mirror session(Master Key, Certificates, Endpoints, users, logins and even the database itself) on all the machines. then I followed the same steps to setup the mirroring session as before. I can proceed the setup until I get to the point of setup the partners. I connect to the Mirror server issue the Set Partner = 'TCP://PrincipalServer.Domain.ie:5024' and it works. Then I connect to the Principal and issue the the Set Partner ='TCP://MirrorServer.Domain.ie:5025' and what I get is this F*** Error 1418 saying that the server network address cannot be reached bla bla bla...

I have done follow the steps twice, so to make sure that I had made any mistakes. I check the ports on both servers to see if they are listen to 5024 and they are. I even reinstalled the damm server again with no success. I check to see if trace flag is enabled , I checked permissions, Endpoints, etc......... No success!

If there is anyone at the end of this line, who have an idea of what is going on, please give me a light. I don't know what else to look at.

regards,

adasilva

Database Mirroring is not working at all in RTM

I tried all the scenarios .

1- three CLEAN machines in the same domain each has a default instance
2- 1 machine having principal and witness and another one having mirror
3- 1 machine has all

the service for all of them is running under 1 account which is the domain admin.

but still i always getting the same error 1418 "the nework address '...' can not be reached or does not exist ..." when i try to add the mirror server as a partner for the principal server from the principal server .

i tried using TSQL and management studio

this error is driving me crazy , i search on google even for it and in forums and BOL and everwhere .
i followed the exact steps .

You know that the mirroring is available only under trace flag in RTM version? You need to use TF-1400

Thanks

|||

This is Books Online info on this flag (1400):

Enables database mirroring. This trace flag is allowed only when using the -T trace# startup option during start up of the server instance. Therefore, enabling mirroring requires a restart of the server instance. For more information about using Setup options, see Using the SQL Server Service Startup Options.

Important:
Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.

After starting up a server instance using this trace flag, you can set up and evaluate database mirroring. For more information, see Setting Up Database Mirroring.

Trace flag 1400 is a temporary flag. This flag will be removed when database mirroring is made available for general production use.

|||Hi getting exactly the same problem, how and where exactly do you put the -T1400 ..? Ive put it what i think is the correct place, but still get the same error as you and the three operating modes under the mirroe tab are still grayed out..?

chris|||Basically, you need to modify the service that startups and add the flag to the end of the file path.|||

Open configuration tools, then choose SQL Server Configuration Manager. Double click on SQL Server; choose the advanced tab then startup parameters.
This is what mine looks like:

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf; -T1400

|||

i tried of course the Trace -T1400

but still the same

|||I was experiencing the same problem until I realised from the example above that there is a ; after the file path and before the trace switch. It's all working nicely now.|||

Hey all,

I am having the same problem. But the funny thing is that I had set up Mirroring on the three servers (Principal, Mirror and Witness) and tested it and everything was working fine unitil I needed to change the Server's computer name. I Stopped Mirroring, renamed my servers, dropped all the objects created during the setup of the mirror session(Master Key, Certificates, Endpoints, users, logins and even the database itself) on all the machines. then I followed the same steps to setup the mirroring session as before. I can proceed the setup until I get to the point of setup the partners. I connect to the Mirror server issue the Set Partner = 'TCP://PrincipalServer.Domain.ie:5024' and it works. Then I connect to the Principal and issue the the Set Partner ='TCP://MirrorServer.Domain.ie:5025' and what I get is this F*** Error 1418 saying that the server network address cannot be reached bla bla bla...

I have done follow the steps twice, so to make sure that I had made any mistakes. I check the ports on both servers to see if they are listen to 5024 and they are. I even reinstalled the damm server again with no success. I check to see if trace flag is enabled , I checked permissions, Endpoints, etc......... No success!

If there is anyone at the end of this line, who have an idea of what is going on, please give me a light. I don't know what else to look at.

regards,

adasilva

Database Mirroring is not working at all in RTM

I tried all the scenarios .

1- three CLEAN machines in the same domain each has a default instance
2- 1 machine having principal and witness and another one having mirror
3- 1 machine has all

the service for all of them is running under 1 account which is the domain admin.

but still i always getting the same error 1418 "the nework address '...' can not be reached or does not exist ..." when i try to add the mirror server as a partner for the principal server from the principal server .

i tried using TSQL and management studio

this error is driving me crazy , i search on google even for it and in forums and BOL and everwhere .
i followed the exact steps .

You know that the mirroring is available only under trace flag in RTM version? You need to use TF-1400

Thanks

|||

This is Books Online info on this flag (1400):

Enables database mirroring. This trace flag is allowed only when using the -T trace# startup option during start up of the server instance. Therefore, enabling mirroring requires a restart of the server instance. For more information about using Setup options, see Using the SQL Server Service Startup Options.

Important:
Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.

After starting up a server instance using this trace flag, you can set up and evaluate database mirroring. For more information, see Setting Up Database Mirroring.

Trace flag 1400 is a temporary flag. This flag will be removed when database mirroring is made available for general production use.

|||Hi getting exactly the same problem, how and where exactly do you put the -T1400 ..? Ive put it what i think is the correct place, but still get the same error as you and the three operating modes under the mirroe tab are still grayed out..?

chris|||Basically, you need to modify the service that startups and add the flag to the end of the file path.|||

Open configuration tools, then choose SQL Server Configuration Manager. Double click on SQL Server; choose the advanced tab then startup parameters.
This is what mine looks like:

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf; -T1400

|||

i tried of course the Trace -T1400

but still the same

|||I was experiencing the same problem until I realised from the example above that there is a ; after the file path and before the trace switch. It's all working nicely now.|||

Hey all,

I am having the same problem. But the funny thing is that I had set up Mirroring on the three servers (Principal, Mirror and Witness) and tested it and everything was working fine unitil I needed to change the Server's computer name. I Stopped Mirroring, renamed my servers, dropped all the objects created during the setup of the mirror session(Master Key, Certificates, Endpoints, users, logins and even the database itself) on all the machines. then I followed the same steps to setup the mirroring session as before. I can proceed the setup until I get to the point of setup the partners. I connect to the Mirror server issue the Set Partner = 'TCP://PrincipalServer.Domain.ie:5024' and it works. Then I connect to the Principal and issue the the Set Partner ='TCP://MirrorServer.Domain.ie:5025' and what I get is this F*** Error 1418 saying that the server network address cannot be reached bla bla bla...

I have done follow the steps twice, so to make sure that I had made any mistakes. I check the ports on both servers to see if they are listen to 5024 and they are. I even reinstalled the damm server again with no success. I check to see if trace flag is enabled , I checked permissions, Endpoints, etc......... No success!

If there is anyone at the end of this line, who have an idea of what is going on, please give me a light. I don't know what else to look at.

regards,

adasilva

sql

Database Mirroring is not working at all in RTM

I tried all the scenarios .

1- three CLEAN machines in the same domain each has a default instance
2- 1 machine having principal and witness and another one having mirror
3- 1 machine has all

the service for all of them is running under 1 account which is the domain admin.

but still i always getting the same error 1418 "the nework address '...' can not be reached or does not exist ..." when i try to add the mirror server as a partner for the principal server from the principal server .

i tried using TSQL and management studio

this error is driving me crazy , i search on google even for it and in forums and BOL and everwhere .
i followed the exact steps .

You know that the mirroring is available only under trace flag in RTM version? You need to use TF-1400

Thanks

|||

This is Books Online info on this flag (1400):

Enables database mirroring. This trace flag is allowed only when using the -T trace# startup option during start up of the server instance. Therefore, enabling mirroring requires a restart of the server instance. For more information about using Setup options, see Using the SQL Server Service Startup Options.

Important:
Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.

After starting up a server instance using this trace flag, you can set up and evaluate database mirroring. For more information, see Setting Up Database Mirroring.

Trace flag 1400 is a temporary flag. This flag will be removed when database mirroring is made available for general production use.

|||Hi getting exactly the same problem, how and where exactly do you put the -T1400 ..? Ive put it what i think is the correct place, but still get the same error as you and the three operating modes under the mirroe tab are still grayed out..?

chris|||Basically, you need to modify the service that startups and add the flag to the end of the file path.|||

Open configuration tools, then choose SQL Server Configuration Manager. Double click on SQL Server; choose the advanced tab then startup parameters.
This is what mine looks like:

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf; -T1400

|||

i tried of course the Trace -T1400

but still the same

|||I was experiencing the same problem until I realised from the example above that there is a ; after the file path and before the trace switch. It's all working nicely now.|||

Hey all,

I am having the same problem. But the funny thing is that I had set up Mirroring on the three servers (Principal, Mirror and Witness) and tested it and everything was working fine unitil I needed to change the Server's computer name. I Stopped Mirroring, renamed my servers, dropped all the objects created during the setup of the mirror session(Master Key, Certificates, Endpoints, users, logins and even the database itself) on all the machines. then I followed the same steps to setup the mirroring session as before. I can proceed the setup until I get to the point of setup the partners. I connect to the Mirror server issue the Set Partner = 'TCP://PrincipalServer.Domain.ie:5024' and it works. Then I connect to the Principal and issue the the Set Partner ='TCP://MirrorServer.Domain.ie:5025' and what I get is this F*** Error 1418 saying that the server network address cannot be reached bla bla bla...

I have done follow the steps twice, so to make sure that I had made any mistakes. I check the ports on both servers to see if they are listen to 5024 and they are. I even reinstalled the damm server again with no success. I check to see if trace flag is enabled , I checked permissions, Endpoints, etc......... No success!

If there is anyone at the end of this line, who have an idea of what is going on, please give me a light. I don't know what else to look at.

regards,

adasilva

Database Mirroring in High Safety Mode

Hello!
I'm reading about database mirroring in High Safety Mode (Synchronous with
no witness). I'm a little confused about what happens if the mirror server
disappears.
From Books Online
"If the mirror server instance goes down, the principal server instance is
unaffected and runs exposed (that is without mirroring the data)."
http://msdn2.microsoft.com/en-us/library/ms179344.aspx
I was to the course [Upgrading your Database Administration Skills to SQL
Server 2005] and the course material does not say the same."Scenario:
Synchronous mirroring without a witness server. If the mirror server becomes
unavailable, the principal server will take the database offline. This action
attempts to maximize the protection of the database and ensure the integrity
of its contents"
I've made tests that shows that Books Online is right,
but I would like to know if this is a behaviour that has changed.
I also would like to know if it's possible to run in a more secure mode,
where exposed operations is not allowed.
Best regards
Ola Hallengren
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
> Hello!
> I'm reading about database mirroring in High Safety Mode (Synchronous with
> no witness). I'm a little confused about what happens if the mirror server
> disappears.
> From Books Online
> "If the mirror server instance goes down, the principal server instance is
> unaffected and runs exposed (that is without mirroring the data)."
> http://msdn2.microsoft.com/en-us/library/ms179344.aspx
> I was to the course [Upgrading your Database Administration Skills to SQL
> Server 2005] and the course material does not say the same."Scenario:
> Synchronous mirroring without a witness server. If the mirror server
> becomes
> unavailable, the principal server will take the database offline. This
> action
> attempts to maximize the protection of the database and ensure the
> integrity
> of its contents"
> I've made tests that shows that Books Online is right,
> but I would like to know if this is a behaviour that has changed.
> I also would like to know if it's possible to run in a more secure mode,
> where exposed operations is not allowed.
>
It's not built in, but if you would prefer to shut down the database rather
than run exposed, you should be able to use an event notification.
Alerting on Database Mirroring Events
http://www.microsoft.com/technet/pro...ingevents.mspx
David
|||Thanks, David.
Am I right that this behavour has changed since the course material states
the following? "If the mirror server becomes unavailable, the principal
server will take the database offline."
About the event notification. I understand the approach, but would that
guarantee that not a single transaction is committed in exposed mode?
"David Browne" wrote:

>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
> It's not built in, but if you would prefer to shut down the database rather
> than run exposed, you should be able to use an event notification.
> Alerting on Database Mirroring Events
> http://www.microsoft.com/technet/pro...ingevents.mspx
>
> David
>
|||The course material was based on a pre-release of SQL Server, and that behavior explained in there
wasn't deemed practical, so it was changed to what you see in BOL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
> Hello!
> I'm reading about database mirroring in High Safety Mode (Synchronous with
> no witness). I'm a little confused about what happens if the mirror server
> disappears.
> From Books Online
> "If the mirror server instance goes down, the principal server instance is
> unaffected and runs exposed (that is without mirroring the data)."
> http://msdn2.microsoft.com/en-us/library/ms179344.aspx
> I was to the course [Upgrading your Database Administration Skills to SQL
> Server 2005] and the course material does not say the same."Scenario:
> Synchronous mirroring without a witness server. If the mirror server becomes
> unavailable, the principal server will take the database offline. This action
> attempts to maximize the protection of the database and ensure the integrity
> of its contents"
> I've made tests that shows that Books Online is right,
> but I would like to know if this is a behaviour that has changed.
> I also would like to know if it's possible to run in a more secure mode,
> where exposed operations is not allowed.
> Best regards
> Ola Hallengren
>
|||Thanks for making clear that this is a changed behaviour.
What worries me is the following.
You have some system instabilities (network, power whatever) and suddenly
your principal server is down (dead).
The dba is called in and is thinking about bringing the mirror server online.
Now there is two scenarios.
1. The servers were synchronized when the principal server went down.
If that's the case then there is no problem bringing the mirror server
online.
2. The servers were not synchronized (running exposed) when the principal
server went down. (That could be because of a network device was temporary
down just before.) If that's the case then there could be severe consequences
bringing the mirror server online.
The problem as I see it is that there is no way of knowing which scenario
that is true.
"Tibor Karaszi" wrote:

> The course material was based on a pre-release of SQL Server, and that behavior explained in there
> wasn't deemed practical, so it was changed to what you see in BOL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
>
|||"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:14AEFB8F-82EA-4220-9CE9-AD0F10BF702F@.microsoft.com...
> Thanks, David.
> Am I right that this behavour has changed since the course material states
> the following? "If the mirror server becomes unavailable, the principal
> server will take the database offline."
> About the event notification. I understand the approach, but would that
> guarantee that not a single transaction is committed in exposed mode?
>
Events are delivered asynchronously, so I don't think there's a guarantee.
You would just be able to limit your exposure.
David
|||I understand the situation, and there's probably not simple solution for that except using a
witness.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:72E953E4-D749-4DDA-890A-3532D0E8BFFE@.microsoft.com...[vbcol=seagreen]
> Thanks for making clear that this is a changed behaviour.
> What worries me is the following.
> You have some system instabilities (network, power whatever) and suddenly
> your principal server is down (dead).
> The dba is called in and is thinking about bringing the mirror server online.
> Now there is two scenarios.
> 1. The servers were synchronized when the principal server went down.
> If that's the case then there is no problem bringing the mirror server
> online.
> 2. The servers were not synchronized (running exposed) when the principal
> server went down. (That could be because of a network device was temporary
> down just before.) If that's the case then there could be severe consequences
> bringing the mirror server online.
> The problem as I see it is that there is no way of knowing which scenario
> that is true.
> "Tibor Karaszi" wrote:
sql

Database Mirroring in High Safety Mode

Hello!
I'm reading about database mirroring in High Safety Mode (Synchronous with
no witness). I'm a little confused about what happens if the mirror server
disappears.
From Books Online
"If the mirror server instance goes down, the principal server instance is
unaffected and runs exposed (that is without mirroring the data)."
http://msdn2.microsoft.com/en-us/library/ms179344.aspx
I was to the course [Upgrading your Database Administration Skills to SQ
L
Server 2005] and the course material does not say the same."Scenario:
Synchronous mirroring without a witness server. If the mirror server becomes
unavailable, the principal server will take the database offline. This actio
n
attempts to maximize the protection of the database and ensure the integrity
of its contents"
I've made tests that shows that Books Online is right,
but I would like to know if this is a behaviour that has changed.
I also would like to know if it's possible to run in a more secure mode,
where exposed operations is not allowed.
Best regards
Ola Hallengren"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
> Hello!
> I'm reading about database mirroring in High Safety Mode (Synchronous with
> no witness). I'm a little confused about what happens if the mirror server
> disappears.
> From Books Online
> "If the mirror server instance goes down, the principal server instance is
> unaffected and runs exposed (that is without mirroring the data)."
> http://msdn2.microsoft.com/en-us/library/ms179344.aspx
> I was to the course [Upgrading your Database Administration Skills to
SQL
> Server 2005] and the course material does not say the same."Scenario:
> Synchronous mirroring without a witness server. If the mirror server
> becomes
> unavailable, the principal server will take the database offline. This
> action
> attempts to maximize the protection of the database and ensure the
> integrity
> of its contents"
> I've made tests that shows that Books Online is right,
> but I would like to know if this is a behaviour that has changed.
> I also would like to know if it's possible to run in a more secure mode,
> where exposed operations is not allowed.
>
It's not built in, but if you would prefer to shut down the database rather
than run exposed, you should be able to use an event notification.
Alerting on Database Mirroring Events
http://www.microsoft.com/technet/pr...ringevents.mspx
David|||Thanks, David.
Am I right that this behavour has changed since the course material states
the following? "If the mirror server becomes unavailable, the principal
server will take the database offline."
About the event notification. I understand the approach, but would that
guarantee that not a single transaction is committed in exposed mode?
"David Browne" wrote:

>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in messag
e
> news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
> It's not built in, but if you would prefer to shut down the database rathe
r
> than run exposed, you should be able to use an event notification.
> Alerting on Database Mirroring Events
> http://www.microsoft.com/technet/pr...ringevents.mspx
>
> David
>|||The course material was based on a pre-release of SQL Server, and that behav
ior explained in there
wasn't deemed practical, so it was changed to what you see in BOL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
> Hello!
> I'm reading about database mirroring in High Safety Mode (Synchronous with
> no witness). I'm a little confused about what happens if the mirror server
> disappears.
> From Books Online
> "If the mirror server instance goes down, the principal server instance is
> unaffected and runs exposed (that is without mirroring the data)."
> http://msdn2.microsoft.com/en-us/library/ms179344.aspx
> I was to the course [Upgrading your Database Administration Skills to
SQL
> Server 2005] and the course material does not say the same."Scenario:
> Synchronous mirroring without a witness server. If the mirror server becom
es
> unavailable, the principal server will take the database offline. This act
ion
> attempts to maximize the protection of the database and ensure the integri
ty
> of its contents"
> I've made tests that shows that Books Online is right,
> but I would like to know if this is a behaviour that has changed.
> I also would like to know if it's possible to run in a more secure mode,
> where exposed operations is not allowed.
> Best regards
> Ola Hallengren
>|||Thanks for making clear that this is a changed behaviour.
What worries me is the following.
You have some system instabilities (network, power whatever) and suddenly
your principal server is down (dead).
The dba is called in and is thinking about bringing the mirror server online
.
Now there is two scenarios.
1. The servers were synchronized when the principal server went down.
If that's the case then there is no problem bringing the mirror server
online.
2. The servers were not synchronized (running exposed) when the principal
server went down. (That could be because of a network device was temporary
down just before.) If that's the case then there could be severe consequence
s
bringing the mirror server online.
The problem as I see it is that there is no way of knowing which scenario
that is true.
"Tibor Karaszi" wrote:

> The course material was based on a pre-release of SQL Server, and that beh
avior explained in there
> wasn't deemed practical, so it was changed to what you see in BOL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in messag
e
> news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
>|||"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:14AEFB8F-82EA-4220-9CE9-AD0F10BF702F@.microsoft.com...
> Thanks, David.
> Am I right that this behavour has changed since the course material states
> the following? "If the mirror server becomes unavailable, the principal
> server will take the database offline."
> About the event notification. I understand the approach, but would that
> guarantee that not a single transaction is committed in exposed mode?
>
Events are delivered asynchronously, so I don't think there's a guarantee.
You would just be able to limit your exposure.
David|||I understand the situation, and there's probably not simple solution for tha
t except using a
witness.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:72E953E4-D749-4DDA-890A-3532D0E8BFFE@.microsoft.com...[vbcol=seagreen]
> Thanks for making clear that this is a changed behaviour.
> What worries me is the following.
> You have some system instabilities (network, power whatever) and suddenly
> your principal server is down (dead).
> The dba is called in and is thinking about bringing the mirror server onli
ne.
> Now there is two scenarios.
> 1. The servers were synchronized when the principal server went down.
> If that's the case then there is no problem bringing the mirror server
> online.
> 2. The servers were not synchronized (running exposed) when the principal
> server went down. (That could be because of a network device was temporary
> down just before.) If that's the case then there could be severe consequen
ces
> bringing the mirror server online.
> The problem as I see it is that there is no way of knowing which scenario
> that is true.
> "Tibor Karaszi" wrote:
>

Database Mirroring in High Safety Mode

Hello!
I'm reading about database mirroring in High Safety Mode (Synchronous with
no witness). I'm a little confused about what happens if the mirror server
disappears.
From Books Online
"If the mirror server instance goes down, the principal server instance is
unaffected and runs exposed (that is without mirroring the data)."
http://msdn2.microsoft.com/en-us/library/ms179344.aspx
I was to the course [Upgrading your Database Administration Skills to SQL
Server 2005] and the course material does not say the same."Scenario:
Synchronous mirroring without a witness server. If the mirror server becomes
unavailable, the principal server will take the database offline. This action
attempts to maximize the protection of the database and ensure the integrity
of its contents"
I've made tests that shows that Books Online is right,
but I would like to know if this is a behaviour that has changed.
I also would like to know if it's possible to run in a more secure mode,
where exposed operations is not allowed.
Best regards
Ola Hallengren"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
> Hello!
> I'm reading about database mirroring in High Safety Mode (Synchronous with
> no witness). I'm a little confused about what happens if the mirror server
> disappears.
> From Books Online
> "If the mirror server instance goes down, the principal server instance is
> unaffected and runs exposed (that is without mirroring the data)."
> http://msdn2.microsoft.com/en-us/library/ms179344.aspx
> I was to the course [Upgrading your Database Administration Skills to SQL
> Server 2005] and the course material does not say the same."Scenario:
> Synchronous mirroring without a witness server. If the mirror server
> becomes
> unavailable, the principal server will take the database offline. This
> action
> attempts to maximize the protection of the database and ensure the
> integrity
> of its contents"
> I've made tests that shows that Books Online is right,
> but I would like to know if this is a behaviour that has changed.
> I also would like to know if it's possible to run in a more secure mode,
> where exposed operations is not allowed.
>
It's not built in, but if you would prefer to shut down the database rather
than run exposed, you should be able to use an event notification.
Alerting on Database Mirroring Events
http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx
David|||Thanks, David.
Am I right that this behavour has changed since the course material states
the following? "If the mirror server becomes unavailable, the principal
server will take the database offline."
About the event notification. I understand the approach, but would that
guarantee that not a single transaction is committed in exposed mode?
"David Browne" wrote:
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
> > Hello!
> >
> > I'm reading about database mirroring in High Safety Mode (Synchronous with
> > no witness). I'm a little confused about what happens if the mirror server
> > disappears.
> >
> > From Books Online
> > "If the mirror server instance goes down, the principal server instance is
> > unaffected and runs exposed (that is without mirroring the data)."
> > http://msdn2.microsoft.com/en-us/library/ms179344.aspx
> >
> > I was to the course [Upgrading your Database Administration Skills to SQL
> > Server 2005] and the course material does not say the same."Scenario:
> > Synchronous mirroring without a witness server. If the mirror server
> > becomes
> > unavailable, the principal server will take the database offline. This
> > action
> > attempts to maximize the protection of the database and ensure the
> > integrity
> > of its contents"
> >
> > I've made tests that shows that Books Online is right,
> > but I would like to know if this is a behaviour that has changed.
> >
> > I also would like to know if it's possible to run in a more secure mode,
> > where exposed operations is not allowed.
> >
> It's not built in, but if you would prefer to shut down the database rather
> than run exposed, you should be able to use an event notification.
> Alerting on Database Mirroring Events
> http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx
>
> David
>|||The course material was based on a pre-release of SQL Server, and that behavior explained in there
wasn't deemed practical, so it was changed to what you see in BOL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
> Hello!
> I'm reading about database mirroring in High Safety Mode (Synchronous with
> no witness). I'm a little confused about what happens if the mirror server
> disappears.
> From Books Online
> "If the mirror server instance goes down, the principal server instance is
> unaffected and runs exposed (that is without mirroring the data)."
> http://msdn2.microsoft.com/en-us/library/ms179344.aspx
> I was to the course [Upgrading your Database Administration Skills to SQL
> Server 2005] and the course material does not say the same."Scenario:
> Synchronous mirroring without a witness server. If the mirror server becomes
> unavailable, the principal server will take the database offline. This action
> attempts to maximize the protection of the database and ensure the integrity
> of its contents"
> I've made tests that shows that Books Online is right,
> but I would like to know if this is a behaviour that has changed.
> I also would like to know if it's possible to run in a more secure mode,
> where exposed operations is not allowed.
> Best regards
> Ola Hallengren
>|||Thanks for making clear that this is a changed behaviour.
What worries me is the following.
You have some system instabilities (network, power whatever) and suddenly
your principal server is down (dead).
The dba is called in and is thinking about bringing the mirror server online.
Now there is two scenarios.
1. The servers were synchronized when the principal server went down.
If that's the case then there is no problem bringing the mirror server
online.
2. The servers were not synchronized (running exposed) when the principal
server went down. (That could be because of a network device was temporary
down just before.) If that's the case then there could be severe consequences
bringing the mirror server online.
The problem as I see it is that there is no way of knowing which scenario
that is true.
"Tibor Karaszi" wrote:
> The course material was based on a pre-release of SQL Server, and that behavior explained in there
> wasn't deemed practical, so it was changed to what you see in BOL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
> > Hello!
> >
> > I'm reading about database mirroring in High Safety Mode (Synchronous with
> > no witness). I'm a little confused about what happens if the mirror server
> > disappears.
> >
> > From Books Online
> > "If the mirror server instance goes down, the principal server instance is
> > unaffected and runs exposed (that is without mirroring the data)."
> > http://msdn2.microsoft.com/en-us/library/ms179344.aspx
> >
> > I was to the course [Upgrading your Database Administration Skills to SQL
> > Server 2005] and the course material does not say the same."Scenario:
> > Synchronous mirroring without a witness server. If the mirror server becomes
> > unavailable, the principal server will take the database offline. This action
> > attempts to maximize the protection of the database and ensure the integrity
> > of its contents"
> >
> > I've made tests that shows that Books Online is right,
> > but I would like to know if this is a behaviour that has changed.
> >
> > I also would like to know if it's possible to run in a more secure mode,
> > where exposed operations is not allowed.
> >
> > Best regards
> >
> > Ola Hallengren
> >
>|||"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:14AEFB8F-82EA-4220-9CE9-AD0F10BF702F@.microsoft.com...
> Thanks, David.
> Am I right that this behavour has changed since the course material states
> the following? "If the mirror server becomes unavailable, the principal
> server will take the database offline."
> About the event notification. I understand the approach, but would that
> guarantee that not a single transaction is committed in exposed mode?
>
Events are delivered asynchronously, so I don't think there's a guarantee.
You would just be able to limit your exposure.
David|||I understand the situation, and there's probably not simple solution for that except using a
witness.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:72E953E4-D749-4DDA-890A-3532D0E8BFFE@.microsoft.com...
> Thanks for making clear that this is a changed behaviour.
> What worries me is the following.
> You have some system instabilities (network, power whatever) and suddenly
> your principal server is down (dead).
> The dba is called in and is thinking about bringing the mirror server online.
> Now there is two scenarios.
> 1. The servers were synchronized when the principal server went down.
> If that's the case then there is no problem bringing the mirror server
> online.
> 2. The servers were not synchronized (running exposed) when the principal
> server went down. (That could be because of a network device was temporary
> down just before.) If that's the case then there could be severe consequences
> bringing the mirror server online.
> The problem as I see it is that there is no way of knowing which scenario
> that is true.
> "Tibor Karaszi" wrote:
>> The course material was based on a pre-release of SQL Server, and that behavior explained in
>> there
>> wasn't deemed practical, so it was changed to what you see in BOL.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
>> news:C6208896-3167-4E66-B4EB-15D10BD1E1F8@.microsoft.com...
>> > Hello!
>> >
>> > I'm reading about database mirroring in High Safety Mode (Synchronous with
>> > no witness). I'm a little confused about what happens if the mirror server
>> > disappears.
>> >
>> > From Books Online
>> > "If the mirror server instance goes down, the principal server instance is
>> > unaffected and runs exposed (that is without mirroring the data)."
>> > http://msdn2.microsoft.com/en-us/library/ms179344.aspx
>> >
>> > I was to the course [Upgrading your Database Administration Skills to SQL
>> > Server 2005] and the course material does not say the same."Scenario:
>> > Synchronous mirroring without a witness server. If the mirror server becomes
>> > unavailable, the principal server will take the database offline. This action
>> > attempts to maximize the protection of the database and ensure the integrity
>> > of its contents"
>> >
>> > I've made tests that shows that Books Online is right,
>> > but I would like to know if this is a behaviour that has changed.
>> >
>> > I also would like to know if it's possible to run in a more secure mode,
>> > where exposed operations is not allowed.
>> >
>> > Best regards
>> >
>> > Ola Hallengren
>> >
>>

database mirroring error? -- Scenario HASL2.3

HASL2.3 refers to the scenario within this article of a Mirror Server Loss
followed by Witness Loss:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
We have SQL Server 2005 Standard Edition, SP1, + hotfix 2153 installed on
Win2k3 R2 servers.
We were planning on upgrading some hardware (adding a disk array) to our
secondary site (datacenter B) and I thought that it would be a good time to
test a couple of database mirroring scenarios.
I set up database mirroring on a test database. The primary was on a server
in datacenter A. I set up a witness on a box located in the test lab at our
office . With this setup I was running database mirroring in a high safety
with automatic failover (synchronous) mode.
Mirroring was working. I manually failed over and back. No problem there.
They shut down the server within datacenter B. The server in datacenter A
could still talk to my watcher box in the lab in our office. The primary
database on the server within datacenter A stayed available but it showed
that the mirrored server was disconnected (as expected).
I stopped SQL Server on the watch box in the lab. At that point the primary
database on the server within datacenter A became unavailable (Principal,
Disconnected / In Recovery). The database was unavailable (as expected).
The fly in the ointment:
They finished working on the server within datacenter B. The box came back
up without any issues. The other databases on that server are intact and
everything looks good. SQL Server is running normally...BUT the database
mirroring did not resume. The article indicates that "If Server B is
restored first, mirroring resumes, although no automatic failover is
possible without a witness." I did NOT experience this. My "Server B" has
been back online for 45 minutes and its status still shows as "Principal,
Disconnected / In Recovery" on Server A (datacenter A) and "Mirror,
Disconnected / In Recovery" on Server B (datacenter B).
Has anyone else experienced this scenario? I could try turning on the watch
server again, but I would like to see the two servers (principal and mirror)
synchronize on their own. I realize that automatic failover will not be
possible until I bring the watch back online, but the mirrored database(s)
should be available now that both the principal and mirror are back online.
Right?
--
Keith Kratochvilmy understanding of the situation is that when both or even one of the
datacenters comes online, it cannot automatically failover because both
are unable to deciede which of them is the actual principal(beacuse of
the link between them is not there) unless there is a witness as u
said. if both come online both try to actively become the principal and
hence the same problem
if u add a witness it should solve your problem or u can manually make
the mirror as your principal
Keith Kratochvil wrote:
> HASL2.3 refers to the scenario within this article of a Mirror Server Loss
> followed by Witness Loss:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>
> We have SQL Server 2005 Standard Edition, SP1, + hotfix 2153 installed on
> Win2k3 R2 servers.
> We were planning on upgrading some hardware (adding a disk array) to our
> secondary site (datacenter B) and I thought that it would be a good time to
> test a couple of database mirroring scenarios.
> I set up database mirroring on a test database. The primary was on a server
> in datacenter A. I set up a witness on a box located in the test lab at our
> office . With this setup I was running database mirroring in a high safety
> with automatic failover (synchronous) mode.
> Mirroring was working. I manually failed over and back. No problem there.
> They shut down the server within datacenter B. The server in datacenter A
> could still talk to my watcher box in the lab in our office. The primary
> database on the server within datacenter A stayed available but it showed
> that the mirrored server was disconnected (as expected).
>
> I stopped SQL Server on the watch box in the lab. At that point the primary
> database on the server within datacenter A became unavailable (Principal,
> Disconnected / In Recovery). The database was unavailable (as expected).
>
> The fly in the ointment:
> They finished working on the server within datacenter B. The box came back
> up without any issues. The other databases on that server are intact and
> everything looks good. SQL Server is running normally...BUT the database
> mirroring did not resume. The article indicates that "If Server B is
> restored first, mirroring resumes, although no automatic failover is
> possible without a witness." I did NOT experience this. My "Server B" has
> been back online for 45 minutes and its status still shows as "Principal,
> Disconnected / In Recovery" on Server A (datacenter A) and "Mirror,
> Disconnected / In Recovery" on Server B (datacenter B).
>
> Has anyone else experienced this scenario? I could try turning on the watch
> server again, but I would like to see the two servers (principal and mirror)
> synchronize on their own. I realize that automatic failover will not be
> possible until I bring the watch back online, but the mirrored database(s)
> should be available now that both the principal and mirror are back online.
> Right?
> --
> Keith Kratochvil|||Please re-read my post.
I had an automatic failover setup configured between data center A
(Primary), data center B (Mirror), and our office (Witness).
I wanted to test what would happen if we lost the Mirror and then the
Witness.
When we lost the Mirror (because it was down for hardware maintenance)
everything kept running because the Witness was still available.
I shut down the Witness and the Primary stopped working (as expected).
The hardware upgrade on the Mirror was complete and we brought that machine
back online. At this point we had
Primary server: on
Mirror server: on
Witness server: off
Mirroring: broken
Expected Result: Primary and Mirror would start "talking" to each other,
synchronize any changes , and come back online.
Actual Result: the database that I mirrored (MirroredDemo) is unavailable.
When I try to connect to or use that database on the primary I receive the
following message:
"Database MirroredDemo is enabled for Database Mirroring, but neither the
partner nor witness server instances are available: the database cannot be
opened.
"
Current situation:
(Via SSMS) The database is showing that it is "Principal, Disconnected / In
Recovery" within the database list. If I look at the database mirroring
properties the status text is: "No connection: cannot connect to the mirror
or witness server instances." In this state the database is UNAVAILABLE
If I bring the witness back online the databae shows that it is "Principal,
Disconnected." Database Mirroring is not functioning. The good news is
that I can access the database. The bad news is that I cannot fail over. I
receive this message when I try to fail over: "Msg 1422, Level 16, State 2,
Line 1
The mirror server instance is not caught up to the recent changes to
database "MirroredDemo". Unable to fail over."
I thought that I might need to resume the mirroring session. With the
witness server still offline, I tried to execute this command to resume
mirroring: "ALTER DATABASE mirroreddemo SET PARTNER RESUME"
No luck.
I brought the witness online. The status (as shown within the database list
of SSMS) is still "Principal/Disconnected."
I tried resuming mirroring (again, with the command "ALTER DATABASE
mirroreddemo SET PARTNER RESUME") but that did not work either.
At this point the P,M, and W are all online.
Still, the status within the database mirroring properties on the principal
shows "No connection: cannot connect to the mirror or witness server
instances"
I am stumped. Why didn't it (mirroring) come back online and start working
after all the servers are back online?
In researching this issue I have discovered the following articles:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
We are in a similar situation as Muhammad. We have a few servers with more
than 100 databases on them. We are hoping to use mirroring as a high
availability solution to keep servers in different data centers in synch.
After reading those posts I am concerned that database mirroring might not
be able to meet our needs.
When we were running on SQL2k we used Replication. That is a path I would
rather not revisit.
Can anyone offer a solution to these problems
--mirroring not resuming properly on this one database
--mirroring not working (playing nice) on a server with many databases
Keith Kratochvil
"Udit Ghai" <ghai.udit@.gmail.com> wrote in message
news:1160458659.575183.108970@.i3g2000cwc.googlegroups.com...
> my understanding of the situation is that when both or even one of the
> datacenters comes online, it cannot automatically failover because both
> are unable to deciede which of them is the actual principal(beacuse of
> the link between them is not there) unless there is a witness as u
> said. if both come online both try to actively become the principal and
> hence the same problem
> if u add a witness it should solve your problem or u can manually make
> the mirror as your principal
> Keith Kratochvil wrote:
>> HASL2.3 refers to the scenario within this article of a Mirror Server
>> Loss
>> followed by Witness Loss:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>>
>> We have SQL Server 2005 Standard Edition, SP1, + hotfix 2153 installed on
>> Win2k3 R2 servers.
>> We were planning on upgrading some hardware (adding a disk array) to our
>> secondary site (datacenter B) and I thought that it would be a good time
>> to
>> test a couple of database mirroring scenarios.
>> I set up database mirroring on a test database. The primary was on a
>> server
>> in datacenter A. I set up a witness on a box located in the test lab at
>> our
>> office . With this setup I was running database mirroring in a high
>> safety
>> with automatic failover (synchronous) mode.
>> Mirroring was working. I manually failed over and back. No problem
>> there.
>> They shut down the server within datacenter B. The server in datacenter
>> A
>> could still talk to my watcher box in the lab in our office. The primary
>> database on the server within datacenter A stayed available but it showed
>> that the mirrored server was disconnected (as expected).
>>
>> I stopped SQL Server on the watch box in the lab. At that point the
>> primary
>> database on the server within datacenter A became unavailable (Principal,
>> Disconnected / In Recovery). The database was unavailable (as expected).
>>
>> The fly in the ointment:
>> They finished working on the server within datacenter B. The box came
>> back
>> up without any issues. The other databases on that server are intact and
>> everything looks good. SQL Server is running normally...BUT the
>> database
>> mirroring did not resume. The article indicates that "If Server B is
>> restored first, mirroring resumes, although no automatic failover is
>> possible without a witness." I did NOT experience this. My "Server B"
>> has
>> been back online for 45 minutes and its status still shows as "Principal,
>> Disconnected / In Recovery" on Server A (datacenter A) and "Mirror,
>> Disconnected / In Recovery" on Server B (datacenter B).
>>
>> Has anyone else experienced this scenario? I could try turning on the
>> watch
>> server again, but I would like to see the two servers (principal and
>> mirror)
>> synchronize on their own. I realize that automatic failover will not be
>> possible until I bring the watch back online, but the mirrored
>> database(s)
>> should be available now that both the principal and mirror are back
>> online.
>> Right?
>> --
>> Keith Kratochvil
>|||Hello?
Is anyone out there?
More importantly, does anyone have any ideas, suggestions, or helpful
comments?
--
Keith Kratochvil

Database mirroring connection errors

I'm currently using database mirroring as a hot-standby, without a witness. Everything appears to be running fine, but I'm receiving intermittent errors that appear to state that my servers are loosing connectivity, thus disabling (inactivating) the mirror until the connection has been reestablished.

The errors are listed below, basically, I note a block of log entries for each mirrored database stating that mirroring is inactive for the database(s), next Error 1474, next the connection error, then another block of log entries for each mirrored database stating that the mirroring session is once again active.

Database mirroring is inactive for database 'NetRS'. This is an informational message only. No user action is required.

Error: 1474, Severity: 16, State: 1.

Database mirroring connection error 4 '64(The specified network name is no longer available.)' for 'TCP://SQL01NRS:7024'.

Database mirroring is active with database 'Telephony' as the principal copy. This is an informational message only. No user action is required.

I have confirmed that the sessions are in sync, so am not concerned about the data - but would love to get rid of these errors / disable / enable. I'm assumming that if I had setup a Witness, that I would see the mirrored database flip-flopping back and forth between Principal and Mirror.

Please let me know if I can provide any additional details to assist ...

Hi,

What build (service pack) of SQL Server you are runnng.

Check the following link if any of this applies to your situation.

http://support.microsoft.com/kb/912422

Jag

|||

Edition and Build Information on server acting as Principal

Enterprise Edition 9.00.2047.00

Edition and Build Information on server acting as Mirror

Enterprise Edition 9.00.2047.00

|||

Jag:

Thanks for your inquiry and feedback. I looked over the article that you referenced, and appears that this might be applicable. We are running a Synchronous mirroring session without a Witness. Unfortunately, we are running SP1, and this issue appears to have been resolved with SP1. Any ideas?

|||

Hi,

If there are no other errors in SQL server error log, then its possible that the problem is likely to be the windows server or the network.

Error 64 is a windows error.

Check the following:

http://support.microsoft.com/kb/888319/en-us

regards

Jag

Database Mirroring : How to find the TCP port being used for mirroring?

Hi,

I am interested in finding the TCP port which has been setup on a principal/witness/mirror server.

I have queried the sys.database_mirroring_endpoints and sys.endpoints system views but nowhere does it specify the TCP port.

I think the only way to find out is to go to one of the mirrored databases and to right click properties-->Mirroring. I think this is ugly way to find out the TCP port, so if anyones found a better way to find this do tell....
Cheers,
Priyanga
select * from sys.tcp_endpoints

may be what you're looking for...

-Keith.|||Thanks Keith.

Database Mirroring : Asynchronous mirroring without a witness

Hi

I am testing the manual failover of a Asynchronous mirroring without a witness but i get the following error when i issue "ALTER DATABASE testdb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS" on the mirror database server.

Both the mirror and principal is in a synchronized state before i issue the above command.

Msg 1455, Level 16, State 2, Line 1

The database mirroring service cannot be forced for database "testdb" because the database is not in the correct state to become the principal database.

Thanks,

Priyanga

To failover with Asyncronous mirroring, the preferred way to failover is to turn the safety to FULL and issue the ALTER DB SET PARTNER FAILOVER command. that way you will not lose any data when you failover.

The FORCE_SERVICE_ALLOW_DATA_LOSS command should only be used when the principal database is completely broken and it has been decided (by humans) that the mirror server should be promoted to be the new principal. Then the command is issued on the mirror.

State 2 indicates that you are issuing the command on the principal. FORCE_SERVICE can only be issued on the mirror and the mirror cannot talk to the principal.

thanks,
mark

|||

Thanks Mark.

The partner force_service_allow_data_loss command will only work on the mirror when the principal server is not reachable.

Cheers,
Priyanga

Database Mirroring - setting up the witness

SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
Corporate stuff runs in the parent domain (including witness)
Hosted (client) servers run in the child domain(s).
I set up mirroring between two SQL2k5 boxes in our child domain. SQL Server
runs under a network account in the child domain. We run our corparate SQL
Servers under a different account in our parent domain.
I wanted to have the witness box (located in our corporate domain) monitor
the primary and mirrors in the various child domains. I am running into the
following error message: "The ALTER DATABASE command could not be sent to
the remote server instance 'TCP://Witness-Server.TheParentDomain.com:5022'.
The database mirroring configuration was not changed. Verify that the server
is connected, and try again."
I verified that the appropriate endpoints exist within the witness via the
following query: SELECT name, role_desc, state_desc FROM
sys.database_mirroring_endpoints
What am I doing wrong?
Keith KratochvilThe service accounts are most likely different between the two domains. You
need to add the service account of the witness to the SQL Server hosting the
principal and mirror and vice versa. You then have to grant CONNECT TO
permissions to the account for the Database Mirroring endpoint.
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23T73eWR0GHA.2036@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
> Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
> Corporate stuff runs in the parent domain (including witness)
> Hosted (client) servers run in the child domain(s).
>
> I set up mirroring between two SQL2k5 boxes in our child domain. SQL
> Server runs under a network account in the child domain. We run our
> corparate SQL Servers under a different account in our parent domain.
> I wanted to have the witness box (located in our corporate domain) monitor
> the primary and mirrors in the various child domains. I am running into
> the following error message: "The ALTER DATABASE command could not be sent
> to the remote server instance
> 'TCP://Witness-Server.TheParentDomain.com:5022'. The database mirroring
> configuration was not changed. Verify that the server is connected, and
> try again."
> I verified that the appropriate endpoints exist within the witness via the
> following query: SELECT name, role_desc, state_desc FROM
> sys.database_mirroring_endpoints
> What am I doing wrong?
> --
> Keith Kratochvil
>
>

Database Mirroring - setting up the witness

SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
Corporate stuff runs in the parent domain (including witness)
Hosted (client) servers run in the child domain(s).
I set up mirroring between two SQL2k5 boxes in our child domain. SQL Server
runs under a network account in the child domain. We run our corparate SQL
Servers under a different account in our parent domain.
I wanted to have the witness box (located in our corporate domain) monitor
the primary and mirrors in the various child domains. I am running into the
following error message: "The ALTER DATABASE command could not be sent to
the remote server instance 'TCP://Witness-Server.TheParentDomain.com:5022'.
The database mirroring configuration was not changed. Verify that the server
is connected, and try again."
I verified that the appropriate endpoints exist within the witness via the
following query: SELECT name, role_desc, state_desc FROM
sys.database_mirroring_endpoints
What am I doing wrong?
--
Keith KratochvilThe service accounts are most likely different between the two domains. You
need to add the service account of the witness to the SQL Server hosting the
principal and mirror and vice versa. You then have to grant CONNECT TO
permissions to the account for the Database Mirroring endpoint.
--
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23T73eWR0GHA.2036@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
> Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
> Corporate stuff runs in the parent domain (including witness)
> Hosted (client) servers run in the child domain(s).
>
> I set up mirroring between two SQL2k5 boxes in our child domain. SQL
> Server runs under a network account in the child domain. We run our
> corparate SQL Servers under a different account in our parent domain.
> I wanted to have the witness box (located in our corporate domain) monitor
> the primary and mirrors in the various child domains. I am running into
> the following error message: "The ALTER DATABASE command could not be sent
> to the remote server instance
> 'TCP://Witness-Server.TheParentDomain.com:5022'. The database mirroring
> configuration was not changed. Verify that the server is connected, and
> try again."
> I verified that the appropriate endpoints exist within the witness via the
> following query: SELECT name, role_desc, state_desc FROM
> sys.database_mirroring_endpoints
> What am I doing wrong?
> --
> Keith Kratochvil
>
>sql

Database Mirroring - 'NT AUTHORITY\ANONYMOUS LOGON.' failed

Hello,

I have set up 3 servers - Primary, Mirror and Witness. When I run the database mirroring wizard all my endpoints are configured, but when i start the mirroring service i get a 1418 error - in the logs in says -

Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.20.1.5]

I have looked through the BOL and can't find anything helpful. - I'm pretty new to SQL server, so any help is very much appreciated!

Thanks

Kerpoise

This is not a SQL problem but an authentication issue. The right approach is to follow the guidance of the authentication troubleshooting whitepaper: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

HTH,
~ Remus

|||But why is my Primary server trying to logon as NT AUTHORITY/ANONYMOUS LOGON? When I have defined my logon through the Mirroring Wizard as a sysadmin account, with enabled Connect privileges on all servers.

I can't find a simple solution in the white paper. Isn't there a simple way to either - authenticate as a sysadmin account, or to allow NT AUTHORITY/ANONYMOUS to have CONNECT privileges to all servers?

It seems like this should be a very quick thing to do, but I really can't figure it out.

Please help!|||

Do not grant connect permission to ANONYMOUS LOGON.

When connecting the two instances what gets authenticated is the 'service account', the Windows account that runs the SQL Server instance. Depending on how you insnstalled the instances, the appropiate action is:
- if installed as 'LOCAL SERVICE': you must change the service account, it will never work. The SQL Management console in mmc has an option to change the service account, selecta different account.
- if installed as 'NETWORK SERVICE' or 'LocalSystem': you have to register the service SPN for Kerberos authentication to succeed. Use a tool like setspn.exe (available at microsoft download center) and register the SPN. Mirroring will use an SPN on the format 'MSSQLSvc/<partnername>:<partnerport>'. Also, make sure the AUTHENTICATION option on the mirroring endpoint is WINDOWS, WINDOWS NEGOTIATE or WINDOWS KERBEROS (in other words NTLM will not work). After registering the two SPNs (both for mirror and principal!), the authentication will resolve to the machine account ('DOMAIN\MachineName$'), you must create a login for this account and grant connect permission to it.
- if installed as a local account ('PrincipalMachine\LocalAccount' and/or 'MirrorMachine\LocalAccount'): it will not work, you must change the service account(s).
- if installed as domain account ('DOMAIN\UserName'), then it should work as long as both the mirror and the principal are in the same domain, or there is a trust relationship between their domains.

An alternative is to use certificate based authentication, then the whole domain/user/SPNs issue vanishes as the authentication will use a diferent protocol (SChannel's TLS).

HTH,
~ Remus