ADO connection state

My app opens an ADO connection and closes it when shut down. The connection is to SQL Server 7 or 2000.

Sometimes the connection gets dropped, due to connection pooling, I suspect. The user gets native error 11, ADO error -2147647259, "General Network Error". There is a handler in every routine that checks the connection's state, and if it's closes reopens it, but the state is always adStateOpen, so it seems the connection does not get notification of its closure. Does anyone have a workaround to this problem?

Try to define your connection object using WithEvents keyword so you can trap each event possible:

Dim WithEvents adoConn As ADODB.Connection

NOTE: do that in the general declaration section of your form.
After object is defined you will "see" it in the "left" dropdown list in your code window so select it and utilize its events.

The connection is declare as Public in a standard module and is visible throughout the entire app. An object cannot be declared withevents in a standard module. I'd have to wrap it in a class to use events. Ya think I should try that?

Quote:

---

Originally Posted by simondeutsch

... An object cannot be declared withevents in a standard module. ...

---

That's why I said do that in the general declaration section of your form

So, for the sake of debugging your application declare it as public on your form but using WithEvents ...

My project has about 50 forms. It would never compile if I moved the variable :)

I tried it in a test project. The Disconnect event does not get fired when SQL Server drops the connection. Also, the connection cannot be reopened without explicitly closing it because it says Object already open, so it has no clue that it was closed by the data provider.

I just don't get one thing: why does "SQL Server drops the connection" ?

I suspect it's due to connection pooling. It's not really SQL Server either, it's the network library, or ADO on TCP/IP.

The point of connection pooling is to sort of recycle connections to the database. Let's say the database allows 10 concurrent connections, or a pool of 10, the database server will disconnect connections that have been idling for x amount of time so that it can accept other incoming connections. The disconnected connection is still in memory, and reconnecting it takes a lot less time than when a closed connection opens. I think it is possible to turn connection pooling off by passing POOLING=OFF in the connection string, but it's an optimization and setting it to off may have a performance hit, atlthough I'd try it as a last resort.

In my test code I "drop" the connection by using KILL on the spid in SQL Server, because this kind of error is hard to get in a dev. scenario. The front-end effect is the same- the connection gets no notification of it's disconnected state.

I've done some more research. There is a general consensus, it seems, that the State propery of the connection object is useless because it NEVER gets notification of its state. Only when it is explicitly closed in code is the state accurately adStateClosed.

There doesn't seem to be a viable solution to this problem, but if anyone has one, please post it.

The only possible workaround I've seen is to trap the specific error and explicitly close and reopen the connection.

BTW, the error can be due to several types of problems, like network problems or the network card or cables, or because the database server was restarted. But if none of these are present, 99% it's due to connection pooling malfunctioning.

Quote:

---

My app opens an ADO connection and closes it when shut down.

---

There's your problem.

Open Connection, Run Query, Close Connection - its the recommended method. Let ADO, SQL Server et al do their jobs.

Quote:

---

Originally Posted by brucevde

Open Connection, Run Query, Close Connection - its the recommended method. Let ADO, SQL Server et al do their jobs.

---

Yeah, what he said - this is PRESICELY why you should only open the connection just long enough to run your query, then shut it down. Let another user have it.

Quote:

---

Originally Posted by simondeutsch

There doesn't seem to be a viable solution to this problem, but if anyone has one, please post it.

The only possible workaround I've seen is to trap the specific error and explicitly close and reopen the connection.

---

No workaround needed, just a redesign on how you are doing it. That's the viable solution. Here's how I do it:
I create a module level variable that's my connection object. I then have an OpenDatabase function that takes two parameters: ConnectionString and CursorLocation. Inside of OpenDatabase, I first check to see if my connection object is nothign and if so, create a new instance of it. I then check to see if the state of the connection is Open AND if the passed in CursorLocation is different from the current one. If so, then I close the connection. NExt I set the connectionstring, set the cursor location, and open it.

CloseDatabase takes one parameter, a Discard flag. First it checks to see if the connection's state is open and closes it if it is. Then it checks the Discard flag and if it's true, sets the object to Nothing.

Then, at the top of my subs where I need it, or shortly before I need the connection, call OpenDatabase and pass in the connectionstring and the CursorLocation I need. When I'm done, I call CloseDatabase. Favoom! That's it. No mess, no fuss. I know that all my connections are open when I need them, and they all get opened the same way everytime.

Tg

