Transcript Tips_that_Saved_my_Bacon_2015_06_01x

Greg Moore





Operations side DBA
Problem solver
[email protected]
@stridergdm
https://greenmountainsoftware.wordpress.com




Who doesn’t like Bacon?
You can tune a query
You can write CTEs until your eyes bleed
But… will that save your bacon?



No fancy queries
Some are common sense
Sometimes it’s the little things

SQL Saturday
◦ You’re here!
 Learn
 Network

#SQLHELP
Local SQL User Group

(Thanks to the scary biker from hell aka William Wolf)



Yes, it’s great for Disaster Recovery
What is a disaster?
◦ Building on fire
◦ Server crash
◦ Sure that happens but…


Forget the huge stuff
What’s likely?
◦ “Oops, I deleted 100 records, what can we do?”
◦ Customer just called, wants to undo a change.

Yes, it’s that simple!

Save yourself a step

You may need more time
◦ Disable job receiver side until done
◦ Don’t forget to enable it!


Make sure your DR plan takes this into
account
People may come to rely on it
◦ So be Scotty, always estimate longer than it’ll take




A bit more common
But use separate files for your indices
Don’t put your Clustered index there!
Lots of advantages
USE master
GO
ALTER DATABASE IIS_LOGS ADD FILEGROUP
IndexGroup
GO
ALTER DATABASE IIS_LOGS ADD FILE ( NAME =
N'IIS_Index', FILENAME =
N‘I:\NDF\IIS_Logs_index1.ndf' , SIZE =
102400KB , MAXSIZE = 102400KB , FILEGROWTH =
10%) TO FILEGROUP IndexGroup
GO

Put NDF file on separate physical disk
◦ (smaller benefit on same disk as logs or data)

Can have more than one
◦ Can put logs with lots of I/O on faster disks
◦ Readonly indexes on RAID 5 or slower disk
◦ Some recovery scenarios will allow you to at least
read data with a failed index file


This is one I’m surprised isn’t used more
often
Backup to a network share
◦ Need to setup permissions (SQL Agent I believe)
◦ Normal Backup
 BACKUP DATABASE AuditDB TO DISK =
'C:\Temp\AuditBD_backup_20150530_A.bak'
◦ UNC
 BACKUP DATABASE AuditDB TO DISK =
'\\localhost\LS_Backup\AuditBD_backup_20150530_B.
bak'

Disadvantages
◦ Slower over wire
◦ Need some more permissions

Advantages
◦ Can easily add space
◦ If controller or server fails, backups are still
available
◦ Can then backup to tape as convenient



Experiment I did once
Did Backup over network to 1, 2, 3, 4 NAS
devices
Performance increased by a factor of 2x, 3x,
3.9x
◦ Hit network limits
◦ Complicates restores a bit, but can get nearly linear
improvements in performance
◦ This was before 1GB NICs

Love them or Hate them?
◦ Yes!


Can’t control your spindles
BUT, can split off a mirror for a backup

Client had database (500GB)
◦ 350 GB was audit data
◦ Log-shipping over VPN to DR center a pain
◦ Restoring initial restore was a pain



Reduce size of main database
Make initial restore faster/smaller
Do so w/o developer input





Create Database with tables
Create Synonyms
Rename original tables
Copy data over
Caveat: can’t do a truncate
◦ But that’s a WIN here. Truncating an audit table
would be a bad idea!
Create database (we’ll call it Audit)
create synonym AUD_ATI_Forwarding for Audit.dbo.AUD_ATI_Forwarding
INSERT INTO Audit.dbo.AUD_ATI_Forwarding
(OPERATION, FORWARDING_ID, USER_GROUP_ID
,USER_GROUP_ID_FWD, AUD_USER_ACCT_ID, AUD_TIMESTAMP)
Select OPERATION, FORWARDING_ID, USER_GROUP_ID
,USER_GROUP_ID_FWD, AUD_USER_ACCT_ID, AUD_TIMESTAMP
From ExampleCO.dbo.AUD_ATI_Forwarding
exec sp_rename 'AUD_ATI_Forwarding', 'DROP_AUD_ATI_Forwarding'

