Month: August 2016

Changing replication job names

After configuring replication,bunch of jobs are created under SQL server agent. The job names can some times be vague and you may have to check the Tsql step code or job category to determine what type of job it is.

It is, however, possible to rename the job name to something that makes more sense. This can help greatly when you need to check on the job(especially for someone who is not very familiar with the replication terms). Renaming the job name should not cause any issues with the replication. You can also update the internal agents name in the distribution database. This is not required but I think it is worth doing, if there are any scripts that access these tables to pull the corresponding job information.

In the below example, I configured a simple transactional replication and it created three jobs:
1. Snapshot Agent
2. Distribution Agent
3. Log Reader Agent

Step 1:
Verify the job name for each one here:
Use distribution
go
select * from MSdistribution_agents where subscriber_id=0
select * from MSsnapshot_agents
Select * from MSlogreader_agents

Step 2:
Rename the job under SQL agent directly or use sp_update_job to something that’s more easy to understand.
Step 3:
Update the job names in the corresponding Agents table.
Use [Distribution]
GO
Update MSdistribution_agents set Name='SourceDB_TargetDB_Replication_DistributionAgent'
where Name='ANULU\SQL2016-SourceDB-SourceDB_Publication-ANULU\SQL2016-3'

Update MSsnapshot_agents set Name='SourceDB_TargetDB_Replication_SnapshotAgent'
where Name='ANULU\SQL2016-SourceDB-SourceDB_Publication-2'

Update MSLogReader_agents set Name='SourceDB_Replication_LogReader'
where Name='ANULU\SQL2016-SourceDB-2'

Advertisement

Database Backup Encryption

Starting from SQL 2014, we can encrypt the database backups without encrypting the actual database files. Prior to SQL 2014, the only way to encrypt the database backups using native SQL options is by enabling Transparent Database Encryption(TDE) on the database – which basically encrypts the database files at rest and this encrypts the backups as well. Obviously, enabling TDE is a bit overkill if you want to encrypt just the backups. This issue is addressed in SQL 2014 where backups can be encrypted without enabling TDE.

Below is a simple step by step procedure to encrypt backups in SQL 2014 and later.

1. Create Master Key.
2. Create Certificate.
3. Backup the Certificate and Private Key.
4. Use the Certificate to backup the database.
5. Create Certificate on the target server.
6. Restore the database on the target server.

--Step 1:
Create Master key Encryption by Password ='SQL2016Master'
--Step 2:
Create Certificate CW_SQL With Subject='Certificate'
--Step 3:
Backup certificate CW_SQL to file ='C:\Temp\CW_SQL.Cert'
with Private Key
(
File = 'C:\Temp\CW_SQL_PrivateKey.pvt',
Encryption by password='SQLBackup'
)
--Step 4:
BACKUP DATABASE [TestDB] TO DISK = N'C:\Temp\TestDB.bak' WITH FORMAT, INIT, MEDIANAME = N'TestDB Backup',
NAME = N'TestDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [CW_SQL]), STATS = 10, CHECKSUM
--Step 5: on target server
Create certificate CW_SQL from file ='C:\Temp\CW_SQL.Cert'
with Private Key
(
File = 'C:\Temp\CW_SQL_PrivateKey.pvt',
Decryption by password='SQLBackup'
)
--Step 6: On target server
Restore Database TestDB from Disk='C:\Temp\TestDB.bak' with Recovery

When creating the certificate on the target server, the certificate name is not important – you can create the certificate with any name but the thumb print is what will be validated before restoring the database. So, as long as the certificate is created from the same cert\private key source combination even with a different name, it will work.

By default, all certificates created are valid for one year unless explicitly mentioned in the create certificate statement. While expired certificate cannot be used to backup the database, it can still be used to restore the database.
This also means expired certificates can be restored(created) should they be needed to restore the database.In fact, expired certificates will not be shown in the UI dropdown to encrypt the backups.

Without creating the certificate first, none of the restore arguments work to get the details on the backup file. So, commands like restore headeronly, restore verifyonly, restore filelistonly do not work without the certificate.

Also, along with backing up the certificate and private key, you should also backup the private key encryption password, otherwise the certificate cannot be recreated on the target server.

Not In vs Exists in TSQL

When using “Not In” to check for data between source and target tables, beware of Nulls in the target table. The correct output depends on the ANSI_NULLs property of the session and\or the way query is written(using exists;Not NULL and Not IN;ANSI_NULLS OFF and Not In).

Try the below example :

declare @TestA table (sno int)
declare @TestB table (sno int)

INSERT INTO @TestA(sno)
Values (1),(3)
INSERT INTO @TestB(sno)
Values (1),(2),(Null)

— Case#1 : Not In with Ansi_Nulls turned ON
Select * from @TestA where sno not in
(Select sno from @TestB)

— Case#2 : Not Exists with Ansi_Nulls turned ON
Select * from @TestA A where not exists
(Select 1 from @TestB B Where A.sno=B.Sno)

— Case#3 : Not In with Ansi_Nulls turned OFF
Set ANSI_NULLS OFF
Select * from @TestA where sno not in
(Select sno from @TestB)
Set ANSI_NULLS ON

— Case#4 : Not In with Ansi_Nulls turned ON
Select * from @TestA where sno not in
(Select sno from @TestB where sno is not null)