Quote:

---

Originally Posted by RhinoBull

Try to define your connection object using WithEvents keyword so you can trap each event possible:

Dim WithEvents adoConn As ADODB.Connection

NOTE: do that in the general declaration section of your form.
After object is defined you will "see" it in the "left" dropdown list in your code window so select it and utilize its events.

---

Neat trick, thanks for this. Too bad MSDN forgot to document this... :mad:

Anyone have any docs on these Events?

Quote:

---

Too bad MSDN forgot to document this...

---

????

http://msdn.microsoft.com/library/de...nectionpme.asp

I just meant it's not documented in my MSDN CD that came with Visual Studio... It lists the Properties and Methods, but makes no reference whatsoever to Events. Thanks for posting that.

Quote:

---

Originally Posted by brucevde

There's your problem.

Open Connection, Run Query, Close Connection - its the recommended method. Let ADO, SQL Server et al do their jobs.

---

I certainly don't want to start a religious war here - but...

It is not the recommended method - it's simply the one you choose to follow.

Kalen Delaney in the book Inside MS SQL Server 2000 points out the overhead of re-authorization required to OPEN a new connection.

We have about 1000+ teachers, secretaries and admin users connected to a SQL server machine all school day - each one keeps the connection open - from app start to app end.

This works fine and we would never consider changing it.

SQL is more than intelligent enough to cycle "unused" connections to the bottom of the thread pool.

Here's a best practice article - although very general...

SQL Server Magazine

Quote:

---

Originally Posted by szlamany

... It is not the recommended method - it's simply the one you choose to follow. ...

---

I agree - indeed that is your choice.

Quote:

---

Originally Posted by simondeutsch

My app opens an ADO connection and closes it when shut down. The connection is to SQL Server 7 or 2000.

Sometimes the connection gets dropped, due to connection pooling, I suspect. The user gets native error 11, ADO error -2147647259, "General Network Error". There is a handler in every routine that checks the connection's state, and if it's closes reopens it, but the state is always adStateOpen, so it seems the connection does not get notification of its closure. Does anyone have a workaround to this problem?

---

I can't ignore this thread - it's hurting my head.

This server is closing a connection on it's own? Later on I see posts about hardware and network drops failing - what?

Ever use OUTLOOK from one of these machines - does it lose it's connection to the e-mail server? What's going on with the switches - with the domain controller - what's wrong with your network?

Is this a .Net/ASP/Internet app - where connection pooling and shared resources comes into play?

The cause of this problem must be addressed first.

Quote:

---

Originally Posted by szlamany

I can't ignore this thread - it's hurting my head.

This server is closing a connection on it's own? Later on I see posts about hardware and network drops failing - what? ...

---

It bothers me as well ...
I also don't believe that server closes connection by itself - doesn't sound right.
We had on going problems with network and each (problem) was related one way or another to "bad" switches ... it's all fixed now (in fact it's all new network).

I am having the same exact problem. I need to run an intelligent purge and need exclusive access to the database during a maintenance interval, and therefore need to force a disconnect if any clients are still connected. However the ado object in the client never realizes that the connection was closed. The .state does not change and I also tried using the WithEvents method but the Disconnect method never executes unless the client initiates the disconnection....

Quote:

---

Originally Posted by szlamany

I certainly don't want to start a religious war here - but...

It is not the recommended method - it's simply the one you choose to follow.

Kalen Delaney in the book Inside MS SQL Server 2000 points out the overhead of re-authorization required to OPEN a new connection.

We have about 1000+ teachers, secretaries and admin users connected to a SQL server machine all school day - each one keeps the connection open - from app start to app end.

This works fine and we would never consider changing it.

SQL is more than intelligent enough to cycle "unused" connections to the bottom of the thread pool.

---

Hmmm. You will find that ADO Connection Pooling does NOT reauthorise for each connection. Once a connection has been made if the client requests a connection with the same authentication as an object in the pool then that object is served up 1000's times faster than reestablishing a connection.

The methods others are talking about will not be efficient on a 2-tier system (which is what I assume you've got) This is simply because the authentication for each request has not been whittled down to just a few (normally, Read,Write, and ReWrite) and as has been said thousands of different authentications go on - one does assume that each user logs onto the server as themselves and not some 'sa' no password.

As for the connection time look for connection idle timeouts associated with the connection pool. The connection pool will not last forever if a connection is unused it will be dropped -normally 2 minutes. Connection pooling is the operating system artificially keeping the lifetime of the ADO object open even when it's instance count has become zero and IUnknown.Release should be called. The O/S won't keep it there forever, though.

If you have long periods where the connection is inactive and (especially) if you are using disconnected recordsets then try not using disconnected recordsets as you will maintain at least one pointer on the heap which should keep the connection open regardless of pooling.

Quote:

---

Originally Posted by yrwyddfa

Hmmm. You will find that ADO Connection Pooling does NOT reauthorise for each connection. Once a connection has been made if the client requests a connection with the same authentication as an object in the pool then that object is served up 1000's times faster than reestablishing a connection.

The methods others are talking about will not be efficient on a 2-tier system (which is what I assume you've got) This is simply because the authentication for each request has not been whittled down to just a few (normally, Read,Write, and ReWrite) and as has been said thousands of different authentications go on - one does assume that each user logs onto the server as themselves and not some 'sa' no password.

As for the connection time look for connection idle timeouts associated with the connection pool. The connection pool will not last forever if a connection is unused it will be dropped -normally 2 minutes. Connection pooling is the operating system artificially keeping the lifetime of the ADO object open even when it's instance count has become zero and IUnknown.Release should be called. The O/S won't keep it there forever, though.

If you have long periods where the connection is inactive and (especially) if you are using disconnected recordsets then try not using disconnected recordsets as you will maintain at least one pointer on the heap which should keep the connection open regardless of pooling.

---

We use WINDOWS INTEGRATED security - so each user authenticates with that method. No username/password asked for, nor do we use SQL Server authentication.

We use only STORED PROCEDURES - pass all data to the VB client and close the recordset immediately. We use SPROCS for UPDATE when the users post a change or entry.

Two-tier? I guess so - but I feel that having all business logic in SPROCS with "standard" parameter patterns from SPROC to SPROC really make the SPROC's the middle tier - all written in T-SQL - but that's just my opinion.

If a teacher finally makes it back to a PC - and decides that little Johnie is not in class today - and that class is already in the flex grid, then clicking the cell to mark the kid absent had better turn around and UPDATE the database instantly - so keeping a connection open works great for us.

Power users (admin, secretary types probably keep the action going constantly) - the teacher type users can walk away from the PC for quite some time.

Right now there are 73 open connections to this one database - obviously not all 1000+ users ever connect at the same time.

I find no problem with this design - works great - seems to be exactly the way SQL was designed to be used - in my opinion.

At least we aren't like the ACCESS clients that connect to the SERVER - each one of those users typically has 3 or 4 connections running at a time...

Quote:

---

Originally Posted by szlamany

Two-tier? I guess so - but I feel that having all business logic in SPROCS with "standard" parameter patterns from SPROC to SPROC really make the SPROC's the middle tier - all written in T-SQL - but that's just my opinion.

---

I think that SPs are not considered a distinct Tier. They are an extension of the database back-end tier. Consider that you could not leave the SPs in place, and replace the database with, say, Oracle or Postgresql... Your SPs and your DB are inextricably linked. Not that it's a bad thing just my .02

Quote:

---

I find no problem with this design - works great - seems to be exactly the way SQL was designed to be used - in my opinion.

---

This may be an accurate statement, but consider how VB6 was "intended" to be used... You probably see where I'm going with that.

Quote:

---

Originally Posted by Dave Sell

I think that SPs are not considered a distinct Tier. They are an extension of the database back-end tier. Consider that you could not leave the SPs in place, and replace the database with, say, Oracle or Postgresql... Your SPs and your DB are inextricably linked. Not that it's a bad thing just my .02

---

Yes, I've have this tier debate before - I would imagine that most 3-tier designs, with a distinct middle business tier, end up using some construct that ties them to the platform they are on.

MS wants you to do this - they like that T-SQL is not ANSI-standard and hope you use ISNULL instead of COALESCE. They want you to develop EXTENDED SPROCS that tie you forever to the MS SQL/WINTEL world.

We believe in leveraging the technology that we have to the fullest extent.

Quote:

---

Originally Posted by szlamany

Yes, I've have this tier debate before - I would imagine that most 3-tier designs, with a distinct middle business tier, end up using some construct that ties them to the platform they are on.

MS wants you to do this - they like that T-SQL is not ANSI-standard and hope you use ISNULL instead of COALESCE. They want you to develop EXTENDED SPROCS that tie you forever to the MS SQL/WINTEL world.

We believe in leveraging the technology that we have to the fullest extent.

---

Well ya, of course you make an excellent point. However I worked closely with a .NET web-services architecture and their back-end was Unidata, and they exported all their bus. rules as XSL, and their datastructures as XML. They could unplug it from Unidata and plug it back into anything - as long as the bus. rules matched. They had an internal "language" to define their bus. rules.

This was a true middle-tier using MS' flagship arcitecture - Web Services...

Quote:

---

Originally Posted by Dave Sell

Well ya, of course you make an excellent point. However I worked closely with a .NET web-services architecture and their back-end was Unidata, and they exported all their bus. rules as XSL, and their datastructures as XML. They could unplug it from Unidata and plug it back into anything - as long as the bus. rules matched. They had an internal "language" to define their bus. rules.

This was a true middle-tier using MS' flagship arcitecture - Web Services...

---

You know I can appreciate that - it makes sense because you can offer your customers a bigger choice.

But apparently the IT world is heading away from multiple choice. Everyone wants to have fewer platforms - less software - less hardward choices - a single OS - just to save some $$'s.

Designing a true middle tier - plug-and-play like you say - must make the architecture of the tier take 90% of the time - a cost we couldn't justify.

Quote:

---

Originally Posted by szlamany

... MS wants you to do this - they like that T-SQL is not ANSI-standard and hope you use ISNULL instead of COALESCE. They want you to develop EXTENDED SPROCS that tie you forever to the MS SQL/WINTEL world. ...

---

Personally I don't see it as a "bad" approach at all - you're using MS (or whatever) technology(ies) so use their syntax but don't mix it up - you may end up with something completely out of hand ...

Quote:

---

Originally Posted by szlamany

... We believe in leveraging the technology that we have to the fullest extent.

---

It's what you believe - there are plenty of alternatives, however, that could be better ...

Quote:

---

Originally Posted by szlamany

But apparently the IT world is heading away from multiple choice. Everyone wants to have fewer platforms - less software - less hardward choices - a single OS - just to save some $$'s.

---

Not according to Bill Gates:

http://www.codeguru.com/columns/Expe...cle.php/c9161/

Quote:

---

Designing a true middle tier - plug-and-play like you say - must make the architecture of the tier take 90% of the time - a cost we couldn't justify.

---

It's true cost will play a big role here - as will time.

Quote:

---

Originally Posted by Dave Sell

Not according to Bill Gates:

---

I've been involved on a "best practice" committee with a large municipality in my area. With "level-budget" increases over prior years and even budget reductions, they are struggling to see where they can cut costs.

One area that appears to stand out to the IT people I work with is that supporting 3 databases and 2 OS platforms costs too much.

We are suggesting that MS SQL and VB.Net be the choice for all future endevours (this works well for me - an outside vendor to them!).

Another customer I have just got 5% reductions in each department. They were told that if they wanted their cost-of-living increases, they would find that 5% somewhere. Time to drop maintenance on the UPS's.

Do more with less seems to be the mantra of the past 3 or 4 years.

Quote:

---

Originally Posted by szlamany

One area that appears to stand out to the IT people I work with is that supporting 3 databases and 2 OS platforms costs too much.

---

Ya, this sounds wasteful. I'm sure this could lead to a cost savings to consolodate technologies in this case.

Quote:

---

Originally Posted by szlamany

We use WINDOWS INTEGRATED security - so each user authenticates with that method. No username/password asked for, nor do we use SQL Server authentication.

We use only STORED PROCEDURES - pass all data to the VB client and close the recordset immediately. We use SPROCS for UPDATE when the users post a change or entry.

Two-tier? I guess so - but I feel that having all business logic in SPROCS with "standard" parameter patterns from SPROC to SPROC really make the SPROC's the middle tier - all written in T-SQL - but that's just my opinion.

If a teacher finally makes it back to a PC - and decides that little Johnie is not in class today - and that class is already in the flex grid, then clicking the cell to mark the kid absent had better turn around and UPDATE the database instantly - so keeping a connection open works great for us.

Power users (admin, secretary types probably keep the action going constantly) - the teacher type users can walk away from the PC for quite some time.

Right now there are 73 open connections to this one database - obviously not all 1000+ users ever connect at the same time.

I find no problem with this design - works great - seems to be exactly the way SQL was designed to be used - in my opinion.

At least we aren't like the ACCESS clients that connect to the SERVER - each one of those users typically has 3 or 4 connections running at a time...

---

In which case you have an idle connection problem.
http://www.microsoft.com/resources/d...on_pooling.asp

Your session long connection still comes from the connection pool. After a given period of inactivity the connection will timeout.

If you are in this scenario then you should use Group Policies to implement the change - because under your current archictecture your only other possibility is to change the registry of each client manually - all 1000 of them.

Quote:

---

Originally Posted by yrwyddfa

In which case you have an idle connection problem.
http://www.microsoft.com/resources/d...on_pooling.asp

Your session long connection still comes from the connection pool. After a given period of inactivity the connection will timeout.

If you are in this scenario then you should use Group Policies to implement the change - because under your current archictecture your only other possibility is to change the registry of each client manually - all 1000 of them.

---

Not at all - the problem you posted a link to is an ODBC issue - we use ADODB - SQLOLEDB provider. No registry work ever - never - ever. Apparently you have never dealt with a large enterprise - have you?

Users are authorized through Windows Groups - management is a breaze for the IT department.

Quote:

---

Originally Posted by szlamany

Not at all - the problem you posted a link to is an ODBC issue - we use ADODB - SQLOLEDB provider. No registry work ever - never - ever. Apparently you have never dealt with a large enterprise - have you?

Users are authorized through Windows Groups - management is a breaze for the IT department.

---

(i) You can use ADODB with the SQLOLEDB provider or the MSDASQL provider. The second provides access to ODBC connection pooling
(ii) ADO with SQLOLEDB uses session pools. The registry settings should be under the provider. If they are not then it uses internal defaults. It will always try to use the registry. If you haven't turned it off then it's on and you are using session pooling
(iii) I deal with a large enterprise on daily basis: large being 2500 users of whom around 500 are concurrent and are spread throughout the world.
(iv) I thought Windows Groups was simply an abstraction to divide users into manageable lumps. You manage such groups with group policies? Perhaps I'm wrong.

I would like to add that you asked for help. I believe your problem is down to some form of automatic connection closing due to pooling whether ODBC, or OLEDB pooling. Have you ruled this out, yet?

Regardless: I don't see any reason for your rudeness.

http://msdn.microsoft.com/library/de...l/pooling2.asp

Quote:

---

Originally Posted by yrwyddfa

(i) You can use ADODB with the SQLOLEDB provider or the MSDASQL provider. The second provides access to ODBC connection pooling
(ii) ADO with SQLOLEDB uses session pools. The registry settings should be under the provider. If they are not then it uses internal defaults. It will always try to use the registry. If you haven't turned it off then it's on and you are using session pooling
(iii) I deal with a large enterprise on daily basis: large being 2500 users of whom around 500 are concurrent and are spread throughout the world.
(iv) I thought Windows Groups was simply an abstraction to divide users into manageable lumps. You manage such groups with group policies? Perhaps I'm wrong.

I would like to add that you asked for help. I believe your problem is down to some form of automatic connection closing due to pooling whether ODBC, or OLEDB pooling. Have you ruled this out, yet?

Regardless: I don't see any reason for your rudeness.

http://msdn.microsoft.com/library/de...l/pooling2.asp

---

This is not my thread - I do not need nor did I ask for help.

I pointed out that closing a connection is not a rule - just a technique used by some applications.

I am not trying to be rude.

This was my first post in this thread:

Quote:

---

Originally Posted by szlamany

I certainly don't want to start a religious war here - but...

It is not the recommended method - it's simply the one you choose to follow.

Kalen Delaney in the book Inside MS SQL Server 2000 points out the overhead of re-authorization required to OPEN a new connection.

We have about 1000+ teachers, secretaries and admin users connected to a SQL server machine all school day - each one keeps the connection open - from app start to app end.

This works fine and we would never consider changing it.

SQL is more than intelligent enough to cycle "unused" connections to the bottom of the thread pool.

---

That means I don't want to debate the issue of leaving a connection open! That means I wanted to point out that there is no hard and fast rule about connection opening or closing. It's all based on how the client talks to the DB. So please deal with the first persons post - the actual problem.

We spent over three years researching and developing our solution - we have no need to change it - it works great! We never have a connection timeout!

This is from your last link:

Quote:

---

In developing an application that will run in a Web-based or multi-tier environment, pooling becomes very important. Making connections to the database can be one of the application's most time-consuming activities. Maintaining connections to the database in the resource state of the Web server can create scalability problems because all users are forced through the same connection object (not to mention that Web servers are almost by definition "stateless"). Opening a new connection on every page of a Web server is bad because it's slow. MDAC pooling provides a way to get the best of both scenarios: a limited number of connections (just enough to match your system's current load) without introducing a scalability bottleneck.

---

We have no WEB server - we have no need for pooling. Opening a new connection is bad - that's why pooling exists. We don't close the connection, so we don't have a problem.

Quote:

---

Originally Posted by szlamany

This is not my thread - I do not need nor did I ask for help.

I pointed out that closing a connection is not a rule - just a technique used by some applications.

I am not trying to be rude.

This was my first post in this thread:

That means I don't want to debate the issue of leaving a connection open! That means I wanted to point out that there is no hard and fast rule about connection opening or closing. It's all based on how the client talks to the DB. So please deal with the first persons post - the actual problem.

We spent over three years researching and developing our solution - we have no need to change it - it works great! We never have a connection timeout!

---

:blush:

Sorry - looks like i got roped into the wrong reply - apologies.

As for the connection open debate? I 100% agree with you that leaving a connection open is not a hard and fast rule.

The only time it needs to be considered 'as a rule' is when you are hosting code in a configured COM+ application where you should never share state across method calls.

Quote:

---

Originally Posted by yrwyddfa

:blush:

Sorry - looks like i got roped into the wrong reply - apologies.

As for the connection open debate? I 100% agree with you that leaving a connection open is not a hard and fast rule.

The only time it needs to be considered 'as a rule' is when you are hosting code in a configured COM+ application where you should never share state across method calls.

---

No problem - this has been a long thread and I'm not sure the person ever fixed their problem!

Have a great day :)

PS. I'm trying to help a customer right now that let a hardware guy replace a tape drive in their SQL server box without powering it down. Obvious spark - crashed the raid-array - failed to rebuild properly - 4 hours on the phone with MS last night to recover "allowing data loss". Now I get to recon the data to see if we have any issues. One 4-minute backup and copy the .BAK file off the server would have prevented this 5 day drama!

Quote:

---

PS. I'm trying to help a customer right now that let a hardware guy replace a tape drive in their SQL server box without powering it down. Obvious spark - crashed the raid-array - failed to rebuild properly - 4 hours on the phone with MS last night to recover "allowing data loss". Now I get to recon the data to see if we have any issues. One 4-minute backup and copy the .BAK file off the server would have prevented this 5 day drama!

---

OMG! You let a hardware guy near some hardware? I thought that they were just office ornaments . . . . ;)

Quote:

---

Originally Posted by szlamany

No problem - this has been a long thread and I'm not sure the person ever fixed their problem!

Have a great day :)

