-3

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?

0

3 Answers 3

2

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.

0
1

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.

0
1

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:

Take a look at this answer for some pertinent details about the possible pitfalls of using detach-copy-attach.

0

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