September 8, 2014 at 2:21 am
Hi guys,
I have database with a large table (30 Billion rows) because it is so big I seperated the data in quarterly tables and created a partitioned view (with hints for the date column) about 1 billions a quarter. (all in separated filegroups).
The tables themselfes are partitioned by date again, so you slice out one day
However the full-backup of grows and grows and the mainpart of it is "old" but needed data.
So I was thinking to put the older data in a separate database (with separated backup) and then point to the table in my view.
While this is technical possible (leaving out the WITH SCHEMABINDING) I wonder what negative consequences it will have.
I already had to lose "with schemabing".
I have to use separate partioning functions - for each database its own - (partition schemas where already separated due to separated filegroups)
What about query optimization, does the optimizer care that there are two databases?
Thanks for your input
Christian
September 8, 2014 at 2:40 am
I've done it with one of the databases. You have to remember that you also can't use any statement that modified data on the view. In my case it was not a problem because there was always only one table that has data that could be modified, so I could use the table's name for inserts and updates and not the view's name. One more thing to remember is that moving the older data from the active table to the other database is a bit more complicated, then just playing around with partitions.
An alternative for that is to use different file groups, move the data that doesn't change to a designated file group, backup that file group once a month/quarter (whenever you move the old data). During the rest of the times backup only the other file groups. My boss decided to go with different databases. I have to admit that I also preferred using 2 databases instead of starting running backups on file group level.
As for the optimizer – Most of the time we didn't have any problems, and in the cases that we had problems, it had nothing to do with working with tow separated databases. Those problem exist also when we worked with partition table and partition view that had all the tables in one databases.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 8, 2014 at 4:12 am
Hi Adi,
thanks for your input.
I would use the view for read only. Writing is done directly into the tables via the ETL that can decided which table is fitting for the data.
Moving the data in classic not partition-switch-way once a year to the Archive-Database does not seem to be a problem for me.
That could run in the background and when it is finished I can update the view and drop the data-table in the "current"-DB
I feel more comfortable with the full-backup option then filegroup-based.
In case of a disaster recovery it seems to me the safer way.
However I made good experience with the partial restore function that allows you to restore just a few filegroups out of backup instead of restoring the whole database (in case you deleted something)
Christian
September 8, 2014 at 4:25 am
No disagreements that full backups are easier, but the whole purpose of having the filegroup backup is the situation you're in. As the size and complexity of your database setup grows, so does your recovery mechanisms. Also, the risk of those recovery mechanisms. I'd strongly suggest setting up a regular test recovery to ensure you are getting good backups and that you and your team feel very comfy doing the restores.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2014 at 6:47 am
Hi Grant,
I looked into the filegroup backup and found this limitation
Under the simple recovery model, read/write files must all be backed up together. This helps make sure that the database can be restored to a consistent point in time. Instead of individually specifying each read/write file or filegroup, use the READ_WRITE_FILEGROUPS option. This option backs up all the read/write filegroups in the database. A backup that is created by specifying READ_WRITE_FILEGROUPS is known as a partial backup.
As the database uses simple recovery model I cannot specify filegroups to backup.
The only thing I could properly do is to mark the filegroups with older data as read-only to backup just the "new" data.
September 8, 2014 at 7:31 am
Or, implement full recovery. Depending on the type of data, that might be a good idea anyway. It sure makes disaster recovery easier. Well, not easier really, but more effective.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 8, 2014 at 8:14 am
If I do Full Backup I come back to my initial idea with the partitioned views to carve out the old data from my dayly backup.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply