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

Setting up SQL Server Maintenance Plans. Getting the following error set on
some tasks:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'abcdefg'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[4] Database abcdefg: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
-----
As a result, I'm assuming the job must disconnect everyone first, except the
job itself. The only way I can find to do this is to issue a DISCONNECT ALL
statement. This is new turf for me, having never been a DBA. Therefore,
please give me any other options or feedback.
Thanks :-)
You can try modifying the job for the maintenance plan and adding a step to
do the following:
ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
Let us know if it helps.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set
on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Database state cannot be changed while other
users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs
to
> be in single user mode.
> ----
--
> As a result, I'm assuming the job must disconnect everyone first, except
the
> job itself. The only way I can find to do this is to issue a DISCONNECT
ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>
|||Actually the job is attempting to ALTER DATABASE:
[vbcol=seagreen]
so I'm guessing it cannot because there are open connections.
Thanks anyway
"Argenis Fernandez" wrote:

> You can try modifying the job for the maintenance plan and adding a step to
> do the following:
> ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
> Let us know if it helps.
> -Argenis
>
> "Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
> news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> on
> SQL
> users
> SQL
> to
> --
> the
> ALL
>
>
|||Yes, but probably the job is not issuing a "WITH ROLLBACK IMMEDIATE"
statement. Can't tell for sure. You might want to give it a try -- just
remember to bring the DB to MULTI_USER again at the end of the job.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:DB23A1EC-B4EF-469C-BCD9-DAF6466FFF45@.microsoft.com...[vbcol=seagreen]
> Actually the job is attempting to ALTER DATABASE:
statement[vbcol=seagreen]
> so I'm guessing it cannot because there are open connections.
> Thanks anyway
>
> "Argenis Fernandez" wrote:
to[vbcol=seagreen]
set[vbcol=seagreen]
[Microsoft][ODBC[vbcol=seagreen]
statement[vbcol=seagreen]
failed.[vbcol=seagreen]
[Microsoft][ODBC[vbcol=seagreen]
needs[vbcol=seagreen]
> ----
except[vbcol=seagreen]
DISCONNECT[vbcol=seagreen]
Therefore,[vbcol=seagreen]
|||Uncheck the "attempt to repair minor problems" option in your maint plan. If you have a corruption,
you'd want to know about it and do root cause analysis instead of "having someone trying to fix it
overnight".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> -----
> As a result, I'm assuming the job must disconnect everyone first, except the
> job itself. The only way I can find to do this is to issue a DISCONNECT ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>

Database Maintenance Plans

Setting up SQL Server Maintenance Plans. Getting the following error set on
some tasks:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'abcdefg'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[4] Database abcdefg: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode
-----
As a result, I'm assuming the job must disconnect everyone first, except the
job itself. The only way I can find to do this is to issue a DISCONNECT ALL
statement. This is new turf for me, having never been a DBA. Therefore,
please give me any other options or feedback.
Thanks :-)You can try modifying the job for the maintenance plan and adding a step to
do the following:
ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
Let us know if it helps.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set
on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Database state cannot be changed while other
users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs
to
> be in single user mode.
> ----
--
> As a result, I'm assuming the job must disconnect everyone first, except
the
> job itself. The only way I can find to do this is to issue a DISCONNECT
ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>|||Actually the job is attempting to ALTER DATABASE:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> > failed.
so I'm guessing it cannot because there are open connections.
Thanks anyway
"Argenis Fernandez" wrote:
> You can try modifying the job for the maintenance plan and adding a step to
> do the following:
> ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
> Let us know if it helps.
> -Argenis
>
> "Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
> news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> > Setting up SQL Server Maintenance Plans. Getting the following error set
> on
> > some tasks:
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]Database state cannot be changed while other
> users
> > are using the database 'abcdefg'
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> > failed.
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> > [4] Database abcdefg: Check Data and Index Linkage...
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]Repair statement not processed. Database needs
> to
> > be in single user mode.
> > ----
> --
> > As a result, I'm assuming the job must disconnect everyone first, except
> the
> > job itself. The only way I can find to do this is to issue a DISCONNECT
> ALL
> > statement. This is new turf for me, having never been a DBA. Therefore,
> > please give me any other options or feedback.
> >
> > Thanks :-)
> >
>
>|||Yes, but probably the job is not issuing a "WITH ROLLBACK IMMEDIATE"
statement. Can't tell for sure. You might want to give it a try -- just
remember to bring the DB to MULTI_USER again at the end of the job.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:DB23A1EC-B4EF-469C-BCD9-DAF6466FFF45@.microsoft.com...
> Actually the job is attempting to ALTER DATABASE:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> > > failed.
> so I'm guessing it cannot because there are open connections.
> Thanks anyway
>
> "Argenis Fernandez" wrote:
> > You can try modifying the job for the maintenance plan and adding a step
to
> > do the following:
> >
> > ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
> >
> > Let us know if it helps.
> >
> > -Argenis
> >
> >
> > "Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
> > news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> > > Setting up SQL Server Maintenance Plans. Getting the following error
set
> > on
> > > some tasks:
> > >
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]Database state cannot be changed while other
> > users
> > > are using the database 'abcdefg'
> > >
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> > > failed.
> > >
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
failed.
> > > [4] Database abcdefg: Check Data and Index Linkage...
> > >
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]Repair statement not processed. Database
needs
> > to
> > > be in single user mode.
> >
> ----
> > --
> > > As a result, I'm assuming the job must disconnect everyone first,
except
> > the
> > > job itself. The only way I can find to do this is to issue a
DISCONNECT
> > ALL
> > > statement. This is new turf for me, having never been a DBA.
Therefore,
> > > please give me any other options or feedback.
> > >
> > > Thanks :-)
> > >
> >
> >
> >|||Uncheck the "attempt to repair minor problems" option in your maint plan. If you have a corruption,
you'd want to know about it and do root cause analysis instead of "having someone trying to fix it
overnight".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> -----
> As a result, I'm assuming the job must disconnect everyone first, except the
> job itself. The only way I can find to do this is to issue a DISCONNECT ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>

Database Maintenance Plans

Setting up SQL Server Maintenance Plans. Getting the following error set on
some tasks:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other u
sers
are using the database 'abcdefg'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE st
atement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd failed.
[4] Database abcdefg: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs
to
be in single user mode.
----
--
As a result, I'm assuming the job must disconnect everyone first, except the
job itself. The only way I can find to do this is to issue a DISCONNECT ALL
statement. This is new turf for me, having never been a DBA. Therefore,
please give me any other options or feedback.
Thanks :-)You can try modifying the job for the maintenance plan and adding a step to
do the following:
ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
Let us know if it helps.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set
on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Database state cannot be changed while other
users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption com
mand failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs[/vbco
l]
to[vbcol=seagreen]
> be in single user mode.
> ----
--
> As a result, I'm assuming the job must disconnect everyone first, except
the
> job itself. The only way I can find to do this is to issue a DISCONNECT
ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>|||Actually the job is attempting to ALTER DATABASE:

so I'm guessing it cannot because there are open connections.
Thanks anyway
"Argenis Fernandez" wrote:
[vbcol=seagreen]
> You can try modifying the job for the maintenance plan and adding a step t
o
> do the following:
> ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
> Let us know if it helps.
> -Argenis
>
> "Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
> news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> on
> SQL
> users
> SQL
> to
> --
> the
> ALL
>
>|||Yes, but probably the job is not issuing a "WITH ROLLBACK IMMEDIATE"
statement. Can't tell for sure. You might want to give it a try -- just
remember to bring the DB to MULTI_USER again at the end of the job.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:DB23A1EC-B4EF-469C-BCD9-DAF6466FFF45@.microsoft.com...[vbcol=seagreen]
> Actually the job is attempting to ALTER DATABASE:
>
statement[vbcol=seagreen]
> so I'm guessing it cannot because there are open connections.
> Thanks anyway
>
> "Argenis Fernandez" wrote:
>
to[vbcol=seagreen]
set[vbcol=seagreen]
[Microsoft][ODBC[vbcol=seagreen]
statement[vbcol=seagreen]
failed.[vbcol=seagreen]
[Microsoft][ODBC[vbcol=seagreen]
needs[vbcol=seagreen]
> ----
except[vbcol=seagreen]
DISCONNECT[vbcol=seagreen]
Therefore,[vbcol=seagreen]|||Uncheck the "attempt to repair minor problems" option in your maint plan. If
you have a corruption,
you'd want to know about it and do root cause analysis instead of "having so
meone trying to fix it
overnight".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set
on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other
users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption com
mand failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database nee
ds to
> be in single user mode.
> ----
--
> As a result, I'm assuming the job must disconnect everyone first, except t
he
> job itself. The only way I can find to do this is to issue a DISCONNECT A
LL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>

DataBase Log error.

Hi guys,

Toda i try to insert one row in my databse product table.....that time i got this kind of error.....

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'testDatabase' is full. Back up the transaction log for the database to free up some log space.

the transaction log for the database to free up some log space.

/xxxx/yyyy/zzzzzzzzzzzz.asp, line 109

any one know about this kind of error .......pls help me......

thanks in advanseHave a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#EYRAE|||ever since they changed the default recovery mode from 7 to 2000 I wish they had added a screen to the setup with big flashing letters about the implications.|||Can i use following command for this one,

dbcc shrinkfile ([dbname_log])|||well you could do a dbcc shrinkfile ([dbname_log],1) but the real issue you need to address here is your recovery model and what I suspect is your non-existent disaster recovery plan.

sql2k defaults to full recovery. which means you can recover to any point in time as long as you are performing database and transaction log backups. I suspect you are either not doing this or not doing it frequently enough because if you had been your transaction log would not have filled up your drive. unless of course you have your ldf and mdf on the same drive god forbid. In which case you are just straight running out of space and you do not really care what happens if you lose a drive or a machine which will eventually happen.