June 3, 2012 at 1:09 am
Hi Techies,
I have database of 1.5 TB and our backup (Networker) is failing most of the times because its not able to complete the backup before our log file gets full. Log file drive size is 150 GB.
The future growth of this database is 3.4 GB per day and i can see we can not truncate the data for next 3 years.
I have got a solution for our backup team that we should split the our database into six drives 500 GB each and this will help them and all backups will run fine. I am not sure how it will happen but they are pretty sure about it.
Now i have questions here for you guys is
1.) Could you please suggest me if spliting of database is a good option.
2.) What are the steps required for spliting the database in different drives.
PS :- We are using VMAX San Storage.
Many thanks
NU
June 3, 2012 at 8:17 am
Splitting the data file into multiple files *might* help - but it really depends on your IO subsystem and how you have the HBA's configured and the SAN configuration (cache, etc..).
If you are already maxing out the IO subsystem - splitting out to multiple files will not help at all and will in all likelihood cause more performance issues.
As for how to do it - you would create the additional files on the LUNs at the correct size and use DBCC SHRINKFILE(original file, EMPTYFILE).
That will move the data from the original file into all of the new files and spread the data across them all. If you are already having performance issues with the SAN, this is going to be very system intensive and cause performance issues for the users.
FWIW - I have a 1.6+ TB database that I backup in less than 2 hours that is split across 2 files. We could optimize this backup using additional files and adding HBA's, but right now we are well within our maintenance window so it isn't worth the downtime to make these changes.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2012 at 3:45 pm
Jeffrey Williams 3188 (6/3/2012)
As for how to do it - you would create the additional files on the LUNs at the correct size and use DBCC SHRINKFILE(original file, EMPTYFILE).
Problem is your left with a 1TB empty primary file, which yes you could shrink but it resides in the same filegroup as the new files. My preference would be to create extra filegroups\files and move specific objects to these filegroups.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 3, 2012 at 11:45 pm
Thanks Jeff,
We are using the network to take the backup's, As we are using VM's so i am not sure how we can use dedicated HBA's. After my discussion with Management they are not happy to provide additional HBA's.
The problem is now how do i move forward to split the 1.5TB across six different drives. I would appreciate if anyone can give me the step by step instructions as this is our production database.
Many thanks.
June 3, 2012 at 11:56 pm
nitinuniyal (6/3/2012)
The problem is now how do i move forward to split the 1.5TB across six different drives.
Perry Whittle (6/3/2012)
My preference would be to create extra filegroups\files and move specific objects to these filegroups.
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 5, 2012 at 1:35 am
I would advise you to do an analysis of the objects in your db first. Make a list of table and index sizes. Then determine if it is possible to move objects to different locations and spread the load that way. If you have some very large tables you could also consider table partitioning across multiple drives. T
June 6, 2012 at 3:55 am
Hi,
are you doing compressed backups or does Networker copy the entire raw data over the net?
If not compressed, i'd presume that not disk-io but network-io is the limiting factor.
regards
karl
Best regards
karl
June 6, 2012 at 4:45 am
Thanks ...
They are doing the normal backup not compressed...:( We are doing the weekly full and daily incremental..
and i do agree its a problem with network, but we need to split the the files into 6 and then back up will be fast enough as per EMC support.
I am planning to do testing for this but still i didnt know the way i can split the existing mdf into 6 mdfs...
Regards
NU
June 6, 2012 at 4:54 am
that's bad 🙁
Did you ever try a compressing backup software like SQL Litespeed (Quest) or SQLBackup (Red Gate)? (i'm assuming SQL2005 i.e. you do not have native compressed backup)
That is what we are doing here - and since the backup gets compressed before it is going over the network that leaves us with about 50% backup time.
(Litespeed backup to network share, than backing up the files to tape.)
Best regards
karl
June 6, 2012 at 5:39 am
To make more file groups you add ndf files to the database in the options. Relocation of tables can be done with alter table move statements. You might have to drop and recreate indexes and constraints. Another less save but faster option is to dump the information with bulk copy statements, drop the table, recreate the table on the new filegroup and bulk insert the rows back into the table. Be sure to check your SQL version.
If you have 2005 or higher backup with compression can be done without any external tools. This will indeed improve your backup and restore speed. If you have a clear picture about your database growth (what tables hold the bulk of the data) Table partitioning is the way to go.
June 6, 2012 at 5:53 am
sorry, native compressed backups are (as far as i know) only available in
SQL Server 2008 Enterprise Edition
SQL Server 2008 R2 Standard and Enterprise Edition
SQL Server 2012 Standard and Enterprise Edition
For tests you could perhaps try
http://mssqlcompressed.sourceforge.net/
Best regards
karl
June 6, 2012 at 5:56 am
Yes sorry you're absolutely right
June 7, 2012 at 11:58 am
Hi NU,
1 - It is indeed a very good option, especially if you know that data is going to grow even more. I've used this approach on a 45TB Database.
2 - a - Analyze space used and rowcounts by table. Determine current available space in each of the mount points (MP) in your VMAX.
b - Group tables by either schema or functionality so that you can see in which drives (MP) you'll want to locate the objects(tables/views/indexes etc.)
c- Create a new Database that will be comprised of multiple Filegroups (multiple physical files within each FG). Make sure that the PRIMARY FG is as tiny as possible.
The reason is that the PRIMARY will hold the metadata and if you need to work with partial operations(BACKUPS/RESTORE/PIECEMEAL), this will work faster. NEVER store any objects in the PRIMARY.
d- Create the rest of the FG with their respective physical files into the multiple MP.
e- Start appending data from the old layout to the new layout.
June 8, 2012 at 11:48 am
just 2cents...
I'd investigate if the VM Hosts have local drives. Because if so, then you can definately speed up backup time by backing up to the Host's drives that a VM lives on. As it is a striaght Bus IO and not going through the network.
I recently did the exact same thing. Choosing to split the database into 5 filegroups was a pain as we wanted to seperate related tables and indexing to increase multi-IO processing by the server and improve performance. Basically I had to build a table/index rebuild generator that used prebuilt relation FK/PK stuff, operational stats collection data and index info to fill the new file groups up for the 20 or 30 largest tables. Then the remaing table objects were used to make sure that all filegroups (excluding Primary) were equally filled in terms of space and performance/usage weighted values. We decided to leave Primary fairly empty, and have a process to monitor if it grew. We then move object out of there as needed.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply