Database Mirroring, client connections all ODBC so what methods are recommended?

As stated in the subject I have a situation where if database mirroring is employed for either manual or automatic failover, all the client (including web connections) connections use ODBC not ADO, or OLEDB etc... so what methods are recommended? Client side redirect is not available so I could not employe the "Data Source =A; Failover Partner=B..." option.

Right now the method employed (pre database mirroring and basically employing log shipping on SQL 2000) is to have a DNS alias for the ODBC connection so that if the server were to change in a failover situation the DNS record would have to be altered, so that all the client connections would not have to be reconfigured.

Regards,

Dominic Baines

I have one customer in production that uses the DNS name change method to switch over the clients after a failover, and so that method does have uses.

Alternatively, you could have the application handle the failover by issuing the redirect to another server.

Thanks,

Mark

|||

Sorry Mark but just not an option to recode app connection strings.

Several reasons:

1. Most apps are 3rd party and insist on ODBC only connections (I know old method but that is state of affairs).

2. Recoding ODBC connections means we would have to visit physically or by logon/registry script every client to reconfigure connection when failover occurs. Not practical and bound to miss some.

3. When fail back occurs same problem exists in reverse.

We simply have too many applications and over 50 DB's on 6 pairs of servers in the failover/failback situation (canot use clustering) and 100+ app servers and many many direct connect clients involved. And the desktop clients spread over 5 countries including several timezones and as most everyone else these days also India.

It looks like DNS alias is the option unless anyone has a better one? It was originally coded for older NT4 style domain then migrated to AD, which was interesting but that is a different story.

