0

Ok, my Hyper-V Server 2012 looks like this: we currently have 2 VM's in production, one is running SharePoint 2010, and the other is hosting the SQL Server 2008 R2 for SharePoint.

We're getting ready to add another VM for hosting a Ecommerce Web Store for our website. I found one that's open-source and uses MSSQL Server (nopCommerce). My question is: what is best practice for SQL server role here? Should I use our existing SQL Server (currently hosting SharePoint) to host both databases side-by-side? Or should I install a local instance of SQL Server on the WebStore VM, so I end up with 2 VMs dedicated for SharePoint and 1 VM dedicated for Web Store? Or should I even create another SQL Server VM for the Web Store, so I have 2 VMs for SharePoint and 2 VMs for Web Store? (This one seems resource-expensive to me.)

And we have additional consideration: Our Inventory tracking system is currently being hosted as a Access Web-App DB on SharePoint. We want our Inventory data to populate the Web Store. From my Googling around, it seems like linking together SQL databases isn't too big of a deal and happens quite a bit. But I'm not familiar with SQL Best Practices yet, I don't know if that's more of a case for keeping the databases on the same server or not.

HopelessN00b
  • 53,385
  • 32
  • 133
  • 208
IT Bear
  • 129
  • 6

1 Answers1

2

There is no right answer to this, there is only what's right for you, which is not something any of us can really help you with without knowing intimate details about your scenario.

All databases on a single, large server can reduce the management overhead, can make backups simpler, and you can upgrade the SQL server independently of the rest of the servers. All your IOPS are in the one place, so you can upgrade a single server to improve many different applications.

However, having multiple large applications on a single server can be problematic, because if a single database outgrows a single server (such as a large Sharepoint installation) then your other databases can get starved for resources, and you then have to work at splitting them off.

Keeping your application servers and SQL servers together can simply configuration, and you have fewer points of failure to worry about. If you want to increase your ecommerce store's performance if it gets very busy, but are unconcerned about your internal sharepoint, then you can just increase the single application. Disaster recovery/High Availability is simpler with a fully-self contained virtual machine (assuming these machines are virtual).

SQL Server licensing is done by-core these days, and I believe you can have multiple instances per core, so if they all live on the same server, there is probably nothing to save in licensing costs.

We have a mix of both - certain things are all contained in a single virtual machine, but larger things have seperate, dedicated SQL Servers, and yet other smaller things have seperate, shared SQL Servers.

It all depends on what you want to manage.

Mark Henderson
  • 68,316
  • 31
  • 175
  • 255
  • I can't really improve upon Mark's answer except to say that many, many, many businesses end up putting all of their SQL Server db's on one server. However, keeping in mind that this ends up being a single point of failure and if that goes against your business needs, don't do it. There are quite a bit of what-if's and you've not given us very much to go on. – GregD Jul 29 '13 at 21:41
  • And to give you another point of reference, when you obtain an MSSQL database from a hosting provider, it usually exists on one server with a thousand others.. – GregD Jul 29 '13 at 21:43
  • Ah, this was all the information I needed! The Ecommerce VM in question is just going to be a dev server (VM) for now, to give me a chance to play around with it and see if it fits our needs. I think in that case I'll just keep the SQL Server portion on the same VM for now, and possibly put into production that way (we have NO e-commerce solution at all right now, our SharePoint is not growing very quickly, and I have no way of telling how quickly the ecommerce will grow.) Thank you guys for sharing! – IT Bear Jul 29 '13 at 22:42