When I install SQL Server 2008 Express in prompts me to create an instance and aborts if I don't. Then I see that information in an entry in Sql Server Configuration Manager on SQL Server Services. What is a SQL Server instance?
asked May 24, 2009 at 9:59
Add a comment
|
2 Answers
An SQL Server instance is a complete SQL server and you can install many instances on a machine but you can have only 1 default instance.
An SQL Server instance has its own copy of the server files, databases and security credentials.
This url may help you
-
-
1Yes, more than 1 instance can run at a time. You just need a unique name for each instance that you install (as well as filesystem path)– WayneMay 24, 2009 at 10:57
-
4You can even have one instance be SQL 2000 and another 2005; biggest gotcha with multiple instances is memory management, if you use the default settings, all instances will try to claim all memory, it's best to explicitly limit max memory at each instance.– SqlACIDMay 24, 2009 at 11:20
-
5SQL 2000 supports 16 instances. SQL 2005 and SQL 2008 support up to 50 instances per computer. You can run SQL 2000 and SQL 2005 on the same computer, or SQL 2005 and SQL 2008 on the same computer. However you can not run SQL 2000 and SQL 2008 on the same computer.– mrdennyMay 25, 2009 at 3:16
-
1Be aware that not all SQL Server services are "instance aware" or multi instance. Integration Services, for example, is only installed once per Windows host. These instances include Express instances that might be installed by applications. May 29, 2009 at 20:05
SQL SERVER INSTANCE An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. Each instance manages several system databases and one or more user databases. Each computer can run multiple instances of the Database Engine independently of other instances.
SQL Server is composed of three main parts: 1. An engine, which is the software started by a few Windows services that performs lookups, sorts, and other actions; 2. Meta-data such as the master and msdb system databases; 3. User databases where data is stored.
The master database contains the information that the engine reads when it starts up. It includes such things as security settings, file locations, sort orders, and database locations. The msdb database contains the information used by the SQL Server Agent program and information about maintenance plans. Yet another system database, called model, is the "template" from which other databases are created. Finally, the tempdb database is the "scratch" area that the engine software uses. This format holds true for all versions of SQL Server, although other control mechanisms are also implemented as Dynamic Link Libraries, or DLL’s.
This means that a single installation of SQL Server has only one set of certain data, such as server-level security credentials, scheduling information, temporary files and other meta-data.
Beginning with SQL Server 2000, you can run multiple copies of the software, using what Microsoft calls Instances. Instances share a few files between them, mostly dealing with client tools. This allows you to have two different system administrators (sa accounts) and other server-level security on the same hardware. So if you have different security needs, say running more than one company with different administrators, you can install multiple copies of SQL Server on the same hardware.
Another advantage is that since some of the files that run the Instance are duplicated, you can apply service packs separately to each Instance. That way you can host several applications on the same hardware that require different service pack levels.
Instances also allow you to work with multiple versions and even editions of the product. You can install Instances at any time, even after SQL Server is installed and running for some time. So for Instance (no pun intended), you can install SQL Server 2005 Express Edition, SQL Server 2005 Enterprise Edition, SQL Server 2008 Standard Edition and SQL Server 2008 R2 Developer Edition, all on the same hardware.
If a connection request specifies only the name of the computer only, then connection is made to the default instance. A named instance is one where you specify an instance name when installing the instance. A connection request must specify both the computer name and instance name in order to connect to the instance. The computer name and instance name are specified in the format computer_name\instance_name.
Instances apply primarily to the database engine and its supporting components, not to the client tools. When you install multiple instances, each instance gets a unique set of: 1. System and user databases. 2. The SQL Server and SQL Server Agent services. For default instances, the names of the services remain MSSQLServer and SQLServerAgent. For named instances, the names of the services are changed to MSSQL$instancename and SQLAgent$instancename, allowing them to be started and stopped independently of the other instances on the server. The database engines for the different instances are started and stopped using the associated SQL Server service. The SQL Server Agent services manage scheduled events for the associated instances of the database engine. 3. The registry keys associated with the database engine and the SQL Server and SQL Server Agent services separate versions and releases.. 4. Network connection addresses so that applications can connect to specific instances.
