Filegroup Bkp

Hi,

I have a VLDB database replicated to two different servers; one replication is a merge and the other is Transactional. It is a real time data which has a very high growth rate. At present we take full backup daily. The users during the pick time complain for the performance of this database. I am planning to implement a file group backups on this db and seperate tables and indexes on each file group. I would like expert opinion that will this implementation affect on my replication. I am worried for if i do this, my current replication will not be disturbed or stop working? Please advise? Also please suggest how frequent should i take backups?

Thanks

You are reconfiguring the database as well as the table structures. So, yes, it will impact replication when you reconfigure everything. Once moved, replication does not care. It issues insert, update, and delete statements and does not care what file the data is being written to.

The frequency of your backup is based on your data loss requirements. There isn't a specific answer on the frequency.

|||

First of all try to figure it out what is cuasing performance problem by running the trace for high duration, cpu and reads queries/procedures then proceed based on the analysis...

Are you running full backups during peak hours?

In the market there are some third party backup tools make your backups faster.

|||Yes you can test the filegroup backup method in thsi case, but ensure you have tested them in order to ensure it works in the case of an disaster recovery, fyi http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlwriter.mspx link.|||

There are no utilities to make backups "faster". Everything still calls the SQL Server backup engine and that engine runs as fast as the devices on the other side of it can take data. Because these utilities compress a backup, you are writing less to disk. Since you are writing less to disk, the backup runs in a shorter interval. It does not run any faster.

There is also a WIDE variance in times with these utilities. I've tested SQL Litespeed and Red Gate's SQL Backup. After testing more than 100 different databases containing a variety of data, Litespeed gets the job done in about 1/3 of the time that SQL Backup takes (default settings for both out of the box). The only case where I've gotten equivalent performance with the default settings was when I used a database that was almost entirely constructed of large text columns.

|||Does that mean, it will disturb the existing replication if i create a new file group on my db and i will have to rebuild replication?|||Does that mean, it will disturb the existing replication if i create a new file group on my db and i will have to rebuild replication?
|||No. You can add filegroups all you want without any issues. The issue comes when you want to move the tables to those filegroups. That is going to require dropping and recreating the clustered index which is normally the primary key. If you have enabled schema changes, then you will get an error when you try to alter the schema on the subscriber. I'd have to do some testing, I've never tried to move tables around to different filegroups on the subscriber while also keeping replication in place and running.