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
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
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
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
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
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.
Last year I had a 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 a script that could be run in SQLCMD mode and setup mirroring
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
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
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