Hello,
I can't see data for mirror database in Database Mirrororing Monitor.
The mirroring is configured and runs properly between 2 locations. I can
even register mirrored database with both peers - it works the same with
principal and mirrored databases in registration process. I'm using windows
authentication and the account has sysadmin role in both server instances.
However upon registration, the principal database appears in state
Synchronized while mirror database appears with "Error retrieving data" in
Current Role, Mirroring State and Witness Connections columns.
Both SQL servers are 64-bit Enterprise 2005 SP2 versions on 64-bit Windows
Server 2003 SP2.
Could someone help me to setup the monitoring?
Thanks a lot
eXavierHello eXavier,
Can you please answer the following questions:
1. What version of SQL Server 2005 are you running? The build number
resembles
9.0.xxxx.
2. Can you please zip and send me the error logs for each of the servers.
3. Run the following SQL statements and please return the results to me in
an
output file
a. Select * from sys.databases (run this on principle and mirror server)
b. Select * from sys.database_mirroring (run this on principle and mirror
server)
c. Select * from sys.database_mirroring_witnesses (run this on the witness
server)
d. Select * from sys.database_mirroring_endpoints (run on all servers)
e. Select * from sys.tcp_endpoints (run on all servers)
f. Select * from sys.server_principals (run on all servers)
g. Select * from sys.database_recovery_status (run on principal and mirror
server)
h. Select * from msdb.dbo.backupset (run on principal server only)
i. Select * from sys.dm_os_wait_stats (run on principal and mirror servers)
j. Select * from sys.sysprocesses (run on all servers)
k. Verify the connectivity between the servers using Telnet and send the
results.
To do this:
l. Open a command prompt.
m. Run telnet <machine name> <port number> where machine name is the fully
qualified domain name and port number is the port number for the mirroring
endpoint.
n. Run this from the principal to the mirror and from the mirror to the
principal.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql
Showing posts with label mirror. Show all posts
Showing posts with label mirror. Show all posts
Tuesday, March 27, 2012
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
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
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?
sqlDatabase 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...
>
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
>
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 using certificate? (two principles and one mirror)
Hi, all.
here is the situation.
we designated one server as a mirrored server. this server will host all mirrored databases from different principles in different server using certificate.
1) Server A (Principle) Server B(Mirrored)
2) Server C (Principle) Server B(Mirrored)
I was able to set up mirroring between A and B. But unable to set up mirroring between C and B.
My question is "Can I grant connect on endpoint to two different host logins?"
For example, on the server B
I created two different logins for Server A and C in order to assign different certificates to two principle servers.
For Server A.
create login server_A_login with password = 'djkkajnxks';
go
create user server_A_user for login server_A_login
go
create certificate server_A_cert
authorization Server_A_user
from file = 'c:\certificate\server_a_cert.cer'
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [server_a_login];
GO
For Server C.
create login server_C_login with password = 'djkkajnxks';
go
create user server_C_user for login server_c_login
go
create certificate server_c_cert
authorization Server_c_user
from file = 'c:\certificate\server_c_cert.cer'
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [server_c_login];
GO
when I set up like this and try to start mirroring, I am getting error from server c saying, "the network tcp:\serverb.corp.com can not be reached or does not exist"
But mirroring between server a and b is OK. I set up them first, then between c and b.
any ideas?
thanks,
Try to backup the cert from A and put into C, make sure the thumbprint version of the certificate are same. Try to execute SELECT * from sys.certificates to view the thumbprint information. Hope this will help you.
Best Regards,
Hans1982
|||Review the similar information in SQL 2005 BOL http://msdn2.microsoft.com/en-us/ms178578.aspx and additional information http://msdn2.microsoft.com/en-us/ms191140.aspx fyi.Database mirroring setup without downtime
Does anyone know if you can mirror a database while users are banging away on
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.
Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.
Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
Database mirroring setup without downtime
Does anyone know if you can mirror a database while users are banging away o
n
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and i
t
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have t
o
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
n
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and i
t
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have t
o
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
Database mirroring setup without downtime
Does anyone know if you can mirror a database while users are banging away on
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
Sunday, March 25, 2012
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
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 in SQL Server 2005
I have been trying to setup a mirror database using the instructions in the
doc and have not had much success. I either get a message that a service is
not running or that the database mirror is busy. Has any had any luck with
this. If so please let me know the exact sequence of steps that I need to
follow.
Thanks
There's an extremely comprehensive article here
http://www.microsoft.com/technet/pro.../dbmirror.mspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Raji" <Raji@.discussions.microsoft.com> wrote in message
news:174EA9C2-9A9B-4123-A395-95E488A0B14C@.microsoft.com...
>I have been trying to setup a mirror database using the instructions in the
> doc and have not had much success. I either get a message that a service
> is
> not running or that the database mirror is busy. Has any had any luck with
> this. If so please let me know the exact sequence of steps that I need to
> follow.
> Thanks
>
doc and have not had much success. I either get a message that a service is
not running or that the database mirror is busy. Has any had any luck with
this. If so please let me know the exact sequence of steps that I need to
follow.
Thanks
There's an extremely comprehensive article here
http://www.microsoft.com/technet/pro.../dbmirror.mspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Raji" <Raji@.discussions.microsoft.com> wrote in message
news:174EA9C2-9A9B-4123-A395-95E488A0B14C@.microsoft.com...
>I have been trying to setup a mirror database using the instructions in the
> doc and have not had much success. I either get a message that a service
> is
> not running or that the database mirror is busy. Has any had any luck with
> this. If so please let me know the exact sequence of steps that I need to
> follow.
> Thanks
>
Database Mirroring in SQL Server 2005
I have been trying to setup a mirror database using the instructions in the
doc and have not had much success. I either get a message that a service is
not running or that the database mirror is busy. Has any had any luck with
this. If so please let me know the exact sequence of steps that I need to
follow.
ThanksThere's an extremely comprehensive article here
http://www.microsoft.com/technet/pr...5/dbmirror.mspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Raji" <Raji@.discussions.microsoft.com> wrote in message
news:174EA9C2-9A9B-4123-A395-95E488A0B14C@.microsoft.com...
>I have been trying to setup a mirror database using the instructions in the
> doc and have not had much success. I either get a message that a service
> is
> not running or that the database mirror is busy. Has any had any luck with
> this. If so please let me know the exact sequence of steps that I need to
> follow.
> Thanks
>
doc and have not had much success. I either get a message that a service is
not running or that the database mirror is busy. Has any had any luck with
this. If so please let me know the exact sequence of steps that I need to
follow.
ThanksThere's an extremely comprehensive article here
http://www.microsoft.com/technet/pr...5/dbmirror.mspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Raji" <Raji@.discussions.microsoft.com> wrote in message
news:174EA9C2-9A9B-4123-A395-95E488A0B14C@.microsoft.com...
>I have been trying to setup a mirror database using the instructions in the
> doc and have not had much success. I either get a message that a service
> is
> not running or that the database mirror is busy. Has any had any luck with
> this. If so please let me know the exact sequence of steps that I need to
> follow.
> Thanks
>
Database Mirroring in SQL Server 2005
I have been trying to setup a mirror database using the instructions in the
doc and have not had much success. I either get a message that a service is
not running or that the database mirror is busy. Has any had any luck with
this. If so please let me know the exact sequence of steps that I need to
follow.
ThanksThere's an extremely comprehensive article here
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Raji" <Raji@.discussions.microsoft.com> wrote in message
news:174EA9C2-9A9B-4123-A395-95E488A0B14C@.microsoft.com...
>I have been trying to setup a mirror database using the instructions in the
> doc and have not had much success. I either get a message that a service
> is
> not running or that the database mirror is busy. Has any had any luck with
> this. If so please let me know the exact sequence of steps that I need to
> follow.
> Thanks
>
doc and have not had much success. I either get a message that a service is
not running or that the database mirror is busy. Has any had any luck with
this. If so please let me know the exact sequence of steps that I need to
follow.
ThanksThere's an extremely comprehensive article here
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Raji" <Raji@.discussions.microsoft.com> wrote in message
news:174EA9C2-9A9B-4123-A395-95E488A0B14C@.microsoft.com...
>I have been trying to setup a mirror database using the instructions in the
> doc and have not had much success. I either get a message that a service
> is
> not running or that the database mirror is busy. Has any had any luck with
> this. If so please let me know the exact sequence of steps that I need to
> follow.
> Thanks
>
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
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 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">jdbc
<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!
Thursday, March 22, 2012
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 - unable to mirror 59th database
As the subject suggests, I am unable to mirror my 59th database.
(2) database servers
HP ML370 G4
Dual Xeon 3.4Ghz, 8GB RAM
Windows 2003 R2 Enterprise
SQL Server 2005 Standard - patched to 2153
(1) watch server
HP DL 360 G4
Xeon 3Ghz, 2GB RAM
Win2k3 Standard
SQL Server Express - patched to 2153
I was able to restore the database and the transaction log to the desired
mirror. Then I indicated what the partner server should be (via T-SQL).
Then I switched to my SSMS query window that was connected to the primary
server and executed the commands to set the partner, mirror, and alter the
partner timeout.
The database was mirrored, but then I noticed a sign of trouble within the
Database Mirroring Monitor. One of the databases that was already being
mirrored switched status to "(Principal, Disconnected)"
I executed the following query to check the status:
select mirroring_state_desc, count(*)
From sys.database_mirroring
group by mirroring_state_desc
Here are the results:
mirroring_state_desc
-- --
NULL 60
DISCONNECTED 1
SYNCHRONIZED 58
I wanted to make sure that the database mirroring monitoring job (called by
SQL Server Agent) was not part of the problem, so I stopped monitoring by
calling this stored procedure: sp_dbmmonitordropmonitoring
and I waited... No change in status.
I decided to stop mirroring on the newly added database. I used the
following command to stop mirroring:
ALTER DATABASE foo SET PARTNER OFF
and the other database (the one that had a status of Principal, Disconnected
synchronized and came back online.
In researching the problem I discovered the following posts:
http://forums.microsoft.com/MSDN/sho...41900&siteid=1
http://groups.google.com/group/micro...d680cf d4d127
It seems as though others are having the same issue. How can I mirror more
databases on one server? This particular server has 120 databases and I
would like to get that number to 200 before we even start thinking about a
new server.
Keith
Microsoft recommends you limit the number of databases you are mirroring to
10 or so.
Based on the resource (memory and threads) utilization by database
mirroring, it is a best practices recommendation to mirror not more than 10
databases in one instance. The recommended limit of 10 is only approximate
and not an exact number. The limit can vary depending upon your application
and workload.
From -
http://www.microsoft.com/technet/pro...est_pract.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Keith_MSDN" <Keith_MSDN@.newsgroup.nospam> wrote in message
news:71B7CAFA-EAC2-4A1E-A781-0C8D479218AC@.microsoft.com...
> As the subject suggests, I am unable to mirror my 59th database.
> (2) database servers
> HP ML370 G4
> Dual Xeon 3.4Ghz, 8GB RAM
> Windows 2003 R2 Enterprise
> SQL Server 2005 Standard - patched to 2153
> (1) watch server
> HP DL 360 G4
> Xeon 3Ghz, 2GB RAM
> Win2k3 Standard
> SQL Server Express - patched to 2153
> I was able to restore the database and the transaction log to the desired
> mirror. Then I indicated what the partner server should be (via T-SQL).
> Then I switched to my SSMS query window that was connected to the primary
> server and executed the commands to set the partner, mirror, and alter the
> partner timeout.
> The database was mirrored, but then I noticed a sign of trouble within the
> Database Mirroring Monitor. One of the databases that was already being
> mirrored switched status to "(Principal, Disconnected)"
> I executed the following query to check the status:
> select mirroring_state_desc, count(*)
> From sys.database_mirroring
> group by mirroring_state_desc
> Here are the results:
> mirroring_state_desc
> -- --
> NULL 60
> DISCONNECTED 1
> SYNCHRONIZED 58
>
> I wanted to make sure that the database mirroring monitoring job (called
> by
> SQL Server Agent) was not part of the problem, so I stopped monitoring by
> calling this stored procedure: sp_dbmmonitordropmonitoring
> and I waited... No change in status.
>
> I decided to stop mirroring on the newly added database. I used the
> following command to stop mirroring:
> ALTER DATABASE foo SET PARTNER OFF
> and the other database (the one that had a status of Principal,
> Disconnected
> synchronized and came back online.
>
> In researching the problem I discovered the following posts:
> http://forums.microsoft.com/MSDN/sho...41900&siteid=1
> http://groups.google.com/group/micro...d680cf d4d127
> It seems as though others are having the same issue. How can I mirror
> more
> databases on one server? This particular server has 120 databases and I
> would like to get that number to 200 before we even start thinking about a
> new server.
> --
> Keith
|||Hello Keith,
I understand that when you tried to configure more than 58 databases to
mirroring, it failed with disconnected status. If I'm off-base, please
let's know.
Based on my research, this issue seems to be related to virutal memory
allocation issue. Database mirroring sessions consume much virutal memroy
per session. You may want to check if you could find "Failed Virtual
Allocate Bytes" in SQL error log. If so, you may want to increase
memtoleave area by setting -g512 as start parameter to see if this could
workarond the issue. Please see the following article for details on
memeorytoleave area.
http://msdn2.microsoft.com/en-us/library/ms190737.aspx
You may also want to consider 64-bit as well, and This would only be an
issue on x86 due to memory limitation of X86 platform.
If this workaround does not meet your requirement, or you have any
concerns, please get SQL Error log, and send it to me at
petery@.microsoft.com, we will look into this problem further. We look
forward to your reply. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks Peter. Yes, I want to mirror more than 58 databases on one server.
Upgrading to 64 Bit version of SQL (and Windows) isn't an option at this
time.
I looked within the SQL Server Log on the primary, mirror, and witness and I
did NOT find the Failed Virtual Allocate Bytes error. I even used
AgentRansack to search all the log file in the directory. I could not find
any hits for failed virtual (or virtual).
When searching the log files on the mirror I noticed that There is one SQL
Dump in the directory. Let me know if you want me to email the information.
The .mdmp file is 10MB.
I will try increasing memtoleave via the -g startup param. I assume that I
will need to modify this value on the primary and the mirror, is that
correct? This solution will require downtime, so I need to get the ball
rolling on scheduling a brief outage.
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> I understand that when you tried to configure more than 58 databases to
> mirroring, it failed with disconnected status. If I'm off-base, please
> let's know.
> Based on my research, this issue seems to be related to virutal memory
> allocation issue. Database mirroring sessions consume much virutal memroy
> per session. You may want to check if you could find "Failed Virtual
> Allocate Bytes" in SQL error log. If so, you may want to increase
> memtoleave area by setting -g512 as start parameter to see if this could
> workarond the issue. Please see the following article for details on
> memeorytoleave area.
> http://msdn2.microsoft.com/en-us/library/ms190737.aspx
> You may also want to consider 64-bit as well, and This would only be an
> issue on x86 due to memory limitation of X86 platform.
> If this workaround does not meet your requirement, or you have any
> concerns, please get SQL Error log, and send it to me at
> petery@.microsoft.com, we will look into this problem further. We look
> forward to your reply. Thank you!
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications
> <http://msdn.microsoft.com/subscripti...s/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscripti...t/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
(2) database servers
HP ML370 G4
Dual Xeon 3.4Ghz, 8GB RAM
Windows 2003 R2 Enterprise
SQL Server 2005 Standard - patched to 2153
(1) watch server
HP DL 360 G4
Xeon 3Ghz, 2GB RAM
Win2k3 Standard
SQL Server Express - patched to 2153
I was able to restore the database and the transaction log to the desired
mirror. Then I indicated what the partner server should be (via T-SQL).
Then I switched to my SSMS query window that was connected to the primary
server and executed the commands to set the partner, mirror, and alter the
partner timeout.
The database was mirrored, but then I noticed a sign of trouble within the
Database Mirroring Monitor. One of the databases that was already being
mirrored switched status to "(Principal, Disconnected)"
I executed the following query to check the status:
select mirroring_state_desc, count(*)
From sys.database_mirroring
group by mirroring_state_desc
Here are the results:
mirroring_state_desc
-- --
NULL 60
DISCONNECTED 1
SYNCHRONIZED 58
I wanted to make sure that the database mirroring monitoring job (called by
SQL Server Agent) was not part of the problem, so I stopped monitoring by
calling this stored procedure: sp_dbmmonitordropmonitoring
and I waited... No change in status.
I decided to stop mirroring on the newly added database. I used the
following command to stop mirroring:
ALTER DATABASE foo SET PARTNER OFF
and the other database (the one that had a status of Principal, Disconnected
synchronized and came back online.
In researching the problem I discovered the following posts:
http://forums.microsoft.com/MSDN/sho...41900&siteid=1
http://groups.google.com/group/micro...d680cf d4d127
It seems as though others are having the same issue. How can I mirror more
databases on one server? This particular server has 120 databases and I
would like to get that number to 200 before we even start thinking about a
new server.
Keith
Microsoft recommends you limit the number of databases you are mirroring to
10 or so.
Based on the resource (memory and threads) utilization by database
mirroring, it is a best practices recommendation to mirror not more than 10
databases in one instance. The recommended limit of 10 is only approximate
and not an exact number. The limit can vary depending upon your application
and workload.
From -
http://www.microsoft.com/technet/pro...est_pract.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Keith_MSDN" <Keith_MSDN@.newsgroup.nospam> wrote in message
news:71B7CAFA-EAC2-4A1E-A781-0C8D479218AC@.microsoft.com...
> As the subject suggests, I am unable to mirror my 59th database.
> (2) database servers
> HP ML370 G4
> Dual Xeon 3.4Ghz, 8GB RAM
> Windows 2003 R2 Enterprise
> SQL Server 2005 Standard - patched to 2153
> (1) watch server
> HP DL 360 G4
> Xeon 3Ghz, 2GB RAM
> Win2k3 Standard
> SQL Server Express - patched to 2153
> I was able to restore the database and the transaction log to the desired
> mirror. Then I indicated what the partner server should be (via T-SQL).
> Then I switched to my SSMS query window that was connected to the primary
> server and executed the commands to set the partner, mirror, and alter the
> partner timeout.
> The database was mirrored, but then I noticed a sign of trouble within the
> Database Mirroring Monitor. One of the databases that was already being
> mirrored switched status to "(Principal, Disconnected)"
> I executed the following query to check the status:
> select mirroring_state_desc, count(*)
> From sys.database_mirroring
> group by mirroring_state_desc
> Here are the results:
> mirroring_state_desc
> -- --
> NULL 60
> DISCONNECTED 1
> SYNCHRONIZED 58
>
> I wanted to make sure that the database mirroring monitoring job (called
> by
> SQL Server Agent) was not part of the problem, so I stopped monitoring by
> calling this stored procedure: sp_dbmmonitordropmonitoring
> and I waited... No change in status.
>
> I decided to stop mirroring on the newly added database. I used the
> following command to stop mirroring:
> ALTER DATABASE foo SET PARTNER OFF
> and the other database (the one that had a status of Principal,
> Disconnected
> synchronized and came back online.
>
> In researching the problem I discovered the following posts:
> http://forums.microsoft.com/MSDN/sho...41900&siteid=1
> http://groups.google.com/group/micro...d680cf d4d127
> It seems as though others are having the same issue. How can I mirror
> more
> databases on one server? This particular server has 120 databases and I
> would like to get that number to 200 before we even start thinking about a
> new server.
> --
> Keith
|||Hello Keith,
I understand that when you tried to configure more than 58 databases to
mirroring, it failed with disconnected status. If I'm off-base, please
let's know.
Based on my research, this issue seems to be related to virutal memory
allocation issue. Database mirroring sessions consume much virutal memroy
per session. You may want to check if you could find "Failed Virtual
Allocate Bytes" in SQL error log. If so, you may want to increase
memtoleave area by setting -g512 as start parameter to see if this could
workarond the issue. Please see the following article for details on
memeorytoleave area.
http://msdn2.microsoft.com/en-us/library/ms190737.aspx
You may also want to consider 64-bit as well, and This would only be an
issue on x86 due to memory limitation of X86 platform.
If this workaround does not meet your requirement, or you have any
concerns, please get SQL Error log, and send it to me at
petery@.microsoft.com, we will look into this problem further. We look
forward to your reply. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks Peter. Yes, I want to mirror more than 58 databases on one server.
Upgrading to 64 Bit version of SQL (and Windows) isn't an option at this
time.
I looked within the SQL Server Log on the primary, mirror, and witness and I
did NOT find the Failed Virtual Allocate Bytes error. I even used
AgentRansack to search all the log file in the directory. I could not find
any hits for failed virtual (or virtual).
When searching the log files on the mirror I noticed that There is one SQL
Dump in the directory. Let me know if you want me to email the information.
The .mdmp file is 10MB.
I will try increasing memtoleave via the -g startup param. I assume that I
will need to modify this value on the primary and the mirror, is that
correct? This solution will require downtime, so I need to get the ball
rolling on scheduling a brief outage.
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> I understand that when you tried to configure more than 58 databases to
> mirroring, it failed with disconnected status. If I'm off-base, please
> let's know.
> Based on my research, this issue seems to be related to virutal memory
> allocation issue. Database mirroring sessions consume much virutal memroy
> per session. You may want to check if you could find "Failed Virtual
> Allocate Bytes" in SQL error log. If so, you may want to increase
> memtoleave area by setting -g512 as start parameter to see if this could
> workarond the issue. Please see the following article for details on
> memeorytoleave area.
> http://msdn2.microsoft.com/en-us/library/ms190737.aspx
> You may also want to consider 64-bit as well, and This would only be an
> issue on x86 due to memory limitation of X86 platform.
> If this workaround does not meet your requirement, or you have any
> concerns, please get SQL Error log, and send it to me at
> petery@.microsoft.com, we will look into this problem further. We look
> forward to your reply. Thank you!
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications
> <http://msdn.microsoft.com/subscripti...s/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscripti...t/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
Database Mirroring - unable to mirror 59th database
As the subject suggests, I am unable to mirror my 59th database.
(2) database servers
HP ML370 G4
Dual Xeon 3.4Ghz, 8GB RAM
Windows 2003 R2 Enterprise
SQL Server 2005 Standard - patched to 2153
(1) watch server
HP DL 360 G4
Xeon 3Ghz, 2GB RAM
Win2k3 Standard
SQL Server Express - patched to 2153
I was able to restore the database and the transaction log to the desired
mirror. Then I indicated what the partner server should be (via T-SQL).
Then I switched to my SSMS query window that was connected to the primary
server and executed the commands to set the partner, mirror, and alter the
partner timeout.
The database was mirrored, but then I noticed a sign of trouble within the
Database Mirroring Monitor. One of the databases that was already being
mirrored switched status to "(Principal, Disconnected)"
I executed the following query to check the status:
select mirroring_state_desc, count(*)
From sys.database_mirroring
group by mirroring_state_desc
Here are the results:
mirroring_state_desc
-- --
NULL 60
DISCONNECTED 1
SYNCHRONIZED 58
I wanted to make sure that the database mirroring monitoring job (called by
SQL Server Agent) was not part of the problem, so I stopped monitoring by
calling this stored procedure: sp_dbmmonitordropmonitoring
and I waited... No change in status.
I decided to stop mirroring on the newly added database. I used the
following command to stop mirroring:
ALTER DATABASE foo SET PARTNER OFF
and the other database (the one that had a status of Principal, Disconnected
synchronized and came back online.
In researching the problem I discovered the following posts:
http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
It seems as though others are having the same issue. How can I mirror more
databases on one server? This particular server has 120 databases and I
would like to get that number to 200 before we even start thinking about a
new server.
--
KeithMicrosoft recommends you limit the number of databases you are mirroring to
10 or so.
Based on the resource (memory and threads) utilization by database
mirroring, it is a best practices recommendation to mirror not more than 10
databases in one instance. The recommended limit of 10 is only approximate
and not an exact number. The limit can vary depending upon your application
and workload.
From -
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Keith_MSDN" <Keith_MSDN@.newsgroup.nospam> wrote in message
news:71B7CAFA-EAC2-4A1E-A781-0C8D479218AC@.microsoft.com...
> As the subject suggests, I am unable to mirror my 59th database.
> (2) database servers
> HP ML370 G4
> Dual Xeon 3.4Ghz, 8GB RAM
> Windows 2003 R2 Enterprise
> SQL Server 2005 Standard - patched to 2153
> (1) watch server
> HP DL 360 G4
> Xeon 3Ghz, 2GB RAM
> Win2k3 Standard
> SQL Server Express - patched to 2153
> I was able to restore the database and the transaction log to the desired
> mirror. Then I indicated what the partner server should be (via T-SQL).
> Then I switched to my SSMS query window that was connected to the primary
> server and executed the commands to set the partner, mirror, and alter the
> partner timeout.
> The database was mirrored, but then I noticed a sign of trouble within the
> Database Mirroring Monitor. One of the databases that was already being
> mirrored switched status to "(Principal, Disconnected)"
> I executed the following query to check the status:
> select mirroring_state_desc, count(*)
> From sys.database_mirroring
> group by mirroring_state_desc
> Here are the results:
> mirroring_state_desc
> -- --
> NULL 60
> DISCONNECTED 1
> SYNCHRONIZED 58
>
> I wanted to make sure that the database mirroring monitoring job (called
> by
> SQL Server Agent) was not part of the problem, so I stopped monitoring by
> calling this stored procedure: sp_dbmmonitordropmonitoring
> and I waited... No change in status.
>
> I decided to stop mirroring on the newly added database. I used the
> following command to stop mirroring:
> ALTER DATABASE foo SET PARTNER OFF
> and the other database (the one that had a status of Principal,
> Disconnected
> synchronized and came back online.
>
> In researching the problem I discovered the following posts:
> http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
> It seems as though others are having the same issue. How can I mirror
> more
> databases on one server? This particular server has 120 databases and I
> would like to get that number to 200 before we even start thinking about a
> new server.
> --
> Keith|||Hello Keith,
I understand that when you tried to configure more than 58 databases to
mirroring, it failed with disconnected status. If I'm off-base, please
let's know.
Based on my research, this issue seems to be related to virutal memory
allocation issue. Database mirroring sessions consume much virutal memroy
per session. You may want to check if you could find "Failed Virtual
Allocate Bytes" in SQL error log. If so, you may want to increase
memtoleave area by setting -g512 as start parameter to see if this could
workarond the issue. Please see the following article for details on
memeorytoleave area.
http://msdn2.microsoft.com/en-us/library/ms190737.aspx
You may also want to consider 64-bit as well, and This would only be an
issue on x86 due to memory limitation of X86 platform.
If this workaround does not meet your requirement, or you have any
concerns, please get SQL Error log, and send it to me at
petery@.microsoft.com, we will look into this problem further. We look
forward to your reply. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Peter. Yes, I want to mirror more than 58 databases on one server.
Upgrading to 64 Bit version of SQL (and Windows) isn't an option at this
time.
I looked within the SQL Server Log on the primary, mirror, and witness and I
did NOT find the Failed Virtual Allocate Bytes error. I even used
AgentRansack to search all the log file in the directory. I could not find
any hits for failed virtual (or virtual).
When searching the log files on the mirror I noticed that There is one SQL
Dump in the directory. Let me know if you want me to email the information.
The .mdmp file is 10MB.
I will try increasing memtoleave via the -g startup param. I assume that I
will need to modify this value on the primary and the mirror, is that
correct? This solution will require downtime, so I need to get the ball
rolling on scheduling a brief outage.
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> I understand that when you tried to configure more than 58 databases to
> mirroring, it failed with disconnected status. If I'm off-base, please
> let's know.
> Based on my research, this issue seems to be related to virutal memory
> allocation issue. Database mirroring sessions consume much virutal memroy
> per session. You may want to check if you could find "Failed Virtual
> Allocate Bytes" in SQL error log. If so, you may want to increase
> memtoleave area by setting -g512 as start parameter to see if this could
> workarond the issue. Please see the following article for details on
> memeorytoleave area.
> http://msdn2.microsoft.com/en-us/library/ms190737.aspx
> You may also want to consider 64-bit as well, and This would only be an
> issue on x86 due to memory limitation of X86 platform.
> If this workaround does not meet your requirement, or you have any
> concerns, please get SQL Error log, and send it to me at
> petery@.microsoft.com, we will look into this problem further. We look
> forward to your reply. Thank you!
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Keith,
Yes. You shall use it on both primary and the mirror. As for dump file,
will you send me an email with the latest sql error log file at
petery@.microsoft.com, and I may provide a ftp workspace via your email
address so that you could upload the dump file. I will check if the issue
is related to database mirroring,
Since dump analysis has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. If we could solve the issue efficently in
newsgroup, I recommend that you open a Support incident with Microsoft
Product Support Services so that a dedicated Support Professional can
assist with this case. If you need any help in this regard, please let me
know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
If you have any update or questions, please feel free to let's know.
Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Peter, I modified the startup parameters on all three servers - primary,
mirror, and witness as you recommended (adding -g512 - separated from the
other params by a semicolon). I am still unable to add any more databases to
the mirroring setup on these boxes.
I just tried configuring a small database (13MB). When I issued the ALTER
DATABASE command on the mirror to specify the partner the command just ran
and ran and ran and ran. In fact, it is still running as I type this
message. It has been running for 38 minutes and counting.
I just tried calling Microsoft Support because I want to open a support case
for the problem that we are experiencing with mirroring. The response that I
received from the Microsoft employee who took my information was "We are only
offering support on database mirroring technologies to TAP customers."
I find this statement unbelievable. Database mirroring should be fully
supported by Microsoft now that SQL Server 2005 SP1 is out and the feature is
fully enabled without trace flags. I asked the support person (the one who
told me about the support for database mirroring) for their name - just in
case you need to do any followup with them.
Do you mind if I contact you at your Microsoft email address that you
provided so that we can get the ball rolling on finding a fix for this issue?
Thanks
--
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> Yes. You shall use it on both primary and the mirror. As for dump file,
> will you send me an email with the latest sql error log file at
> petery@.microsoft.com, and I may provide a ftp workspace via your email
> address so that you could upload the dump file. I will check if the issue
> is related to database mirroring,
> Since dump analysis has to be done by contacting Microsoft Product Support
> Services. Therefore, we probably will not be able to resolve the issue
> through the newsgroups. If we could solve the issue efficently in
> newsgroup, I recommend that you open a Support incident with Microsoft
> Product Support Services so that a dedicated Support Professional can
> assist with this case. If you need any help in this regard, please let me
> know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> If you have any update or questions, please feel free to let's know.
> Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hello Keith,
Thank you for your feedback. Please contact me at petery@.microsoft.com so I
could follow up the issue with you and I also think this shall be supported
by MS PSS. I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
(2) database servers
HP ML370 G4
Dual Xeon 3.4Ghz, 8GB RAM
Windows 2003 R2 Enterprise
SQL Server 2005 Standard - patched to 2153
(1) watch server
HP DL 360 G4
Xeon 3Ghz, 2GB RAM
Win2k3 Standard
SQL Server Express - patched to 2153
I was able to restore the database and the transaction log to the desired
mirror. Then I indicated what the partner server should be (via T-SQL).
Then I switched to my SSMS query window that was connected to the primary
server and executed the commands to set the partner, mirror, and alter the
partner timeout.
The database was mirrored, but then I noticed a sign of trouble within the
Database Mirroring Monitor. One of the databases that was already being
mirrored switched status to "(Principal, Disconnected)"
I executed the following query to check the status:
select mirroring_state_desc, count(*)
From sys.database_mirroring
group by mirroring_state_desc
Here are the results:
mirroring_state_desc
-- --
NULL 60
DISCONNECTED 1
SYNCHRONIZED 58
I wanted to make sure that the database mirroring monitoring job (called by
SQL Server Agent) was not part of the problem, so I stopped monitoring by
calling this stored procedure: sp_dbmmonitordropmonitoring
and I waited... No change in status.
I decided to stop mirroring on the newly added database. I used the
following command to stop mirroring:
ALTER DATABASE foo SET PARTNER OFF
and the other database (the one that had a status of Principal, Disconnected
synchronized and came back online.
In researching the problem I discovered the following posts:
http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
It seems as though others are having the same issue. How can I mirror more
databases on one server? This particular server has 120 databases and I
would like to get that number to 200 before we even start thinking about a
new server.
--
KeithMicrosoft recommends you limit the number of databases you are mirroring to
10 or so.
Based on the resource (memory and threads) utilization by database
mirroring, it is a best practices recommendation to mirror not more than 10
databases in one instance. The recommended limit of 10 is only approximate
and not an exact number. The limit can vary depending upon your application
and workload.
From -
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Keith_MSDN" <Keith_MSDN@.newsgroup.nospam> wrote in message
news:71B7CAFA-EAC2-4A1E-A781-0C8D479218AC@.microsoft.com...
> As the subject suggests, I am unable to mirror my 59th database.
> (2) database servers
> HP ML370 G4
> Dual Xeon 3.4Ghz, 8GB RAM
> Windows 2003 R2 Enterprise
> SQL Server 2005 Standard - patched to 2153
> (1) watch server
> HP DL 360 G4
> Xeon 3Ghz, 2GB RAM
> Win2k3 Standard
> SQL Server Express - patched to 2153
> I was able to restore the database and the transaction log to the desired
> mirror. Then I indicated what the partner server should be (via T-SQL).
> Then I switched to my SSMS query window that was connected to the primary
> server and executed the commands to set the partner, mirror, and alter the
> partner timeout.
> The database was mirrored, but then I noticed a sign of trouble within the
> Database Mirroring Monitor. One of the databases that was already being
> mirrored switched status to "(Principal, Disconnected)"
> I executed the following query to check the status:
> select mirroring_state_desc, count(*)
> From sys.database_mirroring
> group by mirroring_state_desc
> Here are the results:
> mirroring_state_desc
> -- --
> NULL 60
> DISCONNECTED 1
> SYNCHRONIZED 58
>
> I wanted to make sure that the database mirroring monitoring job (called
> by
> SQL Server Agent) was not part of the problem, so I stopped monitoring by
> calling this stored procedure: sp_dbmmonitordropmonitoring
> and I waited... No change in status.
>
> I decided to stop mirroring on the newly added database. I used the
> following command to stop mirroring:
> ALTER DATABASE foo SET PARTNER OFF
> and the other database (the one that had a status of Principal,
> Disconnected
> synchronized and came back online.
>
> In researching the problem I discovered the following posts:
> http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
> It seems as though others are having the same issue. How can I mirror
> more
> databases on one server? This particular server has 120 databases and I
> would like to get that number to 200 before we even start thinking about a
> new server.
> --
> Keith|||Hello Keith,
I understand that when you tried to configure more than 58 databases to
mirroring, it failed with disconnected status. If I'm off-base, please
let's know.
Based on my research, this issue seems to be related to virutal memory
allocation issue. Database mirroring sessions consume much virutal memroy
per session. You may want to check if you could find "Failed Virtual
Allocate Bytes" in SQL error log. If so, you may want to increase
memtoleave area by setting -g512 as start parameter to see if this could
workarond the issue. Please see the following article for details on
memeorytoleave area.
http://msdn2.microsoft.com/en-us/library/ms190737.aspx
You may also want to consider 64-bit as well, and This would only be an
issue on x86 due to memory limitation of X86 platform.
If this workaround does not meet your requirement, or you have any
concerns, please get SQL Error log, and send it to me at
petery@.microsoft.com, we will look into this problem further. We look
forward to your reply. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Peter. Yes, I want to mirror more than 58 databases on one server.
Upgrading to 64 Bit version of SQL (and Windows) isn't an option at this
time.
I looked within the SQL Server Log on the primary, mirror, and witness and I
did NOT find the Failed Virtual Allocate Bytes error. I even used
AgentRansack to search all the log file in the directory. I could not find
any hits for failed virtual (or virtual).
When searching the log files on the mirror I noticed that There is one SQL
Dump in the directory. Let me know if you want me to email the information.
The .mdmp file is 10MB.
I will try increasing memtoleave via the -g startup param. I assume that I
will need to modify this value on the primary and the mirror, is that
correct? This solution will require downtime, so I need to get the ball
rolling on scheduling a brief outage.
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> I understand that when you tried to configure more than 58 databases to
> mirroring, it failed with disconnected status. If I'm off-base, please
> let's know.
> Based on my research, this issue seems to be related to virutal memory
> allocation issue. Database mirroring sessions consume much virutal memroy
> per session. You may want to check if you could find "Failed Virtual
> Allocate Bytes" in SQL error log. If so, you may want to increase
> memtoleave area by setting -g512 as start parameter to see if this could
> workarond the issue. Please see the following article for details on
> memeorytoleave area.
> http://msdn2.microsoft.com/en-us/library/ms190737.aspx
> You may also want to consider 64-bit as well, and This would only be an
> issue on x86 due to memory limitation of X86 platform.
> If this workaround does not meet your requirement, or you have any
> concerns, please get SQL Error log, and send it to me at
> petery@.microsoft.com, we will look into this problem further. We look
> forward to your reply. Thank you!
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Keith,
Yes. You shall use it on both primary and the mirror. As for dump file,
will you send me an email with the latest sql error log file at
petery@.microsoft.com, and I may provide a ftp workspace via your email
address so that you could upload the dump file. I will check if the issue
is related to database mirroring,
Since dump analysis has to be done by contacting Microsoft Product Support
Services. Therefore, we probably will not be able to resolve the issue
through the newsgroups. If we could solve the issue efficently in
newsgroup, I recommend that you open a Support incident with Microsoft
Product Support Services so that a dedicated Support Professional can
assist with this case. If you need any help in this regard, please let me
know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
If you have any update or questions, please feel free to let's know.
Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Peter, I modified the startup parameters on all three servers - primary,
mirror, and witness as you recommended (adding -g512 - separated from the
other params by a semicolon). I am still unable to add any more databases to
the mirroring setup on these boxes.
I just tried configuring a small database (13MB). When I issued the ALTER
DATABASE command on the mirror to specify the partner the command just ran
and ran and ran and ran. In fact, it is still running as I type this
message. It has been running for 38 minutes and counting.
I just tried calling Microsoft Support because I want to open a support case
for the problem that we are experiencing with mirroring. The response that I
received from the Microsoft employee who took my information was "We are only
offering support on database mirroring technologies to TAP customers."
I find this statement unbelievable. Database mirroring should be fully
supported by Microsoft now that SQL Server 2005 SP1 is out and the feature is
fully enabled without trace flags. I asked the support person (the one who
told me about the support for database mirroring) for their name - just in
case you need to do any followup with them.
Do you mind if I contact you at your Microsoft email address that you
provided so that we can get the ball rolling on finding a fix for this issue?
Thanks
--
Keith
"Peter Yang [MSFT]" wrote:
> Hello Keith,
> Yes. You shall use it on both primary and the mirror. As for dump file,
> will you send me an email with the latest sql error log file at
> petery@.microsoft.com, and I may provide a ftp workspace via your email
> address so that you could upload the dump file. I will check if the issue
> is related to database mirroring,
> Since dump analysis has to be done by contacting Microsoft Product Support
> Services. Therefore, we probably will not be able to resolve the issue
> through the newsgroups. If we could solve the issue efficently in
> newsgroup, I recommend that you open a Support incident with Microsoft
> Product Support Services so that a dedicated Support Professional can
> assist with this case. If you need any help in this regard, please let me
> know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> If you have any update or questions, please feel free to let's know.
> Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||Hello Keith,
Thank you for your feedback. Please contact me at petery@.microsoft.com so I
could follow up the issue with you and I also think this shall be supported
by MS PSS. I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
Subscribe to:
Posts (Atom)