PS. I'm trying to help a customer right now that let a hardware guy replace a tape drive in their SQL server box without powering it down. Obvious spark - crashed the raid-array - failed to rebuild properly - 4 hours on the phone with MS last night to recover "allowing data loss". Now I get to recon the data to see if we have any issues. One 4-minute backup and copy the .BAK file off the server would have prevented this 5 day drama!

---

*shudder* Can you point me to a best-practices doc concerning the backup and restore of a SQL server database? 4 minutes?

Quote:

---

Originally Posted by Dave Sell

*shudder* Can you point me to a best-practices doc concerning the backup and restore of a SQL server database? 4 minutes?

---

I've never seen anything that properly described a best-practice. BOL is vague and the options are overwhelming.

I've got an article from SQL Server Magazine from April 2004 that trys to explain the Database Maintenance plan - but using ENTERPRISE MANAGER-like wizards makes it too magic to fully grasp...

But going into query analyzer and doing this:

Code:

---

BACKUP DATABASE Funds
  TO DISK = 'e:\AMC\Funds_M21.bak'

---

would have taken about 4 minutes - created the nearly 4 gig .BAK file and taken less than 10 minutes to copy to another server.

I'm going to make sure that everyone in that IT group understands this is a requirement before the SQL box is pulled out of the rack!

