February 7, 2011 at 8:11 pm
Hi,
We are planning on implementing a Shared SQL Server in our organization. We have three different reporting applications (Cognos reportnet, upfront and planning), SharePoint server, one ERP system and 2-3 different types of windows and web applications. Right now all of them, each one has its dedicated SQL servers but our new plan is to having one common SQL server for all of them. We will be getting very high configurated Server and yet to decide on which version it will be.
Please help me how feasible this would be and any suggestions towards implementing it.
What type of performance issues that can occur with having the same backend for all different applications. Almost all of the above mentioned apps are in high demand on daily basis so clearly traffic will be heavy. For the situation like this what are the solutions that you may suggest.
Thanks in advance !
@Moderator - Please feel free to move it to the appropriate forum if you think it doesn't fit here.
February 7, 2011 at 8:42 pm
There will be a heavy load on SQL Server as all the application will access it. What is the current SQL Server configuration ? What configuration you are planning for ?
For each application and reports how many users access SQL Server , what is CPU load and Memory usage. I think it could be better fist to analyze all this for each application and the we can decide if by making Shard SQL Server then what type of load it will have what will be the physical resource config.
"More Green More Oxygen !! Plant a tree today"
February 8, 2011 at 3:25 am
The common problem on shared server are other applications eating all the CPU or the disks.
You need to monitor your current sql server and keep the data for reporting for several days (even month) to get a good view of the CPU, Disk and memory usage.
If none of your current server is using a lot of CPU or Disk then you can consolidate without too much problem.
The ones using a lot of CPU are most likely better on their own server, even if the consolidated server has more CPU power (the application is either badly written, doesn't scale well or the server is too small; either way this is a bad candidate for consolidation).
The ones using a lot of disks should problably get their own LUNs (Data and Logs) on the consolidated server.
If it's the tempDB disk being hit hard you might want to consider either leaving it on it's own server or putting it on the consolidated server but on a different instance (so it get it's own tempDB on it's own disks).
February 8, 2011 at 7:29 am
Are the collations the same on all of the sql databases? If not, you will have trouble with things such as temporary tables, which use the collation from the tempdb.
February 8, 2011 at 7:42 am
Another thing to consider is maintenance windows for the different applications for things like OS patching, etc.
Critical applications can have mutually exclusive maintenance windows so there is no good time to schedule this activity.
February 8, 2011 at 10:39 am
Patching is a big concern, especially if you use things like SSIS differently between them.
The load, as mentioned, is something I'd be careful of, and I'd examine the memory usage carefully for all of the instances first. You'll be sharing tempdb and sharing a buffer pool, and if you don't have enough hardware to support the multiple instances, I'd be careful.
In the past, I have typically consolidated onto one instance only when I find a number of separate instances that are not heavily used. If they are heavily used, then they can sometimes interfere with each other.
One thing that I would also have ready is a plan to move an application back off the instance if you have issues. To that point, I might have people connect not to the new instance name, but an alias that can be easily moved if you need to migrate an instance back off this shared server.
February 8, 2011 at 11:20 am
Steve Jones - SSC Editor (2/8/2011)
Patching is a big concern, especially if you use things like SSIS differently between them.The load, as mentioned, is something I'd be careful of, and I'd examine the memory usage carefully for all of the instances first. You'll be sharing tempdb and sharing a buffer pool, and if you don't have enough hardware to support the multiple instances, I'd be careful.
In the past, I have typically consolidated onto one instance only when I find a number of separate instances that are not heavily used. If they are heavily used, then they can sometimes interfere with each other.
One thing that I would also have ready is a plan to move an application back off the instance if you have issues. To that point, I might have people connect not to the new instance name, but an alias that can be easily moved if you need to migrate an instance back off this shared server.
Steve, how do you do that switch?
February 8, 2011 at 11:27 am
Do you mean the alias?
I tend to use FQDNs rather than client side aliases. So if I had cognos, Sharepoint, and ERP applications, I'd setup.
- Cognos.mycompany.com
- Sharepoint.mycompany.com
- ERP.mycompany.com
using my own domain instead of mycompany.com. It's perfectly valid to use either an internal DNS server, or even the external one on the Internet, putting in local addresses for those servers. Cognos.mycompany.com could point to 192.168.12.50, a private address.
If I had ports, I'd include those locally.
Then if I move to .50, and the load is too high, I can redirect cognos.mycompany.com to a new instance on another server, 192.168.12.51 instead, and the clients could flush their DNS cache and connect to the new server without an issue.
February 8, 2011 at 11:52 am
Steve Jones - SSC Editor (2/8/2011)
Do you mean the alias?I tend to use FQDNs rather than client side aliases. So if I had cognos, Sharepoint, and ERP applications, I'd setup.
- Cognos.mycompany.com
- Sharepoint.mycompany.com
- ERP.mycompany.com
using my own domain instead of mycompany.com. It's perfectly valid to use either an internal DNS server, or even the external one on the Internet, putting in local addresses for those servers. Cognos.mycompany.com could point to 192.168.12.50, a private address.
If I had ports, I'd include those locally.
Then if I move to .50, and the load is too high, I can redirect cognos.mycompany.com to a new instance on another server, 192.168.12.51 instead, and the clients could flush their DNS cache and connect to the new server without an issue.
Yup, so you have to go to all the clients pcs and flush some cash? That seems hard to implement. I always figured there HAS to be a way to flush 1 router's cache and boom it's done.
And how do you keep the 2nd server perfectly in sync so that you have seconds swith over to the old server?
February 8, 2011 at 11:56 am
Thanks to all for the replies.
@Minaz amin - Like I had mentioned above, each application has its own Sql server so the current configuration of each SQL server is differing from others. Some are still on SQL Server2000 and some are on SQL Server 2005. For new Shared SQL Server which configuration will it be is still on plan. Thanks for the given factors and will start analyzing those for each application.
@Oliiii - I will start analyzing these factors.
so far we are manually analyzing these factors using SQL profiler, do you suggest any other tool to perform it automatically and get me the reports on daily basis?
@david-2 - yes the collations are the same on all of the sql databases. so once we go for shared sql server planning on it so that it will still be the same on the same shared server
@michael-2 - thanks for rising the topic on maintenance windows. will look further in that direction too.
February 8, 2011 at 12:00 pm
Well now just to add to that list, you need to make sure the sql 7,8,9 dbs will work on 2008 R2 (assuming you buy the latest version of sql as well).
So you're not only talking about moving the DB, but making sure you can upgrade as well (microsoft upgrade advisor and best practices advisor would help you here).
Or you might consider having 2-3 sql servers online to avoid any upgrades and all the hassle that comes with it.
February 8, 2011 at 12:10 pm
@steve-2 - Thanks for ur reply as well.
The main base line for this plan is cost cutting. Instead of paying for 6 different sql licenses, thinking of spending the whole on getting the hardware and use just one sql license, so ready to get the hardware whatever it takes. But interms of application usage all of them are heavily in use daily from 5 different branches of the company.
For backup in case of any issues the plan is to implement a good disaster recovery software and have active and passive servers in synch at all the time.
I some how missed your explanation on client side aliases, can please throw some more light on it. Thanks.
February 8, 2011 at 12:14 pm
Ninja's_RGR'us (2/8/2011)
Yup, so you have to go to all the clients pcs and flush some cash? That seems hard to implement. I always figured there HAS to be a way to flush 1 router's cache and boom it's done.And how do you keep the 2nd server perfectly in sync so that you have seconds swith over to the old server?
You can send a batch to every client. There DNS cache is local, and while you can lower TTL, I don't set it for DR situations.
Note that this isn't DR. If I need to migrate the database to a new server, there's downtime and TTL might be exceeded anyway. Even in DR, I've usually come up for admins only so that we can verify things before we turn users live. This isn't designed for sub-sec switches. None of the SQL HA technologies are there for sub second moves.
February 8, 2011 at 3:05 pm
Svs2010 (2/8/2011)
@Ninja's- Yes we are thinking of getting the latest version only and have to make sure all other SQL servers can be upgraded and the applications supports them as well. so whole lot of work on each of the application.
You have no idea. Even moving only 1 application is a tone of work. Moving many PLUS upgrades is a monster.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply