Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
 Posted - 2004-10-26 : 04:51:20
|
| I have a database containing customer reference data (pretty integral to the business) which is approx 42gb. It is split accross 2 files with the main data in the primary filegroup and the import / loading tables for updates on a seperate disk. The bulk of the data is in just 2 tables of approx 45 million rows each. We get weekly updates which are fine and do not affect performance at all. But we are now about to start getting a complete refresh of the data every 6 months and I'm trying to decide the best way of reloading it all from scratch. The data arrives in approx 50 text files held on DAT. I've tried loading the data into a copy of the database on a different server for testing, then moving a copy of the files on the production server, detattching the current db and attaching the new one. This worked great in that downtime was about 5 seconds but I could not get the same file structure in place. I couldn't get it to attach a log file from a specified drive and gave up completely on a secondary data file.The only other way I can think to do it would be to load into the test environment, then use DTS to copy the data in the main tables over to the production server. But this would surely mean some downtime which I really want to avoid.Any suggestions / advice welcome.thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-26 : 08:39:20
|
| How about mount the data in staging tables and then UpSert it into the Live tables - any existing rows get UPDATEd to new data, and New rows get inserted, and no-longer-existing rows get deleted.Could happen as some sort of background task over a period of time (although the data will be in "hybrid" mode during the process.Also, determining that a row is now "stale" will require marking all existing rows as "Flagged", clearing the Flag on each Update / Insert, and then deleting all rows still flagged at the end.This could be done in batches of n,000 rows so as to not bring the server to its kneesKristen |
 |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2004-10-27 : 05:23:57
|
| Thanks, but it still sounds like a pretty resource intensive process? And, rather than just updating records that have changed, there is a business requirement that the data is completely replaced and documented as such.Any other ideas? or how about a way of calculating or estimating the downtime / impact on the server. I don't want any nasty surprises when I do eventually do this. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-27 : 05:47:23
|
"it still sounds like a pretty resource intensive process?"Yeah  "there is a business requirement that the data is completely replaced "Can't see the difference myself, but.To make sure I have understood this correctly. They are going to send you a complete data dump file, in which a very small percentage of the rows will be different to the current data, and they want you to throw away all the existing data and import the new data ... rather than to create a "delta" of differences and just import those?Seems daft to meKristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-10-27 : 08:46:58
|
| I'd suggest creating an empty copy of the table in the same database, preferably on an entirely separate filegroup (even more preferably on a separate disk). You can bcp or BULK INSERT the data into this copy. When you're all done, either drop the original table or rename it using sp_rename, then rename the copy to the original name. The "downtime" would be 10 seconds or less. While you *may* be able to do this on the fly I would recommend putting the DB in single user mode while you're switching the tables. That way you know all transactions have been completed and no others are active at the time you switch.The only problem I see is that bulk loading the data could fill your transaction log if you're using the full recovery model. If you can switch to bulk-logged while importing that will speed up the process a bit. |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2004-10-27 : 10:39:16
|
| I like that solution, and I had considered it already actually, but unfortunately there is not enough disk space in the server to take it. It's part of a 3rd party maintained cluster and I don't much fancy my chances of getting another drive. When I put a copy of the db on to try attaching it, I ending up putting the datafile in the drive allocated for logs. Not really a viable solution long term :(any other ideas? |
|
|
|
|
|
|
|
|
deciding how best to populate a db