I personally like to have a maintenance plan that does a image backup daily and hourly transaction log backups - seems to be sufficient for most situations. But getting them copied to another server (or at least another physical drive) is a requirement to make them useful in a recover operation.

Quote:

---

Originally Posted by szlamany

I've never seen anything that properly described a best-practice. BOL is vague and the options are overwhelming.

I've got an article from SQL Server Magazine from April 2004 that trys to explain the Database Maintenance plan - but using ENTERPRISE MANAGER-like wizards makes it too magic to fully grasp...

But going into query analyzer and doing this:

Code:

---

BACKUP DATABASE Funds
  TO DISK = 'e:\AMC\Funds_M21.bak'

---

would have taken about 4 minutes - created the nearly 4 gig .BAK file and taken less than 10 minutes to copy to another server.

I'm going to make sure that everyone in that IT group understands this is a requirement before the SQL box is pulled out of the rack!

I personally like to have a maintenance plan that does a image backup daily and hourly transaction log backups - seems to be sufficient for most situations. But getting them copied to another server (or at least another physical drive) is a requirement to make them useful in a recover operation.

---

That takes care of the easy part - the backup. The restore is usu. a different animal. Do you have a corresponding restore mechanism?

Thanks!

Dave

Quote:

---

Originally Posted by yrwyddfa

OMG! You let a hardware guy near some hardware? I thought that they were just office ornaments . . . . ;)

---

Not my site - I'm just software :bigyello:

Funny thing is the guy was replacing the "backup" tape drive because a tape was stuck in it for several days.

So you got a machine that hasn't had a successful backup in a couple of days - pulled out of the rack - top off - power on (users still in the application performing daily work!).

I happened to be on-site that day in a meeting - walked in a couple of minutes after the lightning stuck - you could see he adrenalin pumping through this guy!

Glad it wasn't me!

Quote:

---

Originally Posted by Dave Sell

That takes care of the easy part - the backup. The restore is usu. a different animal. Do you have a corresponding restore mechanism?

Thanks!

Dave

---

This does a backup and restore (to a different DB name - but that's easy to change).

Code:

---

BACKUP DATABASE Funds
  TO DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE FILELISTONLY
  FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
RESTORE DATABASE Funds_Copy
  FROM DISK = 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.bak'
  WITH MOVE 'Funds_data' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.mdf',
  MOVE 'Funds_log' TO 'd:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Funds_Copy.ldf'
GO

---

We do stuff like this all the time to make copies of production DB's - sometimes bringing them to laptops and off-site to our development shop.

The hardest part about restoring a production DB is that the security falls apart when you delete the original DB and restore. We use database-roles and Windows-groups for authentication - so it's easier for us. But some of my associates use SQL authentication and have hundreds of username/pw problems if a restore is done.

Hey, I'm the original poster.

I didn't know there was so much activity here on this thread :)

I agree with szlamany that closing the connection is not a hard-and-fast rule. My application doesn't have that many users, and pretty much all users will be doing constant data operations such as SELECTs at least, so closing and reopening after each command would be total overkill, not to mention stupid. Users' connections only idle occasionally, or on lunch.

In my understanding, OLEDB (the SQLOLEDB provider), also uses connection pooling, not only ODBC. What I have been unable to figure out is exactly when pooling is used, but I suspect the OS of the box SQL Server is installed might have something to do with it. To all the people asking what's wrong with the network or database, nothing. Read up on Connection Pooling and see why database connections can be disconnected on a fully functional system and network. We used to have a peer-to-peer network, and pooling was not on then, when we upgraded to a client-server with Windows Server 2003, we began having the disconnection problems, so maybe it's only on the OS, because the version of SQL Server is the same.

The workaround (pathetic, I know) I did is in each sub that does database access, the first line in the handler is

VB Code:

If ReOpenConn() = True then Resume

And in a module,

VB Code:

Public Function ReOpenConn() As Boolean
Dim iProp As Integer
'If ADO/ODBC is using connection pooling, any client idling for X minutes gets disconnected.  Must reconnect
For iProp = 0 To modCn.Properties.Count - 1
    If modCn.Properties(iProp).Name = "Connection Status" Then Exit For
Next
If iProp >= modCn.Properties.Count Then ReOpenConn = True: Exit Function
If modCn.Properties("Connection Status").Value = 1 Then ReOpenConn = True: Exit Function
 
Screen.MousePointer = vbHourglass
modCn.Close
modCn.Open
Screen.MousePointer = vbDefault
ReOpenConn = True
End Function

Connection Status is a dynamic property on the connection object that Surprise! accurately reflects the connection's state. The code runs in a loop to find the property because it is not guaranteed to be there; dynamic properties vary between versions of MDAC and/or providers.

So far the code seems alright. There is a miniscule delay, and the connection reopens.