Overdue Diligence

SQL Server has battled two perception problems since its inception: that the database system isn't scalable enough for enterprise implementations and that it isn't reliable enough for such implementations. With the release of SQL Server 7.0, Microsoft laid to rest the scalability issue with an updated architecture that provides improved very large database (VLDB) support, dynamic self-tuning, disk management, and multisite management through the Microsoft Management Console (MMC). If that weren't enough, SQL Server 2000's federated-database feature combined with Windows 2000 Datacenter Server's support for 32-way systems and 64GB of RAM drove the final nail in the scalability coffin.

However, questions about SQL Server's reliability are proving harder to overcome. Whereas scalability springs from a combination of software architecture and hardware capabilities, reliability encompasses these components as well as the nebulous realm of management and operations practices. Ironically, Microsoft might be its own worst enemy in promoting responsible management and operations practices. For example, SQL Server is ideal for smaller businesses, where database experience is in short supply, because the database system is so easy to get up and running. You can just accept all the default installation settings and walk away. SQL Server is even self-tuning and boasts a database maintenance wizard that can run unattended.

These types of hands-off installations, however, often perpetuate the notion that SQL Server is unreliable. I've heard from several companies that installed SQL Server by using the out-of-the-box settings, then when something went wrong, they blamed SQL Server for all their problems. I wasn't surprised to learn that they didn't have a SQL Server DBA, they didn't bring in outside SQL Server consulting, and they performed little or no database planning or management. You probably couldn't even install Oracle or DB2 under similar conditions.

What can you do to maintain a secure and reliable SQL Server implementation? First, devise a SQL Server management plan, which includes a plan for disk placement and RAID levels for your databases. Make sure you have a solid security strategy, which includes changing the systems administrator (sa) password on your production systems as well as any development systems. Implement a standardized database and object naming scheme and a standardized object ownership plan. Do design walkthroughs when implementing new databases and indexes, and profile new applications before you take them live. Develop quantifiable user response time expectations, and collect baseline performance statistics. Then, regularly capture performance statistics and check server event logs and SQL Server logs. Make sure you have a backup plan and a disaster recovery plan in place—then test those plans. Finally, periodically audit your database administration practices.

These tasks represent just some of the responsible database management practices that SQL Server DBAs need to incorporate into their daily operations. A reliable system demands that you spend time and effort up front to think through your needs. But this extra effort and due diligence will save you a lot of time—and headaches—over the long run.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.