Security – When to use an MS SQL instance vs. different database on same instance

Securitysql server

We have some MS SQL servers that are setup with different instances on the same server to separate applciation DB's as well as some servers that are setup with all DB's on the same instance, just separated with security settings.

When is it advisable to create a new instance for SQL server and install your DB's in that instance as opposed to just creating a new DB on the same instance and putting security around the database itself? Is there more to the decision that just a security aspect?

Best Answer

Here's some of the reasons to use multiple instances:

Using different instances (named instances) allows you to run different application databases at different SQL Server Service Pack / fix levels, or indeed on different versions of SQL Server.

If an application (particularly from third party vendors) requires elevated privileges, then it makes sense to separate it by putting it on its own instance.

Using different instances is a primitive way of resource allocation - allocating only so much memory to one instance, and offering a different amount of memory to another instance.

In a clustered environment, using instance-stacking is a good way of getting a better ROI - you've paid for all that hardware, and the licencing costs.

Related Solutions

Sql-server – SQL Server 2005 and 2008 instances on the same machine

If you upgrade you will not be able to (with ease) move a database from 2008 back to 2005.

Like lynnroth, I run multiple instances (05 standard, 08 dev, and a 05 express instance) for testing specific things.

If you're worried about backwards comptible SQL you can use various compatibility level values. It's not exactly like running the SQL against a different version of SQL Server, but it makes migrating from version to version easier.

Also, to note. You can only have one default instance of SQL Server on a machine (machinename). I find it easier to manage which version I'm on by naming the instances accordingly...

machineName - default (used the most)

