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