In SQL 2012 you can now be a user on a database without a SQL login. This requires containment level to be set to partial at the database level.
Everyone is part of the Public role (server or database level).
Sysadmin at the server level maps to dbo in databases. Sysadmins have the abilities to impersonate all users be default.
If running proc which executes as a user, the user running the proc must have impersonate access to the user the proc is running under.
If you write audit events to a file, you can only truly read it from within SQL using the function sys.fn_get_audit_file.
CDC - This records data changes and what the changes were.
###AM Session - Automating Server Management With SQL Server Agent
Setup Database Mail
Server > Management > Database Mail > Right Click - Configure Database Mail If you grant a user the ‘DatabaseMailUserRole’ on a database level you can create them a private database mail profile.
Configure SQL Agent To Use Database Mail
Server > Right Click - SQL Server Agent > Properties > Alert System Also configure failsafe operator. This is useful if used pager email operators and no one is on duty. Use pager email addresses so you can schedule peoples hours.
Keep on top of house keeping for mail log size
SQL Agent operators are humans / email addresses that will receive emails.
Alerts in a SQL Agent Job are like a trigger / monitor, not an notification.
Three type of alerts: Standard Error Messages Stat Counters WMI Event
Standard Error Codes - 1033 is english and severity 19 or above is classed as fatal errors.
message ID 50000 > Is a custom message Can add a custom message
For a TSQL type Agent Job, you need to set run as under advance menu/page, not general. Below will walk through creating a new credential.
Map an credential to an underlying account. You map a Windows account to a credential within SQL Server. Note passwords are manually managed / stored within SQL Server. Server > Credentials
You then have to bind the credential to what it will be used for and is given a name a proxy Server > SQL Agent > Proxies > New
SSMS can only connect to one instance of SSIS on a machine so you have to mess around with the below ini then restart SSIS Service for SSMS to connect. In the XML you’re looking for <ServerName></ServerName> C:Program FilesMicrosoft SQL Server110DTSBinnMsDtsSrvr.ini.xml
House keeping for agent error logs - This happens on restart of the service or you can run the below command to manually cycle the logs.
For an alert to send out an email if configured, then it must enabled in sys.messages by settings is_event_logged column to 1:
###PM Session - Ongoing DB maintenance
A DBCC CHECK with TABLOCK will run much quicker as it isn’t using database snapshots though will lock the table while running. This therefore may cause user impact for large tables if running during office hours.
824 error indicates a detected logicalic consistency IO based issue. Always check reading certain records if done a SELECT * to get a 824. You can see page errors in the below table. In the enterprise edition of SQL Server, you are able to do page restores.
Tip - Defragment within SQL not on the OS level as it’s not SQL aware.
###PM Session - Indexes
Clustered Index - Only one per table - Creating a PK by default creates a clustered index in the background. Note - If a clustered index is created on a heap with several existing non-clustered indexes, all the non-clustered indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Similarly, if a clustered index is dropped on a table that has several non-clustered indexes, the non-clustered indexes are all rebuilt as part of the DROP operation. This may take significant time on large tables. http://technet.microsoft.com/en-us/library/ms186342.aspx
Non-clustered Index - You can have up to 999 indexes. Non-clustered indexes contain the clustering key value instead of the row identifier.
non-left level - index pages left level - is the data http://blogs.developpeur.org/raptorxp/pages/sql-server-data-structures.aspx
// AVERAGE_FRAGMENTATION_IN_PERCENT //
MS say less than 30% use REORGANIZE above use REBUILD though might be better to wait till 50% for REBUILD. Each system and requirement are unique.
Physical - Locks for the duration. Transaction log intensive
Use fillfactor on the index rebuild to leave free space on each page so that if new items are added it gives the option for the index to have space to insert in the correct place to short term help with preventing fragmentation. This example will leave 80% free on each page.
Logical - Can access data while running. Will skip page if locked, by user for example. Can get index_name from sp_helpindex [TABLENAME]
###PM Session - Maintenance Plans
Server > Management > Maintenance Plans Pretty straight forward. Not really much notes needed as maintenance plans reference other system functionality.
Good script as health check for when taking over a new server http://www.brentozar.com/blitz/
http://www.codeplex.com http://bidshelper.codeplex.com/ http://www.idera.com/productssolutions/freetools/powershellplus http://ola.hallengren.com/