As a kid, I can remember always liking Dr. Seuss’ book Green Eggs and Ham. The basic storyline for those unfamiliar with the book, is “A character known as “Sam I Am” pesters an unnamed character to taste a bizarre-looking dish of green eggs and ham. He declines, claiming to dislike green eggs and ham. However, the persistent Sam I Am will not cease following him around and trying to encourage him to try the green eggs and ham, asking him if he will sample the dish if he does it in various locations (like a boat or a house) and with an assortment of dining partners (like a goat or a mouse). At the end, the unnamed character gives in to Sam I Am’s pestering and takes a bite of green eggs and ham, which he finds he does indeed like.” (Reference: http://en.wikipedia.org/wiki/Green_Eggs_and_Ham). Of course, this book is good to encourage kids to try different foods. But, the reason I have always liked it, was the underlying principle …
You really can’t say you don’t like something or that it will not work unless you try it.
All around us, there is no shortage of guidance or suggestions that you will hear from others, through a variety of formats and media. And as an IT professional, you have to be able to sift through what is useful and what is not. For example, in the past, I have worked with lots and lots of vendors. At one employer, they had over 60 different vendors with hundreds of pieces of software and databases. And almost every vendor always said they needed their own SQL server for their database(s), regardless of how big or small it was. Could you imagine the situation that would exist if we just blindly followed their recommendations? You could end up with some serious SQL Server Sprawl.
Why is SQL Server sprawl bad?
- Leads to a poor use of resources – Causes IT personnel to have added responsibilities in administering and maintaining servers. Also, it is common to see servers that are oversized and thus waste resources.
- Leads to a lack of best practices being implemented in the environment – Sometimes the servers are not implemented and maintained in the same manner, which could lead to security risks and performance problems.
- Leads to increased SQL Server licensing costs – Because SQL Server is licensed by CPU sockets (or cores for 2012), more servers will cost more money. (NOTE: An exception to this could be in how a virtual server is licensed.) (SQL Server Licensing: http://www.microsoft.com/sqlserver/en/us/get-sql-server/how-to-buy.aspx)
- Leads to increased time and expense in monitoring – Regardless of whether you monitor your SQL Servers in a manual or automatic fashion, having more servers will involve more time, effort, and possibly money (in the case of purchasing third party monitoring software).
So, since every DBA wants to avoid SQL Server sprawl, what questions should you ask a vendor (or anyone who needs a database)?
- Database(s) – How many databases are installed as part of this application? How big do you anticipate these database(s) getting (i.e. size)? What type of database activity and performance should be expected? What is the collation sequence specified on the database and tables? Can these databases be installed on a shared or enterprise server?
- Installation – How is the software delivered or installed – through database scripts or through database backups? Does the software require the “sa” account or administrator rights (sysadmin, serveradmin, securityadmin, etc.) in the installation procedure? Does the software include any extended stored procedures and thus require installing the DLLs for those procedures? Does the software require any custom services and/or middle-tier components (e.g. COM+) to be installed on a server?
- Security – Does the software require elevated rights to run? Does the software support trusted connections from domain logins or does it use SQL Server logins? How is the security of access to the data managed – through a software interface or through SQL server directly?
How those questions are answered should affect our decision on whether the database can go on a shared server. For instance, if the following is true, you may decide against putting the databases for that software on a shared server:
- Software creates an unspecified amount of databases dynamically
- Databases are in a different collation or have high resource requirements
- Additional non-SQL components need to be installed on the SQL server
- Software requires elevated privileges or changes to SQL Server settings
The goal to this is to avoid SQL Server sprawl or at least help you to determine when an application’s database really can’t co-exist with other databases. But, regardless of whether you are convinced one way or another … TRY and SEE!
Since I know everyone has a test environment that is similar to production … albeit maybe less powerful, testing should ALWAYS happen before releasing an application to production. Otherwise, you are making a recipe for disaster and just going to create problems for yourself.
In my experience, many times when you put the databases on a shared test server and test all the application out, everything runs fine. Now, sometimes you may have to grant extra permissions for the initial install, but in most cases they can be scaled down afterwards. But, in those cases where you can’t have these databases live together, there are options:
- Multiple instances of SQL Server on the same server – perhaps this will work for you if the issues are just with settings and security on that instance
- Server virtualization – possibly a better option if resources or database sprawl may be a concern, using a technology like VMWare
So, by having a good test environment and following the try and see method, you can avoid SQL Server sprawl.