September 13, 2011 at 10:03 am
Today we have a system with 2 databases. One database with production data and one with historic data. In the production database there are only valid production data that haven't be archived by our system and moved to the historic database. In the historic database we have all data produced by the system and those databases can be quite large.
Now we want to get rid och having two databases to maintain and move the tables in the historic database to the production database so that we have all the tables in one database. This would make our database development a bit more easy and especially get rid of the problems that can occuor when we need data from two separate databases.
Is this a bad idea? Is it good to store the historic data in a separate database? I think it's better to store all data in one database to make it more easy to write queries where we need data from both historic and production database?
Any thoughts and experiances from anything similar is much appreciated 🙂
Regards
Daniel
September 13, 2011 at 10:06 am
How big are the dbs?
Making the history process is usually a big pita. I wouldn't destroy it if it works.
September 13, 2011 at 10:15 am
Ninja's_RGR'us (9/13/2011)
How big are the dbs?Making the history process is usually a big pita. I wouldn't destroy it if it works.
Well it depends, production is usaually some GB tops, the bigest historic database a customer have is around 40-50GB.
Since the historic database have unique names, all we need to do is to add those tables to the production database and the history shall be created without any problems. The difference will be that historic data will be in separate tables instead of a separate database.
September 13, 2011 at 10:26 am
All you save is dbo.archive vs dbname.dbo.archive?
You can use synonyms for that. The app upgrade would be far simpler.
September 13, 2011 at 10:33 am
Ninja's_RGR'us (9/13/2011)
All you save is dbo.archive vs dbname.dbo.archive?You can use synonyms for that. The app upgrade would be far simpler.
Not sure if I know how you mean, but we copy some data to historic that is needed but not all data from many tables. But not sure if I understand how you mean we could use synonyms?
September 13, 2011 at 10:37 am
Avalin (9/13/2011)
Ninja's_RGR'us (9/13/2011)
All you save is dbo.archive vs dbname.dbo.archive?You can use synonyms for that. The app upgrade would be far simpler.
Not sure if I know how you mean, but we copy some data to historic that is needed but not all data from many tables. But not sure if I understand how you mean we could use synonyms?
Let's go about this in the other way. What do you expect to gain from wasting weeks / months doing that upgrade?
September 13, 2011 at 10:46 am
Ninja's_RGR'us (9/13/2011)
Avalin (9/13/2011)
Ninja's_RGR'us (9/13/2011)
All you save is dbo.archive vs dbname.dbo.archive?You can use synonyms for that. The app upgrade would be far simpler.
Not sure if I know how you mean, but we copy some data to historic that is needed but not all data from many tables. But not sure if I understand how you mean we could use synonyms?
Let's go about this in the other way. What do you expect to gain from wasting weeks / months doing that upgrade?
I expect it to be easier to maintain only one database, easier to write queries that need data from both databases, easier to in the future improve the database design in the historic database. And we are trying this approach with all tables in one database for one customer and hope that it will go well and that we can transfer this concept to all customers.
September 13, 2011 at 10:50 am
Actaully nope on all counts. The only difference is 1 prefix less in the queries and that's it. After a while you shouldn't even think about it, it should almost type itself without your fingers.
The only "gain" I can imagine is simpler backup strategy which comes with longer restore times. I wouldn't do that trade-off, even at 50 GB (which is a <very> small db BTW).
September 13, 2011 at 11:05 am
Ninja's_RGR'us (9/13/2011)
Actaully nope on all counts. The only difference is 1 prefix less in the queries and that's it. After a while you shouldn't even think about it, it should almost type itself without your fingers.The only "gain" I can imagine is simpler backup strategy which comes with longer restore times. I wouldn't do that trade-off, even at 50 GB (which is a <very> small db BTW).
Okey well that's not the experience we have of setting up the communication between two separate databases, we have had alot of problems with security, and different customers have different security on their live servers.
And one reason for doing this is also to inte the long run be able to have less redundant data, because of today we have some tables in the production database that must be replicated to the historic database in order to make it to work. But oyur recomendation is to have two separate databases instead of one?
September 13, 2011 at 11:10 am
No my recommendation is to not waste time on something that has 0 benefit.
Now you're starting to make a little more sense. But I still wouldn't do it unless I saw a clear gain somewhere, which you have not fully depicted to me so far (how much gain vs how much hours invested in the upgrade).
September 13, 2011 at 11:20 am
Does this historic data change?
If not, you can do this and put it in a separate filegroup. That potentially allows you to recover the primary production data quickly and restore historic data later (if you have Enterprise Edition).
If that isn't the case, I'm not sure I think this is a benefit. The hassles of writing code to hit a second database, while there, aren't huge compared to the benefits of separate databases. Lots of people go to trouble to separate out history or archives for a few reasons. One is that if you need to move this to another server, you can. If you have lots of history (and you should over time), you reduce the load on a primary server in terms of memory, tempdb, etc. when querying the historical data.
There's also the RTO for disaster recovery. If you move that 40GB back, then you are restoring a 45GB database if you have an issue (corruption, dropped table, server explodes, etc), instead of a 5GB database. That alone would have me re-thinking your ideas of moving this to one database.
What I would recommend is that you design your code and application to make a separate connection to the historical database. I know this means you might pull data back and muck with it on the client, but ultimately this gives you a little more scalability and flexibility from the client. They could even go to something like cloud services for production data and local servers for historical data. Or you could have lower cost, slower servers (and storage) serving historic data and newer, faster, perhaps smaller servers working with production data.
My opinion is that there are more benefits from keeping things separate. Security is not that hard, and you can develop a framework or guidelines for clients. The development isn't that hard. It is more work, but that's not an excuse for a better architected system for the clients. Developers should learn to work with distributed data, and learn to write the code. Once you get a pattern down, it's not that hard.
September 13, 2011 at 11:22 am
Ninja's_RGR'us (9/13/2011)
No my recommendation is to not waste time on something that has 0 benefit.Now you're starting to make a little more sense. But I still wouldn't do it unless I saw a clear gain somewhere, which you have not fully depicted to me so far (how much gain vs how much hours invested in the upgrade).
Haha well sorry for being so bad explaining 😛 I can see a clear gain at least of doing this and think the hours it takes is well spended time. We db developers feels it's a bit tideous to maintain this extra historic database, so biggest gain that we see is more time to do better stuff. Hard to explain the time aspect, but I think that is the biggest gain for us. Also it can depend on how our system and databases are designed. Hard to explain 😛
Anyway, the main concern I have i performance issues that can be when we move all data into the same database, but I think it will not be such a big problem since 50gb was little data and the queries goes to the same tables with the same amount of data. Maybe if the customer run some big statistic operations on historic data it can be slow down the production...
September 13, 2011 at 11:28 am
Avalin (9/13/2011)
Ninja's_RGR'us (9/13/2011)
No my recommendation is to not waste time on something that has 0 benefit.Now you're starting to make a little more sense. But I still wouldn't do it unless I saw a clear gain somewhere, which you have not fully depicted to me so far (how much gain vs how much hours invested in the upgrade).
Haha well sorry for being so bad explaining 😛 I can see a clear gain at least of doing this and think the hours it takes is well spended time. We db developers feels it's a bit tideous to maintain this extra historic database, so biggest gain that we see is more time to do better stuff. Hard to explain the time aspect, but I think that is the biggest gain for us. Also it can depend on how our system and databases are designed. Hard to explain 😛
Anyway, the main concern I have i performance issues that can be when we move all data into the same database, but I think it will not be such a big problem since 50gb was little data and the queries goes to the same tables with the same amount of data. Maybe if the customer run some big statistic operations on historic data it can be slow down the production...
From an admin side, all your reasons have 0 benefits and <huge> pitfalls to them.
Since you can't (or don't want) to explain those reasons then you just seem like you don't why you want to do something which is not a good reason to mess with a <perfectly> working application.
Seriously, the archive part is already coded. How much harder is it to add a union all here and there to get the history data has well? I have this setup here and it's far from a pita. Makes tuning a heck of a lot easier too.
Keep in mind that tunin a 10 GB table is a heck of a lot different than 100 MB. The queries hitting those table will also look quite differently (likely to have olap hitting archive and oltp hitting "live" table).
The more I think of it the less likely I'd be to sign off on this.
September 13, 2011 at 11:48 am
Thanks for your time, thoughts and warnings 🙂
I will consider your inputs and take a discussion with my colleuges if we shall do this or not. It seems like we shall think it through many times before we do.
Sorry for the bad explanations 🙁
September 13, 2011 at 11:48 am
Avalin (9/13/2011)
Thanks for your time, thoughts and warnings 🙂I will consider your inputs and take a discussion with my colleuges if we shall do this or not. It seems like we shall think it through many times before we do.
Sorry for the bad explanations 🙁
NP, we can only answer to what we see ;-).
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply