Andrew Fraser DBA

Study notes crib sheet for exams 70-457 70-458 70-459 MCSE SQL Server 2012

Here is the crib sheet/study notes I used for the three exams:
70-457 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 1
70-458 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 – Part 2
70-459 Transition your MCITP on SQL Server 2008 to MCSE: Data Platform

Each of the exams has two components, one mostly straight DBA and the other more developer (T-SQL and SSIS). The passing score remains at 70%, 700 out of possible 1000, but you need to pass both of the two component parts of each exam. The first two exams are straightforward, but 70-459 is a little trickier with case study questions in addition to the normal multiple choice questions.

Microsoft often give special offers, it is worth checking that (http://www.microsoft.com/learning/en/us/offers.aspx) before booking and paying for any exam – because the offer code has to be entered at the time of booking or you will lose the offer.  Currently (March 2013), Microsoft are offering free resits of any exam, but only if you enter their offer code before booking and paying for the original exam.

Study Notes/Crib Sheet (also in pdf here)

1. alter schema Schema2 transfer dbo.Customers — to change table owner
2. 1 = true
3. reversible encryption for credit card numbers http://technet.microsoft.com/en-us/library/hh994549(v=ws.10).aspx
4. one way encryption for passwords (uses hash)
5. with check option = allows insert on view verifying where clause
6. order by offset fetch = new 2012 server side paging
7. Data tier application DACPAC = deploy database application via package
8. Common table expression (with…) = fast recursive heirarchy queries

1. to add a witness: endpoint, same login+perimssions endpoint, principal use endpoint as witness
2. fuzzy lookup similarity – 1.00 is closest to accurate match
3. CDC: SELECT * FROM cdc.fn_cdc_get_net_changes_HR_Department(@from_lsn, @to_lsn, ‘all’)
4. package logging: use OnError event handler
5. pacakge invalid data rows: set output SSIS data flow arrow to use a data tap
6. fact table referential integrity: create a dimension that can join to both at respective granularity
7. gacutil to deploy custom component SSIS http://msdn.microsoft.com/en-us/library/ms403356.aspx
8. WFC to minmize storage
9. tuning advisor evaluates current and potential indexes
10. backup with norecovery for failover (plus normal use during pre-restore to get last t-log info)
11. backup priority – highest number is more likely to be used

1. create trigger mytrig on myview instead of insert as begin insert into … select … from inserted – allow inserts complex views.
2. union all – includes duplicates, so faster because no distinct filtering
3. SELECT IIF(TRY_PARSE(’01/01/2011′ AS datetime2) IS NULL, ‘True’, ‘False’) AS Result; – does a convert/cast
4. varbinary(max) – stores documents inside database
5. isolation level – ‘snapshot’ is same as oracle behaviour – reads don’t block anything. ‘read committed’ is default.
6. datetimeoffset – datetime with timezone information
7. AF new condition LIKE, new policy, enable policy – to enforce a new policy management
8. sys.dm_exec_requests or sp_who – shows what is blocking a session
9. alter user dom\user with login dom\user; – when copying database to new server
10. continue_after_error – on backup, so it does not stop if encountering any checksum errors due to bcp/bulk insert operations.
11. AF instant file initialization needs perform volume maintenance security, plus restart to take effect
12. from cus left outer join ord = from cus, ord where cus.cid = ord.cid(+) — all customers, even those without orders.
13. create trigger trig1 on tab1 for update as if update(col1) begin…end
14. xml auto = <salesorder ordernumber=”1″ … (=attributes)
15. xml auto,elements = <salesorder><ordernumber>1</ordernumber>…
16. Good examples at http://beyondrelational.com/modules/2/blogs/28/posts/10418/for-xml-workshop-understanding-for-xml-raw.aspx
17. set xact_abort on = errors cause entire transaction to be terminated and rolled back (by default is off)
18. The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
19. columnstore index on read only table (AF add a columnstore index to cover the query)
20. view with schemabinding – prevents definition changes to underlying tables. Required for indexed views. Functions can also use schemabinding, in their case required for persisted columns.
21. SQL Config Manger > Protocols > Certificates + restart – to force use a particular ssl certificate to connect to sql server
22. affinity I/O mask – AF
23. (cpu) affinity mask – AF
24. cpu affinity – AF set in windows OS http://msdn.microsoft.com/en-gb/library/ms189629.aspx
25. Cross apply/outer apply – table value function for each row in select
26. System Monitor can generate alerts.
27. tablock hint locks entire table
28. AF To use filetables: enable filestream on instance, create filestream file group, set filestream directory name in database
29. Integration services catalog is needed for SSIS project deployment model
30. set statistics showplan_xnl_on will generate execution plans
31. kill 64 – kill session 64
32. AF Server audit specification can write messages to application log
33. Decimal isdatatype is the same as numeric, can give fixed precision and scale
34. ‘encryption’ in stored procedure = obfuscation
35. SELECT FORMAT ( @d, ‘d’, ‘en-US’ ) AS ‘US English Result’ — format datetimes to particular countries outputs
36. compatibility level 110 for SQL Server 2012 databases.
37. group by grouping sets http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/
38. index reorganize does not take index offline, while index rebuild does complete drop/recreate.
39. Lag gives values in previous row (lead would give next row) e.g. lag partition by terrotory order by year
40. AF master: create server audit, alter with state=on, userdb: create database audit specification for server audit, alter with state=on
41. View creation: with check option comes after the select, other with options (e.g. with schemabinding for indexed views) come before the select
42. DECLARE @xml1=xml(Sales.InvoiceSchema) – declare variable of xml type
43. merge..when not matched by target – upsert
44. if (xact_state()) = 1 – transaction is commitable, -1 for uncommittable.
45. where cityid not in (select… = where cityid <> all (select… http://msdn.microsoft.com/en-gb/library/ms173545.aspx
46. create database audit specification c_auditspec
47. slow link agent profile for merge replication on poor networks with large (varchar/xml) datatypes
48. windows failover cluster manager (not setup) for windows, other way around for sql part.
49. 1222 to trace deadlocks, either with trace flag and restart or with dbcc traceon
50. Page restore needs full or bulk logged recovery to work (generally does not work even with bulk logged), will not work with simple.
51. Backup prority for AvG – highest numbers (100) get the highest priority, the mostly likely to be used for the backup.
52. Cast to update nvarchar(max) – e.g. update blogentry set summary=cast(n’text’ as nvarchar(max) where id in select top10…
53. COLUMNS_UPDATED tests for UPDATE or INSERT actions performed on multiple columns (with bitmaps). To test for UPDATE or INSERT attempts on one column, use UPDATE().
54. Create credential, add a proxy that references credential
55. Introduce a witness server: Create endpoint for witness, ensure login with connect, On principal alter to use witness endpoint. http://msdn.microsoft.com/en-us/library/ms190430.aspx
56. Create a database snapshot for fast rollback.
57. Convert to contained: sp_configure(‘contained database authentication,1), alter database containment=partial, sp_migrate_user_to_contained for each user.
58. UDF
59. Grant view server state, view any database to ROLE
60. Trace flag startup options are in SQL Server Configuration Manager

Steps to TDE database http://msdn.microsoft.com/en-gb/library/bb934049.aspx :

1. create a master key (encrypted by password) AF
2. create a certificate in the master database protected by the master key
3. create a database encryption key in the user database and protect it by the certificate
4. set the database option to enable encryption

• affinity http://msdn.microsoft.com/en-gb/library/ms189629.aspx
• Transparent Data Encryption
• transaction isolation
• Filestream
• group by grouping sets/cube/rollup
• server audit, database audit specification
• pivot
• all, some|any http://msdn.microsoft.com/en-gb/library/ms173545.aspx
• audit specification cc280386,448,404 for 155 (d38)

If loose  failover cluster node: Evict > Install > Windows Failover Cluster Manager > SQL Server Setup
http://msdn.microsoft.com/en-us/library/ms181075.aspx
After Node 1 fails, the SQL Server FCI fails over to Node 2.
1) Evict Node 1 from the FCI. To do this, from Node 2, open the Failover Cluster Manager snap-in, right-click Node1, click Move Actions, and then click Evict Node.
Verify that Node 1 has been evicted from the cluster definition.
2) Install new hardware + Windows OS to replace the failed hardware in Node 1.
3) Using the Failover Cluster Manager snap-in, add Node 1 to the existing cluster. For more information, see Before Installing Failover Clustering.
Ensure that the administrator accounts are the same on all cluster nodes.
4) Run SQL Server Setup to add Node 1 to the FCI. For more information, see Add or Remove Nodes in a SQL Server Failover Cluster (Setup).

