Richmond SQL Server Users Group

We have a SQL Server 2000 database on a production database server that has 3GB memory.  The database size is 26 GB and there are 256 tables.  Users have read access to the database server.  They connect to the server using Access and run long running adhoc queries for reporting purposes.  We have application performance issues and the following have been recommended to improve the performance:
1) Upgrade memory from 3gb to 8gb
2) Create another database on the same server for reporting purposes and copy production data every night.  Users can run their queries on this new database.  Set the database recovery model as Simple Recovery and mark the database as read-only. 

The question is what is the best approach to automate the copy process every night?
1) Log shipping
2) Snapshot
3) DTS package

 Thank you!

Hi Ravi,

A couple questions: Where does the data come from? You mention ad hoc queries and application performance. What's the schema? Is this OLTP and 3rd normal form or thereabouts?

Here's why I ask: If your application is being impacted by long-running ad hoc reporting queries I would agree with the recommendation to move the data each night to another server. More memory is always a good thing, but your OS must be able to manage it.

I would seriously consider upgrading to SQL Server 2005. Mainstream support for SQL Server 2000 ended a couple months ago. If something "bad" happens, it may be cheaper to upgrade than to fix. If you can tolerate both a wait (a couple/three months) and are willing to risk deploying a RTM version, you could jump straight to SQL Server 2008. Combine all this with Windows Server 2008 and you will have an up-to-date system.

The "pain" of upgrading to SQL Server 2005 or 2008 from 2000 is roughly equal. Also, you get SSIS which will move more data - and faster - than DTS.

 :{> Andy

Andy - It is a vendor application (OLTP) and is used by all employees in the bank for training purposes.  I do not know if it is a 3rd normal form.
 
The OS is Windows 2003 server.  I have already recommended upgrading to SQL Server 2005 and we are checking with the vendor on this.   Do you recommend Log Shipping/Sanpshot/DTS for copying the 26gb database every night on the same server?  What is the best option for SQL Server 2000?

 Thank you!

It's been a couple years since I worked with SQL Server 2000 and I only ever used stored procedures, DTS, or third-party products to move data in SQL Server 2000. Without testing each method on this particular database, I cannot tell you which will be better.

That said, Win 2003 Server will manage 8GB of RAM. Throwing memory at it will most likley ease the immediate memory pressure.

I would definitely keep the vendor in the loop, and on the hook.

:{> Andy

Ravi,

I have had a similar situation where Access was used as the front end to a SQL server database. We ended up actually using a DTS package to upload the data from SQL Server to Access so the users would only hit the Access database and not impact the SQL Server database for reporting.

Now this method may not work if your reports need all 26Gb available. You could do log shipping, but that was a bear to maintain in 2000.

I would recommend (if you haven't already) running Profiler against the SQL Server database to isolate the long running ad hoc processes and see if some tuning (indexes, defrag, etc.) would prove useful.

BTW - Which version of Access are you running?

Michael

Thanks Michael!  Our DBA recommended taking a snapshot every night.  He is testing it in Development environment.  I am not sure Log Shipping works if both source and destination servers are same. 

 I do not know what version of Access users are running.

Thank you!

My $.02:

Write a simple process to backup and restore the DB every night.  It won't take you very long to put it together.  Then just setup a SQL Agent job to run it.  The main gotchas you might want to avoid:

• Make sure you kill any connections to the destination database, assuming that's what you want to do, or else any user that left a connection open will keep you from restoring over the new database
• Use the WITH REPLACE option so that you don't get errors about the transaction log not being backed up on the destination DB
• Test the permissions on the new DB, which should work since it's the same server, but you want to make sure the user permissions the reporting people need came over with the DB
• Create a backup file specifically for this process so that you don't have to worry about changing file names.  Sure you could write a script to generate the filename each day, but why bother?  Then you just delete the file at the end of the script.

Wow, I just noticed it's been a month since you posted your stuff, hope you have a solution by now!

I would agree to most of the comments with Andy, that you should look into migrating to next version either 2005 or 2008.  In my experience from past, we have used log shipping on a oltp database during nightly jobs that has worked good (sql server 2K database).  I am not sure about snapshot, but definitely would not use DTS, since it was Microsoft's first cut of ETL, which became a viable option in SSIS.