Create a feedback loop with the developers. Unless they have insight into the runtime or growth characteristics of their code, they cannot proactively address the issues they do not know about.
Tips
Effective management of the SQL Server environment is only possible by having data captured and available. At a minimum, capture SQL inventory and configuration, performance, security, audit and maintenance data.
Always design the database environment top down even if the environment consists of only a couple servers or several hundred. By approaching the design top down, many efficiencies can be gained on every level.
Independent of how much automation and self tuning a database package has, it needs to be reviewed bi-annually. A SQL health check is an important task to achieve optimal performance and stability.
Choosing between physical and virtual implementations of SQL Server does not have to be an all-or-nothing approach to your deployments. Both strategies are equally valid and can co-exist peacefully.
Testing is one of the cornerstones for ensuring success in production, including both performance and availability. DBAs and IT are responsible for testing things like updates in non-production environments prior to rollout as well as ensuring disaster recovery plans work. What you do not know can and will work against you.
Benchmark/baseline performance from the start and continue to do it throughout a solution’s lifecycle. Usage (such as the number of users, which adds load) will change over time and you should be able to understand what “normal” looks like to anticipate problems as well as keep track of usage for future capacity planning.
An optimal disk design for SQL Server will take into account both speed and capacity. They are two different concepts with often conflicting goals. Since writing and reading data quickly is crucial for SQL Server’s performance, deploying on a suboptimal disk subsystem will cause you pain. A SAN is not a guarantee of performance.
Achieving high availability is a combination of people, process, and technology. Never lead with a technological solution or technology. A solution must start with proper requirements that meet the actual need of the business.
Performance tuning can be performed on many levels including the hardware, software, schema, code base. Focus on the hardware and software configuration first as these need to be optimal and known before reviewing the schema and code base. I always say, “Clean the room up first so we can better see what we are dealing with.”
SQLHA Experts Tip #8
Testing is one of the cornerstones for ensuring success in production, including both performance and availability. DBAs and IT are responsible for testing things like updates in non-production environments prior to rollout as well as ensuring disaster recovery plans work. What you do not know can and will work against you.
