November 3, 2009 at 1:47 pm
I’ve been asked to add a second SQL instance to a production SQL server. I have never had to add a second instance to a SQL server before. My understanding is that I would re-run the SQL 2005 install CD and when prompted enter a NEW instance name, the user(s) start Management Studio and connect to that instance.
The server is running SQL 2005 Standard Edt SP2.
These are my questions/concerns:
1.During the install will the default SQL services restart?
a.Or will there be a second set of services for the new instance?
2.Where will this new instance and Data Files reside?
a.Will I have a chance to point it some place else (another server)?
b.Where will the install files go?
c.Can this second instance reside on the same server?
Or will I have to have the data files on another server?
****************************************************************************
OK.
So I ran my own test to see what would happen when I attempted to add a second instance to a machine already running SQL 2005 Standard. I successfully installed/added a second instance to the server without any errors. I noticed the installation added its own set of SQL services and when I pointed the install to a new directory it added the necessary folder structure.
Could some one tell me if these are the correct generic steps to add another instance to a SQL server? Also what affect does running the install have on the Default Instance?
1. Accept the License Agreement.
a. Install checks for previous components.
2. Install Wizzard Starts, when ready click Next.
3. The System Configuration Checker runs, when ready click Next.
4. Registration Name appears with the same information as the last install, when ready I click Next.
a. should this be altered or left alone?
5. I ONLY select “SQL Server Database Services”, when ready I click Next.
a. Click the Advanced button then select my new location for the new instance.
6. Select “Named Instance” and give it a name, when ready I click Next.
7. Use my domain account, when ready I click Next.
8. Select “Mixed Mode” and enter a NEW sa password for my new instance, when ready I click Next.
9. Select the appropriate Collation, when ready I click Next.
10. When setup completes click Finish, when ready I click Next.
11. Once again click Finish to complete the install.
November 3, 2009 at 2:07 pm
The Steps mentioned looks to be fine, But the question about adding another Instance on a Production Server needs some clarity, You must be aware that the Performance would be hit if you add multiple Instances on a single Machine and if that is not a concern, then you may follow the steps to install as you have mentioned, looks to be fine with the steps you mentioned.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 3, 2009 at 3:41 pm
shusta (11/3/2009)
These are my questions/concerns:1.During the install will the default SQL services restart?
a.Or will there be a second set of services for the new instance?
2.Where will this new instance and Data Files reside?
a.Will I have a chance to point it some place else (another server)?
b.Where will the install files go?
c.Can this second instance reside on the same server?
Or will I have to have the data files on another server?
1. Probably not, but I wouldn't do it during the normal operational window of the other server anyway.
1a. Yes, you will get several new services at least 1 SQL Server and 1 SQL Agent, probably some others depending on your install, you will only have 1 SSIS install and 1 browser install though.
2. You will specify a partial location during install.
a. local, not another server.
b. many of them are already there.
c. It will be on the same server.
Now, my 2 cents, absolutely do NOT do this for the very first time on your live production machine. Find a desktop or build a quick vm to try this, some of the install questions are not obvious and their effects should be know before you do it live. I guarantee you will be much more confident doing this just once when you go to do it live, I know I was..
CEWII
November 4, 2009 at 12:33 am
shusta (11/3/2009)
I8. Select “Mixed Mode” and enter a NEW sa password for my new instance, when ready I click Next.
Mixed mode?? Not a best practice. That is the only concern I see.
November 4, 2009 at 1:48 am
you need to ensure that sql server services start on domain service account rather than local system
November 4, 2009 at 2:05 am
Krishna -878600 (11/4/2009)
shusta (11/3/2009)
I8. Select “Mixed Mode” and enter a NEW sa password for my new instance, when ready I click Next.
Mixed mode?? Not a best practice. That is the only concern I see.
It may not be a best practice but I can only think of a handful of servers that I have experienced in 15+ years that did need to be setup that way.. Best practice always makes way for real world..
CEWII
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply