Solved

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

Posted on 2014-10-09
12
75 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 48

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

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 48

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 48

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CDC and AOG on MS SQL 2012 13 25
MSSQL - Lock Row from reading by other programs 9 40
RAISERROR WITH NOWAIT 2 18
SQL 2008 R2 problem with DB 9 18
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

821 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