Database Mirroring: Cannot set the Mirror Server instance as the partner on Principle server

Hi,

I’m trying to get database mirroring working but having problem when principal and mirror are not on the Domain. (I got it working once if both principle and mirror servers are on the domain.) Both servers have fully qualified domain names. Both have Windows server 2003 SP1 and SQL Server 2005 June CTP.

I used Local System Acct/Mixed mode and follow the Setting up Database Mirroring using Certificates article in the BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/df489ecd-deee-465c-a26a-6d1bef6d7b66.htm

I kept getting error when trying to set the Mirror Server instance as the partner on Principle server with this command:

ALTER DATABASE Testdb

SET PARTNER = 'TCP://<mirror server.fqdomainname.com>:5022';

GO

Here is the error I kept getting:

Msg 1460, Level 16, State 2, Line 1

The partner for database "Testdb" is not defined or is different from the one attempting to establish database

How can I troubleshoot this further? Could anyone please point me in the right direction?

Thanks,
Pamb

so, i went to the devloper and asked "What is the 1460 error?"

he looked around and he finally said "We don't throw that error any more. Go tell the customer that, in fact, they are not seeing this error. Use the Force. :)"

OK, then i offered to buy him a beer.

two minutes later, it turns out that the error occurs when comparing the server names AND there are different collations for the two different servers. the code was changed recently to compare the server names differently.

so, make sure that the collations on the two servers are the same.

hope that helps!!

thanks,
mark|||Hi Mark,

Thanks for your response. Unfortunately, I have a few more questions...

I could check the Collation setting on the Principal database and it is set to "SQL_Latin1_General_CP1_CI_AS" but since the Mirror Database is in the restoring mode, I can't seem to check it's setting. Well, is this the right collation setting first of all?

Thanks in advance again for your help.

Pam|||Hi Mark,

I managed to check the collation setting at the Server-level and Database-level on both Principle and Mirror databases and the Collation is set to "SQL_Latin1_General_CP1_CI_AS".

What's next? Tequila? :)

Thanks again,
Pam

|||Pam,

I have the same error ... how did you finally manage?
Thanks
Jerome|||We shouldn't be breakin' out the Tequila yet.

From the developer:

--
you can get the 1460 error if:

1) your _servers_ (not the dbs themselves) have different collations

2) you are not specifying the name of the partner correctly (use netstat on the boxes to see what the box thinks it's called)

3) you are trying to establish on a secondary network
1. i know that you said you checked the server collations, but make sure.
2. make sure that you are specifying the correct FQDN.
3. this isn't a security problem. it is failing at the direct string compares. so, what you provided at the setup for the string compare is not what the server thinks that it is called.

are there multiple NICs on your machines?

thanks,
mark

|||Hi Mark,

What do you mean by a Secondary Network?

Yes, there are two nics on both machines. So, we just tried setting them up using just one IP address and issued the set partner query again but got same error.

I then started from scratch by uninstalling server/data and followed the same mirroring process but still got the same error.

[We also noticed that the TCP/IP protocols (Server IP and Localhost IP) are disabled under the Server Configuration Manager so I enabled them.]
Which table does this query issue against?
ALTER DATABASE Scout
SET PARTNER = 'TCP://<FQDN:5022';
Thanks,
Pam

|||

Hi Mark,

We have finally figured this out and would actually like to report what seems to be the software problem.

Here are the steps that we took...

1. We modified the Hosts file (under Windows32\System\Drivers\etc) on both machines so they could resolve each others fully qualified domain names:

principal.foo.com 10.0.0.132
mirror.foo.com 10.0.0.133

2. In the Advanced TCP/IP settings for DNS, we set the "DNS suffix for this connection" to foo.com.

3. We then issued the set partner queries using FQD name:

Alter database set PARTNER = 'TCP://principal.foo.com:5022'; [on the Mirror server]

Alter database set PARTNER = 'TCP://mirror.foo.com:5022'; [on the Principal server]

This failed the same 1460 error

4. We then tried it without the domain names:

Alter database set PARTNER = 'TCP://principal:5022'; [on the Mirror server]
Alter database set PARTNER = 'TCP://mirror:5022'; [on the Principal server]

*Success!*
What gave us a clue was running the Database Mirroring Properties Wizard on the Principal server. We noticed that the Wizard shows a non-FQDN as the Principal server, i.e. ‘TCP://principal:5022’.

It’s not clear to us how SQL is generating the principal name that has to be later string matched against name issued from the mirror server. This is a pretty fragile mechanism. We’d like to suggest that the TCP://[hostname] be resolved in the typical way, i.e. convert hostnames to IP’s as necessary and compare the IP addresses.

Thanks for all your help! [you owe us a beer ;-)]

Pam

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 question

