Where should the new database be placed?

By Blogger
Credits: Cea.
You get a request to create a database for an application and when you ask the requester where do they want their database be placed, they may leave it to you. This often happens. Now with so many SQL Server instances in your environment where would you place it ? The target size of the DB and available free disk space on server are one of the thing to be checked but there are other things too. The one question which would arise is should the database be placed on a SQL instance with other databases, or on a separate SQL instance of its own or in a separate windows host of its own? Following is one of the way to identify where the new database be placed:


  • Application requires a login to be given access to server role on the instance
    In this case if the database is placed with other databases on a shared SQL instance then there is a thereat that the login which is given access to server has access to other databases too. In this case the database should be placed in its separate SQL instance.
  • Database needs to be hosted to an instance with different server settings
    If the required server settings are different from settings on your current servers then you have to place the database in a separate instance with required settings.
  • You already have a database with same name.
    If you already have a database with same name on an instance, which could happen, then you have to place the database on a separate instance.
  • Database is resource intensive.
    If the database is going to use resources very heavily then place the database on its separate windows host, physical or virtual.
  • Else
    If none of the above case then you can place the database on a shared SQL instance with other databases.

Comments

Post a Comment

Make Money from Surveys

Make money by filling online surveys

Popular posts from this blog

Schema.Object has an unresolved reference to Schema

By Blogger
If while building a database project face this error: Severity Code Description Project File Line Suppression State Error SQL71501: Function: [schema].[object] has an unresolved reference to Schema [schema]. DBProject.Database D:\TFS\path\DBProject.Database\schema\Functions\schema.object.sql Then this error might be because you have object definition referring to the schema under which they are supposed to be created but there is not script to create that schema. Suppose your schema name is empschema then you should add file smpschema.sql to the project with following code: CREATE SCHEMA empschema AUTHORIZATION dbo;
... more »

The current master key cannot be decrypted

By Blogger
We had the requirement to migrate our cluster to a different domain. We knew that Microsoft does not recommend to migrate the existing cluster to a different domain (Read http://support.microsoft.com/kb/269196 ). The business could not afford the time to build a new cluster in the target domain and move the databases to the SQL Server instance created on target cluster. We uninstalled the SQL server after backing up each database. Server team removed the nodes from the cluster and then moved each node to a new domain. We then installed the SQL Server and attached (since time was constraint) each databases back. All the databases were running fine and we could use them. We restored the master and msdb database so that all the logins, jobs and integration packages are back in place. Later while setting up distribution for replication we faced the first error: There is no remote user 'distributor_admin' mapped to local user '(null)' from the remote server 'repl_dist...
... more »

There is insufficient memory available in the buffer pool

By Blogger
Image
Recently one of the developers sent me following error: Message: SQL Error [Microsoft OLE DB Provider for SQL Server: There is insufficient memory available in the buffer pool. SQL State: 42000    Native Error: 802 State: 20     Severity: 17 SQL Server Message: There is insufficient memory available in the buffer pool. I checked the memory usage by components under Memory Consumption server report. Following is the screenshot of the report: I found that CACHESTORE_SQLCP was allocated most memory. This type of memory clerk indicates memory consumption by plans of SQL statements or batches not found in any stored procedures, triggers or functions. I just wanted to clear pool for CACHESTORE_SQLCP. The name for this memory clerk is "SQL Plans". Which I found by querying sys.dm_os_memory_clerks dynamic management view. I then executed the following command to clear the pool for CACHESTORE_SQLCP. DBCC FREESYSTEMCACHE('SQL Plans') The memory a...
... more »