SQL server replication question

[niall5098]

Hi There,

I have a client who has a large database, circa 150GB.

They are currently having performance issues and they suspect it is down to performance.

i have not had a look at the database as yet.

their intention is to archive data from live to a second server but they want to have the second instance as a DR.

I explained to them that any method of replication would leave the secondary instance in the same state as live.

so in a nutshell they want to reduce the size of the live database but keep the data on seperate server, preferably in read only mode for reporting.

Any ideas?

Regards,

Niall

 

[SQLScholar]

Not really following you here

They are currently having performance issues and they suspect it is down to performance.

errr... yep!

their intention is to archive data from live to a second server but they want to have the second instance as a DR.

I explained to them that any method of replication would leave the secondary instance in the same state as live.

Ok so they want to have a secondary server for DR, but the live server doesnt have the archived info. I am no expert on this - but it sounds like partitioning the data to give you an archive may help. It may also improve performance with still keeping the full dataset on one server. Then you can replicate this accross to the other server so both servers have a replicatd but partitioned database.

If you partition you can put older and newer data into different files and disks etc. The indexes can be partitioned too. Think you need SQL enterprise though.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

This may help:

http://technet.microsoft.com/en-us/library/dd578580(v=sql.100).aspx

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[niall5098]

apoligies, typo, they are basing the performance issues on the size of the data.

by archiving the data they hope to improve performance.

and they want a dr site.

i have not setup a partitioned database before so am i right in sating in that you need to:

partition the database.
create triggers or stored procedures to migrate the data to the partitioned tables.

then setup the dr using either log shipping or database mirroring.

is my thinking correct?

 

[SQLScholar]

Not quite. Here is a good example:

http://www.mssqltips.com/sqlservert...e-with-horizontal-partitioning-in-sql-server/

To get the data into the correct partitons you use a partitioning function. This specifies for inserts where the data goes.

To be honest - i have never needed to do this, but been on a course to know what it does. I dont know however how you get the existing data to move to the correct partition.

The other thing to be careful is then with queries. When you query the data make sure you specify a where clause that matches the function. That way queries wont have to use both partitions and just the one that the data is in. Also an index on the partitioning column helps.

Dan



----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------