Adding bulk rights improved

Obviously the system databases normally do not have a demand for bulk users and should be excluded from these updates. This is the syntax. This script changes the db owner in all but the system to sa to make sure a user is not the owner.

DECLARE @cmd nvarchar(255);

SET @cmd = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE ? exec sp_changedbowner sa  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

Adding bulk rights for users

This really needs to be modified to eliminate the system DBs but I don't have time right now to add it:


EXEC sp_MSForEachDB 'Use ?; exec sp_changedbowner sa'

EXEC sp_MSForEachDB 'ALTER DATABASE ? SET RECOVERY SIMPLE;'

EXEC sp_MSForEachDB 'ALTER DATABASE ? SET COMPATIBILITY_LEVEL = 110;'


EXEC sp_MSForEachDB 'Use ?; CREATE USER [DOMAIN\User_or_Group] WITH DEFAULT_SCHEMA = dbo;'

EXEC sp_MSForEachDB 'Use ?; ALTER ROLE db_owner ADD MEMBER  [DOMAIN\User_or_Group];'

SQL Server Veeam backups

Had a situation where we want to use differential backups due to the size and high availability of the database servers. I found out the Veeam backups are not truly copy only and interrupt the lsn chain of backups. This effect of this is you cannot take a differential backup after a Veeam SQL backup as it will say there is no reference backup.

Information I have found:

It appears there is an issue using both VEEAM and differential SQL backups.

This is our strategy to minimize slow performance times and data file sizes in Sharepoint and SAP QA and production.

Below is the recommendations based on the VEEAM forums:

http://forums.veeam.com/veeam-backup-replication-f2/sql-do-not-truncate-logs-ignored-t20054.html

http://forums.veeam.com/veeam-backup-replication-f2/sql-backup-jobs-t15803.html

The recommendation that makes the most sense to me is:

Hi uweiss

1. Disable application aware processing, either at the job level or at the individual VM level.
2. Ensure you're not using VMware Tools Quiescence (job level)
3. Respectfully, running databases in simple recovery mode, but then performing a diff every 3 hours - you're contradicting yourself. You either need the ability to restore to point in time or you don't. You will also be wasting a lot of space with diff backups ever 3 hours.

The general rule of thumb we use is
a) Full backups on a Sunday (say 9PM)
As part of the Full backup plan, add a Cleanup files / cleanup history - files/jobs/tasks older than 2 weeks
b) Diff backups Mon-Sat (again, 9PM)
c) Transaction Log backups every 15 minutes

This gives you the ability to restore to any point in time in the last 2 weeks. Making the cleanup task part of the full backup task means you never accidentally delete a full backup required as the start of a chain. Using diffs keeps the daily backup sizes down.

cheers
Lee.

More Info:

http://www.virtuallyimpossible.co.uk/sql-server-full-and-transaction-log-backups-completing-but-differential-backups-failing-because-of-veeam-br-configuration/

Add Database Encryption User

USE Master
GO
--Assign Password to the instance
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

--Create the certificate for the database
CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE_Certificate';

--Create a backup to allow the password to be recovered
BACKUP CERTIFICATE tdeCert TO FILE = 'E:\backups\20140221_tdeCert.crt'
WITH PRIVATE KEY (
FILE = 'tdeCert',
ENCRYPTION BY PASSWORD = 'Password123');

USE [target database]
GO

--Use the certificate produced to assign it to the database
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert;

--Start the encryption
ALTER DATABASE [target database] SET ENCRYPTION ON;

Database security and role management

During the SQL Server 2012 class I was working to finally nail down options for managing security across the numerous web platforms. One this to note is there is no good reason why dbo cannot own these roles as they should be created for reuse. The exception would be the single use like settings where it may make more sense to isolate at a lower level. It would take a typical and look like this

The disadvantage of this method is unless you include db_datareader or db_datawriter every object must be added by hand. however it can be used for masking tables and source objects from users. To set below allow the SP to be shown and executed.

Final screen shot of the role allowing access but locking the user out of changes and the definition

 

The masking for select would be as below