Solved

SQL Server Re-allocate database files with added storage Part 1 of 2

Posted on 2014-10-09
12
71 Views
Last Modified: 2014-10-10
I have an issue that I will explain in more detail in the next question. It has to do with file reallocation of the database files across a recently added 'disk' on the SAN.

I think the primary concern here is to test the setup in a lab environment with easy access to the physical files. A copy of the database and its files should be created on a VM on a local machine. The problem is that there is only a WAN connection via a VPN. This is fine when most operations are server side but not so much for transferring large files and the bandwith it would consume. The main database is just under 300GB. There is one or two more non-trival databases.

An RDP session to the database server is the only real option. Setting up additional transfer protocols (FTP etc) are not really feasible. Is there a way to 1 - divide up the files of the larger databases into chunks to be transferred over a period of days and reassemble once all of the pieces are received. We did this back in the modem days with very large (comparatively) files. The entire database is preferable but I am not sure why. 2 - Script out the schema, which is a big job in of it self and then a subset of the data with a concern being that I get most of the records in the low row count tables and a subset of the high row count tables.

Thanks
0
Comment
Question by:phil916
  • 6
  • 4
  • 2
12 Comments
 

Author Comment

by:phil916
ID: 40370562
Oh if possible maybe a date range cutoff could be used instead of a sampling. The last 3-6 months maybe. This would be preferable to gauge performance since querying cal be run against a realistic set of data. Maybe not...
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40370726
You have some options. The one I prefer is to backup with compress option. It's possible since SQL Server 2008 and you can have compression rates of 70%-80%.
You can also ZIP datafiles, since they are mostly text will have a very good compression rate also.
Another option is to have data in more than one datafile so you can copy a file at once. And you can ZIP each datafile as well.
0
 

Author Comment

by:phil916
ID: 40370833
The only problem I see with this is that I have limited disk space to do this in. The reason for the problem in the first place. At 80% compression 300GB gets me 60GB which is workable. But then lets say I can use Winrar or something to split the files into 2GB checks. I probably wont achieve any more compression but I can get the split. That would give me about one file over 15-20 minutes I would guess. There would be 20 files 4 files per day = 5 days. A bit longer than I was hoping for.

Also I would have to have room both for the compressed backup and the file parts which wold put me over. I have a little less than 100GB
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40370840
Don't you have a backup policy set for that database? Where are the backups stored?
0
 

Author Comment

by:phil916
ID: 40370851
On a different system. These are the parameters I have to work with.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40370865
Then why don't you work with those backups already executed?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:phil916
ID: 40371846
It's is not really a question of "work with". There are many different types of backups and backup solutions. This one does not lend itself to my purpose.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40372036
Here is what you need to do,
1.  Do a Full backup.
2.  Restore the Full Backup with  NORECOVERY.  These twp steps can take as long as needed.
3.  In a maintenance window, stop all activity.
4. Do a Differential backup.
5. Restore the Differential backup with RECOVERY.  Depending on activity, these last two steps could take as little as 15 minutes.

If you cannot afford 15 minutes or you are unable to do backups and restores,  then i suggest you contact a reputable DBA in your area.
0
 

Author Comment

by:phil916
ID: 40372220
Anthony Perkins - The question is not about backups.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40372606
Phil, I don't think you have many options here. You can try to increase the network speed (fiber optic) :)
0
 

Author Comment

by:phil916
ID: 40372684
OK. I get it. The only so much data will fit through the pipe. I am not sure what else I am looking for.

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40374282
The question is not about backups.
Please tell me where I stated the question had to do with backups?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now