At work our database server is humming along. It runs a little under 50% on the CPU, the RAM is a bit high, and the IO is very responsive. But, it could be better. About a quarter of the load on that server comes from one application. It’s a reporting solution built to handle end users looking for certain bits of information collected throughout the system. The problem is these reports sometimes cause blocking.
Nothing excessive, but as we grow, these blocks could grow worse. So time to snip that problem in the bud while we have the time to deal with it.
There are a few different methods we could use to deal with the problem. We could copy the databases used by those reports over to another server and update the reporting solution to go to that new machine. We could set up replication to send just the reporting information from our OLTP environment over to the reporting server. That would reduce all the reads to one SQL Agent that would read the data, copy it to reporting. Then all our users would hit that server.
But the odds that management would give us a new server are pretty low.
There is another solution. We could set up a second, named instance of SQL on the same server. Detach the reporting databases from the primary instance, attach them to the named instance, then update the reporting system to point to the named instance.
The upside of going this way is we’re ready to go when we do get a separate server. We can pick up the databases, move them to the new server, bring them online, update the connections, and boom. We’re on a new server.
Also, since the overall load on the sever will remain the same, there’s no extra load on the server.
The big win here? All those reads are on a separate instance, there’s no way they can cause contention with the OLTP database. Couple that with the fact no changes are made to the OLTP system…. BIG WIN.
Over the next few months, I’ll continue to monitor both systems, and make sure the performance stays the same, but with far fewer blocks. If so, this has been an awesome chance to use the named instance installer. I’ll cover how to set up a named instance in a later post!
If you have any questions about named instances, please… send them in! I’m here to help!