Monthly Archives: June 2012

Remove database mirroring with SQLCMD

As an addition to my previous post, I just want to share this script to remove the database mirroring from a database with SQLCMD.  Again, make sure that you have activated the SQLCMD Mode in your management studio (Menu – Query – SQLCMD Mode).

Remark: The database on the mirror server will be removed.

/****DEFINE THE VARIABLES THAT ARE NECESSARY TO REMOVE SQL DATABASE MIRRORING***/
:setvar MYDATABASE [AdventureWorks2012]
:setvar PRINCIPAL SQLSRV01
:setvar MIRROR SQLSRV02

:CONNECT $(PRINCIPAL)
GO
ALTER DATABASE $(MYDATABASE) SET PARTNER OFF
GO
:CONNECT $(MIRROR)
GO
RESTORE DATABASE $(MYDATABASE) WITH RECOVERY
GO
DROP DATABASE $(MYDATABASE)
GO

Setup Database Mirroring with SQLCMD

Hi all, I just want to share this script.
Today I had to setup SQL Database Mirroring for 12 databases. Instead of configuring this manually with the SQL Server Management Studio, I created a script to implement database mirroring by using SQLCMD. The script must be executed in SQLCMD Mode in the management studio. To activate this select in the menu Query – SQLCMD Mode. On the mirror server, I’m using the default data and log path to store the database files and the log files.
Just fill in the correct variables:
Variable Description
MYDATABASE Database name that you want to mirror
PRINCIPAL Principal SQL Server
MIRROR Mirror SQL Server
WITNESS Witness SQL Server. If you don”t want to use automatic failover, leave the WITNESS variable empty
DOMAIN The domain of the servers
BACKUPPATH Specify where the full backup file should be stored
RESTOREPATH Specify where the path where the backup file will be stored on the mirror server
COPYPATH Specify the path where the backup files should be copied to
PrincipalListenerPort Principal listener port
MirrorListenerPort Mirror listener port
WitnessListenerPort Witness listener port
Timeout Seconds to wait until automatic failover
/****DEFINE THE VARIABLES THAT ARE NECESSARY TO SETUP SQL DATABASE MIRRORING***/
:setvar MYDATABASE [AdventureWorks2012]
:setvar PRINCIPAL SQLSRV01
:setvar MIRROR SQLSRV02
:setvar WITNESS “SQLWITNESS”
:setvar DOMAIN pieter.local
:setvar BACKUPPATH I:MSSQLBackupMirroring
:setvar RESTOREPATH I:MSSQLBackupMirroring
:setvar COPYPATH i$MSSQLBackupMirroring
:setvar PrincipalListenerPort 5022
:setvar MirrorListenerPort 5022
:setvar WitnessListenerPort 5022
:setvar Timeout 10
SET NOCOUNT ON
GO
–Verify if witness is defined
if ‘$(WITNESS)’ =”
print ‘*** WARNING: NO witness server has been defined ***’

–1. Create endpoints on principal, mirror and witness.
:CONNECT $(PRINCIPAL)
GO
–*** Creating endpoint on the principal ***
if exists (select 1 from sys.endpoints where name = ‘Mirroring’)
print ‘*** WARNING: Endpoint ”Mirroring’2014-03-28 19:35:36′ already exists on $(PRINCIPAL) ***’
else
CREATE ENDPOINT Mirroring STATE=STARTED AS tcp (listener_port=$(PrincipalListenerPort)) FOR database_mirroring (ROLE=all)
GO
:CONNECT $(MIRROR)
GO
–*** Creating endpoint on the mirror ***
if exists (select 1 from sys.endpoints where name = ‘Mirroring’)
print ‘*** WARNING: Endpoint ”Mirroring” already exists on $(MIRROR) ***’
else
CREATE ENDPOINT Mirroring STATE=STARTED AS tcp (listener_port=$(MirrorListenerPort)) FOR database_mirroring (ROLE=all)
GO
:CONNECT $(WITNESS)
GO
–*** Creating endpoint on the witness ***
if exists (select 1 from sys.endpoints where name = ‘Mirroring’)
print ‘*** WARNING: Endpoint ”Mirroring” already exists on $(WITNESS) or witness server was not defined ***’
else
CREATE ENDPOINT Mirroring STATE=STARTED AS tcp (listener_port=$(WitnessListenerPort)) FOR database_mirroring (ROLE=WITNESS)
–2. Take Full Backup and COPY backup files to the mirror
:CONNECT $(PRINCIPAL)
GO
print ‘*** Take full backup of principal database $(MYDATABASE) ***’
IF  (left(cast(SERVERPROPERTY(‘ProductVersion’)as varchar),5)=’10.00′ and SERVERPROPERTY(‘EngineEdition’)=3) OR (left(cast(SERVERPROPERTY(‘ProductVersion’)as varchar),5)=’10.50′ and SERVERPROPERTY(‘EngineEdition’)in(2,3))
BEGIN
BACKUP DATABASE $(MYDATABASE) TO DISK = ‘$(BACKUPPATH)$(MYDATABASE).bak’
WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = ‘$(MYDATABASE) Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10,COMPRESSION
print ‘*** Take transaction log backup of principal database $(MYDATABASE) ***’
BACKUP LOG $(MYDATABASE) TO  DISK = ‘$(BACKUPPATH)$(MYDATABASE).trn’
WITH NOFORMAT, INIT,  NAME = ‘$(MYDATABASE) Transaction Log Backup’, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10,COMPRESSION
END
ELSE
BEGIN
BACKUP DATABASE $(MYDATABASE) TO DISK = ‘$(BACKUPPATH)$(MYDATABASE).bak’
WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = ‘$(MYDATABASE) Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
print ‘*** Take transaction log backup of principal database $(MYDATABASE) ***’
BACKUP LOG $(MYDATABASE) TO  DISK = ‘$(BACKUPPATH)$(MYDATABASE).trn’
WITH NOFORMAT, INIT,  NAME = ‘$(MYDATABASE) Transaction Log Backup’, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10
END
GO
print ‘*** Copy principal database $(MYDATABASE) from principal server $(PRINCIPAL) to mirror server $(MIRROR) ***’
!!ROBOCOPY $(BACKUPPATH) $(MIRROR)$(COPYPATH) $(MYDATABASE).*
GO

