Scheduling Database Backups: Need a Solution

Recently we had this issue.

We have a huge database server with 2.5 TB of database space. The server has 8 GB of RAM and two dual core processors. It also has a not so good storage device. Due to the limitations in RAM and hard disk, (the disks are really slow) we were facing a problem in taking full backups.

Initially, the database server had a set of jobs, each one was starting on 5th of every month at 2:00 am. It created a whole heap of issues, including too many (is 59 a big number?) backup jobs starting at the same time causing each one blocking the other due to memory crunch and hard disk bottleneck.

So we decided to schedule the task one after the other. We also scheduled it to happen on a weekend of the moth. (example: First Saturday) We did a calculation on hard disk speed and database size and schedule the jobs to start at different times. For the first month everything worked nicely. But when the databases started growing, it forced us to reschedule the backup jobs. Can we reschedule the jobs every month? Currently we have 29 servers to maintain and some have more databases. If we don’t re schedule the jobs we may face one of the two scenarios (actually both scenarios)

1. Before a database backup is completed, the second one starts and creates memory and disk contention.
2. One backup completes earlier than expected but the next backup waits until its time comes.

We also had another issue: most of the databases are inter-connected so the backup jobs need to be completed one after the other. As some of the databases are really huge, the backup jobs pass the 24 hour time frame. It created another issue for scheduling backups.

 How to solve the issue?

Please come up with your solutions.

Share this post :

Develop Database For Future

• Do you write TSQL statements which do not end with a semicolon?
• Do you use system tables or system procedures in your code?
• Is your code contains ANSI settings?
• Do you have text, ntext or image columns in your databases? 

Your database is having a danger. The above list is just a few of the massive list of items marked for deprecation. The best option is now itself startchanging them to the new standards. Add them for your standards and whenever you need to change the code to add a functionality or to remove a bug, change the things marked for deprecation also. To my surprise, the vardecimal storage format, whcih came with SQL Server 2005 sp2, is also marked for deprecation.

Check the complete list here: http://msdn.microsoft.com/en-us/library/ms143729.aspx

When talking about writing for future, we also need to consider about reserved keywords. Microsoft keeps on adding new reserved keywords to the list, where words like “LineNo” and “National” are reserved keywords already. The recommended practice is to use them enclosed with square brackets (for example , [Description]). Unfortunately, a word which was not a keyword now, can become one in the next release. So, how can we prevent this? Gogula gave a nice answer in his recent presentation: “Always enclose with square brackets”.

Read about it more here: http://dbantics.blogspot.com/2009/08/t-sql-best-practices-and-coding.html

Finally, beware of less used features. They are most likely the candidates for deprecation. You all know what happened to notification services right?

Share this post :

 

Discount offer for Microsoft Certification

Prometric is offering 15% - 25% discount for many certifications including many Microsoft certifications.
This offer includes most of the SQL Server related certificates if not all.
Interested?
If Prometric has the information of you it would have sent you a mail on this.
If not just visit their site and give your basic information.  They will mail the voucher to you.

http://www.prometric.com/microsoft/careeroffer.htm

The only catch is you need to complete the exam before december 31. (Is it too long? Set an internal target for October 31)
Good Luck!

Share this post :

Log Shipping Issue With Subscriber Database

We had another Issue with our migration process.

I had a database created with replicated tables. Now the bigger question was how to create the replication in secondary (log shipped tables) database.

Be default, when you create log shipping, the secondary database is in read only mode. You are not allowed to create any additions to the tables. If you create a publication, on such a database, it will fail because it tries to create/update system tables, and add new tables regarding replications. (Yes it creates them in distribution database and in publication database)

So we came up with a different approach:

We decided not to create publications. We enabled replication on secondary server and on the day of transfer, we stopped all transactions and created replications manually.

But it has a different problem: By default transactional/merge replications require a snapshot replication to be created. We overrode the default settings saying just ignore the snapshot. As all the data was transferred, it will continue to function.

Did our plan worked? Well the answer is yes and no. The replication started working without snapshot is being created. However, we messed up few things in restoring the final log backup (tail-log backup) which forced us to deploy a fresh backup.

More on this tomorrow:

Just a piece to monitor: The picture here shows the setting we need to select when setting up replication using management studio.  The default is "drop existing object and create new one", but we need to select "keep existing object unchanged"

Share this post :

Log Shipping Issue

When we setup Log shipping manually it seems to be an easy task. But within a couple of days we were proved to be wrong. The log shipped database didn’t get the updates on time. Additionally we started receiving messages stating the log backup has failed. It took some time to identify the cause. All backups were set to start at the same time... So we manually changed the starting time so they started working fine.

Soon we faced the next situation: Since we have more than 60 databases, and setting them independently while giving enough room for log shipping to complete became a problem. Wither the time si not enough or we step into another log shipping time. Doing such a minute calculation was little costly for the operation we did, so we decided to live with this problem until the migration completes.

Do we have a solution for it. I think we have. We are planning to try that next week and I will update when we do that. Please keep your breath until then.

Database Migration

Today we completed migration of one of the database server (64 databases and around 200 GB of size with around 200 articles are replicated). The databases were moved to new server to a new hard disk location, with up to the minute data, logins, jobs and replication. Normally transferring the data will take a longer time. So we decided to adopt a new mechanism to make sure all databases are transferred while users are using the old server itself.

We used Log shipping to transfer the data.

There are a couple of things to learn from the experience. We had a couple of issues:

• Unexpected server restart,
• Failure of restore the tail log backup
• Restore over an already restored database.
• Replication failing constantly reporting "login failed for 'sa'".

With all these issues we were able to complete the tasks with 2 hours of downtime.

I am planning to write what we have done and lessons learned from all the issues for the next few days. Stay tuned!