Posted by arlene

The introduction of SQL Server 2000 enables us to have multiple instances of SQL Server running on a single machine. In the past we only had one instance of SQL Server, which in SQL Server 2000 is called the Default instance. Now we can have as many named instances as we want. Microsoft has tested up to 15 named instances and one default instance! I am sure that is more than enough for most peoples’ needs.

So why multi-instance support? In the past when we had very small client databases with separate security settings we were required to have several servers so that we could configure each one differently. This became a very expensive overhead for application providers.

With multi-instance support, each instance of SQL Server 2000 is totally separate, so it is like having multiple servers. They are totally configurable without causing the changes implemented on one to affect another.

Each instance shares only two things, the client tools and Microsoft Data Access Components (MDAC). These include Query Analyzer, Enterprise Manager, and Profiler. Be a little careful if you upgrade MDAC because it will affect each instance on the server.

How did Microsoft achieve this new miracle? In the Registry, SQL Server 2000 will create a new entry for each instance, and on the local file system everything will be installed under the folder name MSSQL$InstanceName. The dollar sign ($) allows us to have separate folders for each instance.

SQL Server 2000 normally uses as much memory as it can, but if another application wants some, SQL Server 2000 will release the amount of memory that the next process needs.

If you have multiple instances of SQL Server 2000 installed, each instance will try to take as much memory as it can. When the first instance releases memory to the next application (in this case SQL Server 2000), it will not realize that it is another copy of SQL Server 2000 requesting the memory. Currently there is no sharing of memory space between SQL Server 2000 instances.

Finally, because it can be resource intensive, Microsoft does not recommend installing too many instances of SQL Server 2000 on one machine. In fact, if you have very high performance transactional databases, keep them on separate servers. The resources will not be shared, thus improving performance.

This feature was really only introduced to cater to developers moving an application to testing, or a smaller database server being freed up to allow a bigger server to be moved into its own environment.

You have to admit; this is a pretty cool new feature, especially when we are developing applications that require different settings on each server.

Using Multiple Collation

In the past with SQL Server, once we had set the collation setting for a server, each and every one of our databases would have to use those settings.

SQL Server 2000 normally uses as much memory as it can, but if another application wants some, SQL Server 2000 will release the amount of memory that the next process needs.

If you have multiple instances of SQL Server 2000 installed, each instance will try to take as much memory as it can. When the first instance releases memory to the next application (in this case SQL Server 2000), it will not realize that it is another copy of SQL Server 2000 requesting the memory. Currently there is no sharing of memory space between SQL Server 2000 instances.

Finally, because it can be resource intensive, Microsoft does not recommend installing too many instances of SQL Server 2000 on one machine. In fact, if you have very high performance transactional databases, keep them on separate servers. The resources will not be shared, thus improving performance.

This feature was really only introduced to cater to developers moving an application to testing, or a smaller database server being freed up to allow a bigger server to be moved into its own environment.

You have to admit; this is a pretty cool new feature, especially when we are developing applications that require different settings on each server.

But now that has all changed. With SQL Server 2000 we can specify a collation setting for not only the server, but for each database as well. But wait—there’s more! Not only can we specify the collation setting for the database, but we can also specify the collation setting for each column in our tables. This gives us a greater range of flexibility when we have international data feed in. We can get extended character support for Japanese, Arabic, and virtually any data character.

Because we have different collation settings we can also have different sort orders, case sensitivity, and so on. Talk about flexibility!

Possibly related posts: (automatically generated)
Running Multiple Instances on One Machine

• Web Technology & Ecommerce Online Solutions
• What Is a Domain Tree? What Is a Forest?
• Windows NT Domain Models
• Ecommerce revolution, Online Marketing
• Migration Considerations: Centralized Versus Decentralized Management
• Anatomy of the Online Store
• Client/Server must Know
• Inter-domain Trust Relationships
• The Active Directory and Dynamic DNS continue...
• Primary, Secondary, and Caching-Only Name Servers