Need Advice on setup of 2 SQL-based applications

[circulent]

We are currently using an application which uses SQL Server 2005. Both sit on the same box.

This month we're adding another piece of software to our network on a new box which also depends on SQL Server. Since we don't have a directed attached storage device to store (as recommended by Microsoft due to I/O issues), we're contemplating installing SQL Server again just for this application. Of course this means that we have to maintain two separate SQL Server databases.

Any thoughts on what we should do?

Thanks

 

[circulent]

One more thing...If we used a DAS, would SQL still need to be installed on the second box? I figure we would just place the database files on the DAS. But then how would the actual database be shared?

Thanks

 

[mrdenny]

If the IO requirements and CPU require that you have two SQL Servers then you need two SQL servers. There really isn't much you can do about that. Have more than one SQL Server at a company is fairly normal.

All a DAS is, is storage. What ever server it's attached to will need to have SQL Server installed on it.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[circulent]

Ideally I'd like to have both applications talk to one database server, but am not sure what my options are in setting it up.

Thoughts?

 

[ptheriault]

You'll find that in most cases it might be better to have more than one SQL Server for larger applications. What happens when you have to shut down one SQL server for maintance or upgrade? In your case you would have to stop all applications using that server. (can you afford to impact that many users?) You will also need to check the requirements for each application. Can your server handle the added I/O load?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[circulent]

Hey Paul,

Good point with having the shut one SQL server down.

How can I determine if my serevr can handle the added I/O?

Thanks

 

[ptheriault]

You will need to get the hardware requirements for each application. If you have one SQL server what will be the total number of users for both applications? Will that surpas the requirements for one application? Check your current I/O load on the server. What is the current memory and disk utilization? There are all questions you'll need to answer to determine if you server can handle the added load.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[circulent]

OK cool. Now here's another question:

If I decide to stick it out not and not buy a dedicated SQL server (meaning there will be two separate), how difficult will it be to merge both into one later on?

Thanks

 

[ptheriault]

It is very easy to move databases and users from one server to another. I'm not sure about your application though. You would have to change the connection string. How does it connect to the server. Is there some config file? ODBC?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[circulent]

The app that's already up is Microsoft Dynamics CRM. We're adding Microsoft Dynamics GP to the other.

As for CRM, is an ODBC connection required if the database is on the same box? I wouldn;t think so, but please correct me if I'm wrong.

ML

 

[ptheriault]

I have seen some apps use ODBC for a connection on the same server. Like remedy's helpdesk application. anyway, the hardest part of moving your database to another server could be setting up the application connection.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[circulent]

Paul,

Another question: If we decide to source a dedicated db server, what challenges are there with adding two instances for each app, but with one common master db?

Thanks

 

[ptheriault]

There are some security concerns with installing two instances on the same the server as well as I/O.
1. Do you leave built-in admin in your SQL instance? (you shouldn't but some companies do.) This would allow local admins on the server 'sa' access to both instances.
2. Who would need local admin access to the server for both instances for each application. Do they conflict?
3. If you lost the server you loose both apps.
4. Make sure the server can handle both instances.

By installing two instances on one server you would be able to stop one with affecting the other.

If you really wanted to put two instances on one server an these are important enterprise applications you should consider making your db server a cluster. You don't want to put all your "eggs in one basket"

- Paul
- If at first you don't succeed, find out if the loser gets anything.