I Posted this in SQL Server general and it was suggested I move it here.
Hello,
SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
In mirror monitoring witness indicates disconected from mirror.
All else is green.
Can ping witness from mirror and connect via MSSMS.
No errors in Witness SQL server errorlog.
What can I do to debug this? I have drilled into the status and I do not
see any errors. I kinda assumed I there is a connectivity problem. Any
ideas on how to debug/resolve it?
If you can't mirror the database due to network connectivity problems
mirroring will be shut down in High Safety, High Availability modes.
I would run a ping -t between your publisher and mirror to check network
stability, and then copy files back and forth to see if you get the same
latencies each way.
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
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:538D73DD-AB07-4970-9F78-C591F6806CD7@.microsoft.com...
>I Posted this in SQL Server general and it was suggested I move it here.
> Hello,
> SQL Server 2005 SP1 SE on WINDOWS 2003 sp1 both principal and mirror
> SQL Serverver Express SP1 on WINDOWS 2003 sp1 Witness
> In mirror monitoring witness indicates disconected from mirror.
> All else is green.
> Can ping witness from mirror and connect via MSSMS.
> No errors in Witness SQL server errorlog.
> What can I do to debug this? I have drilled into the status and I do not
> see any errors. I kinda assumed I there is a connectivity problem. Any
> ideas on how to debug/resolve it?

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 Client-Side Redirect

I am having a problem connecting to mirror (new Principal) server after principal failover. I am using Microsoft's JDBC driver V1.1 (I have tried version 1.2 as well).

Lets say Machine A is the principal and Machine B is the mirror. Both machines are on the same network, however, the plan is to have them distributed over a WAN and as a result, they were configured to use certificate authentication.

I replaced the ips with x and the password with *. The connection information in the hibernate config file is as follows:

<property name="connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>

<property name="connection.url">jdbcqlserver://x.x.x.x:1433;failoverPartner=jdbcqlserver://x.x.x.x:1433</property>

<property name = "loginTimeout">1</property>

<property name="connection.databaseName">APS_SQL_DEV</property>

<property name="connection.username">sa</property>

<property name="connection.password">******</property>

When Machine A and Machine B are operating in their assigned roles, the connection works and SQL statements execute as expected.

Problem:

When I force Machine A to failover and Machine B becomes the principal server, the application hangs. Looking at Machine A's log file, for the period of application hang, I noticed that user sa could not be logged in. I would expect this to be the case since Machine A is the Mirror at this point.

I decided to test the application again in the problem state. So I ran my sql statement and the application hung for about a minute. While it was hanging, I made Machine B failover and Machine A became the principal again. As soon as Machine A became the principal, the application finished executing and returned my query results.

I am not sure if this is a problem with the JDBC Driver not detecting that Machine A is throwing a login exception and not attempting to connect to the failoverPartner, or, if the SQL Instances are configured incorrectly and not returning the exception to the driver.

Please let me know if you need any log file data. Any help is greatly appreciated. Thank you inadvance!

Chris

<property name="loginTimeout">1</property>

Should read <property name="connection.loginTimeout">1</property>

Also, I have tried using

<property name="connection.integratedSecurity">true</property>

and it still did not work.

Does anyone know if the last property is required for certificate authentication? I am not sure, but I thought it was only needed for mindows authentication.

ANY help would be GREAT! Thank you!

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 Mirror failover from Principal to Mirror

All,
I have two sql servers setup for Data Mirroring. when I do a failover to the
mirror server. The application can not connect to the database Mirror server
that has become the Principal server. I'm using sql authentication for the
application.
I have to drop the user in the Database and recreate it to allow it to
connnect to the database. Below is the error message:
The server principal "nyccs" is not able to access the database "jztest"
under the current security context.
What security context do I have to setup the data Mirror inorder to
automatically failover without any failures.
Thanks,
Naana.
Message posted via http://www.droptable.comHave you read and followed the info here?
http://msdn2.microsoft.com/en-us/library/ms177412.aspx
In specific, check this section:
http://msdn2.microsoft.com/en-us/library/ms366346.aspx
"Naana via droptable.com" wrote:

> All,
> I have two sql servers setup for Data Mirroring. when I do a failover to t
he
> mirror server. The application can not connect to the database Mirror serv
er
> that has become the Principal server. I'm using sql authentication for the
> application.
> I have to drop the user in the Database and recreate it to allow it to
> connnect to the database. Below is the error message:
> The server principal "nyccs" is not able to access the database "jztest"
> under the current security context.
> What security context do I have to setup the data Mirror inorder to
> automatically failover without any failures.
> Thanks,
> Naana.
> --
> Message posted via http://www.droptable.com
>

Database Mirror + Snapshot + Replication Questions

All,

We have SQL 2005 db mirror configured with a witness server for high availability. Node 1 is the principal and Node 2 is the mirror. A nightly job creates a snapshot on Node 2. The snapshot is used for previous day reporting queries. We have now been asked to present another copy of the database for near-time reporting. I thought about possibly adding a peer-to-peer replication as part of my environment but was hoping to see what everyone else out there is doing.

Regards,

Ian

I tend to use transactional replication with a pollinginterval of 1 and replicate the execution of stored procedures as much as possible.