Done, or are we?
CREATE Trigger Synonym_safety ON DATABASE FOR DROP_SYNONYM
AS
SET NOCOUNT ON;
declare @recipients nvarchar(200)
declare @subject nvarchar(100)
declare @body nvarchar(1000)
declare @profile_name nvarchar(100)
select @profile_name='sql admins'
select @recipients='[email protected]'
select @subject = 'Alert: Drop Synonym trigger on '+@@servername
select @body ='Someone tried to drop the synonyms on this box. This may be an error, but unless maintenance is being
performed, should be investigated.'
select @body = @body + CHAR(13)+ ' Command text: ' +
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
select @body = @body + CHAR(13)+ ' From SPID: ' + EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(max)')
select @body = @body + CHAR(13)+ ' User: ' + EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)')
select @body = @body + CHAR(13)+ ' DBName: ' + EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)')
select @body = @body + CHAR(13)+ ' Posttime: ' + EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(max)')
RAISERROR ('You must disable trigger "Synonym_safety" to drop synonyms!',11, 1) with log
ROLLBACK
exec msdb.dbo.sp_send_dbmail @profile_name=@profile_name, @recipients=@recipients,
@subject=@subject, @body=@body, @importance='High'

Common problem
◦ Too many people have access to the database with
privileges higher than they should!

Two solutions
◦ Schema Trigger change
 What I’ve done
◦ Events
 What I’ll do next time
CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
Insert code here


Useful to see who/what is altering your DDL
Can catch hackers


Synchronous
May require email
◦ That’s what I’ve got setup

The Baking DBA
◦ Michael Bourgon


http://thebakingdba.blogspot.com/search/la
bel/Event%20Notifications
Recommend his talk and his approach
◦ It’s better than mine!

The defaults are bad!
◦ Some users make them worse

% Growth is horrible for two reasons:
◦
◦
◦
◦
◦
◦
◦
1 gig becomes 1.1
1.1 becomes 1.21
1.21 becomes 1.33
1.33 becomes 1.46
1.46 becomes 1.61
1.61 becomes 1.77
1.77 becomes 1.95
1000MB
1210MB
1330MB
1460MB
1610MB
1770MB
1940MB – 1.7x longer



Size of your log has doubled in 9 growths
Time to grow has increased 1.7x
So only if you need 10MB more, your 9th
growth will give you 200MB!
2.5
2
1.5
Size
1
0.5
0
1
2
3
4
5
6
7
8
9


ALTER DATABASE IIS_LOGS ADD FILE ( NAME
= N'IIS_Index', FILENAME =
N'C:\NDF\IIS_Logs_index1.ndf' , SIZE =
102400KB , MAXSIZE = 102400KB ,
FILEGROWTH = 10%) TO FILEGROUP
IndexGroup
Change to a reasonable absolute value!

Big Caveat
◦ Multiple growths can create performance issues
◦ Never set max size equal to the entire disk
 Even better, make it much less
 If you’re not monitoring and you hit it, you can change
it, let it grow while you troubleshoot
 If it’s max and hits it, you’re limited in options (can
add another file group, but that’s overkill!)

Ok, I’ll admit I “stole” this one from Chris Bell

Configuration Tools
◦ Protocols
 Force Encryption Yes




Encrypts, does NOT authenticate
TEST IT
See Chris Bell’s talk for more details
Use it!
◦ Unless it breaks stuff
◦ Need higher level



Just this morning found an issue with
network shares in this demo!
SAN vendor lost business because would NOT
let me pull a drive during a demo
Another time with a SAN, before we put it into
production, I insisted on a full fail-over test
◦ It failed!
◦ Why? Vendor’s test laptop not plugged in



Hopefully you never need them
Most are simple
If you have your own and I can steal them, let
me know!





Operations side DBA
Problem solver
[email protected]
@stridergdm
https://greenmountainsoftware.wordpress.com