• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

What's the best way to deploy multiple SQL Server solutions

Hi guys, I'm getting ready to engage 3 to 4 projects that require MS SQL. Across all of the applications and projects the recommendation is to house the SQL server on its own machine and the actual application on another. I have a few questions about this.

Is the best way to have the app on one server and the SQL server on another machine?
Can I have one SQL server for all my applications (i.e all my applications connect to the same server)?
Is it better to have a separate SQL machine for each of my applications i'm launching?
Is it ok to run my SQL server off of virtual server? If so, what are the recommendations?

You're probably wondering what the applications are. But you probably wouldn't know if I told you. These are just 3rd party software vendors that use SQL. Ones a payroll solution out of Saudi Arabia. Another is some warehouse software. Barring their own idiosyncrasies, they all use a standard approach to using SQL server.
0
js_sts
Asked:
js_sts
4 Solutions
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
here's a good whitepaper on using swl server and virtual machines:

http://www.vmware.com/files/pdf/solutions/sql_server_virtual_bp.pdf

a lot of what you ask depends on the specs of the machine and what you expect your site traffic to be like, as well as what resources you have. If you're only expecting a few hundred visitors each day, then you can get away with having everything on one machine. If you're expecting millions of users though, you'd probably want to have separate servers. You also need to take into account what kind of backup plan you want. do you want to just take backups every night or do you want a secondary server to come online if the primary goes down?
0
 
Brian CroweCommented:
As The_Big_Daddy mentions, how much you can group your applications/Databases is largely dependent on how busy they will be, but yes it is preferable to put your applications(s) on a separate server from your SQL Server instance(s).  SQL Server doesn't play well with others as a general rule.

SQL Server works just fine in a virtualized environment so I wouldn't have any reservations on that front and the ability to add resources as the need arises is a nice bonus.
0
 
ZberteocCommented:
And yes, generally you should use on server for 3 or even more applications. SQL server is powerful enough to deal with multiple databases and applications unless one or some of them have unusually high number of transactions, which I doubt. Another aspect is that SQL server is pretty expensive to afford the luxury to have one for each of your apps.
0
 
Scott PletcherSenior DBACommented:
? app on one server and the SQL server on another?
    Yes

? one SQL server [shared] for all my apps ?
    Yes; SQL costs alone generally make this much more preferable.  If you need to, "beef up" the RAM and other hardware on the server to accommodate the load.

? better to have a separate SQL machine for each of my apps ?
    No, just too expensive.

? ok to run SQL off of virtual server ?
    I've never found that to work all that well, due to performance loss, but BriCrowe has obviously had different results.
0
 
js_stsAuthor Commented:
thanks for input guys. I think all of you have shed light on this. I think I will just go with two severs, one for SQL and one for my app. I will probably virtualize my apps. I think I can get away with virtualizing SQL but my gut says to not vituralize sQL. I will probably get a backup server for my SQL server and have two 1U SQL servers.

I feel much better now. Thanks guys!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now