10 Things Every Project Manager Must Know When Deploying a SQL Server

As someone who lives and breathes SQL servers, let me tell you they are complicated beasts. With as many moving parts as the international space station (or thereabouts), it might seem impossible for an outsider to understand what’s important and what’s chaff. So, if you’re implementing an SQL server project and you don’t have vast experience in the field, what are the key things you should be looking at to make sure the project runs on time and on budget?

Below are the top 10 items that are either overlooked or deemed unimportant at the project stage but that WILL hurt you down the track. Trying to implement these later can be costly and overly complex. Here’s the list, in no specific order as they’re all equally as important.

Hardware load testing

No doubt the project specification includes a level of storage and IO commensurate with the future requirements, but how do you know that what’s being built is sufficient? That’s where hardware load testing comes into play. Throw a dummy query at it a few thousand times and observe the results. Try these gems for detailed stress testing: https://www.brentozar.com/archive/2015/05/how-to-fake-load-tests-with-sqlquerystress/ and https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

Performance baseline environment

At some point in the life of an SQL Server, someone will complain about performance. Often this is due to changes in the environment, but without a baseline of performance analytics, it will be incredibly difficult to see what’s changed. Check out https://archive.codeplex.com/?p=pal for a fantastic way to get key metrics in one place.

Scope for growth

Straightforward but still often overlooked, allowing room to grow is vital to get the full life out of your server. Review the past 6-12 months of server load data to establish trending and ensure the new environment can handle this growth. You don’t need the resource up front, but if you know that you’ll need additional CPUs, memory or storage down the track then there had better be a cost-effective way to add it in at that time.

OLTP vs OLAP

‎Short for online transaction processing and online analysis processing, these are the functions that get the data and analyse it. Understand the nature of the transaction workloads for the environment to ensure that SQL Server is configured and tuned accordingly.

Disk Layout

This will come back to what storage solution is being used; however, it’s best practice to separate out the data, logs and tempDB to separate disk allocations within Windows. Even though the underlying storage subsystem may be the same, separate allocations into Windows provides additional read/write handles from the OS to the disk, avoiding possible delays in server IO that will slow down your system.

Backups

There’s more to backups than just having the data available. In fact, the two key indicators you must consider are Recovery Time Objective (RTO) and Recovery Point Objective (RPO). RTO refers to how quickly you need the server to recover from a failure whereas RPO refers to the amount of data loss that is acceptable. The smaller these are, the more robust the system is, but also more expensive.

High Availability Options

Understand what High Availabilities are available: SQL Server Availability Groups, Database Mirroring, SQL Replication, Log shipping, scheduled backups/restores. All have pros and cons and must be weighed against the budget and requirements of the project scope.

Disaster Recovery

What happens if the entire business infrastructure suffers a catastrophic failure? What is the business’ recovery position? Off-site backups? Replication to the cloud? If you can’t answer this question, then it’s probably bankruptcy.

Migration Strategies

Once the SQL Server solution has been built, you need a strategy for moving your production databases and workloads into this new infrastructure. This will minimise the disruption to business and application services that depend on these databases.

Ongoing Support

Once in production, SQL Servers are not a set-and-forget solution. Databases grow, queries evolve, execution plans change as the SQL compiler finds more optimal paths for retrieving the data, warranting new indexes and fragmentation maintenance to keep things optimal. Having the right monitoring and support solution in place can make the difference between a smooth-running machine and an expensive outage, the latter being a difficult position to justify to a business owner if this is overlooked at the outset.

You don’t need to hire a separate team for ongoing SQL Server support. Remote SQL Server support services give you all the benefits of an in-house technical resource without the full-time overheads. DBA Services is here to help you minimise the risk and impact of costly server outages. We will serve as a natural extension of your in-house IT team, focusing on server monitoring and prompt incident response.

You don’t need to be a database expert to properly implement a SQL server project. If you’ve considered the above issues and asked questions of the right people, you’ll be miles ahead of others in your field. And if it all seems a little overwhelming, well help is available. Besides remote DBA monitoring, we offer SQL Server consultancy services. We provide guidance in during the design and development phase of your SQL server infrastructure, identifying procedures and pitfalls. This way, implementation will be smooth sailing.

Get in touch with us at DBA Services.