Everyone DBA probably has one database that just gives them fits. It seems there's always at least one problem database that causes headaches whenever you try to do anything to it. I have one of those. It's an 800 GB database on one of my SQL 2005 servers.
The size itself isn't what's driving me crazy. Yes, a full backup takes several hours and yes, doing any sort of maintenance takes forever. But all those things can be handled - if the database was designed correctly. Databases of this size need to be set up using multiple filegroups. Tables should be partitioned. In short, there are a bunch of design strategies you can implement to make working with very large databases more manageable.
This database has none of that.
And, what's more, I'm not allowed to fix it. This is a database for a third party product we use and the vendor will not support us if I make any changes to the database structure. And, as any experienced DBA can tell you, vendors rarely know how to design a database for good performance.
For regulatory reasons, we need to retain the data this db stores for 7 years. We've had the product in place for just over 2 years so far and, based on current growth rates, the database will reach a maximum size of 1.7 TB by the time it has 7 years of data in it.
1.7TB in a single database file. No partitioned tables. No filegroups. One big file.
Backups take several hours now. I hate to think how long they will take then. And forget about performing a restore, or even a DBCC CHECKDB.
Obviously, this can't go on. Apparently, other people have had the same issue because the vendor has developed a process to move data into archive databases. A business decision was made for us to retain 2 years worth of data in the live database and move data older than that to an archive database. OK. I can live with that.
That process was put in place last weekend and, once again, I am surprised by the lack of database design skills of the vendor. Well, I guess I'm not really surprised. It's more like I was disappointed to have my low expectations confirmed.
First off, the entire process is unnecessarily convoluted. The SQL job that does the archiving does so by calling a batch file. The batch file calls osql, the SQL command line utility, to execute a stored procedure. Why couldn't the job just execute the stored procedure? I could find no reason.
Second, the process moves the old data into a new database, which is named for the year of data it contains. OK. That makes sense. It's easier to have 7 archive databases than 1 big one. Then I took a closer look at the databases the process creates.
They were created in full recovery mode. This wasn't explicitly specified in the process, but was just a result of how my model database is configured, so I can't really blame the vendor. Except.. Maybe I can. It's an archive database. By definition, it's going to have old data in it that won't be changing (except possibly by new data being inserted during the weekly archive process). There's no reason it can't be specified to be in simple recovery mode. But no big deal. I changed the recovery mode and also changed the vendor's procedure so that newly created archive databases will be in simple recovery mode.
Then I looked at the tables in the archive database. There's about 85 tables. Every single one of them is a heap. Not a single clustered index to be found. Not a single non-clustered index. Just heaps of heaps.
Because this is archived data that isn't going to be accessed much, if at all, I can understand not having non-clustered indexes. No need to take up disk space and the lack of indexes will speed data loads. But no clustered indexes? That will actually slow things down. It's also not recommended.
My first archive database is 27 GB of heaps. I want that crap off my server. I'm checking with the various business units involved with this product, but I'm hoping I can take these archive databases offline so they won't be included in my standard backup and maintenance jobs. We can still retain the databases for 7 years, but I don't think they need to be online all the time. If we need to look up any data from them, I can always load the database onto the server again.