| Author |
 Topic  |
|
|
aed
Honorable But Hopeless Addict
USA
1213 Posts
Status: offline |
 Posted - 05/09/2012 : 09:52:54 AM
|
We are starting to have more products that are wanting to have SQL Server, full version not just the Express, installed. I'm curious as it if it's better to have one central SQL Server with multiple instances/db's on a active/passive cluster for ease of management, patching, and licensing? Or if licensing costs are not an issue, is it better to let each product have a local install and thus secure since it's talking within the box and would not have to be exposed on the network?
Side Note: Majority of servers run in a Virtual Environment and I work at a University so licensing costs are next to null.
|
|
|
wobble_wobble
Honorable But Hopeless Addict
Ireland
4516 Posts
Status: offline |
Posted - 05/09/2012 : 3:07:40 PM
|
Bigger well spec'd boxes are the better way to go, but if you have a lot of DB's to manage, then the opinion of a well versed DBA is the best place to go for the advice...
Now where is Stacy when you need him?
|
Joe
After everything that has happened during the month of Jan 07, I do believe that pigs fly backwards!
http://whatismyv6.com/ |
 |
|
|
NMDANGE
Honorable But Hopeless Addict
USA
2054 Posts
Status: offline |
Posted - 05/09/2012 : 6:01:07 PM
|
Performance/security wise, you should not collocate your application and database on the same server. In most cases, many SQL-based applications can share the same SQL cluster. However, there are always those few apps that need some global setting to be different, or the vender that requires only "their databases" run on the server for support.
I'd stick as many apps on a single instance physical SQL Server cluster as possible. For those that need their own, a small standalone VM. I prefer to stay away from multiple instances on the same server. |
Michael D'Angelo
(former)MVP-MIIS, Pace University Senior Systems Administrator (Windows)
(MS)NMDANGE
PhoeniX WorX Systems Administrator. If you play Total Annihilation, please join us. http://www.phoenixworx.org |
|
|
|
chamezzzz
Honorable But Hopeless Addict
United Kingdom
2297 Posts
Status: offline |
Posted - 05/09/2012 : 6:03:22 PM
|
It is acceptable to virtualise SQL Server. I am assuming you don't have particularly pressing loads as you have been getting away with the express version until now.
What do you use for your hyper-visor?
VMWare KB here
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1037959
Hyper-V - nice post here from Mohamed M Malek
http://blogs.technet.com/b/meamcs/archive/2012/04/10/creating-an-active-active-sql-cluster-using-hyper-v-part1-virtualized-storage.aspx
Cluster is good because you can patch and remain up.
It's a challenge because it adds a lot more complexity. Set up/ Configuration/ Documentation and so on.
Regardless make the right decision 1st time if possible by testing a few labs.
Nothing worse than having to re-configure a SQL Server and it's live databases.
If you can do it and have the time, I would go for the cluster. Good learning experience.
Or even better, set up a standalone SQL and put your systems live.
Then build a cluster as a separate project and once happy move your databases from the stand-alone environment to the cluster.
Lots of learning and experience guaranteed!
EDIT: - as per Michael's post - stay away from multiple instances, nothing worse than the bespoke application that does not work on a named instance. They introduce more hassle, more data paths on the file structure. It can cause confusion.
Also, as per Michael keep your applications on separate servers. Sound advice again from Michael. Keep SQL for DB's only if possible. |
James |
Edited by - chamezzzz on 05/09/2012 6:07:00 PM |
|
|
|
aed
Honorable But Hopeless Addict
USA
1213 Posts
Status: offline |
Posted - 05/10/2012 : 09:33:49 AM
|
Thanks to everyone for their advice. We have both hyper-visors and our workload isn't much. Perhaps that will change but at least I know where to start. Also, thanks on whether to go with multiple instances or a single instance.
Now, I need to brush up on my accidental dba certification.
Edit: Will have to figure out do I failover the whole server, or just the SQL Instance? Or is the Instance Failover only available in SQL 2012? |
Edited by - aed on 05/10/2012 10:07:31 AM |
|
|
|
chamezzzz
Honorable But Hopeless Addict
United Kingdom
2297 Posts
Status: offline |
Posted - 05/11/2012 : 03:01:01 AM
|
Yes, go with SQL 2012 if you can.
I have not used it myself, but the introduction of AlwaysOn Availability groups in SQL 2012 adds another level of data protection.
Its very powerful, I am just reading about it now.
Trying to get my head around it!
For a small shop it looks like overkill but the Datacentre/Big Data guys, it has lots of functionality that looks really powerful.
More here
http://msdn.microsoft.com/en-us/library/hh510230 |
James |
|
|
|
aed
Honorable But Hopeless Addict
USA
1213 Posts
Status: offline |
Posted - 05/11/2012 : 11:27:44 AM
|
| I agree but but the problem with SQL 2012 is that it isn't supported by very many applications, yet. |
|
|
|
chamezzzz
Honorable But Hopeless Addict
United Kingdom
2297 Posts
Status: offline |
Posted - 05/11/2012 : 3:18:41 PM
|
Yes but if memory serves, I have had the same with most upgrades and have been winging it with SQL Server since 2000 release.
A lot of apps connect using the hostname and port 1433.
SQL 2005 broke a lot of stuff by locking everything down by default.
But in my *limited* experience applications, they rarely check the SQL version, the app server communicates to the db using 1433.(often more - this is a topic for another time, the hours of my life wasted because someone has changed the port - or more ports are required and not documented anywhere)
Again, you have the licenses and the infrastructure to do it, go for it.
You could always build an 2008 SQL Server if needed. |
James |
|
|
|
Curt
Moderator
USA
6648 Posts
Status: offline |
Posted - 05/16/2012 : 4:43:43 PM
|
Jerrod, find out what the apps do.
SQL database are NOT water heaters or gas tanks.
Databases often have complex stored proceedures, triggers, special indexing that requires lots attention.
Virtual SQL servers are good but they do have their issues. We discussed drivers at the Minasi meeting with our VMware speaker Scott.
Each type of virtual server shares memory with the host in different ways. Also have to consider the type of application.
For instance, I would never put a Financial application database on a server that has a database where there is a database for an ecommerce site. Not Smart.
Share Point REALLY NEEDS resouces for it's databases and there is loads of indexing.
Also some apps may not use the same data formating.
Managing the databases can also be a problem with people from all over are using the services. 2012 is good but............ cost a lot of coin. Try the strategy of SQL 2008 first. figure out the maintenance windows and see if standard will do for light stuff.
SQL is a great adventure. I consider the databases like Hard disk.
Would you put all your apps on one array of disk?
|
Curt Spanburgh
Microsoft Certified Business Solution Specialist.
Dynamics CRM MVP
Contributing Editor, Windows IT Pro
He that is walking with wise persons will become wise, but he that is having dealings with the stupid ones will fare badly.
Proverbs 13:20
|
|
|
| |
Topic |
|
One server or Multiple