–3. Restore database on the mirror
:CONNECT $(MIRROR)
GO
print ‘*** Create database directories ***’
DECLARE @root_folder nvarchar(128);
EXECUTE [master].[dbo].[xp_instance_regread]
@rootkey = N’HKEY_LOCAL_MACHINE’,
@key = N’SoftwareMicrosoftMSSQLServerMSSQLServer’,
@value_name = N’DefaultData’,
@value = @root_folder OUTPUT;

select @root_folder=@root_folder + ‘$(MYDATABASE)’
EXEC master.dbo.xp_create_subdir @root_folder

EXECUTE [master].[dbo].[xp_instance_regread]
@rootkey = N’HKEY_LOCAL_MACHINE’,
@key = N’SoftwareMicrosoftMSSQLServerMSSQLServer’,
@value_name = N’DefaultLog’,
@value = @root_folder OUTPUT;

select @root_folder=@root_folder + ‘$(MYDATABASE)’
EXEC master.dbo.xp_create_subdir @root_folder

GO
print ‘*** Restore full backup of database $(MYDATABASE) ***’
RESTORE DATABASE $(MYDATABASE)
FROM  DISK = ‘$(RESTOREPATH)$(MYDATABASE).bak’
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10,REPLACE
GO
print ‘*** Restore transaction log backup of database $(MYDATABASE) ***’
RESTORE LOG $(MYDATABASE) FROM  DISK = ‘$(RESTOREPATH)$(MYDATABASE).trn’
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

–4.Activate Mirroring
:CONNECT $(MIRROR)
GO
print ‘*** Set partner on the Mirror DB ***’
ALTER DATABASE $(MYDATABASE) SET PARTNER = ‘TCP://$(PRINCIPAL).$(DOMAIN):$(PrincipalListenerPort)’
GO
:CONNECT $(PRINCIPAL)
GO
print ‘*** Set partner on the Principal DB ***’
ALTER DATABASE $(MYDATABASE) SET PARTNER = ‘TCP://$(MIRROR).$(DOMAIN):$(MirrorListenerPort)’
GO
print ‘*** Set PARTNER SAFETY FULL on the Principal***’
ALTER DATABASE $(MYDATABASE) SET PARTNER SAFETY FULL
GO
print ‘*** Declare the witness on the Principal ***’
if ‘$(WITNESS)’ <> ”
ALTER DATABASE $(MYDATABASE) SET WITNESS = ‘TCP://$(WITNESS).$(DOMAIN):$(WitnessListenerPort)’
print ‘*** Setting the timeout on the principal to $(TIMEOUT) seconds ***’
ALTER DATABASE $(MYDATABASE) SET PARTNER TIMEOUT $(TIMEOUT)
GO

If you should have remarks or suggestions, just let me know.
Happy mirroring!

Can you give me a list of all the users that can connect to our databases?

I often get the following question of my customers “Can you give me a list of all the users that can connect to our databases”. Actually, the customer wanted to have a list of users per database. To be complete, I added also the list of the Server Logins.

I created the following queries to answer that question.

First query will get all the server logins and the assigned server role(s). The logins that only have the public server role are also included in the list.

select sp2.name as [Login], ISNULL(sp.name,”public”) as Serverrole
from sys.server_role_members srm
join sys.server_principals sp on sp.principal_id=srm.role_principal_id and sp.type=”R”
right outer join sys.server_principals sp2 on srm.member_principal_id=sp2.principal_id
where sp2.type in (”U”,’2014-03-28 19:35:36’G”,”S”) and sp2.is_disabled=0
order by 1

The second query will get all the database users for each database and the assigned database roles. The users that are not a member of any role are also included in the result set.

create table #DatabaseUsers (
[Database] sysname,
DBRole varchar(256),
DBUser varchar(256)
)

DECLARE @CMD VARCHAR(MAX)
SET @CMD = N”use [?]
select db_name() as [Database],ISNULL(rp.name,””No database role assigned””) as database_role, mp.name as database_user
from sys.database_role_members drm
join sys.database_principals rp on (drm.role_principal_id = rp.principal_id)
right outer join sys.database_principals mp on (drm.member_principal_id = mp.principal_id)
join sys.database_permissions dp on mp.principal_id=dp.grantee_principal_id and dp.permission_name=””CONNECT””
where mp.type<>””R”””

Insert into #DatabaseUsers
exec sp_MSForEachDB @CMD

select * from #DatabaseUsers
drop table #DatabaseUsers

Remark: Disabled server logins and database users that are not allowed to connect to the database (REVOKE CONNECT) are not shown in the result set.

Have fun!