4 ranking functions:
row_number – 1,2,3,4,5
rank – 1,1,1,4,4 (3 joint 1st)  <= AF this one for consecutive ranks must be skipped when the same rank is assigned.
dense_rank – 1,1,1,2,2 (3 joint 1st)
ntile(4) – quartiles

create trigger cascade_utrig
on titles
for update as
if update(title_id)
begin
update titleauthor
set title_id = inserted.title_id
from titleauthor, deleted, inserted
where deleted.title_id = titleauthor.title_id
update roysched
set title_id = inserted.title_id
from roysched, deleted, inserted
where deleted.title_id = roysched.title_id
update salesdetail
set title_id = inserted.title_id
from salesdetail, deleted, inserted
where deleted.title_id = salesdetail.title_id
end

Transparent Data Encryption http://msdn.microsoft.com/en-us/library/ff773063.aspx
http://msdn.microsoft.com/en-gb/library/bb934049.aspx
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<UseStrongPasswordHere>’;
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘My DEK Certificate’;
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO

— 1) Create a database master key and a certificate in the master database.
USE master ;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘*rt@40(FL&dasl1’;
GO
CREATE CERTIFICATE TestSQLServerCert
WITH SUBJECT = ‘Certificate to protect TDE key’
GO
— Create a backup of the server certificate in the master database.
— The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server
— (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA).

BACKUP CERTIFICATE TestSQLServerCert
TO FILE = ‘TestSQLServerCert’
WITH PRIVATE KEY
(
FILE = ‘SQLPrivateKeyFile’,
ENCRYPTION BY PASSWORD = ‘*rt@40(FL&dasl1′
);
GO
— Create a database to be protected by TDE.
CREATE DATABASE CustRecords ;
GO
— Switch to the new database.
— Create a database encryption key, that is protected by the server certificate in the master database.
— Alter the new database to encrypt the database using TDE.
USE CustRecords;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128  — this is the default algorithim.
ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;
GO
ALTER DATABASE CustRecords
SET ENCRYPTION ON;
GO

— 2) Move/restore the encrypted database on another machine
— Detach the TDE protected database from the source server.
USE master ;
GO
EXEC master.dbo.sp_detach_db @dbname = N’CustRecords’;
GO
— Move or copy the database files from the source server to the same location on the destination server.
— Move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server.
— Create a database master key on the destination instance of SQL Server.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘*rt@40(FL&dasl1’;
GO
— Recreate the server certificate by using the original server certificate backup file.
— The password must be the same as the password that was used when the backup was created.

CREATE CERTIFICATE TestSQLServerCert
FROM FILE = ‘TestSQLServerCert’
WITH PRIVATE KEY
(
FILE = ‘SQLPrivateKeyFile’,
DECRYPTION BY PASSWORD = ‘*rt@40(FL&dasl1′
);
GO
— Attach the database that is being moved.
— The path of the database files must be the location where you have stored the database files.
CREATE DATABASE [CustRecords] ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf’ ),
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF’ )
FOR ATTACH ;
GO