April 18, 2014 at 3:39 am
I am a new DBA working for a charity and I have project to consolidate the multiple databases scattered across the organization.
I have identified the main databases that are mission critical to the organisation. We also have a virtualised infrastructure so plenty of VM's. We currently run SQL Server 2008R2.
Fundraising Database (off the shelf package - 30GB in size with about 100 users)
Fundraising Data warehouse (off the shelf package - 30GB in size with about 5 users)
SQL Reporting Services Database (1GB - reports used across the whole organisation so about 400 users)
Case Load Services Database (Developed in house - 4GB in size with about 300 users)
Finance Database (off the shelf package - 2GB in size with about 150 users)
GFI Exchange archive databases - (off the shelf database - 30GB in size with about 300 users)
Then we have about 10 other smaller databases for system monitoring etc... that are important but not mission critical.
Now the question I have is (bearing in mind limited budget and resources) is whether I should put all of these databases on 1 single instance on 1 single Windows Server 2008 VM. Or would it be better to have them on a single box with separate named instances? Or possibly even separate VM Servers splitting out certain databases each?
My thoughts was to have 2 VM's. SQL01 for the missions critical databases. SQL02 for the other databases that are non mission critical. I was even toying with the idea of separate named instances on SQL01 for security and the ability to manage services separately without affecting other databases? For example if we need to restart a service during working hours we would not have to take out the whole organisation but only users on 1 database.
I know there are many factors that affect this decision but from your experiences and knowledge what is the general rule of thumb when redesigning the architecture for a SQL database landscape like we have? Obviously each of the system requirements of the off the self packages say a single database ideally not contending with other database but I take it that its not really what happens in the real world as licensing SQL and hardware resources would be significantly wasted that way.
April 18, 2014 at 5:03 am
Since you're working for a non-profit, I'd probably concentrate on keeping licensing costs down, so, as few servers as possible. While you can split your databases across multiple instances, you then have a lot of overhead for each instance and you'll be managing memory in ways that are, frankly, a pain. I'm pretty sure I'd go with two servers as you already have. Some of the second-tier systems on one and the more important systems on the other. These are all very small databases, so I'd probably keep them all in a single instance. The one real trick you can do is to get more drives and drive controllers involved in this to spread that load as much as possible. Also, 64-bit so you can add memory as needed (and the correct amount of memory is either "All of it" or "More").
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 18, 2014 at 6:15 am
I mostly agree with Grant, but I would put the Reporting database on the same instance as the non-mission critical (and rarely used) databases. That way any reporting doesn't interfere with the normal transactional processing needs (since it can sometimes slow down production if too many reports hit the server at the same time).
April 18, 2014 at 6:39 am
Yeah, Brandie has a good point. Reports can usually afford to wait a bit. Active OLTP systems can't.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 24, 2014 at 3:36 am
Thank you all for your input onto this.
So is it quite common to put missions critical databases all into one instance? I would want to ensure that the model we come up with is sustainable over the next few years and that even as a charity we invest into building a solid platform for our databases. I don't see us increasing the number of database by many or their sizes to increase dramatically but for me the important thing is to ensure availability and performance is kept to a high standard.
April 24, 2014 at 4:20 am
Frequently, yes. Because you're going to lavish attention on that instance, know when it's running, possibly set up some type of high availability for it, etc. Yes, if you have money to spend, separating them out to more than one server is a good thing. But managing multiple instances on a single server is frequently more trouble than it's worth.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 24, 2014 at 5:13 am
If you have the money to invest in high availability options, you should. Clustered servers, mirrored instances, etc. are all good options for mission critical databases. And, as Grant said, it's more of a headache to have those databases sorted out into different instances or different servers. On the other hand, if you can afford to have multiple servers to spread out the load, it allows for a certain degree of CYA because losing one server means you won't lose all of your mission critical databases at the same time.
But it sounds like you don't have the money or the employee bandwidth to handle something like that. So what you need to do is group them together for easier maintenance and make sure you have a kick-butt disaster recovery plan to go with it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply