Solved

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

Posted on 2014-10-09
12
76 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 50

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 50

Expert Comment

by:Vitor Montalvão
ID: 40370865
Then why don't you work with those backups already executed?
0
 

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 50

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

739 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