Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
atharahmad
Starting Member
1 Post |
 Posted - 2011-05-02 : 04:05:22
|
| Within MS SQL 2008 we have two seperate mirrored databases each with a number of tables (say DB1 & DB2). The databases are queried by our Web Server to access data for our end clients, with approx half million daily hits.Every day we update the databases using the BCP utility to insert 2 million+ rows. During the update process on DB1 the web Server switches to query data from DB2. Once DB1 has been updated the Web Server switches back to getting data from DB1, then DB2 is updated using BCP utility. Quite straight forward you’d think.Our problem is during this transfer the whole of SQL Server database gets jammed/slows down to the extent we receive website timeouts during the transfer. We have ample CPU / Ram capacity. I have searched extensively but have not been able to find a solution. I am not a database administrator but work as an application developer using SQL server 2008 databases. I would really appreciate if anyone could guide me in the right direction.Thanks |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-02 : 09:18:39
|
| You mean that access to DB1 is compromised while DB2 is being updated?first check that you are really updating and accessing the correct database.then check to see what is common between the two systems, sharing disks (data or log), controllers, tempdb, etc.For this I would put te two databases on separate servers with separate hardware.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-02 : 09:23:58
|
| Sounds like you're running database mirroring in high-safety (synchronous) mode. This requires the mirror partner to commit all transactions before the mirror principal can commit them. While this is best for data integrity and reliability it is generally incompatible with bulk loading.If you're using SQL Sever Enterprise edition you can switch to high-performance (asynchronous) mode to do the bcp operation, then switch back to high-safety. Books Online has details under the ALTER DATABASE statement. |
|
|
|
|
|
|
MS SQL Bulk Copy Program (BCP) Issue: SQL becomes