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 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