machineName\SQLEXPRESS (sql express' default instance name)

machineName\SQL2008 - 2008

Don't forget that running databases against multiple instances is different resource-wise than running all databases against a single instance.

Kris

Tools to Manage SQL Server 2008 Database Mirroring

I created a series of batch files and SQL scripts to do the steps below. I only included the scripts for the mirroring stuff but the other referenced SQL scripts are pretty basic SQL commands (except the restore database script, that one is kind of tricky due to how I've implemented it) and creating them will give you an opportunity to change the SQLCMD mode variables. If you want to Direct Message me on twitter (@jcumberland) with your email address I can send you the full set of scripts. I have removed the full paths on the file names as well.

sets recovery mode for database
removes mirroring
remove mirror database
backup database
backup log
copies bak file
copies trn file
restores database backup
restores log backup
deletes bak file
deletes trn file
set up mirroring between the principal and mirror

/*------------------------------------------------------*/
--  REPLACE THESE VALUES
/*------------------------------------------------------*/











/*------------------------------------------------------*/
--  BATCH FILE 1, CALLS UTILITY BATCH FILE
/*------------------------------------------------------*/
@echo off

set PRIMARYHOST=
set MIRRORHOST=
set INST=
set DRIVE=
set DRTARGETDIR="%DRIVE%:\MSSQL.1\MSSQL\DATA\"
set LOGDIR="%DRIVE%:\MSSQL.1\MSSQL\DATA\"
set PORT=
set PRIMARYINST=%PRIMARYHOST%\%INST%
set MIRRORINST=%MIRRORHOST%\%INST%

@echo on


call drdbsetup.bat %PRIMARYHOST% %MIRRORHOST% %PRIMARYINST% %MIRRORINST% %DRIVE% %DRTARGETDIR% %PORT%  %LOGDIR%

pause







/*------------------------------------------------------*/
--  BATCH FILE 2 - CALLS THE SQL SCRIPTS
/*------------------------------------------------------*/
rem -- set recovery mode to full
sqlcmd /E /S%3 -i SetRecoveryMode.sql -v DATABASE="%8"

rem -- remove mirroring
sqlcmd /E /S%3 -i MirroringRemove.sql -v DATABASE="%8"

sqlcmd /E /S%4 -i MirroringRemoveMirror.sql -v DATABASE="%8"

rem -- full backup
sqlcmd /E /S%3 -i BackupDatabaseFull.sql -v BACKUPPATH="%5:\" -v DATABASE=%8

rem -- log backup
sqlcmd /E /S%3 -i BackupDatabaseLog.sql -v BACKUPPATH="%5:\" -v DATABASE=%8

rem -- copy backup files to mirror
copy \\%1\%5$\*.bak %6 /y
copy \\%1\%5$\*.trn %6 /y

rem -- remove backup files from primary
del \\%1\%5$\*.bak
del \\%1\%5$\*.trn

rem -- restore database from backup directory
sqlcmd /E /S%4 -i RestoreDatabase.sql -v BKDIR=%6 -v DATADIR=%6 -v LOGDIR=%9

rem -- restore database log
sqlcmd /E /S%4 -i RestoreLog.sql -v BACKUPPATH=%6 -v DATABASE=%8

rem -- remove backup files from mirror
del %6\*.bak
del %6\*.trn

rem -- set up mirroring
sqlcmd /E /S%4 -i MirroringSetup.sql -v PRINCIPAL="%3" -v MIRROR="%4" -v PRINCIPAL_DNS="%1." -v MIRROR_DNS="%2.yourdomain.local" -v DATABASE_NAME="%8" -v PORT=%7







/*------------------------------------------------------*/
--  MirroringSetup.sql
/*------------------------------------------------------*/


/*-----------------------------------------------------*/
--  run on principal
/*-----------------------------------------------------*/
:connect $(PRINCIPAL)
GO

--  creates endpoint
if not exists (select * from sys.endpoints where name = 'Mirroring')
begin
    CREATE ENDPOINT Mirroring
    AUTHORIZATION []
    STATE = STARTED AS TCP (
        LISTENER_PORT = $(PORT)
        ,LISTENER_IP = ALL
    )
    FOR DATA_MIRRORING (
        ROLE = PARTNER
        ,AUTHENTICATION = WINDOWS NEGOTIATE
        ,ENCRYPTION = REQUIRED ALGORITHM RC4
    )
end
go

/*-----------------------------------------------------*/
--  run on mirror
/*-----------------------------------------------------*/
:connect $(MIRROR)
GO

--  creates endpoint
if not exists (select * from sys.endpoints where name = 'Mirroring')
begin
    CREATE ENDPOINT Mirroring
    AUTHORIZATION []
    STATE = STARTED AS TCP (
        LISTENER_PORT = $(PORT)
        ,LISTENER_IP = ALL
    )
    FOR DATA_MIRRORING (
        ROLE = PARTNER
        ,AUTHENTICATION = WINDOWS NEGOTIATE
        ,ENCRYPTION = REQUIRED ALGORITHM RC4
    )
end
go

-- Sets up mirror partnership
ALTER DATABASE [$(DATABASE_NAME)] SET PARTNER = N'TCP://$(PRINCIPAL_DNS):$(PORT)'
go

-- Removes mirror monitoring
if exists (select * from msdb..sysjobs where [name] ='Database Mirroring Monitor Job')
begin
    EXEC sp_dbmmonitordropmonitoring
end
go

-- Sets up mirror monitoring
EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute
go


/*-----------------------------------------------------*/
--  run on principal
/*-----------------------------------------------------*/
:connect $(PRINCIPAL)
GO

-- Sets up mirror partnership
ALTER DATABASE [$(DATABASE_NAME)] SET PARTNER = N'TCP://$(MIRROR_DNS):$(PORT)'
go

-- Removes mirror monitoring
if exists (select * from msdb..sysjobs where [name] ='Database Mirroring Monitor Job')
begin
    EXEC sp_dbmmonitordropmonitoring
end
go

-- Sets up mirror monitoring
EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute 
go






/*------------------------------------------------------*/
--  DRMirroringRemove.sql
/*------------------------------------------------------*/
if exists ( select * from sys.database_mirroring where db_name(database_id) = '$(DATABASE)' and mirroring_guid is not null )
begin
    ALTER DATABASE [$(DATABASE)] SET PARTNER OFF
end
go

WAITFOR DELAY '00:00:10'
go







/*------------------------------------------------------*/
--  DRMirroringRemoveMirror.sql
/*------------------------------------------------------*/
if exists ( select * from sys.database_mirroring where db_name(database_id) = '$(DATABASE)' and mirroring_guid is not null )
begin
    ALTER DATABASE [$(DATABASE)] SET PARTNER OFF
end
go

WAITFOR DELAY '00:00:10'
go

DROP DATABASE [$(DATABASE)]
go

Best Answer

Related Solutions

Sql-server – SQL Server 2005 and 2008 instances on the same machine

Tools to Manage SQL Server 2008 Database Mirroring

Related Topic