June 18, 2018 at 8:28 am
A customer has an production system that saves production data in a
local SQL Server Express. A stored procedure, which runs forever,
copies new and changed data (using triggers together with the stored procedure[1])
to a central SQL Server 2008 R2 Standard. This system runs on every machine and
at the moment they have 4 machines.
The key data that is saved is a serial number for each product. Each product is marked
with this number. If there is some error in the product, they want to be able to lookup
this serial number in the database and see which batch (and a lot of other data) it belongs to, and take the necessary precautions....
Each machine creates about 2GB data per month, more machines will be installed!
The data must be saved for 10 years. The central database contains at the
moment about 50GB.
The database on the central SQL Server, is "read-only". It is just for reading, except
for the machines to update it with new rows.
Our idea is to let data for the last year be in the active database. Older data is archived
into a separate database, which is backuped and stored on backup media.
Each table has a Creation datetime column that can be used for selecting.
Unfortunately neither I or the person that is responsible for the database at the company
are DBA's. I'm a C# programmer, and he is a resource person that has many hats.
I'm now looking for some archiving solutions!
This must be a problem for all that have a database.
Please recommend how to solve this problem!
// Anders
[1] Not the best solution, but it works.
Disclaimer! I was not involved in the development of this system 😉
June 18, 2018 at 9:08 am
Create one database for each month using an extension of _YYYYMM as a part of the database name. The "table" in each of these databases can be the same name across all the databases. The "table" in each database must have a constraint that limits it to the scope of the month that the database/table encompasses.
Create a partitioned view to string them together as necessary. THAT can easily be done programmatically and you can certainly have more the one partitioned view for "Current Month", "Previous + Current Month", "Previous 3 months", "Previous Quarter", "All Data", etc, etc, and a monthly job can easily handle the changing of the partitioned view definitions.
There are several really cool parts about doing it this way. If you want to do restores to a test system, it's super easy... just restore the databases you want (and only want), run the proc that rebuilds the views, and Bob's your uncle (without having to restore all the history). Also, for older months that a guaranteed to be static, you can truly set the individual databases to READ_ONLY, back them up one final time, and never have the need to back them up again. Another really cool thing is that the "current month" and, perhaps, certain "recent months" may have different index requirements to support nasty fast recent queries while longer span queries need different indexes. That's possible to do and part of the "archive process" of making a given database READ_ONLY would be to first change the indexes (taking some great care not to generate a lot of empty space in the database prior to making it READ_ONLY).
I'd recommend such a thing (Partitioned Views) even if you had the Enterprise version and could use partition tables. I made that mistake and it's impossible to restore "just certain months" to make a smaller Dev or QA environment and will change it in the future. Still, it contains more than 9 years of data and it takes just a couple of minutes to do backups because each older partition has been set to Read_Only and there's no longer a need to backup the whole Terabyte of data.
Think of it as the ultimate "Divide'n'Conquer".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply