K2 database backup best practices

  • 21 January 2009
  • 8 replies
  • 9 views
E
Badge +5

I'm looking for some documentation, tips, suggestions, etc. around best practices for backing up the K2 databases (not the archiving in the workspace). 


I am familiar on how to perform SQL Server backups, however I'm curious if there is anything in particular that I need to be aware of that would prevent me from successfully restoring the backup onto a new SQL Server that isn't necessarily the original box (i.e. the box melted and I have new physical hardware and new SQL instance).  For instance, are there any master keys, certificates, etc. that would prevent me from restoring a backup in this example or is it really just a simple matter of restoring the database onto the new machine from the backup?  Thanks for any input.

8 replies

G
Badge +6

Hi there,


I think I had a good document on this subject around, I will dig around and see if I can still find it. Some general guidelines that I can give you of the top of my head is that when you backup your K2 DBs you will have to stop the K2 Host server. The reason for this is that if K2 is logging data from the K2Server DB to the K2Log DB while you do the backup, then your DBs will be out of sync when you restore them.


 Secondly you do not need any license keys or special serial numbers for the SQL Server box, BUT if your K2 server melted and you had to replace the physical hardware, then you would need a new license for the K2 Server.


Appart from that, restoring the DBs (preferably all of them, again so that things dont go out of sync) is all that you would need on the SQL side.


Regards,

P
Badge +3
Don't transaction logs solve the problem you are talking about with " when you backup your K2 DBs you will have to stop the K2 Host server"? Is there something different about k2 than any other system?
J
Badge +9

The issue is that the K2 Host Servers are continuously writing data from the K2Server database to the K2ServerLog database (for reports).  So if the K2 Host Server is running and your database backups occur serially, the 2 database backups would not entirely be in sync if DB writes were occurring.


 This could be solved if you are using enterprise backup tools and you have the point-in-time backup/restore option.  If you are using the standard backup tools, it is normally recommended that you stop the K2 Host Servers so that no writes occur to the K2 databases when the backup job runs.

P
Badge +3

You can see what a problem this would be in a 24x7 operation, right? ;-) We can't just shut down our k2 servers except during scheduled maintenance windows that occur once or twice per month.

I do see what you are saying and see why this could be a problem. Of course if you actually need your backup it will be a problem anyway, because since you did your backup, even if you had shut down the database, transaction logs will be checkpointed and flushed at different times between the 2 databases. Then you restore from your last backup, and apply transaction logs since that point in time and you have a sync problem.

I am going to talk with our dba's about this, because having many databases is pretty common. 

Are there any sort of recovery options that could move data from the k2 database to the log database when there are sync problems?

J
Badge +9

If it's a 24X7 operation, you probably would have a enterprise backup solution such as Symantec BackupExec with the SQL Agent option.  Note this is an example not a product recommendation.


This point is taken off their web site.


Backup Exec Agent for Microsoft SQL Server:
- Restore with one-pass recovery of the last full and any subsequent backup as a single restore job, or perform "rollback restores," enabling a database to be recovered to a specific moment in time, rather than a specific restore point based on the last backup job


So the backups could still occur as normal without shutting down the K2 servers but in the event of a failure, you need to restore to a certain point in time (i.e. sometime before the backup started) so that all the databases are in sync.  Does that make sense?

P
Badge +3

Sure it does, thanks. Unfortunately, it also messes with our enterprise backup standards, so that may have to be addressed. We already use backup exec, so that is not a problem. Having to restore to a certain point in time also means you are absolutely sure you will lose all data entered after that point. Generally for disaster recovery planning you will pick a timeframe you can accept for data loss, and that policy is what we will have to think through more.


With the out of sync restore, you would at least know what you lost. You might also render your server inoperative from what you are suggesting!


 


good topic

D
Badge

So is there any laid out best practices / recommendataions for backing up and restoring all of the K2 databases in a 24/7 system? 


 I've searched and searched, and can only find these two things:


1) Stop your servers, then do the backup


2) Use a backup product with point in time restore. 


 In a 24/7 operation, 1 isn't an option.  With 2 what would I need to do to setup this?  The example given uses Symantec BackupExec with the SQL Agent option.  Is there any more information on what we actually have to setup within Symantec?  Backing up multiple databases isn't an odd request, however backing up multiple databases that have to be backed up to the exact same point in time seems like bad design.  Can anyone help me ease my pain here by giving me some hints? 


 Thanks.


Andy

T
Badge +3

Yes is clearlly a bad design decision. Databases are the unit of backup and recovery, the decision of not taking that in consideration is a remarkable one.

 

And I think there is no real way around the fact that, you can't backup K2 databases. I'm sorry, but you just can't backup K2 databases; you can, tough, backup K2 database data, which is a bit different. And has most of you have come to the conclusion, data backup isn't easy, is a pain, ususally we use databases to solve that issue.

 

Great job K2 DBAs.

 

If you don't agree with me when I say you can't backup K2 databases. Think for second, if you have to consider the actual data when backing up, that is not a DATAbase, that is just DATA. You can forget the base part of database, you are dealing with raw data, even FK and all other constranits, are then data themselves, only usefull when matched with the correct corresponding data (ie: K2ServerLog or whatever) to make up .. 'the database'.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings