Archive for the ‘DBA’ Category

TSQL: Partially Contained Databases 70-462

Here’s some TSQL for the Partially Contained Databases section of the 70-462. Explanatory comments are included. ?View Code TSQL– enable show advanced options and view current config EXEC sp_configure ‘show advanced options’, 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure; GO — enable the feature EXEC sp_configure ‘contained database authentication’, 1; GO RECONFIGURE WITH OVERRIDE; […]

Server Roles with TSQL

Here’s a few bits of TSQL you can use when working with Server-Level Roles in SQL Server 2012. List the server roles setup on an instance… ?View Code TSQLEXEC sp_helpsrvrole; List the members of a role… ?View Code TSQLEXEC sp_helpsrvrolemember ‘sysadmin’; List the permissions assigned to a server-level role… ?View Code TSQLEXEC sp_srvrolepermission ‘sysadmin’; Test […]

TSQL: Transparent Data Encryption exercise for 70-462

Here’s some TSQL for the WingTipToys2012 Transparent Data Encryption (TDE) exercise in the 70-462 training materials. ?View Code TSQLCREATE DATABASE WingTipToys2012; GO   USE WingTipToys2012; GO   USE [master]; GO   CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘SecretPa$$w0rd’; GO   CREATE CERTIFICATE server_cert WITH SUBJECT = ‘My DEK Certificate’; GO   USE WingTipToys2012; GO […]

TSQL: Partitioned table exercise for 70-462

Here’s some TSQL for the WingTipToys2012 table partitioning exercise in the 70-462 training materials. ?View Code TSQLCREATE DATABASE WingTipToys2012   USE WingTipToys2012; GO   CREATE PARTITION FUNCTION WTPartFunction(INTEGER) AS RANGE LEFT FOR VALUES (30, 60)   CREATE PARTITION SCHEME WTPartScheme AS PARTITION WTPartFunction TO (fgOne, fgTwo, fgThree);   CREATE TABLE toys ( column1 INTEGER NOT […]

Installing Analysis Service & Reporting Services from the command-line

Here’s just a few examples of installing Analysis Services and Reporting Services from the command-line. Install Analysis Services in multidimensional mode. ?View Code DOSD:\Setup.exe /q /ACTION=Install /FEATURES=AS /INSTANCENAME=ASMulti /ASSERVERMODE=MULTIDIMENSIONAL /ASSVCACCOUNT=NetworkService /ASSYSADMINACCOUNTS="CONTSO\Kim_Akers"/IACCEPTSQLSERVERLICENSETERMS Install Analysis Services in tabular mode. ?View Code DOSD:\Setup.exe /q /ACTION=Install /FEATURES=AS /INSTANCENAME=ASTabular /ASSERVERMODE=TABULAR /ASSVCACCOUNT=NetworkService /ASSYSADMINACCOUNTS="CONTSO\Kim_Akers"/IACCEPTSQLSERVERLICENSETERMS Install a Reporting Services instance (with the DB […]