Tracy Boggiano's SQL Server  Blog Rotating Header Image

T-SQL Tuesday #53 – Why So Serious? No SQL Authenication Use Your Own Account

At a previous job I had several developers that had passwords for SQL authenticated accounts for systems that would give them more access than they needed when they had Windows accounts that would give them enough to troubleshoot issues.  Our trick was to write a login trigger that would block any SQL Authentication accounts that […]

Exam 70-458 – Objective 7 – Configure and deploy SSIS solutions

Troubleshoot data integration issues Performance issues Performance Counters (http://msdn.microsoft.com/en-us/library/ms137622.aspx) Connectivity issues Execution of a task or transformation failed Logic issues Demonstrate awareness of the new SSIS logging infrastructure (http://msdn.microsoft.com/en-us/library/ms140246.aspx) Troubleshoot a failed package execution to determine the root cause of failure Troubleshoot SSIS package failure from an invalid datatype Implement break points To see variables […]

Exam 70-458 – Objective 8 – Build Data Quality solutions

Install and maintain Data Quality Services Installation prerequisites (http://msdn.microsoft.com/en-us/library/gg492277.aspx#PreInstallationTasks) Server – Min 2 GB, Rec 4 GB+ RAM, SQL Server 2012 Database Engine Client – .NET Framework 4, IE 6.0 SP1+ Use Data Quality Server Installer Install Data Quality Services Run DQSInstaller.exe Add users to the DQ roles (http://msdn.microsoft.com/en-us/library/hh213045.aspx) IN DQS_MAIN database add to roles Identity […]

Exam 70-458 – Objective 5 – Extract and transform data

Design data flow Define data sources and destinations (http://technet.microsoft.com/en-us/library/ms140080%28v=sql.110%29.aspx) Distinguish blocking and non-blocking transformations Integration Services: Performance Tuning Techniques Understanding SSIS Data Flow Buffers (SQL Server Video) Use different methods to pull out changed data from data sources Change Data Capture Run sys.sp_cdc_enable_db on database then for each table run sys.sp_cdc_enable_table There are CDC transforms […]

Exam 70-458 – Objective 6 – Load data

Design control flow Determine control flow (http://msdn.microsoft.com/en-us/library/ms137681.aspx) Determine containers and tasks that are needed Tasks (http://msdn.microsoft.com/en-us/library/ms139892.aspx) Execute SQL, Execute Package, Execute process File System task Send mail task Task grouping Containers (http://msdn.microsoft.com/en-us/library/ms137728.aspx) Sequence (http://msdn.microsoft.com/en-us/library/ms139855.aspx) For loop (http://msdn.microsoft.com/en-us/library/ms139956.aspx) Foreach loop (http://msdn.microsoft.com/en-us/library/ms141724.aspx) Determine precedence constraints (http://msdn.microsoft.com/en-us/library/ms141261.aspx) Simple – Tasks execute in parallel by default Constraints and expressions […]

T-SQL Tuesday #051: Place Your Bets

This month’s T-SQL Tuesday is brought to us by Jason Brimhall.  We are writing about times someone took chances with the databases that the DBAs felt were not worth it.  I am going to talk about taking chances with your backups, our #1 job as DBAs is to have backups. Let’s start with the scenario. […]

Script to Setup SQL Server Database Mirroring for Multiple Databases

Last year I had project that involved using database mirroring between two servers that had 20+ user databases on them and setting those up one a time takes way too long.  I wrote this script to generate script that could be ran in SQLCMD mode and setup mirroring between the two servers.  The @newmirror variable […]

Exam 70-458 – Objective 4 – Design and implement a data warehouse

Design and implement dimensions Design shared/conformed dimensions Conformed dimensions have same meaning to each fact table (http://www.jamesserra.com/archive/2011/11/conformed-dimensions/) Determine whether you need support for slowly changing dimensions (http://msdn.microsoft.com/en-us/library/ms141715.aspx) If old values must be retained then you need this Determine attributes (http://msdn.microsoft.com/en-us/library/ms174760.aspx) Design hierarchies (http://msdn.microsoft.com/en-us/library/ms174935.aspx) Determine whether you need star or snowflake schema (http://dwhlaureate.blogspot.com/2012/06/difference-between-star-snowflake.html) Star schema Table […]

Exam 70-458 – Objective 3 – Implement high availability

Implement AlwaysOn Implement a mirroring solution using AlwaysOn Availability modes (http://technet.microsoft.com/en-us/library/ff877931.aspx) Asynchronous-commit mode Synchronous-commit mode Steps to perform Create mirroring endpoint (http://technet.microsoft.com/en-us/library/ff878259.aspx) Enable AlwaysON (http://technet.microsoft.com/en-us/library/ff878259.aspx) Create an availability group (http://technet.microsoft.com/en-us/library/ff878307.aspx) ALTER AVAILABILITY GROUP GroupA ADD DATABASE DB Create an availability group listener (http://msdn.microsoft.com/en-us/library/hh213080.aspx) Add a secondary replica (http://technet.microsoft.com/en-us/library/hh213078.aspx) ALTER DATABASE db SET HADR AVAILABILITY GROUP […]

Exam 70-458 – Objective 2 – Implement Security

Manage logins and server roles Configure server security (http://msdn.microsoft.com/en-us/library/bb283235.aspx) Secure the SQL Server using Windows Account/SQL Server accounts, server roles Server-Level Roles (http://msdn.microsoft.com/en-us/library/ms188659.aspx) Create log in accounts (http://msdn.microsoft.com/en-us/library/aa337562.aspx) Manage access to the server, SQL Server instance, and databases Database Roles (http://msdn.microsoft.com/en-us/library/ms189121.aspx) Create and maintain user-defined server roles (http://msdn.microsoft.com/en-us/library/ee677627.aspx) Manage certificate logins CREATE LOGIN Example D […]