As this appears to be the case can the failover be used to trigger a script in SQL2005 (that will update the DNS (I can handle that script). How could you detect the failover has occured? Still a bit too new to SQL2005 to know the answer to this one.

(and...thanks for the message on the webcast, Kimberley has got in touch).

Regards,

Dom

|||

I understood that the SQL 2005 ODBC provider (SQL Native Client ODBC provider) was part of the SNAC stack and therefore would support failover. Of course the SQL 2005 SNAC bits and pieces would need to be installed on the clients/servers and the connection string(s)/DSNs modified to support a mirror, but that would be a one off operation, albeit maybe a large one.

As per BOL : "The SQL Native Client ODBC driver complies with the Microsoft Win32? ODBC 3.51 specification. The driver supports applications written with the ODBC 2.5 or earlier versions of the ODBC functions in the manner defined in the ODBC 3.51 specification."

Am I missing something?

|||

I have marked as answer as basically the solution does provide an answer that will probably assist some.

However, I will carry on using the DNS method for the moment.

There is no scope right now to upgrade the native ODBC drivers as I am 'stuck' with desktops with older drivers and reconfiguring the client driver or connections is not possible as it would also mean rolling out new desktop images.

Regards,

Dom

sql

Database Mirroring vs SQL Server FailOver (Performance)

Hi there.

I′m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.

The tests i′m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.

My question here is: What if i use Sql Server failover mechanism?

I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?

Database mirroring performance will vary depending on many factors. I assume you have been using Mirrroring in high protection mode which ensures all writes are synchronously made to both databases. This has a larger impact on throughput than the high performance asynchronous mode. The failover mechanism should not have any impact on the performance of you database workload since it's basic purpose is to re-direct the connection to the mirror database when the principal fails.

|||

I think you missunderstood me. My question isn′t very clear, sorry for that.

I′m using High Availability mode (with witness).

I want to know if, in fact, i should expect better performance when using SQL Server Failover Clustering(with shared disk array).

Thanks in advance.

|||

If you are asking if you are getting better performance (throughput) using database mirroring or Failover clustering, then the answer is Failover clustering will have better peformance.

In fact, the cluster performance should be about the same (if not exactly the same) as a stand alone server.

Failover times are another matter, though.

|||

I also was thinking that cluster performance would be the same, when comparing to a stand alone server. But i′m not very deep into this mechanism and needed an answer from someone with "know how" in the area.

Thanks for your help Mark.

|||Database mirroring is useful technology for disaster scope. You can have two failover clusters in two geographics positions and database mirroring help you data be in consistent state (sorry for my english :( )|||

To be clear, failover times for the database mirroring can be in the seconds, because the instance of SQL is up and running and constantly redoing as much it can.

For failover clustering, for a failover to happen, the instance on the new node has to start up and then run recovery on the database before it is opened. This generally takes 10s of seconds and even could take place on the order of minutes.

Thanks,

Mark

|||

Fail-over clustering will perform better than database mirroring (in synchronous mode), since the Principal has to wait for the Mirror to commit transactions. This makes you dependent on the bandwidth and latency of the network connection between the two databases, and on the disk IO on the Mirror.

DB Mirroring will fail-over much faster than fail-over clustering, so it is better for routine maintence.

Database Mirroring vs SQL Server FailOver (Performance)

Hi there.

I′m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.

The tests i′m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.

My question here is: What if i use Sql Server failover mechanism?

I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?

Database mirroring performance will vary depending on many factors. I assume you have been using Mirrroring in high protection mode which ensures all writes are synchronously made to both databases. This has a larger impact on throughput than the high performance asynchronous mode. The failover mechanism should not have any impact on the performance of you database workload since it's basic purpose is to re-direct the connection to the mirror database when the principal fails.

|||

I think you missunderstood me. My question isn′t very clear, sorry for that.

I′m using High Availability mode (with witness).

I want to know if, in fact, i should expect better performance when using SQL Server Failover Clustering(with shared disk array).

Thanks in advance.

|||

If you are asking if you are getting better performance (throughput) using database mirroring or Failover clustering, then the answer is Failover clustering will have better peformance.

In fact, the cluster performance should be about the same (if not exactly the same) as a stand alone server.

Failover times are another matter, though.

|||

I also was thinking that cluster performance would be the same, when comparing to a stand alone server. But i′m not very deep into this mechanism and needed an answer from someone with "know how" in the area.

Thanks for your help Mark.

|||Database mirroring is useful technology for disaster scope. You can have two failover clusters in two geographics positions and database mirroring help you data be in consistent state (sorry for my english :( )|||

To be clear, failover times for the database mirroring can be in the seconds, because the instance of SQL is up and running and constantly redoing as much it can.

For failover clustering, for a failover to happen, the instance on the new node has to start up and then run recovery on the database before it is opened. This generally takes 10s of seconds and even could take place on the order of minutes.

Thanks,

Mark

|||

Fail-over clustering will perform better than database mirroring (in synchronous mode), since the Principal has to wait for the Mirror to commit transactions. This makes you dependent on the bandwidth and latency of the network connection between the two databases, and on the disk IO on the Mirror.

DB Mirroring will fail-over much faster than fail-over clustering, so it is better for routine maintence.

Database Mirroring vs SQL Server FailOver (Performance)

Hi there.

I′m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.

The tests i′m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.

My question here is: What if i use Sql Server failover mechanism?

I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?

Database mirroring performance will vary depending on many factors. I assume you have been using Mirrroring in high protection mode which ensures all writes are synchronously made to both databases. This has a larger impact on throughput than the high performance asynchronous mode. The failover mechanism should not have any impact on the performance of you database workload since it's basic purpose is to re-direct the connection to the mirror database when the principal fails.

|||

I think you missunderstood me. My question isn′t very clear, sorry for that.

I′m using High Availability mode (with witness).

I want to know if, in fact, i should expect better performance when using SQL Server Failover Clustering(with shared disk array).

Thanks in advance.

|||

If you are asking if you are getting better performance (throughput) using database mirroring or Failover clustering, then the answer is Failover clustering will have better peformance.

In fact, the cluster performance should be about the same (if not exactly the same) as a stand alone server.

Failover times are another matter, though.

|||

I also was thinking that cluster performance would be the same, when comparing to a stand alone server. But i′m not very deep into this mechanism and needed an answer from someone with "know how" in the area.

Thanks for your help Mark.

|||Database mirroring is useful technology for disaster scope. You can have two failover clusters in two geographics positions and database mirroring help you data be in consistent state (sorry for my english :( )|||

To be clear, failover times for the database mirroring can be in the seconds, because the instance of SQL is up and running and constantly redoing as much it can.

For failover clustering, for a failover to happen, the instance on the new node has to start up and then run recovery on the database before it is opened. This generally takes 10s of seconds and even could take place on the order of minutes.

Thanks,

Mark

|||

Fail-over clustering will perform better than database mirroring (in synchronous mode), since the Principal has to wait for the Mirror to commit transactions. This makes you dependent on the bandwidth and latency of the network connection between the two databases, and on the disk IO on the Mirror.

DB Mirroring will fail-over much faster than fail-over clustering, so it is better for routine maintence.

Database Mirroring Client-Side Redirect

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

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

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

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

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

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

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

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

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

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

Problem:

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

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

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

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

Chris

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

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

Also, I have tried using

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

and it still did not work.

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

ANY help would be GREAT! Thank you!

Database Mirroring and Log Shipping

Hello All Database Mirroring Experts,

I'm interested in how Combining Log Shipping and Database Mirroring works when failover occurs.
From SQL BOL, it says:

"Topic: Database Mirroring and Log Shipping
...
To run in high-safety mode with automatic failover the mirroring

session is configured with an additional server instance known as the witness.

If the principal database is lost for any reason after the database is

synchronized and if the mirror server and witness can still communicate

with each other, automatic failover occurs. An automatic failover

causes mirror server to assume the principal role and bring its

database online as the principal database. For more information, see Automatic Failover

[ http://msdn2.microsoft.com/en-us/library/ms189590.aspx ] . If the log

shipping backup location is accessible to the new principal/primary

server, its backup jobs begin to ship log backups to that location. The

database mirroring synchronous mode guarantees that the log chain is

unaffected by a mirroring failover and that only valid log is restored.

The secondary servers continue to copy log backups without knowing that

a different server instance has become the primary server.
..."
Source: http://msdn2.microsoft.com/en-us/library/ms187016(d=printer).aspx

Could anyone tell me that how the database mirroring synchronous mode guarantees that the log chain is

unaffected by a mirroring failover and that only valid log is restored?

Let me elaborate the situation (if anything I said is incorrect, please correct me )
Here is the time line of the failover happens:

- tn-1 - tn - tf -- tn+1 > t

-> t: the time line.
tn: the moment that the log shipping backup job and copy job is done for the transaction log obtained between the time interval tn-1 and tn.
tf: the moment that mirroring failover occurs in the database mirroring session.
the time interval between each tn and tn-1 are constant, say h seconds, for all n are positive integers.

Here is the question that I want to ask:
In database mirroring synchronous mode, it guarantees that all the committed transaction from the moment tn to tf is copied to the mirror database. All the transaction log backup for log shipping are done on the original principal before the moment tf. After the mirroring failover occurs at the moment tf, how the log shipping mechanism guarantees that the transaction log between the interval tn and tn+1 that can be unaffected by a mirroring failover?
That's the point that I interested in.

Thanks a lot,
Terence

Hi Terence,

The key point to consider in the above scenario is the fact that the principal and mirror which participate in the mirroring relationship collaborate to similulate a single database which has had no failure. Starting from that observation, if there is a failover to the mirror, the next log backup will still capture all the transactions that have not yet been backed up on the principal, preserving the log chain. In your example above, the next transaction log backup from the mirror will contain all the log records past point tn.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||Hi Matt,

Thanks a lot for your information.
It helps me a lot to understand Database Mirroring and Log Shipping. : )

Regards,
Terence

Database Mirroring and Log Shipping

Hello All Database Mirroring Experts,

I'm interested in how Combining Log Shipping and Database Mirroring works when failover occurs.
From SQL BOL, it says:

"Topic: Database Mirroring and Log Shipping
...
To run in high-safety mode with automatic failover the mirroring

session is configured with an additional server instance known as the witness.

If the principal database is lost for any reason after the database is

synchronized and if the mirror server and witness can still communicate

with each other, automatic failover occurs. An automatic failover

causes mirror server to assume the principal role and bring its

database online as the principal database. For more information, see Automatic Failover

[ http://msdn2.microsoft.com/en-us/library/ms189590.aspx ] . If the log

shipping backup location is accessible to the new principal/primary

server, its backup jobs begin to ship log backups to that location. The

database mirroring synchronous mode guarantees that the log chain is

unaffected by a mirroring failover and that only valid log is restored.

The secondary servers continue to copy log backups without knowing that

a different server instance has become the primary server.
..."
Source: http://msdn2.microsoft.com/en-us/library/ms187016(d=printer).aspx

Could anyone tell me that how the database mirroring synchronous mode guarantees that the log chain is

unaffected by a mirroring failover and that only valid log is restored?

Let me elaborate the situation (if anything I said is incorrect, please correct me )
Here is the time line of the failover happens:

- tn-1 - tn - tf -- tn+1 > t

-> t: the time line.
tn: the moment that the log shipping backup job and copy job is done for the transaction log obtained between the time interval tn-1 and tn.
tf: the moment that mirroring failover occurs in the database mirroring session.
the time interval between each tn and tn-1 are constant, say h seconds, for all n are positive integers.

Here is the question that I want to ask:
In database mirroring synchronous mode, it guarantees that all the committed transaction from the moment tn to tf is copied to the mirror database. All the transaction log backup for log shipping are done on the original principal before the moment tf. After the mirroring failover occurs at the moment tf, how the log shipping mechanism guarantees that the transaction log between the interval tn and tn+1 that can be unaffected by a mirroring failover?
That's the point that I interested in.

Thanks a lot,
Terence

Hi Terence,

The key point to consider in the above scenario is the fact that the principal and mirror which participate in the mirroring relationship collaborate to similulate a single database which has had no failure. Starting from that observation, if there is a failover to the mirror, the next log backup will still capture all the transactions that have not yet been backed up on the principal, preserving the log chain. In your example above, the next transaction log backup from the mirror will contain all the log records past point tn.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||Hi Matt,

Thanks a lot for your information.
It helps me a lot to understand Database Mirroring and Log Shipping. : )

Regards,
Terence

Database mirroring and automatic failover

Hi,
I need some advice on database mirroring. We have our databases
mirrored successfully in a test envirnment using High Availiblity and
Automatic Failover. I have alerts configured for transaction delays
and mirroring changes etc, but we are not sure how to best handle the
production reboots. For example, we schedule reboots on the principal
server weekly - I assume this will cause automatic failover as long as
it exceeds my timeout period. Is the suspend, resume command the best
way to handle this or do I have other options? I was thinking about
scheduling a job to run these commands during the reboot. I don't want
to set the timeout for more than 30 seconds.
ALTER DATABASE DB SET PARTNER SUSPEND
--after suspension, resume
ALTER
DATABASE DB SET PARTNER RESUME
Is this the proper way to proceed? Anyone with experience with this
issue out there or something similar?
Thanks,
KristinaHi Kristina
Why do you need to schedule or to do weekly reboots on a system configured
for high availability?
Ben Nevarez
"Kristina" wrote:
> Hi,
> I need some advice on database mirroring. We have our databases
> mirrored successfully in a test envirnment using High Availiblity and
> Automatic Failover. I have alerts configured for transaction delays
> and mirroring changes etc, but we are not sure how to best handle the
> production reboots. For example, we schedule reboots on the principal
> server weekly - I assume this will cause automatic failover as long as
> it exceeds my timeout period. Is the suspend, resume command the best
> way to handle this or do I have other options? I was thinking about
> scheduling a job to run these commands during the reboot. I don't want
> to set the timeout for more than 30 seconds.
>
> ALTER DATABASE DB SET PARTNER SUSPEND
> --after suspension, resume
> ALTER
> DATABASE DB SET PARTNER RESUME
> Is this the proper way to proceed? Anyone with experience with this
> issue out there or something similar?
> Thanks,
> Kristina
>|||On Jan 8, 8:59=A0pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Hi Kristina
> Why do you need to schedule or to do weekly reboots on a system configured=
> for high availability?
> Ben Nevarez
>
> "Kristina" wrote:
> > Hi,
> > I need some advice on database mirroring. We have our databases
> > mirrored successfully in a test envirnment using High Availiblity and
> > Automatic Failover. I have alerts configured for transaction delays
> > and mirroring changes etc, but we are not sure how to best handle the
> > production reboots. For example, we schedule reboots on the principal
> > server weekly - I assume this will cause automatic failover as long as
> > it exceeds my timeout period. Is the suspend, resume command the best
> > way to handle this or do I have other options? I was thinking about
> > scheduling a job to run these commands during the reboot. I don't want
> > to set the timeout for more than 30 seconds.
> > ALTER DATABASE DB SET PARTNER SUSPEND
> > --after suspension, resume
> > ALTER
> > DATABASE DB SET PARTNER RESUME
> > Is this the proper way to proceed? Anyone with experience with this
> > issue out there or something similar?
> > Thanks,
> > Kristina- Hide quoted text -
> - Show quoted text -
Ben,
Basically these are scheduled updates from Microsoft.
Do you have any ideas around this?
Kristina|||If you are configuring a database server with high availability you should
not have weekly reboots for Windows updates. Perhaps you can apply those
updates every one or two months, and just pay special attention to security
updates.
When you reboot the principal server the database will fail over to the
mirror, which becomes the new principal. When the server is back online it
will become the new mirror. All of this is automatic with no DBA
intervention. So I do not understand why you want to change the timeout or
the status to SUSPENDED.
Ben Nevarez
"Kristina" wrote:
> On Jan 8, 8:59 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> > Hi Kristina
> >
> > Why do you need to schedule or to do weekly reboots on a system configured
> > for high availability?
> >
> > Ben Nevarez
> >
> >
> >
> > "Kristina" wrote:
> > > Hi,
> >
> > > I need some advice on database mirroring. We have our databases
> > > mirrored successfully in a test envirnment using High Availiblity and
> > > Automatic Failover. I have alerts configured for transaction delays
> > > and mirroring changes etc, but we are not sure how to best handle the
> > > production reboots. For example, we schedule reboots on the principal
> > > server weekly - I assume this will cause automatic failover as long as
> > > it exceeds my timeout period. Is the suspend, resume command the best
> > > way to handle this or do I have other options? I was thinking about
> > > scheduling a job to run these commands during the reboot. I don't want
> > > to set the timeout for more than 30 seconds.
> >
> > > ALTER DATABASE DB SET PARTNER SUSPEND
> > > --after suspension, resume
> > > ALTER
> > > DATABASE DB SET PARTNER RESUME
> >
> > > Is this the proper way to proceed? Anyone with experience with this
> > > issue out there or something similar?
> >
> > > Thanks,
> > > Kristina- Hide quoted text -
> >
> > - Show quoted text -
> Ben,
> Basically these are scheduled updates from Microsoft.
> Do you have any ideas around this?
> Kristina
>|||"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:73b02d00-0119-455c-8384-3b7e9f80b104@.f47g2000hsd.googlegroups.com...
On Jan 8, 8:59 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
>Ben,
>Basically these are scheduled updates from Microsoft.
>Do you have any ideas around this?
>Kristina
Yes, don't apply them.
Heresy you say? Sort of.
For a production high availability DB I would schedule these infrequently
and only after OTHER people have tested them on their servers. If it's a
supercritical patch, I might rush it on, but typically I'm very conservative
about applying patches, especially to production systems that are already
well controlled by other methods.
For example, simply not permitting any outside access to port 1433 will
greatly increase your safety. If the outside world can't get to it, doesn't
matter if say a buffer overflow exists (i.e. Code Red).
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On Jan 9, 10:53=A0pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:73b02d00-0119-455c-8384-3b7e9f80b104@.f47g2000hsd.googlegroups.com...
> On Jan 8, 8:59 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> >Ben,
> >Basically these are scheduled updates from Microsoft.
> >Do you have any ideas around this?
> >Kristina
> Yes, don't apply them.
> Heresy you say? =A0Sort of.
> For a production high availability DB I would schedule these infrequently
> and only after OTHER people have tested them on their servers. =A0If it's =a
> supercritical patch, I might rush it on, but typically I'm very conservati=ve
> about applying patches, especially to production systems that are already
> well controlled by other methods.
> For example, simply not permitting any outside access to port 1433 will
> greatly increase your safety. =A0If the outside world can't get to it, doe=sn't
> matter if say a buffer overflow exists (i.e. Code Red).
> --
> Greg Moore
> SQL Server DBA Consulting =A0 =A0 =A0 =A0 =A0 Remote and Onsite available!=
> Email: sql =A0(at) =A0greenms.com =A0 =A0 =A0 =A0 =A0http://www.greenms.co=
m/sqlserver.html
Thanks for all the advice guys. I am not in charge of the servers and
the patches in my current envirnment. I will be using this information
to convice the networking team to not schedule the reboots as
frequently. If they do, I will just create a job to suspend and then
re-initiaize the mirroring during the period that the server is down. I

Database mirroring and automatic failover

Hi,
I need some advice on database mirroring. We have our databases
mirrored successfully in a test envirnment using High Availiblity and
Automatic Failover. I have alerts configured for transaction delays
and mirroring changes etc, but we are not sure how to best handle the
production reboots. For example, we schedule reboots on the principal
server weekly - I assume this will cause automatic failover as long as
it exceeds my timeout period. Is the suspend, resume command the best
way to handle this or do I have other options? I was thinking about
scheduling a job to run these commands during the reboot. I don't want
to set the timeout for more than 30 seconds.
ALTER DATABASE DB SET PARTNER SUSPEND
--after suspension, resume
ALTER
DATABASE DB SET PARTNER RESUME
Is this the proper way to proceed? Anyone with experience with this
issue out there or something similar?
Thanks,
Kristina
Hi Kristina
Why do you need to schedule or to do weekly reboots on a system configured
for high availability?
Ben Nevarez
"Kristina" wrote:

> Hi,
> I need some advice on database mirroring. We have our databases
> mirrored successfully in a test envirnment using High Availiblity and
> Automatic Failover. I have alerts configured for transaction delays
> and mirroring changes etc, but we are not sure how to best handle the
> production reboots. For example, we schedule reboots on the principal
> server weekly - I assume this will cause automatic failover as long as
> it exceeds my timeout period. Is the suspend, resume command the best
> way to handle this or do I have other options? I was thinking about
> scheduling a job to run these commands during the reboot. I don't want
> to set the timeout for more than 30 seconds.
>
> ALTER DATABASE DB SET PARTNER SUSPEND
> --after suspension, resume
> ALTER
> DATABASE DB SET PARTNER RESUME
> Is this the proper way to proceed? Anyone with experience with this
> issue out there or something similar?
> Thanks,
> Kristina
>
|||On Jan 8, 8:59Xpm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Hi Kristina
> Why do you need to schedule or to do weekly reboots on a system configured
> for high availability?
> Ben Nevarez
>
> "Kristina" wrote:
>
>
> - Show quoted text -
Ben,
Basically these are scheduled updates from Microsoft.
Do you have any ideas around this?
Kristina
|||If you are configuring a database server with high availability you should
not have weekly reboots for Windows updates. Perhaps you can apply those
updates every one or two months, and just pay special attention to security
updates.
When you reboot the principal server the database will fail over to the
mirror, which becomes the new principal. When the server is back online it
will become the new mirror. All of this is automatic with no DBA
intervention. So I do not understand why you want to change the timeout or
the status to SUSPENDED.
Ben Nevarez
"Kristina" wrote:

> On Jan 8, 8:59 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> Ben,
> Basically these are scheduled updates from Microsoft.
> Do you have any ideas around this?
> Kristina
>
|||"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:73b02d00-0119-455c-8384-3b7e9f80b104@.f47g2000hsd.googlegroups.com...
On Jan 8, 8:59 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
>Ben,
>Basically these are scheduled updates from Microsoft.
>Do you have any ideas around this?
>Kristina
Yes, don't apply them.
Heresy you say? Sort of.
For a production high availability DB I would schedule these infrequently
and only after OTHER people have tested them on their servers. If it's a
supercritical patch, I might rush it on, but typically I'm very conservative
about applying patches, especially to production systems that are already
well controlled by other methods.
For example, simply not permitting any outside access to port 1433 will
greatly increase your safety. If the outside world can't get to it, doesn't
matter if say a buffer overflow exists (i.e. Code Red).
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||On Jan 9, 10:53Xpm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:73b02d00-0119-455c-8384-3b7e9f80b104@.f47g2000hsd.googlegroups.com...
> On Jan 8, 8:59 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
>
>
> Yes, don't apply them.
> Heresy you say? XSort of.
> For a production high availability DB I would schedule these infrequently
> and only after OTHER people have tested them on their servers. XIf it's a
> supercritical patch, I might rush it on, but typically I'm very conservative
> about applying patches, especially to production systems that are already
> well controlled by other methods.
> For example, simply not permitting any outside access to port 1433 will
> greatly increase your safety. XIf the outside world can't get to it, doesn't
> matter if say a buffer overflow exists (i.e. Code Red).
> --
> Greg Moore
> SQL Server DBA Consulting X X X X X Remote and Onsite available!
> Email: sql X(at) Xgreenms.com X X X X Xhttp://www.greenms.com/sqlserver.html
Thanks for all the advice guys. I am not in charge of the servers and
the patches in my current envirnment. I will be using this information
to convice the networking team to not schedule the reboots as
frequently. If they do, I will just create a job to suspend and then
re-initiaize the mirroring during the period that the server is down. I

Database Mirroring : Asynchronous mirroring without a witness

Hi

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

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

Msg 1455, Level 16, State 2, Line 1

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

Thanks,

Priyanga

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

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

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

thanks,
mark

|||

Thanks Mark.

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

Cheers,
Priyanga

Database Mirror failover from Principal to Mirror

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

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

Database Mirror and Failover

Hello,
I'm just configuring a Database Mirror with SQL 2005 SP2. I have two SQL-Servers SQL1 and SQL2 and one Whitness. Everything is working fine, when I plug out the ethernet cable from SQL1 the whitness is detecting the failure an is switching over to SQL2 and so on. The running application is connecting to SQL2 after the failure. In case of a restart of the application the client tries to connect to SQL1, which is not reachable. As I'm expecting there should be a connect to SQL2 after the sqlclient can't reach SQL1 but with netstat -na I only see connection attempts to SQL1. From a former posting I got the Information about some existing connections to SQL1, but I made a restart of the client so be sure that there is no existing connection to SQL1. When both SQL-Servers are reachable and I do a manual failover everything is working fine, so I think database mirroring only works, when both servers are online. Is this true?
My connection string looks like this:
Data Source=DBSRV1;Failover Partner=DBSRV2;Initial Catalog=TEST;User ID=test;Password=test;Network Library=DBMSSOCN;Load Balance Timeout=5"
Thanks for your help
Greetings from Germany
Christian
Christian Grbner
MVP ISA Server
Hilfe & Infos rund um den ISA Server: http://www.msisafaq.de !!!!
NEU !!! Das Handbuch zum ISA 2006 - http://www.msisafaq.de/buch/
Database mirroring works either way depending on which mode you're in. It's
the automatic failover in high safety mode that may or may not meet your
needs. Specifically, not the failover part but rather the new client
connections part post failover (as per your scenario).
Automatic failover only works if your client's data access layer (SNAC or
ADO/ODBC.NET) has cached both server names. This happens in the first
connection attempt on the server. This is usually the primary server unless
you have specific code in your app (just a few simple lines) to test for
both. Many DB Mirroring implementations are "as-is" using exising apps
without code tweaks so they connect to their "original" primary as per
normal ops but benefit from automatic reconnect in a failover.
However, if the primary server was down before the client connects for the
first time, it would not know there is a mirror server to connect to which
is what you're seeing. Whether it's a new client connection or a re-started
app/web server, it's the same effect; you don't have the mirror server name
cached so the app/web server or client doesn't know it exists.
joe.
"Christian Grbner [MVP]" <newsgroups@.groeby.net> wrote in message
news:uunWT7wuHHA.2360@.TK2MSFTNGP06.phx.gbl...
Hello,
I'm just configuring a Database Mirror with SQL 2005 SP2. I have two
SQL-Servers SQL1 and SQL2 and one Whitness. Everything is working fine, when
I plug out the ethernet cable from SQL1 the whitness is detecting the
failure an is switching over to SQL2 and so on. The running application is
connecting to SQL2 after the failure. In case of a restart of the
application the client tries to connect to SQL1, which is not reachable. As
I'm expecting there should be a connect to SQL2 after the sqlclient can't
reach SQL1 but with netstat -na I only see connection attempts to SQL1. From
a former posting I got the Information about some existing connections to
SQL1, but I made a restart of the client so be sure that there is no
existing connection to SQL1. When both SQL-Servers are reachable and I do a
manual failover everything is working fine, so I think database mirroring
only works, when both servers are online. Is this true?
My connection string looks like this:
Data Source=DBSRV1;Failover Partner=DBSRV2;Initial Catalog=TEST;User
ID=test;Password=test;Network Library=DBMSSOCN;Load Balance Timeout=5"
Thanks for your help
Greetings from Germany
Christian
Christian Grbner
MVP ISA Server
Hilfe & Infos rund um den ISA Server: http://www.msisafaq.de !!!!
NEU !!! Das Handbuch zum ISA 2006 - http://www.msisafaq.de/buch/

Database marked as suspect when bringing up a clustered server

We have a 2 node clustered server (active/passive failover) that when it
fails over (forced or otherwise) or if we just reboot the server, when it
comes up, some of the user databases come up marked as suspect. Which
databases get marked varies each time. If I execute an sp_resetstatus on the
database it tells me that nothing was changed because the flags are fine. I
then run a restore database with recovery it tells me that it completed
successfully and processed 0 pages in 0 sec.
I then go into the cluster administrator, take sql server offline and then
bring it back up and the databases will be ok. We're looking for some sort of
explanation of this behavior.
What does the SQL Server error logs say when the system is coming online and the databases are marked suspect?
I'd check the SQL Server cluster resource dependencies. When you say that after you've repaired everything and recycle SQL Server, how are you doing that? Are you just restarting services, taking cluster resources offline, taking the entire resource group offline, or using SQL EM to stop and restart the services?
Sincerely,
Anthony Thomas

"tim" <tim@.discussions.microsoft.com> wrote in message news:6B363DFC-293D-46F4-9F5E-CAAB0A669B33@.microsoft.com...
We have a 2 node clustered server (active/passive failover) that when it
fails over (forced or otherwise) or if we just reboot the server, when it
comes up, some of the user databases come up marked as suspect. Which
databases get marked varies each time. If I execute an sp_resetstatus on the
database it tells me that nothing was changed because the flags are fine. I
then run a restore database with recovery it tells me that it completed
successfully and processed 0 pages in 0 sec.
I then go into the cluster administrator, take sql server offline and then
bring it back up and the databases will be ok. We're looking for some sort of
explanation of this behavior.|||The SQL Server error log shows: Error 9004, Severity: 21, State 10
An error occurred when processing the log for database .....
As far as stopping and restarting SQL Server, I'm doing it from the Cluster
Administrator, go to the Active Resources folder and take SQL Server offline
and
then bring it back online. And then the database(s) will be available.
"AnthonyThomas" wrote:

> What does the SQL Server error logs say when the system is coming online and the databases are marked suspect?
> I'd check the SQL Server cluster resource dependencies. When you say that after you've repaired everything and recycle SQL Server, how are you doing that? Are you just restarting services, taking cluster resources offline, taking the entire resource g
roup offline, or using SQL EM to stop and restart the services?
> Sincerely,
>
> Anthony Thomas
>
> --
> "tim" <tim@.discussions.microsoft.com> wrote in message news:6B363DFC-293D-46F4-9F5E-CAAB0A669B33@.microsoft.com...
> We have a 2 node clustered server (active/passive failover) that when it
> fails over (forced or otherwise) or if we just reboot the server, when it
> comes up, some of the user databases come up marked as suspect. Which
> databases get marked varies each time. If I execute an sp_resetstatus on the
> database it tells me that nothing was changed because the flags are fine. I
> then run a restore database with recovery it tells me that it completed
> successfully and processed 0 pages in 0 sec.
> I then go into the cluster administrator, take sql server offline and then
> bring it back up and the databases will be ok. We're looking for some sort of
> explanation of this behavior

Database marked as suspect when bringing up a clustered server

We have a 2 node clustered server (active/passive failover) that when it
fails over (forced or otherwise) or if we just reboot the server, when it
comes up, some of the user databases come up marked as suspect. Which
databases get marked varies each time. If I execute an sp_resetstatus on the
database it tells me that nothing was changed because the flags are fine. I
then run a restore database with recovery it tells me that it completed
successfully and processed 0 pages in 0 sec.
I then go into the cluster administrator, take sql server offline and then
bring it back up and the databases will be ok. We're looking for some sort of
explanation of this behavior.The SQL Server error log shows: Error 9004, Severity: 21, State 10
An error occurred when processing the log for database .....
As far as stopping and restarting SQL Server, I'm doing it from the Cluster
Administrator, go to the Active Resources folder and take SQL Server offline
and
then bring it back online. And then the database(s) will be available.
"AnthonyThomas" wrote:
> What does the SQL Server error logs say when the system is coming online and the databases are marked suspect?
> I'd check the SQL Server cluster resource dependencies. When you say that after you've repaired everything and recycle SQL Server, how are you doing that? Are you just restarting services, taking cluster resources offline, taking the entire resource group offline, or using SQL EM to stop and restart the services?
> Sincerely,
>
> Anthony Thomas
>
> --
> "tim" <tim@.discussions.microsoft.com> wrote in message news:6B363DFC-293D-46F4-9F5E-CAAB0A669B33@.microsoft.com...
> We have a 2 node clustered server (active/passive failover) that when it
> fails over (forced or otherwise) or if we just reboot the server, when it
> comes up, some of the user databases come up marked as suspect. Which
> databases get marked varies each time. If I execute an sp_resetstatus on the
> database it tells me that nothing was changed because the flags are fine. I
> then run a restore database with recovery it tells me that it completed
> successfully and processed 0 pages in 0 sec.
> I then go into the cluster administrator, take sql server offline and then
> bring it back up and the databases will be ok. We're looking for some sort of
> explanation of this behavior

Database marked as suspect when bringing up a clustered server

We have a 2 node clustered server (active/passive failover) that when it
fails over (forced or otherwise) or if we just reboot the server, when it
comes up, some of the user databases come up marked as suspect. Which
databases get marked varies each time. If I execute an sp_resetstatus on the
database it tells me that nothing was changed because the flags are fine. I
then run a restore database with recovery it tells me that it completed
successfully and processed 0 pages in 0 sec.
I then go into the cluster administrator, take sql server offline and then
bring it back up and the databases will be ok. We're looking for some sort o
f
explanation of this behavior.What does the SQL Server error logs say when the system is coming online and
the databases are marked suspect?
I'd check the SQL Server cluster resource dependencies. When you say that a
fter you've repaired everything and recycle SQL Server, how are you doing th
at? Are you just restarting services, taking cluster resources offline, tak
ing the entire resource group offline, or using SQL EM to stop and restart t
he services?
Sincerely,
Anthony Thomas
--
"tim" <tim@.discussions.microsoft.com> wrote in message news:6B363DFC-293D-
46F4-9F5E-CAAB0A669B33@.microsoft.com...
We have a 2 node clustered server (active/passive failover) that when it
fails over (forced or otherwise) or if we just reboot the server, when it
comes up, some of the user databases come up marked as suspect. Which
databases get marked varies each time. If I execute an sp_resetstatus on t
he
database it tells me that nothing was changed because the flags are fine.
I
then run a restore database with recovery it tells me that it completed
successfully and processed 0 pages in 0 sec.
I then go into the cluster administrator, take sql server offline and then
bring it back up and the databases will be ok. We're looking for some sort
of
explanation of this behavior.|||The SQL Server error log shows: Error 9004, Severity: 21, State 10
An error occurred when processing the log for database .....
As far as stopping and restarting SQL Server, I'm doing it from the Cluster
Administrator, go to the Active Resources folder and take SQL Server offline
and
then bring it back online. And then the database(s) will be available.
"AnthonyThomas" wrote:

> What does the SQL Server error logs say when the system is coming online a
nd the databases are marked suspect?
> I'd check the SQL Server cluster resource dependencies. When you say that after y
ou've repaired everything and recycle SQL Server, how are you doing that? Are you j
ust restarting services, taking cluster resources offline, taking the entire resourc
e g
roup offline, or using SQL EM to stop and restart the services?
> Sincerely,
>
> Anthony Thomas
>
> --
> "tim" <tim@.discussions.microsoft.com> wrote in message news:6B363DFC-293
D-46F4-9F5E-CAAB0A669B33@.microsoft.com...
> We have a 2 node clustered server (active/passive failover) that when it
> fails over (forced or otherwise) or if we just reboot the server, when i
t
> comes up, some of the user databases come up marked as suspect. Which
> databases get marked varies each time. If I execute an sp_resetstatus on
the
> database it tells me that nothing was changed because the flags are fine
. I
> then run a restore database with recovery it tells me that it completed
> successfully and processed 0 pages in 0 sec.
> I then go into the cluster administrator, take sql server offline and th
en
> bring it back up and the databases will be ok. We're looking for some so
rt of
> explanation of this behavior

Database Limits on Mirroring

I am looking at the failover options for SQL Server 2005 and the mirroring option as documented in Using Database Mirroring with Office SharePoint Server and Windows SharePoint Services seems to cover this however I have a concern about the recommended limit of 10 databases.

Has anyone had any experience with this, i.e. can it support more and also what sizing limits would be expected (I assume as it uses transactions this is not an issue).

Hi Andrew,

The database size shouldn't really matter. Its the usage. I think the capacity is limited because of the numbers of worker threads that are consumed by each database mirroring session.

regards

Jag

|||

Thanks Jag

I think mirroring is a really fantastic solution, it just has limitiations that require careful design. One option may be (not tested or really comfortable doing) would be to do this via Virtual Machines? If the main bottleneck is threads, however I can see lots of other issues here with networks cards, processors and ram.... perhap just purchase more machines :).

I'll look at clustering options and the use of log shipping for DR

Andrew

|||

Hi,

Just wondering if you found any more information out on the max threads/sessions limit? I'm looking to mirror around 340 DB's from 17 locations (Each location to hold 20 db's of varing size)

Cheers,

Steve

|||

Virtual memory fragmentation is also a key consideration for mirroring, so you should definitiely not to your scenario on 32-bit servers.

64-bit servers with enough memory and processors to support the thread and memory usage might be OK depending on the load, but you should do testing of your specific scenario.

|||

Is there a way to understand/formula on how many mirroring database there can be. If i have two nodes and both "active" for some principle databases can i run more or are there just as many threads on the mirror is there are on the principle? The recomendations of 10 seem quite small for todays hardware of dual core boxes with 8 gigs of memory.

Scott

Database Limits on Mirroring

I am looking at the failover options for SQL Server 2005 and the mirroring option as documented in Using Database Mirroring with Office SharePoint Server and Windows SharePoint Services seems to cover this however I have a concern about the recommended limit of 10 databases.

Has anyone had any experience with this, i.e. can it support more and also what sizing limits would be expected (I assume as it uses transactions this is not an issue).

Hi Andrew,

The database size shouldn't really matter. Its the usage. I think the capacity is limited because of the numbers of worker threads that are consumed by each database mirroring session.

regards

Jag

|||

Thanks Jag

I think mirroring is a really fantastic solution, it just has limitiations that require careful design. One option may be (not tested or really comfortable doing) would be to do this via Virtual Machines? If the main bottleneck is threads, however I can see lots of other issues here with networks cards, processors and ram.... perhap just purchase more machines :).

I'll look at clustering options and the use of log shipping for DR

Andrew

|||

Hi,

Just wondering if you found any more information out on the max threads/sessions limit? I'm looking to mirror around 340 DB's from 17 locations (Each location to hold 20 db's of varing size)

Cheers,

Steve

|||

Virtual memory fragmentation is also a key consideration for mirroring, so you should definitiely not to your scenario on 32-bit servers.

64-bit servers with enough memory and processors to support the thread and memory usage might be OK depending on the load, but you should do testing of your specific scenario.

|||

Is there a way to understand/formula on how many mirroring database there can be. If i have two nodes and both "active" for some principle databases can i run more or are there just as many threads on the mirror is there are on the principle? The recomendations of 10 seem quite small for todays hardware of dual core boxes with 8 gigs of memory.

Scott