Supporting packaged SQL Server apps: What you can get away with

Supporting packaged SQL Server apps: What you can get away with

One of the biggest headaches “reluctant DBAs” have to deal with involves supporting SQL Server when it’s being used to maintain someone else’s application. What can you safely do without potentially breaking the app?

First, you should get really familiar with backup and recovery in SQL Server; you can always roll back a change if you do break something. Second, document every change you attempt and try to limit your experimentation to one change at a time. That way if something does break, you’ll have to do less work to fix it.

Now, for the stuff you can always do without fear:

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in May 2010

  • Server indexes     can offer a major performance advantage. Keep in mind that the application may not work correctly while a rebuild is underway, and it will definitely work more slowly.
  • Configure most database options, with the exception of the compatibility level -- In other words, you can configure autogrow and autoshrink of files, turn on auto-update of statistics, etc. -- most of which are good ideas.
  • Add memory to your server -- SQL Server loves memory, and you can never give it too much. Ditto with processors, so you should never configure processor affinity or memory limits.
  • Backup and restore the database, or even pieces of it -- This won’t break anything, and you can never have too many backups. On really critical databases, I like to grab the usual full or differential backups, but I love grabbing transaction log backups as frequently as possible since they’re fast and offer a pretty granular level of recovery.
  • Use SQL Server Profiler to capture server traffic for analysis -- Basically, using SQL Profiler like this is a passive operation, so the app won’t even know you’re doing anything.

Next, let’s discuss the things you can usually do without causing a problem. A well-written application shouldn’t mind any of these, but remember that not every application is well-written!

  • Use the Database Engine Tuning Advisor to add or drop indexes. Adding indexes usually won’t be a problem for an app, but some apps may try to refer to specific indexes by name. This means that if you drop one, it could break something.

It shouldn’t, though; the only legit reason for an application to have an index name hardcoded into it is if the app has its own maintenance routines to kick off per-index reorgs or rebuilds.

  • Split the database objects across multiple database files, thus letting you place different files on different disks for performance reasons.     

I don’t see many environments where this is necessary, because today we pretty much put files on RAID arrays. The physical layout of the database is abstracted from the logical structure, so applications won’t generally care where the files physically sit—unless, of course, the app runs its own backup routines, in which case it may be relying on particular file or filegroup names.

With most applications, you can do almost any performance tuning or maintenance tasks you want and the app won’t care. Poorly-written apps may have some dependencies on things like specific index names, in which case you might want to find the vendor and ask them what they heck they were thinking.

Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.

ABOUT THE AUTHOR:
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top