Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

For the past several years, I have been running my SQL Server 2008 databases on the same machine as my IIS installation. I've now been convinced to separate them, and will have a dedicated SQL box.

(1) I am planning to copy the databases over using the "Copy Database..." function on the SSMS context menu. I assume that this is the best method?

(2) Once I have the new box set up, what is the best method (in terms of both security and speed) for having my IIS applications on my old box connect to the new server? i.e., what should my connection strings look like? how should I authenticate the web users on my IIS box to access the new SQL server? using Windows or SQL authentication?

(3) Are there any important configuration options that I need to set on my new server to optimize the performance of SQL Server 2008, now that it will be running on a dedicated box?

Any advice would be appreciated.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Transferring the database is straight forward. I believe there a few wizards built in to facilitate this.

Windows authentication is the recommended approach. Also, here is a link to some info regarding security.

Once you have created/altered the anonymous user account that the web users will access the database by, you should only have to change the server name in your connection string to the new server name.
Here is a good tool for connection strings.

And this link has some information on optimization.

share|improve this answer

The database transfer is a piece of cake. Back up the databases and move them manually or you can use a utility for transfer.

IIS and database authentication is something else. Security is something that gives me the heartburn. Microsoft always recommends using activie directory for access to the system. I don't like to use users that are controlled by the AD admin for access to the database. I prefer the DBA to control security on the system and cut out AD group entirely. I like to create a user in the database and have the connection login using sql server authentication.

For IIS, this I DO like them have the Active directory Admin team control access. This is their domain and they should be managing this type of security. Use of integrated security is best when you can pull it off. The best thing to do is to have a SSL site. It reduces the chances of any one from sniffing your connections and stealing login info.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.