T-SQL Tuesday #53 – Why So Serious? No SQL Authentication 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 […]

Read More »

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

This entry is part 7 of 8 in the series Exam 70-458

This entry is part 7 of 8 in the series Exam 70-458  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 […]

Read More »

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

This entry is part 8 of 8 in the series Exam 70-458

This entry is part 8 of 8 in the series Exam 70-458  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 […]

Read More »

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

This entry is part 5 of 8 in the series Exam 70-458

This entry is part 5 of 8 in the series Exam 70-458  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 […]

Read More »

Exam 70-458 – Objective 6 – Load data

This entry is part 6 of 8 in the series Exam 70-458

This entry is part 6 of 8 in the series Exam 70-458  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 […]

Read More »

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. […]

Read More »

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 […]

Read More »

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

This entry is part 4 of 8 in the series Exam 70-458

This entry is part 4 of 8 in the series Exam 70-458  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) […]

Read More »

Exam 70-458 – Objective 3 – Implement high availability

This entry is part 3 of 8 in the series Exam 70-458

This entry is part 3 of 8 in the series Exam 70-458  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) […]

Read More »

Exam 70-458 – Objective 2 – Implement Security

This entry is part 2 of 8 in the series Exam 70-458

This entry is part 2 of 8 in the series Exam 70-458  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 […]

Read More »