Yet a more improve bulk DB set up file

This on attempt to do more and protect the system DBs

This script will:
1. Change the DB owner
2. Set all to simple mode. Cannot figure out how to stop the tempdb message
3. Set all DBs to 2012 mode
4. Add the domain users as owners. Please use this as a prototype for other test and dev users
5. Shrink all logs to 500 MB to ensure there is plenty of space

Left to do standardize file growths, block create user when the user exists

USE Master
GO


DECLARE @prefix nvarchar(120) = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN  ';
DECLARE @postfix nvarchar(50) = ' END';
DECLARE @cmd nvarchar(512);

SET @cmd =  @prefix + 'PRINT ''?''; USE ? exec sp_changedbowner sa ' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd
PRINT 'Successfully set DB owner to sa';


SET @cmd =  @prefix + 'ALTER DATABASE ? SET RECOVERY SIMPLE;' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Set all DBs to SIMPLE';

SET @cmd =  @prefix + 'ALTER DATABASE ? SET COMPATIBILITY_LEVEL = 110;'+ @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Upgrade all DBs to 2012';

SET @cmd =  @prefix + 'Use ?; CREATE USER [[DOMAIN]\[USER]] WITH DEFAULT_SCHEMA = dbo;' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Add group [DOMAIN]\[USER]';

SET @cmd =  @prefix + 'Use ?; ALTER ROLE db_owner ADD MEMBER [[DOMAIN]\[USER]];' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Add db_owner [DOMAIN]\[USER]';

SET @cmd =  @prefix + 'Use ?; DBCC SHRINKFILE (N''?_log'' , 500);' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Shrink all logs to 500 MB';

SQL Connection Error: "The target principal name is incorrect. Cannot generate SSPI context"

Welcome to the rabbit hole.

This can be caused when there are more than one entry for a SQL Server entry in Kerberos. Sometimes it is caused when a SQL Server is installed under one domain user and is then is switch to another.

Technet article: How to troubleshoot the "Cannot generate SSPI context" error message
https://support.microsoft.com/en-us/kb/811889?wa=wsignin1.0 

Really good description but no examples:
How Windows Server 2012 Eases the Pain of Kerberos Constrained Delegation, Part 2

Basically you delete the existing entries and make new ones. You have to be an AD admin to make the deletions.

Commands of use:

List Command 

setspn -L [Machine name if default instance]

C:\windows\system32>setspn -L wkonedev01

Registered ServicePrincipalNames for CN=WKONEDEV01,OU=Member Servers,DC=******,

DC=com:

        MSSQLSvc/WkOneDev01.******.com:1433

        MSSQLSvc/WkOneDev01.******.com

        WSMAN/wkonedev01.******.com

        TERMSRV/wkonedev01.******.com

        RestrictedKrbHost/wkonedev01.******.com

        HOST/wkonedev01.******.com

        WSMAN/WKONEDEV01

        TERMSRV/WKONEDEV01

        RestrictedKrbHost/WKONEDEV01

        HOST/WKONEDEV01

Delete Command

setspn -D MSSQLsvc/[Machine Name].[Domain].com:1433 [Domain]\[Domain User Name]

 C:\windows\system32>setspn -D MSSQLsvc/wkonedev01.******.com:1433 ******\wkone

dev01server

Unregistering ServicePrincipalNames for CN=wkonedev01Server,OU=Service Accounts,

DC=*******,DC=com

        MSSQLsvc/wkonedev01.*******.com:1433

Updated object

Safe Add Command

setspn -S MSSQLsvc/[Machine Name].[Domain].com:1433 [Domain]\[Domain User Name]

C:\windows\system32>setspn -A MSSQLsvc/wkonedev01.*******.com:1433 ********\wkone

dev01server

Registering ServicePrincipalNames for CN=wkonedev01Server,OU=Service Accounts,DC

=********,DC=com

        MSSQLsvc/wkonedev01.********.com:1433

Updated object

After the commands make sure AD is given time to update the DNS then run

C:>ipconfig /flushdns

C:>ipconfig /renew

Adding Bulk Rights SQL 2005

Was trying to do some quick administration and realized the SQL Server 2005 had a different method than 2012. Do not put any [] brackets in the user name. However, make sure you put brackets around the DB name especially with Sharepoint to handle the dashed in the DB name.


DECLARE @cmd nvarchar(255);

--first create user
SET @cmd = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] CREATE USER [domain\user] FOR LOGIN [domain\user]''  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

--second assign role
SET @cmd = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] exec sp_addrolemember ''db_owner'', ''Domain\User''  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

Clear Temp DB without restart

Doesn't always work but sometimes magic happens. This is essentially other peoples work put here so I remember.


use tempdb
GO

DBCC FREEPROCCACHE -- clean cache
DBCC DROPCLEANBUFFERS -- clean buffers
DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
DBCC SHRINKDATABASE(tempdb, 20); -- shrink tempdb
DBCC shrinkfile ('tempdev') -- shrink db file
DBCC shrinkfile ('templog') -- shrink log file
GO

-- report the new file sizes
SELECT name, size
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

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/