Using a duplicate SQL Server database for queries

Question

I have a very large (100+ gigs) SQL Server 2005 database that receives a large number of inserts and updates, with less frequent selects. The selects require a lot of indexes to keep them functioning well, but it appears the number of indexes is effecting the efficiency of the inserts and updates.

Question: Is there a method for keeping two copies of a database where one is used for the inserts and updates while the second is used for the selects? The second copy wouldn't need to be real-time updated, but shouldn't be more than an hour old. Is it possible to do this kind of replication while keeping different indexes on each database copy? Perhaps you have other solutions?

Answer 1

You could task schedule a bcp script to copy the data to the other DB.

You could also try transaction log shipping to update the read only db.

Answer 2

Your looking to setup a master/child database topology using replication. With SQL server you'll need to setup replication between two databases (preferrably on separate hardware). The Master DB you should use for inserts and updates. The Child will service all your select queries. You'll want to also optimize both database configuration settings for the type of work they will be performing. If you have heavy select queries on the child database you may also want to setup view's that will make the queries perform better than complex joins on tables.

Some reference material on replication:

http://technet.microsoft.com/en-us/library/ms151198.aspx

Just google it and you'll find plenty of information on how to setup and configure:

http://search.aim.com/search/search?&query=sql+server+2005+replication&invocationType=tb50fftrab

Answer 3

Generally, all set-based operations (including updating indexes) are faster than non set-based ones

1,000 inserts will most probably be slower than one insert of 1,000 records.

You can batch the updates to the second database. This will, first, make the index updating more fast, and, second, smooth the peaks.

Answer 4

Transactional replication can do this as the subscriber can have a number of aditional indexes compared with the publisher. But you have to bear in mind a simple fact: all inserts/updates/deletes are going to be replicated at the reporting copy (the subscriber) and the aditional indexes will... slow down replication. It is actually possible to slow down the replication to a rate at wich is unable to keep up, causing a swell of the distribution DB. But this is only when you have a constant high rate of updates. If the problems only occur durink spikes, then the distribution DB will act as a queue that absorbes the spikes and levels them off during off-peak hours.

I would not take this endevour without absolute, 100% proof evidence that it is the additional indexes that are slowing down the insert/updates/deletes, and w/o testing that the insert/updates/deletes are actually performing significantly better without the extra indexes. Specifically , ensure that the culprit is not the other usual suspect: lock contention.

Answer 5

Don't forget to adjust the fill factor when you create your two databases. It should be low(er) on the database with frequent updates, and 100 on your "data warehouse"/read only database.

Answer 6

The Mirror File System can replicate file between servers in real time. The primary server can be used for read and write. The backup server can be used for read. http://www.TwinPeakSoft.com/