How Stable Is Your SQL Server Environment?

Databases, regardless of system, have a hierarchy of needs, but many IT professionals don’t always view them that way. If your team skips any of these needs or addresses them out of order, you run the risk of having a very unstable database environment.

A stable SQL Server environment provides consistently accessible and reliable data, which is crucial for business operations depending on up-to-date information. Consider each of these five database needs to better understand the importance of a stable SQL Server environment.

1. Backups

Two key concepts in database backups can promote stable SQL Server environments: Recovery Point Objective (RPO) and Recovery Time Objective (RTO).

• The recover point is the latest available point in time to which a database can be restored.
• Recover time is a measure of how long it takes to restore a database.
• RPO and RTO are simply the stated desired amount of time (objectives) involved in these two.

Suppose you’re running only “FULL” database backups every night at midnight, with no “LOG” backups. At noon the following day, it’s discovered something bad happened sometime that morning. The recover point is as of midnight the night before, or 12 hours. If it takes four hours to restore the database, that’s the recover time.

The decision of deciding the recover point objective and recovery time objective needs to be made jointly between operations and finance leaders, not the IT managers. The latter needs to be asking some probing questions of the former:

• “How much time (data) are you be willing to lose in the event of a database corruption event requiring a full restore?” This is the data (business, sales, orders, invoices etc.) that has already been generated and recorded but is now lost.
• “How long are you willing to wait after a corruption event to get the database back online?” Data workers are going to be sitting idle while the database is restored.
• “How much are you willing to budget to get these times to an acceptable level?”

Setting RPO and RTO should be a collaborative effort between IT, operations, and finance. IT departments provide technical insights and feasibility assessments, while operations and finance leads make decisions based on business impact, cost considerations, and strategic priorities. This collaborative approach helps recovery objectives remain practical, achievable, and aligned with the overall business strategy.

To promote stable SQL Server environments, these recovery objectives should be well documented in writing for management to see and review as necessary. Also, don’t blindly assume your backups are good. Do a regular test by restoring a database, and measure how long it takes. Did it meet the RTO?

2.Security

Stable SQL Server environments include security reviews. Server security is quite complex, but not much different than other systems’ security. It boils down to keeping out those who have no business being in, and letting in those who need it. In some cases, there’s a requirement to keep track of who viewed or changed what, and when they did it.

There are also complexities and nuances around server certificates, credentials, and object (table, row, view, stored procedure) level security. When you let someone in, what are they going to be allowed to do once connected?

3. Capacity

Does your database system have the disk space to do what it needs to do? Backups should be stored on separate drives for two reasons:

1. If the drive gets corrupted, backups are lost along with the database itself.
2. If the backups consume all the space, you run the risk of stopping the server and databases from functioning.

To establish a stable SQL Server environment, you should have sufficient space for the database to grow for the next 6 to 12 months without concern. This should be reevaluated every year as part of a standard IT systems review.

Likewise, as databases grow, backup files grow. The most recent backup should not be the ONLY backup. A standard practice is to have at least a few full database backups readily available, going back, for example one, two, and three days; one week ago; and one month ago.

4. Performance

Performance tuning involves getting the most out of the physical resources allocated to SQL Server. The goal of performance tuning is to make it go faster with what you already have on hand.

To that end, there are many aspects of SQL Server that can affect performance.

Server and database settings

There are some default settings that were fine for when SQL Server first hit the mainstream 25 to 30 years ago. But improvements both in hardware and SQL Server’s internal process have made those settings obsolete, sometimes even detrimental to good performance.

Database model and table structure

A database normalized to the proper extent is going to be the most efficient. Typically, transactional databases — like line-of-business databases for taking orders or processing invoices — will have more tables, relationships, and foreign keys than analytic databases. This aspect is usually one that can’t be adjusted when attempting to improve database performance.

Indexing strategy

Too many indexes can be just as detrimental as too few, or as problematic as the wrong indexes.

An indexing strategy in a SQL Server environment involves planning, creating, and maintaining indexes to improve the performance of database queries. An effective indexing strategy can lead to significant improvements in query performance by allowing the SQL Server engine to retrieve data more efficiently, while still allowing new data to be saved quickly.

Efficient queries

There are three query styles that read data from a database: queries for front-end applications, those for analytic systems or reports, and ad-hoc queries. While the query styles differ, any of them can be resource-intensive (using CPU, RAM, etc.) and may affect overall performance.

Table and column structure

This involves using the most efficient data type and size to store data. For example, dates with a time element should be stored in a DATETIME data type field; a field to store U.S. state codes need be only two characters in size, city maybe 100, and ZIP code no more than 10.

Usage patterns

This is another aspect over which a performance tuner has no control. If enough users or processes are competing for the same piece of data at the same time, the result is going to be locking, blocking, and in the worst case, deadlocks.

5. Future proofing

Future proofing involves making sure the server and databases continue to operate smoothly over the next 5 to 10 years. This involves, among other things:

• OS and SQS Server patching
• Eventual upgrades
• Addressing evolving security threat vectors
• Not getting locked into an antiquated technology or third-party add-ons
• Regular review of the drive capacities

Almost as important, it involves financial and time budgeting for each aspect.