This is my situation right now: I'm working with SQL Server 2012, and I have 2 databases, one is 3.5 GB and the other one is 7.6 GB, and the company I work with is going to migrate the server to a new (better) one, I need to move these databases to the new server, the problem is, that these databases are in Production Environment, and if I start to compress them, the system gets down, this is caused because the current server is using 99% of the CPU and RAM resources.

So, to avoid this problem, how does these databases should be administered when I move them to the new server?

I was thinking that once I move them, should I split the information into separate databases using the information by year?, i.e. DatabaseName_Year (AdventureWorks_2016).

Is this a good approach to avoid having huge database files to move when required?

Is there a better way to administer large databases?

  • 1
    As I read your question, you're asking what to do with these databases once they're on the new server*, not how to move them to the new server. Is that correct? Also, can you clarify what you mean by "if I start to compress them"; are you trying to shrink the database files, turn on compression for some tables/indexes, or something else? – RDFozz May 4 '17 at 19:10
  • @RDFozz You are right, I want to know how to administrate these databases once I migrate them to the new server, right now, the issue is that our server is in India, we connect through remote desktop, so I tried to compress the databases into a RAR file, but it takes a long time and resources to accomplish, and then send the file through FTP server, this is the worst way to do it, I know, but it is the only way I know to do it. – Andres Valencia May 4 '17 at 19:21
  • 2
    3.6 and 7.6 GB are not 'large databases' – McNets May 4 '17 at 20:31
  • Are you trying to compress the MDF and LDF file or a backup of the database? – Wes May 4 '17 at 21:38
  • 1
    @Wes As I am a beginner with SQL, I don't know a lot of these features, I have always been doing the simpler way, I didn't know that SQL already can compress the backup. Will definitely try this out and see what happens, it would be a great relief if the size gets down a lot, so I can transfer it faster, thanks! – Andres Valencia May 4 '17 at 22:20

First, always remember "large" is in the eye of the beholder. I've regularly worked with databases that were 100 - 400 GB. so yours don't seem that big to me.

That said: The primary purpose of (most) databases is to serve the needs of a user base, usually via an application of some sort. If application performance is good, then there's absolutely nothing wrong with database as large or far larger than yours.

I'd generally think of the hierarchy of what's most important as:

  • Data recoverable with acceptable loss, in acceptable time, in case of emergency/disaster (note that the business owner should define acceptable here - download BrentOzar.com's First Responder Kit for (among a lot of other great stuff) a checklist for working out what timeframes are acceptable for the above. Of course, what most people want vs. what they're willing to pay for are often two different things....
  • Separate environment for testing changes/updates to DB or to application
  • Acceptable (or better) performance for most (or all) users;
  • Ease of administration.

Note that the business owners want to put the third item on that list at the top. Until, of course, something happens because one of the first two items was skipped. If the higher-ups insist on changing the order, that's OK - but make sure it's documented, and that you've tried to make them understand the potential problems involved. Losing a day's worth (or more!) of orders due to a server issue, or trying to operate with an "updated" application that's significantly broken in some way (for example, can't take orders, or can't print them out to be shipped) is the only way some people learn. And, unfortunately, some people will blame the DBA, even when you've told them this could happen, and they chose to ignore it.

It's good to look ahead, and considering moving older data from your database to an archive of some sort is reasonable, even if it doesn't need to be done right away. However, that's the sort of thing that needs to be worked through with representatives of the user community, and with the business owner (usually not the same, note). Older data may still be needed on a regular basis; if it's not in the database the application sees, then it's not available. If you set up a separate interface to get to older data, it's still not convenient (and, there's a chance that someone will get mixed up, and manage to enter new data into the archived database). It may be more reasonable to look at partitioning your tables based on dates. The older data could be moved to a separate file that's still part of the same database.

As far as compressing the data, you mentioned in a comment that you'd tried compressing the database into a RAR archive. SQL Server data (and log) files are active as long as the server is up and running; if you tried to compress the actual .mdf and .ldf files, you'd wind up with a resulting file that wouldn't be valid. You should try to back up the databases. The built-in BACKUP DATABASE command in SQL Server allows you to compress the backups - mine usually wind up being about 1/6 th the size of the actual data (but your results may vary). You can right-click on the DB in SQL Server Management Studio, chose "Tasks", then choose "Backup...". I would still recommend doing this during a period when the database is least heavily used, and it may still be an issue, but it's at least worth a try.

If the backup files are still too large to easily move from the old server to the new one, or if the down time involved in doing so would be prohibitive, then consider a more gradual method of getting the database transferred to the new server. sog shipping (as noted in Rafael Piccinelli's answer) is definitely an option, especially if it may take several days to get everything moved and set up in the new environment. If the problem of moving the files is less severe, you could probably shortcut from actual log shipping to something simpler: Take a full backup of the database anywhere from a few hours to a day before go-live; then, take a differential backup right after the start of your down time. You can have the full backup restored (but not recovered) on the new server, waiting for the differential. That file would presumably be much smaller than your full backup, so copying it to the new server should be much faster. Once it's there, restore it to the new server, recover the database, and you should be ready to go.

Note: Whatever method you decide to use to move to the new server, do at least one successful test run before finalizing your go-live date.

| improve this answer | |
  • I know that these databases may not be that huge, but it is just the ones I have worked with, and we have the problem that the information is in a remote server (India) and we want to move it to another remote server (Florida). And when I talked about compressing them, I first made a backup as you mentioned, because other way I could have wrecked the databases. Right now, the best option I have is to stop the SQL Server instance, get a downtime to make the backups and then start compressing them and try to send the file in FTP, this if I don't find a better solution – Andres Valencia May 4 '17 at 21:18
  • Updated with suggestions for moving DB (see log shipping answer, or try full backup to prime new server, then differential to catch up to present - allows less down time. – RDFozz May 4 '17 at 21:37
  • Thanks for your suggestions, I'm checking about Log Shipping method, it seems the best one, but I wonder if it will work between two remote servers – Andres Valencia May 4 '17 at 22:16

I would go with Log Shipping.

I could migrate a 1.5TB Database from a server to another, in 2 minutes. It's going to take some time to create the log shipping config. Basically you will configure your database in the primary server, to use log shipping to the destination server. After this, you just need to stop the application for some time ( minutes ), and change some strings to point the new server with the new database.

These are some question I made during this process:

Log Shipping "out of sync" but none of the jobs are failing

What does 'Could not find a log backup file that could be applied' means on log shipping?

It was really easy to solve problems and to configure the environment to the log Shipping.

| improve this answer | |
  • If my database is in a remote server and I want to send the database to another remote server, can I use Log Shipping to accomplish this? – Andres Valencia May 4 '17 at 19:31

Since you cannot backup your database without setting your server on fire then you can Detach your Database, transfer to new server and then Attach it in the new instance. To get started please see:

| improve this answer | |
  • please don't recommend detach-and-attach without talking about the dangers that are inherent in that – Max Vernon May 4 '17 at 19:39
  • Every process/method has a danger. As a DBA you the user should understand that and do his own research. – Raidenlee May 4 '17 at 20:17
  • 2
    This OP is ostensibly not a DBA, otherwise they would not be asking this question. – Max Vernon May 4 '17 at 20:40
  • I see, I'll fill in some risks of doing the detach and attach. – Raidenlee May 9 '17 at 18:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.