Multiple copies of Same DB

Question

Post reply

Multiple copies of Same DB

azhar.iqbal499

SSCrazy

Points: 2812
More actions
August 27, 2011 at 3:10 am

#256269

We have data in our live DB of six regions. We want to shift the load from one DB by making 6 copies of same DB at same Server. We will route the traffic of 6 regions by making changes in Connection String in web.config.

My Question is that is there any performance benefit of making duplicate copies or not? because All Dbs requests will be handeled by Same Server.

Thanks in Advance.

😀

Azhar

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Devil's Bloody Advocate SSC-Addicted Points: 451 More actions · Answer 1

You have a good chance of making everything slower, specially if the data is identical in all the 6 copies. You will also be requiring the hardware to do more. Another aspect is how are you going to keep the data in sync.

If on the other hand the data is naturally segmented to the 6 regions and the databases _only_ contain the relevant data then perhaps-ish. But tuning the queries might be sisimplernd get you bigger gains.

What performance issues are you seeing? Where is the first set of bottlenecks?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

Since it's all on the same server you're sharing, by default, the same memory, CPU and tempdb. The one area of separation you can get is with disks. Not knowing your system can I tell you that separating out the I/O is going to be extremely benefiicial? Nope. You'll have to do testing to understand where your system places the load. Is it primarily on the disk? If so, then this could be a big benefit for you. If not, you might just be shooting yourself in the foot. Testing your system and understanding where your bottlenecks are is the key.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

azhar.iqbal499 SSCrazy Points: 2812 More actions · Answer 3

Thanks for your time.

Actually, we will synchronize the data by replicating the all dbs. We dont have extra servers right now to shift all subscriber at the different physicals servers. In this case we will have one synchronized DB at publisher. We have huge reports extracting huge data after calculations. So we want to distribute reporting load. We will just use replicated Dbs for reporting purpose. Currently we have dropped our Regions from 6 to 4 and we will run our publisher at one server and all subscriber at another server.

We face blocking when heavy reports encounter there and Activity monitor shows blocking chain with head blocker.

Furtherore We want to replicate data to other DBs on the basis of RegionID filter.I mean to apply filter at the subscriber that will push data to proper DB on the basis of RegionID Filter. Is it possible or not?

Thanks.