April 9, 2009 at 2:37 am
Can anyone tell me what does instance mean in SQL server. Is it databases or different version of SQL server. What does instance do in SQL server?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 9, 2009 at 2:49 am
An instance is an installation of SQL server.
you can have many instances on one server.
Each instance will have a set of system databases and it's own configuration.
Each instance can have many user databases
Each instance will have it's own security
an instance will be represented or identified by the servername and an instance name in the format
server\instance
for example
myserver\performancetest
(with one exception- the default instance is known just by the servername)
MVDBA
April 9, 2009 at 2:51 am
A single computer(server) can have many sql server installations.Each computer can have 16 instances.(50 in 2008).One will be the default instance and the rest are named instances.
So instance is not the version .It is the sql server to which you connect.Each instance will have a different instance name to which you connect.They may be of same version or different.
Ex: your computer name is ABC001
The default instance will be ABC001
The named instance will be ABC001\instance1
Iam just throwing light on these....there's a lot still.
April 9, 2009 at 3:44 am
Hi Michel..Thanks for your information.... I want to know more about it. Say, I have one application which is using SQL server and it has multiple instances. Can the application use multiple instances? How can I check how many instances are running in my SQL server? Are the reporting services,analysis services and other services different from each other for two different instances?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 9, 2009 at 4:01 am
sumanta.roy52 (4/9/2009)
Hi Michel..Thanks for your information.... I want to know more about it. Say, I have one application which is using SQL server and it has multiple instances. Can the application use multiple instances? How can I check how many instances are running in my SQL server? Are the reporting services,analysis services and other services different from each other for two different instances?
In theory your application can connect to as many instances as it wants . All you have to do is change the connection string in your application to point at a differrent instance
you can check how many instances are running by looking at the services screen on your server (services.msc) and looking at the SQL services running
your default instance will have a service called MSSQLSERVER and all of your named instances will have the Service MSSQLSERVER$INSTANCENAME
each instance of SQL server will have it's own SQLAGENT service (scheduled jobs) and any reporting services and analysis services will also have seperate services
MVDBA
April 9, 2009 at 5:10 am
Thank you for the information..
I have some doubt...
Can I have two instance with same configuration and can I use them in clustering? Will I get option while opening SQL server management studio for running one of the instance?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 9, 2009 at 5:41 am
I have installed SQL sever 2005.Could you pleae tell me how to create named instances?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 9, 2009 at 5:44 am
Clustering is a whole technology and no, to my knowledge, you can't cluster two instances on the same server. Clustering is a mechanism for having more than one server available as a means for up time and availability. Putting the cluster on a single server would completely defeat the purpose and I suspect you couldn't do it anyway.
A single application usually will only connect to a single instance of SQL Server. More often than not you have more than one instance to support more than one application. If you really need two data stores for a single application you can create more than one database on an instance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2009 at 5:52 am
Thanks for the information....
In one instance we can have many user database which can be used by different application. Then why do we need different instances.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
April 9, 2009 at 5:58 am
You use a second instance in order to manage different security for a system, to isolate an application from others, to have a SQL Server with different configurations, but still minimize the number of physical boxes used... there are tons of reasons. You can limit the amount of resources each instance uses so that they're not stepping on each other, or you can increase the resources for a really important instance and decrease them for a lesser instance. Also, you pay Microsoft licenses per physical machine, not per instance. There are lots of reasons beyond the few I've listed here. Also, don't get suckered in by the language we've been using. You can have a single instance with lots of databases for lots of applications. It's a management tool in the tool box.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply