Latest Blog Posts - Chris's SQL Blog - SQLServerCentralhttp://www.sqlservercentral.com/blogs/
The largest free SQL Server community.en-USDelayed Durability in the wild…http://www.sqlservercentral.com/blogs/chriss-sql-blog/2016/11/14/delayed-durability-in-the-wild/
Tue, 15 Nov 2016 00:02:15 UT/blogs/chriss-sql-blog/2016/11/14/delayed-durability-in-the-wild/0http://www.sqlservercentral.com/blogs/chriss-sql-blog/2016/11/14/delayed-durability-in-the-wild/#comments<h1>Background</h1>
<p>We have recently been working on large data migration project for one of our clients and thought I would share how Delayed Durability helped us overcome a performance issue when the solution was moved to the client&#8217;s Development domain.</p>
<p>I won&#8217;t go into details of the project or the finer detail of our proposed solution as I have plans to put some more content together for that but in short the migration of the data was to be run by a (large) number of BIML generated SSIS (Child) packages for each table to be migrated, derived from a meta data driven framework with each stage being run by a master package, all of which run by a MasterOfMaster packages.</p>
<p>To maximize throughput, utilise as much processing power as possible, reduce the time it would take to run the migration and control the flow we built a series of sequence containers each running it&#8217;s own collection of Child Packages. We built the framework in such a way that these could be run in parallel or linear and each master package could contain as many containers (no pun intended) of child packages as required. This also allowed us to handle the order that packages were run in, especially those with dependencies whilst keeping the potential for parallelising (is that a word? No idea but I like it) the whole process as much as possible. Leaving the MaxConcurrentExecutables property to -1 mean&#8217;t we could push the processing to run up to 10 packages at once due to the VM having 8 cores (on Integration, 4 cores on Development) and this <a href="https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.maxconcurrentexecutables.aspx">value of -1 allows the maximum number of concurrently running executables to equal the number of processors plus two</a>.</p>
<p>An small example of how the MasterOfMaster and a Master Package for a stage looked is shown below:</p>
<a href='https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/masterofmaster/'><img width="87" height="150" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/masterofmaster.png?w=87&#038;h=150" class="attachment-thumbnail size-thumbnail" alt="" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/masterofmaster.png?w=87&#038;h=150 87w, https://chrisjarrintaylor.files.wordpress.com/2016/11/masterofmaster.png?w=174 174w" sizes="(max-width: 87px) 100vw, 87px" data-attachment-id="554" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/masterofmaster/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/masterofmaster.png" data-orig-size="246,424" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="masterofmaster" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/masterofmaster.png?w=174" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/masterofmaster.png?w=246"></a>
<a href='https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/masterpackage/'><img width="150" height="127" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/masterpackage.png?w=150&#038;h=127" class="attachment-thumbnail size-thumbnail" alt="" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/masterpackage.png?w=150&#038;h=127 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/masterpackage.png?w=300 300w" sizes="(max-width: 150px) 100vw, 150px" data-attachment-id="555" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/masterpackage/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/masterpackage.png" data-orig-size="904,765" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="masterpackage" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/masterpackage.png?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/masterpackage.png?w=612"></a>
<p>Each container number could have Parallel and/or Linear Containers and both must succeed before the next Container level can start.</p>
<p><strong>NOTE</strong> that this is just an example representation, naming conventions shown do not reflect the actual solution.</p>
<h1>Problem</h1>
<p>During development and initial testing on our own hardware, we had the migration at the time running at ~25minutes for around 600 packages (ie. tables) covering (what we termed) RawSource&#8211;&gt;Source&#8211;&gt;Staging which was well within the performance requirements for the stage that development was at and for what was initially set out. The rest of this blog post will hone in specifically on Source&#8211;&gt;Staging only.</p>
<p>However, once we transferred the solution to the clients development environment things took a turn for the worse. In our environment we were running VMs with 8 cores, 16GB RAM and utlising SSDs. The client environment was running SQL Server 2016 Enterprise on VMWare vSphere 5.5, 8 vCPUs, 32GB RAM (for Integration, Development was half this) but the infrastructure team have done everything in their power to force all VMs onto the lower tier (ie. slow disks) of their 3-PAR SAN and throttle them in every way possible, just to make things more of a challenge. Even though the VM&#8217;s themselves were throttled we were confident that we wouldn&#8217;t see too much of a performance impact, especially as this was only a subset of the processing to be done so we needed it to be quick and it will only ever get longer and longer.</p>
<p>How wrong we were. On the first run the processing (for Source&#8211;&gt;Staging) took around 141 minutes, yes you read that right, a full <strong>116 minutes</strong> longer than the whole process took on our hardware! Wowza, didn&#8217;t see that one coming. I won&#8217;t delve too much into the investigations as again that will be saved for another blog post but essentially we were seeing a huge amount of the WRITELOG wait type since moving to the client environment. We believed the reason for this was due to the significant amount of parallel processing (running of SSIS packages in parallel loading to the same DB) we were doing and the SAN didn&#8217;t seem to be able to handle it. One other thing to note,<span style="color:#ff0000;"><a href="https://chrisjarrintaylor.co.uk/2016/11/14/why-would-you-never-use-ssis-fast-load/" target="_blank" rel="noopener noreferrer"> due to truncations not being flagged as error&#8217;s</a></span> in OLE DB Destination fast load data mode access, some of the packages that weren&#8217;t a direct copy where we knew the schema was exactly the same were run in non-fast load, ie row-by-row which puts additional stress on the system as a whole.</p>
<p>I will be blogging at a later date regarding how we managed to get everything running in fast load and handle the truncation via automated testing instead.</p>
<h1>Solution</h1>
<p>Enter Delayed Durability.</p>
<p>I won&#8217;t enter into too much detail regarding what this is or how it specifically works as this has been blogged by many others (<a href="http://www.sqlskills.com/blogs/paul/delayed-durability-sql-server-2014/" target="_blank" rel="noopener noreferrer">Paul Randal</a>, <a href="https://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014" target="_blank" rel="noopener noreferrer">Aaron Bertrand</a> to name just a couple) but my favourite description of delayed durability is comes from the msdn blogs and they refer to it as a &#8220;<a href="https://msdn.microsoft.com/en-gb/library/dn449490.aspx" target="_blank" rel="noopener noreferrer">lazy commit</a>&#8220;. Before you ask, yes we understood the issues of implementing such a change but the migration process was always a full drop and reload of the data so we didn&#8217;t care if we lost anything as we could simply run the process again.</p>
<p>Setting delayed durability at the database level we were able to control which Databases involved in the process we wished to have this without altering the BIML framework or code itself to handle it at the transaction level. By simply applying this to the Source and Staging databases we reduced the processing time from 141 minutes to 59 minutes. This wasn&#8217;t exactly perfect but shaving more than half the time off with one simple change and pushing the WRITELOG wait stat way way down the list was a great start.</p>
<p>As a side not, we have managed to get the processing from <strong>~59mins</strong> to <strong>~30mins</strong> without changing the VM/hardware configuration but I will leave that for another post.</p>
<h1>Proof</h1>
<p>When I first set out with this blog post it was only going to be a few paragraphs giving an insight into what we did however, I thought that all this would be pointless without some visualisation of the processing both before and after.</p>
<h2>Row-by-Row with no Delayed Durability</h2>
<p>We needed to get a baseline and where better to start than capturing the metrics through <a href="http://www.sentryone.com" target="_blank" rel="noopener noreferrer">SentryOne</a> and using <a href="http://sqlblog.com/blogs/adam_machanic/default.aspx" target="_blank" rel="noopener noreferrer">Adam Mechanic&#8217;s</a> <a href="http://sqlblog.com/blogs/adam_machanic/archive/tags/sp_5F00_whoisactive/default.aspx" target="_blank" rel="noopener noreferrer">spWhoIsActive </a>we can see what I was talking about with the WRITELOG wait stat:</p>
<p><img data-attachment-id="685" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/spwhoisactive_output/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/spwhoisactive_output.png?w=612" data-orig-size="602,194" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="spwhoisactive_output" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/spwhoisactive_output.png?w=612?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/spwhoisactive_output.png?w=612?w=602" class=" size-full wp-image-685 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/spwhoisactive_output.png?w=612" alt="spwhoisactive_output" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/spwhoisactive_output.png 602w, https://chrisjarrintaylor.files.wordpress.com/2016/11/spwhoisactive_output.png?w=150 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/spwhoisactive_output.png?w=300 300w" sizes="(max-width: 602px) 100vw, 602px"></p>
<p>Granted the wait time themselves was relatively low, these were apparent almost every time we hit F5 and running our wait stat scripts in was in the top 3. A sample of the processing indicating this wait stat can also be seen below:</p>
<p><img data-attachment-id="692" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/waitstats_writelog/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/waitstats_writelog.png?w=628&#038;h=167" data-orig-size="602,160" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="waitstats_writelog" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/waitstats_writelog.png?w=628&#038;h=167?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/waitstats_writelog.png?w=628&#038;h=167?w=602" class=" wp-image-692 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/waitstats_writelog.png?w=628&#038;h=167" alt="waitstats_writelog" width="628" height="167" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/waitstats_writelog.png 602w, https://chrisjarrintaylor.files.wordpress.com/2016/11/waitstats_writelog.png?w=150&amp;h=40 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/waitstats_writelog.png?w=300&amp;h=80 300w" sizes="(max-width: 628px) 100vw, 628px"></p>
<p>As stated previously, overall the Source&#8211;&gt;Staging process took 141 minutes and the overall processing from SentryOne PA was captured:</p>
<p><img data-attachment-id="697" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/so_fullprocess_output/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_output.png?w=612" data-orig-size="602,310" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="so_fullprocess_output" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_output.png?w=612?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_output.png?w=612?w=602" class=" size-full wp-image-697 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_output.png?w=612" alt="so_fullprocess_output" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_output.png 602w, https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_output.png?w=150 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_output.png?w=300 300w" sizes="(max-width: 602px) 100vw, 602px"></p>
<h2>Row-by-Row with Delayed Durability</h2>
<p>So when we ran the same process with Delayed Durability we can see straight away that the transactions/sec ramp up from ~7000 to ~12500. Top left shows without Delayed Durability, bottom left with Delayed Durability and right shows them side by side:</p>
<a href='https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/transsec_1/'><img width="150" height="96" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_1.png?w=150&#038;h=96" class="attachment-thumbnail size-thumbnail" alt="" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_1.png?w=150&#038;h=96 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_1.png 248w" sizes="(max-width: 150px) 100vw, 150px" data-attachment-id="675" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/transsec_1/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_1.png" data-orig-size="248,158" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="transsec_1" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_1.png?w=248" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_1.png?w=248"></a>
<a href='https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/transsec_2/'><img width="150" height="96" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_2.png?w=150&#038;h=96" class="attachment-thumbnail size-thumbnail" alt="" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_2.png?w=150&#038;h=96 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_2.png 234w" sizes="(max-width: 150px) 100vw, 150px" data-attachment-id="676" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/transsec_2/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_2.png" data-orig-size="234,149" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="transsec_2" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_2.png?w=234" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_2.png?w=234"></a>
<a href='https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/transsec_3_compare/'><img width="150" height="83" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_3_compare.png?w=150&#038;h=83" class="attachment-thumbnail size-thumbnail" alt="" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_3_compare.png?w=150&#038;h=83 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_3_compare.png?w=300 300w" sizes="(max-width: 150px) 100vw, 150px" data-attachment-id="677" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/transsec_3_compare/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_3_compare.png" data-orig-size="323,178" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="transsec_3_compare" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_3_compare.png?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/transsec_3_compare.png?w=323"></a>
<p>The overall process for Source&#8211;&gt;Staging took only 59 minutes. I&#8217;ve tried to capture the before/after in the image below, the highlighted section being the process running with Delayed Durability forced:</p>
<p><img data-attachment-id="703" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/so_fullprocess_withdd_output/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_withdd_output.png?w=612" data-orig-size="1854,830" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="so_fullprocess_withdd_output" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_withdd_output.png?w=612?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_withdd_output.png?w=612?w=612" class=" size-full wp-image-703 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_withdd_output.png?w=612" alt="SO_FullProcess_withDD_Output.png" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_withdd_output.png?w=612 612w, https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_withdd_output.png?w=1224 1224w, https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_withdd_output.png?w=150 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_withdd_output.png?w=300 300w, https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_withdd_output.png?w=768 768w, https://chrisjarrintaylor.files.wordpress.com/2016/11/so_fullprocess_withdd_output.png?w=1024 1024w" sizes="(max-width: 612px) 100vw, 612px"></p>
<p>You can see from this the drastic increase in Transactions/sec and reduction in Log Flushes.</p>
<p>Two package execution time examples (trust me that they are the same package) showing that with Delayed Durability the processing time was only 43% (166sec down to 72sec and 991sec to 424sec) ) of that without Delayed Durability set. Apologies for the poor image quality&#8230;.</p>
<a href='https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/example_logging_1/'><img width="612" height="28" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_1.png?w=612&#038;h=28" class="attachment-full size-full" alt="" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_1.png?w=612&#038;h=28?w=612&amp;h=28 612w, https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_1.png?w=612&#038;h=28?w=150&amp;h=7 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_1.png?w=612&#038;h=28?w=300&amp;h=14 300w, https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_1.png?w=612&#038;h=28 722w" sizes="(max-width: 612px) 100vw, 612px" data-attachment-id="715" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/example_logging_1/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_1.png?w=612&#038;h=28" data-orig-size="722,33" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="example_logging_1" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_1.png?w=612&#038;h=28?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_1.png?w=612&#038;h=28?w=612"></a>
<a href='https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/example_logging_2/'><img width="612" height="28" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_2.png?w=612&#038;h=28" class="attachment-full size-full" alt="" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_2.png?w=612&#038;h=28?w=612&amp;h=28 612w, https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_2.png?w=612&#038;h=28?w=150&amp;h=7 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_2.png?w=612&#038;h=28?w=300&amp;h=14 300w, https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_2.png?w=612&#038;h=28 728w" sizes="(max-width: 612px) 100vw, 612px" data-attachment-id="716" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/delayed-durability-in-the-wild/example_logging_2/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_2.png?w=612&#038;h=28" data-orig-size="728,33" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="example_logging_2" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_2.png?w=612&#038;h=28?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/example_logging_2.png?w=612&#038;h=28?w=612"></a>
<p>To me that is a huge reduction for such a simple change!</p>
<h1>Conclusion</h1>
<p>So should you go out and apply this to all your production databases right this second? No, of course you shouldn&#8217;t. We applied this change for to fix a very specific problem in an isolated environment and were willing to take the hit on losing data if the server crashed &#8211; are you, or more importantly your company willing to lose that data? I&#8217;m taking an educated guess that this will be a <strong>no</strong> but for certain situations and environments this configuration could prove to be very useful.</p>
<h1>Links</h1>
<ul>
<li><a href="https://msdn.microsoft.com/en-gb/library/dn449490.aspx" rel="nofollow">https://msdn.microsoft.com/en-gb/library/dn449490.aspx</a></li>
<li><a href="http://www.sqlskills.com/blogs/paul/delayed-durability-sql-server-2014/" rel="nofollow">http://www.sqlskills.com/blogs/paul/delayed-durability-sql-server-2014/</a></li>
<li><a href="https://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014" rel="nofollow">https://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014</a>
<ul>
<li>&#8230;&#8230;or use you preffered search engine and search for &#8220;SQL Server delayed durability&#8221;</li>
</ul>
</li>
<li><a href="https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.maxconcurrentexecutables.aspx" rel="nofollow">https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.maxconcurrentexecutables.aspx</a></li>
</ul>
<div class="wpcnt">
<div class="wpa wpmrec">
<span class="wpa-about">Advertisements</span>
<div class="u"> <div style="padding-bottom:15px;width:300px;height:250px;float:left;margin-right:5px;margin-top:0px">
<div id="atatags-26942-5ab084007a523">
<script type="text/javascript">
__ATA.cmd.push(function() {
__ATA.initSlot('atatags-26942-5ab084007a523', {
collapseEmpty: 'before',
sectionId: '26942',
width: 300,
height: 250
});
});
</script>
</div></div> <div style="padding-bottom:15px;width:300px;height:250px;float:left;margin-top:0px">
<div id="atatags-114160-5ab084007a55a">
<script type="text/javascript">
__ATA.cmd.push(function() {
__ATA.initSlot('atatags-114160-5ab084007a55a', {
collapseEmpty: 'before',
sectionId: '114160',
width: 300,
height: 250
});
});
</script>
</div></div></div>
<div id="crt-1623384172" style="width:300px;height:250px;display:none !important;"></div>
<script type="text/javascript">
(function(){var c=function(){var a=document.getElementById("crt-1623384172");window.Criteo?(a.parentNode.style.setProperty("display","inline-block","important"),a.style.setProperty("display","block","important"),window.Criteo.DisplayAcceptableAdIfAdblocked({zoneid:388248,containerid:"crt-1623384172",collapseContainerIfNotAdblocked:!0,callifnotadblocked:function(){a.style.setProperty("display","none","important");a.style.setProperty("visbility","hidden","important")}})):(a.style.setProperty("display","none","important"),a.style.setProperty("visibility","hidden","important"))};if(window.Criteo)c();else{if(!__ATA.criteo.script){var b=document.createElement("script");b.src="//static.criteo.net/js/ld/publishertag.js";b.onload=function(){for(var a=0;a<__ATA.criteo.cmd.length;a++){var b=__ATA.criteo.cmd[a];"function"===typeof b&&b()}};(document.head||document.getElementsByTagName("head")[0]).appendChild(b);__ATA.criteo.script=b}__ATA.criteo.cmd.push(c)}})();
</script> <div id="crt-1271186238" style="width:300px;height:250px;display:none !important;"></div>
<script type="text/javascript">
(function(){var c=function(){var a=document.getElementById("crt-1271186238");window.Criteo?(a.parentNode.style.setProperty("display","inline-block","important"),a.style.setProperty("display","block","important"),window.Criteo.DisplayAcceptableAdIfAdblocked({zoneid:837497,containerid:"crt-1271186238",collapseContainerIfNotAdblocked:!0,callifnotadblocked:function(){a.style.setProperty("display","none","important");a.style.setProperty("visbility","hidden","important")}})):(a.style.setProperty("display","none","important"),a.style.setProperty("visibility","hidden","important"))};if(window.Criteo)c();else{if(!__ATA.criteo.script){var b=document.createElement("script");b.src="//static.criteo.net/js/ld/publishertag.js";b.onload=function(){for(var a=0;a<__ATA.criteo.cmd.length;a++){var b=__ATA.criteo.cmd[a];"function"===typeof b&&b()}};(document.head||document.getElementsByTagName("head")[0]).appendChild(b);__ATA.criteo.script=b}__ATA.criteo.cmd.push(c)}})();
</script>
</div>
</div>Why would you never use SSIS Fast Load…?http://www.sqlservercentral.com/blogs/chriss-sql-blog/2016/11/14/why-would-you-never-use-ssis-fast-load/
Mon, 14 Nov 2016 22:25:19 UT/blogs/chriss-sql-blog/2016/11/14/why-would-you-never-use-ssis-fast-load/4http://www.sqlservercentral.com/blogs/chriss-sql-blog/2016/11/14/why-would-you-never-use-ssis-fast-load/#comments<p style="text-align:justify;">We all know that if you want SQL Server to push data into a table then you want to batch the inserts / use a bulk insert mechanism but is there a time when performance isn&#8217;t everything?</p>
<h1 style="text-align:justify;">Background</h1>
<p style="text-align:justify;">Although it has its critics, SSIS is a very powerful tool for Extracting, Transforming and ultimately Loading data from and to various systems. I kind of have a love / hate relationship with SSIS, I love it but it seemingly hates me with a passion.</p>
<p style="text-align:justify;">During a recent data migration project we had a series of packages using a stored procedure as the source and a SQL Server table as the destination. By using the OLE DB Destination task you have a series of options Data Access Modes which can provide various additional configurations. I won&#8217;t delve into all of these but have a look at the msdn link provided at the end for further information.</p>
<p style="text-align:justify;">The ones I want to concentrate on are:</p>
<ul>
<li>Table or view</li>
<li>Table or view &#8211; Fast Load</li>
</ul>
<p style="text-align:justify;">In short, fast load does exactly what it says on the tin, it loads data fast! This is because it is optimised for bulk inserts which we all know SQL Server thrives on, it isn&#8217;t too keen on this row-by-row lark.</p>
<h1 style="text-align:justify;">Problem</h1>
<p style="text-align:justify;">Now, I won&#8217;t be providing performance figures showing the difference between running a package in fast load compared to row-by-row, this has been done to death and it is pretty much a given (in most cases) that fast load will out perform row-by-row.</p>
<p style="text-align:justify;">What I do want to bring to your attention is the differences between the two when it comes to redirecting error rows, specifically rows that are truncated. One of the beauties of SSIS is the ability to output rows that fail to import through the error pipeline and push them into an error table for example. With fast load there is a downside to this, the whole batch will be output even if there is only 1 row that fails, there are ways to handle this and a tried and tested method is to push those rows into another OLE DB Destination where you can run them either in smaller batches and keep getting smaller or simply push that batch to run in row-by-row to eventually output the 1 error you want. Take a look at <a href="http://blog.in2bi.com/biml/creating-a-meta-data-driven-ssis-solution-with-biml-3-creating-a-table-package/">Marco Schreuder&#8217;s blog </a>for how this can be done.</p>
<p style="text-align:justify;">One of the issues we have exerienced in the past is that any truncation of a column&#8217;s data in fast load will not force the package to fail. What? So a package can succeed when in fact the data itself could potentially not be complete!?! Yes this is certainly the case, lets take a quick look with an example.</p>
<h2 style="text-align:justify;">Truncation with Fast Load</h2>
<h3 style="text-align:justify;">Setup</h3>
<p style="text-align:justify;">I have provided a script to setup a table where we can test this. I will attempt through SSIS to insert data which is both below and above 5 characters in length and show the output.</p>
<pre class="brush: sql; title: ; notranslate">
USE tempdb;
GO
DROP TABLE IF EXISTS dbo.TruncationTest;
DROP TABLE IF EXISTS dbo.TruncationTest_error;
CREATE TABLE dbo.TruncationTest
(
TruncationTestID INT IDENTITY(1,1),
TruncationTestDescription VARCHAR(5)
)
GO
CREATE TABLE dbo.TruncationTest_error
(
TruncationTestID INT,
TruncationTestDescription VARCHAR(1000) --Make sure we capture the full value
)
GO
</pre>
<p style="text-align:justify;">This code will set up 2 tables, one for us to import into (TruncationTest) and another to capture any error rows that we will output (TruncationTest_error).</p>
<p style="text-align:justify;">I set up a very quick and dirty SSIS package to run a simple select statement to output 3 rows and use the fast load data access mode:</p>
<pre class="brush: sql; title: ; notranslate">
SELECT ('123') AS TruncationTestDescription UNION ALL
SELECT ('12345') UNION ALL
SELECT ('123456789');
</pre>
<p style="text-align:justify;">The OLE DB Source Editor looks like this:</p>
<p style="text-align:justify;"><img data-attachment-id="794" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/why-would-you-never-use-ssis-fast-load/sourceeditor/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/sourceeditor.png?w=612" data-orig-size="837,721" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="sourceeditor" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/sourceeditor.png?w=612?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/sourceeditor.png?w=612?w=612" class="alignnone size-full wp-image-794" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/sourceeditor.png?w=612" alt="sourceeditor" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/sourceeditor.png?w=612 612w, https://chrisjarrintaylor.files.wordpress.com/2016/11/sourceeditor.png?w=150 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/sourceeditor.png?w=300 300w, https://chrisjarrintaylor.files.wordpress.com/2016/11/sourceeditor.png?w=768 768w, https://chrisjarrintaylor.files.wordpress.com/2016/11/sourceeditor.png 837w" sizes="(max-width: 612px) 100vw, 612px"></p>
<p style="text-align:justify;">the OLE DB Destination data access mode:</p>
<p style="text-align:justify;"><img data-attachment-id="797" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/why-would-you-never-use-ssis-fast-load/desteditor_fastload/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_fastload.png?w=612" data-orig-size="511,357" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="desteditor_fastload" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_fastload.png?w=612?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_fastload.png?w=612?w=511" class=" size-full wp-image-797 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_fastload.png?w=612" alt="desteditor_fastload" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_fastload.png 511w, https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_fastload.png?w=150 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_fastload.png?w=300 300w" sizes="(max-width: 511px) 100vw, 511px"></p>
<p style="text-align:justify;">Finally, this is how the package looks:</p>
<p style="text-align:justify;"><img data-attachment-id="800" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/why-would-you-never-use-ssis-fast-load/package_fastload/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload.png?w=612" data-orig-size="217,282" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="package_fastload" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload.png?w=612?w=217" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload.png?w=612?w=217" class="alignnone size-full wp-image-800 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload.png?w=612" alt="package_fastload" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload.png 217w, https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload.png?w=115 115w" sizes="(max-width: 217px) 100vw, 217px"></p>
<p style="text-align:justify;">Note the truncation warning. This is easy to see when viewing a package in Visual Studio, not so easy to pick up when you are dynamically generating packages using BIML.</p>
<p style="text-align:justify;">Let’s run it……</p>
<p style="text-align:justify;"><img data-attachment-id="804" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/why-would-you-never-use-ssis-fast-load/package_fastload_success/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload_success.png?w=612" data-orig-size="221,291" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="package_fastload_success" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload_success.png?w=612?w=221" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload_success.png?w=612?w=221" class=" size-full wp-image-804 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload_success.png?w=612" alt="package_fastload_success" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload_success.png 221w, https://chrisjarrintaylor.files.wordpress.com/2016/11/package_fastload_success.png?w=114 114w" sizes="(max-width: 221px) 100vw, 221px"></p>
<p>Great, 3 rows populated into the TruncationTest table, everything worked fine! So let’s check the data:</p>
<pre class="brush: sql; title: ; notranslate">
SELECT * FROM dbo.TruncationTest
</pre>
<p style="text-align:justify;"><img data-attachment-id="809" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/why-would-you-never-use-ssis-fast-load/results_1/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/results_1.png?w=306&#038;h=85" data-orig-size="277,77" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="results_1" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/results_1.png?w=306&#038;h=85?w=277" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/results_1.png?w=306&#038;h=85?w=277" class=" wp-image-809 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/results_1.png?w=306&#038;h=85" alt="results_1" width="306" height="85" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/results_1.png 277w, https://chrisjarrintaylor.files.wordpress.com/2016/11/results_1.png?w=150&amp;h=42 150w" sizes="(max-width: 306px) 100vw, 306px"></p>
<p style="text-align:justify;">Eh? What happened there???? Where’s my &#8216;6789&#8217; gone from row 3???</p>
<p style="text-align:justify;">From this example you can see that the package succeeds without error and it looks as though all rows have migrated entirely but by querying the data after the package has completed you can see that the description column has indeed been truncated.</p>
<p style="text-align:justify;">Let’s try the same test but changing the Data Access Mode to non-fast load (ie. Row-By-Row)</p>
<h2 style="text-align:justify;">Truncation with row-by-row</h2>
<p style="text-align:justify;">In this example you can see that the row with truncation is in fact pushed out to the error pipeline as you would hope and expect.</p>
<p><img data-attachment-id="813" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/why-would-you-never-use-ssis-fast-load/desteditor_nonfastload/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_nonfastload.png?w=612" data-orig-size="513,163" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="desteditor_nonfastload" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_nonfastload.png?w=612?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_nonfastload.png?w=612?w=513" class=" size-full wp-image-813 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_nonfastload.png?w=612" alt="desteditor_nonfastload" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_nonfastload.png 513w, https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_nonfastload.png?w=150 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/desteditor_nonfastload.png?w=300 300w" sizes="(max-width: 513px) 100vw, 513px"></p>
<p><img data-attachment-id="815" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/why-would-you-never-use-ssis-fast-load/package_nonfastload_success/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_nonfastload_success.png?w=612" data-orig-size="226,287" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="package_nonfastload_success" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_nonfastload_success.png?w=612?w=226" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_nonfastload_success.png?w=612?w=226" class=" size-full wp-image-815 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_nonfastload_success.png?w=612" alt="package_nonfastload_success" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/package_nonfastload_success.png 226w, https://chrisjarrintaylor.files.wordpress.com/2016/11/package_nonfastload_success.png?w=118 118w" sizes="(max-width: 226px) 100vw, 226px"></p>
<p>We now have 3 rows being processed but one row pushing out to the error pipeline which is what we would expect and hope for.</p>
<p>Let’s take a look at the output:</p>
<pre class="brush: sql; title: ; notranslate">
SELECT * FROM dbo.TruncationTest ORDER BY TruncationTestID
SELECT TruncationTestDescription FROM TruncationTest_error
</pre>
<p><img data-attachment-id="820" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/why-would-you-never-use-ssis-fast-load/results_2/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/results_2.png?w=612" data-orig-size="277,171" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="results_2" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/results_2.png?w=612?w=277" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/results_2.png?w=612?w=277" class=" size-full wp-image-820 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/results_2.png?w=612" alt="results_2" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/results_2.png 277w, https://chrisjarrintaylor.files.wordpress.com/2016/11/results_2.png?w=150 150w" sizes="(max-width: 277px) 100vw, 277px"></p>
<p>The results highlighted in red are those from the fast load, in green are the results from the row-by-row indicating that the error row was piped out to the error table.</p>
<h1 style="text-align:justify;">Solution(?)</h1>
<p style="text-align:justify;">You have a few different options here:</p>
<ol style="text-align:justify;">
<li>Not really care and push the data through in fast load and suffer the concequences</li>
<li>Run in row-by-row and suffer the performance hit</li>
<li>Amend the OLE DB Source Output to be the same length as the destination column and redirect error rows from there.</li>
<li>Probably loads of others involving conditional splits, derived columns and/or script tasks</li>
<li>Apply option #1 and make sure that relevant (automated or otherwise) testing is applied</li>
</ol>
<p style="text-align:justify;">During the recent data migration project we were involved in we chose option #5. The reasons for this are:</p>
<ol>
<li style="text-align:justify;">We wanted to keep the BIML framework, the code and the relevant mappings as simplistic as possible</li>
<li style="text-align:justify;">Performance was vital&#8230;.</li>
<li style="text-align:justify;">&#8230;..but more importantly was the validity of the data we were migrating</li>
</ol>
<p style="text-align:justify;">We already had a series of automated tests setup for each package we were running and table we were migrating and we had to add to this a series of additional automated tests to check that no data itself was being truncated.</p>
<p style="text-align:justify;">NOTE: Option #4 was also a very valid choice for us but due to the nature of the mapping between source and destination this was not something that was easily viable to implement.</p>
<p style="text-align:justify;">I will leave the how we implemented these test this for another blog post <img src="https://s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f642.png" alt="??" class="wp-smiley" style="height: 1em; max-height: 1em;"></p>
<h1 style="text-align:justify;">Conclusion</h1>
<p style="text-align:justify;">Taking a look at the error redirect in the OLE DB Destination we can clearly see that Truncation is greyed out and no option is provided so I have to assume that it simply isn&#8217;t an option to configure it here.</p>
<p><img data-attachment-id="836" data-permalink="https://chrisjarrintaylor.co.uk/2016/11/14/why-would-you-never-use-ssis-fast-load/errorredirect/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/errorredirect.png?w=612" data-orig-size="645,80" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="errorredirect" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/errorredirect.png?w=612?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2016/11/errorredirect.png?w=612?w=612" class=" size-full wp-image-836 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2016/11/errorredirect.png?w=612" alt="errorredirect" srcset="https://chrisjarrintaylor.files.wordpress.com/2016/11/errorredirect.png?w=612 612w, https://chrisjarrintaylor.files.wordpress.com/2016/11/errorredirect.png?w=150 150w, https://chrisjarrintaylor.files.wordpress.com/2016/11/errorredirect.png?w=300 300w, https://chrisjarrintaylor.files.wordpress.com/2016/11/errorredirect.png 645w" sizes="(max-width: 612px) 100vw, 612px"></p>
<p>I used to have a link to an article which mentions that truncation cannot be deemed an error in a bulk import operation via SSIS due to the mechanics of how it all works but for the life of me I cannot find it :(. I am hoping someone who reads this will be able to provide me with this but for now I will have to draw my own conclusions from this. The closest thing I can find is an answer from Koen Verbeeck (<a href="http://sqlkover.com/" target="_blank" rel="noopener noreferrer">b</a>|<a href="http://twitter.com/Ko_Ver" target="_blank" rel="noopener noreferrer">t</a>) in an <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/49efb33f-d43a-4243-be77-ed9a360f8e85/oledb-destination-fast-load-auto-truncation?forum=sqlintegrationservices" target="_blank" rel="noopener noreferrer">msdn forum question</a> where he states:</p>
<blockquote><p>The only thing you get is a warning when designing the package.</p>
<p>You get truncation errors when you try to put data longer than the column width in the data flow buffer, i.e. at the source or at transformations, but not at the destination apparently.</p></blockquote>
<p>What I still don&#8217;t understand is why in tSQL you will get an error when trying to &#8220;<em>bulk insert</em>&#8221; (loose sense of the term&#8230;&#8230;ie. using an INSERT&#8230;.SELECT) data that will truncate data but SSIS does not. Hopefully someone far cleverer than me will be able to shed some light on this!</p>
<p style="text-align:justify;">The idea behind this blog post was not to focus too much on the importance of testing any data that is moved from one place to another but I wanted to highlight how easy it is to believe that what you are migrating is all fine n dandy because the SSIS package told you so but in actual fact you could be losing some very very important data!!</p>
<p style="text-align:justify;">You have been warned <img src="https://s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f609.png" alt="??" class="wp-smiley" style="height: 1em; max-height: 1em;"></p>
<h1 style="text-align:justify;">Links</h1>
<ul>
<li style="text-align:justify;"><a href="https://msdn.microsoft.com/en-us/library/ms188439.aspx" rel="nofollow">https://msdn.microsoft.com/en-us/library/ms188439.aspx</a></li>
<li style="text-align:justify;"><a href="http://blog.in2bi.com/biml/creating-a-meta-data-driven-ssis-solution-with-biml-3-creating-a-table-package/" rel="nofollow">http://blog.in2bi.com/biml/creating-a-meta-data-driven-ssis-solution-with-biml-3-creating-a-table-package/</a></li>
<li style="text-align:justify;"><a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/49efb33f-d43a-4243-be77-ed9a360f8e85/oledb-destination-fast-load-auto-truncation?forum=sqlintegrationservices" rel="nofollow">https://social.msdn.microsoft.com/Forums/sqlserver/en-US/49efb33f-d43a-4243-be77-ed9a360f8e85/oledb-destination-fast-load-auto-truncation?forum=sqlintegrationservices</a></li>
</ul>
<div class="wpcnt">
<div class="wpa wpmrec">
<span class="wpa-about">Advertisements</span>
<div class="u"> <div style="padding-bottom:15px;width:300px;height:250px;float:left;margin-right:5px;margin-top:0px">
<div id="atatags-26942-5ab08400948d7">
<script type="text/javascript">
__ATA.cmd.push(function() {
__ATA.initSlot('atatags-26942-5ab08400948d7', {
collapseEmpty: 'before',
sectionId: '26942',
width: 300,
height: 250
});
});
</script>
</div></div> <div style="padding-bottom:15px;width:300px;height:250px;float:left;margin-top:0px">
<div id="atatags-114160-5ab084009490e">
<script type="text/javascript">
__ATA.cmd.push(function() {
__ATA.initSlot('atatags-114160-5ab084009490e', {
collapseEmpty: 'before',
sectionId: '114160',
width: 300,
height: 250
});
});
</script>
</div></div></div>
<div id="crt-815490236" style="width:300px;height:250px;display:none !important;"></div>
<script type="text/javascript">
(function(){var c=function(){var a=document.getElementById("crt-815490236");window.Criteo?(a.parentNode.style.setProperty("display","inline-block","important"),a.style.setProperty("display","block","important"),window.Criteo.DisplayAcceptableAdIfAdblocked({zoneid:388248,containerid:"crt-815490236",collapseContainerIfNotAdblocked:!0,callifnotadblocked:function(){a.style.setProperty("display","none","important");a.style.setProperty("visbility","hidden","important")}})):(a.style.setProperty("display","none","important"),a.style.setProperty("visibility","hidden","important"))};if(window.Criteo)c();else{if(!__ATA.criteo.script){var b=document.createElement("script");b.src="//static.criteo.net/js/ld/publishertag.js";b.onload=function(){for(var a=0;a<__ATA.criteo.cmd.length;a++){var b=__ATA.criteo.cmd[a];"function"===typeof b&&b()}};(document.head||document.getElementsByTagName("head")[0]).appendChild(b);__ATA.criteo.script=b}__ATA.criteo.cmd.push(c)}})();
</script> <div id="crt-2024610397" style="width:300px;height:250px;display:none !important;"></div>
<script type="text/javascript">
(function(){var c=function(){var a=document.getElementById("crt-2024610397");window.Criteo?(a.parentNode.style.setProperty("display","inline-block","important"),a.style.setProperty("display","block","important"),window.Criteo.DisplayAcceptableAdIfAdblocked({zoneid:837497,containerid:"crt-2024610397",collapseContainerIfNotAdblocked:!0,callifnotadblocked:function(){a.style.setProperty("display","none","important");a.style.setProperty("visbility","hidden","important")}})):(a.style.setProperty("display","none","important"),a.style.setProperty("visibility","hidden","important"))};if(window.Criteo)c();else{if(!__ATA.criteo.script){var b=document.createElement("script");b.src="//static.criteo.net/js/ld/publishertag.js";b.onload=function(){for(var a=0;a<__ATA.criteo.cmd.length;a++){var b=__ATA.criteo.cmd[a];"function"===typeof b&&b()}};(document.head||document.getElementsByTagName("head")[0]).appendChild(b);__ATA.criteo.script=b}__ATA.criteo.cmd.push(c)}})();
</script>
</div>
</div>Windows 10 Upgrade on Surface Pro 3 – Sending email issue FIXEDhttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2015/08/02/windows-10-upgrade-on-surface-pro-3-sending-email-issue-fixed/
Mon, 03 Aug 2015 03:44:38 UT/blogs/chriss-sql-blog/2015/08/02/windows-10-upgrade-on-surface-pro-3-sending-email-issue-fixed/0http://www.sqlservercentral.com/blogs/chriss-sql-blog/2015/08/02/windows-10-upgrade-on-surface-pro-3-sending-email-issue-fixed/#comments<p>I very rarely blogabout anything other than SQL Server but felt that with a lot of the SQL Community using SP3&#8217;s that this may actually be helpful to some.</p>
<p>After playing around in a VM for a while now I decided to upgrade my SP3 to Windows 10 which comes as a free upgrade. I was surprised that after making the schoolboy error of believing my charger was actually charging the unit during the upgrade and the battery running flat 30% through the upgrade, it fired back up and started off from that point with no issues whatsoever! I honestly thought that was it and it would be a factory reset.</p>
<p>I won’t go on about what’s changed, what I like and what I don’t like, you can read plenty of that around on the internet and it is out of the scope of this blog post.</p>
<p>However, I did discover one issue which I know several others have had when upgrading their SP3. After the upgrade I could no longer send emails via (IMAP) outlook. I could send via every other device so knew it was specific to the upgrade. I managed to find a couple of others on the <a href="http://forums.windowscentral.com/windows-10-pc-laptop/370056-after-installing-windows-10-i-can-no-longer-send-emails-outlook-2013-a.html">windows forums</a> with the same issue and they provided the solution which I thought I would share.</p>
<p>The fix is actually very simple, all you need to do is open a command prompt, run <em>sfc /scannow </em>and wait ~10mins.</p>
<p>The <a href="https://support.microsoft.com/en-gb/kb/929833">System File Checker</a> will scan your Windows system files for corruption and attempt to repair them, below is the output from my run:</p>
<p><a href="https://chrisjarrintaylor.files.wordpress.com/2015/08/sfc.jpg"><img data-attachment-id="450" data-permalink="https://chrisjarrintaylor.co.uk/2015/08/02/windows-10-upgrade-on-surface-pro-3-sending-email-issue-fixed/sfc/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2015/08/sfc.jpg" data-orig-size="494,285" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="sfc" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2015/08/sfc.jpg?w=300&#038;h=173" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2015/08/sfc.jpg?w=494" class="alignnone size-medium wp-image-450" src="https://chrisjarrintaylor.files.wordpress.com/2015/08/sfc.jpg?w=300&#038;h=173" alt="sfc" width="300" height="173" srcset="https://chrisjarrintaylor.files.wordpress.com/2015/08/sfc.jpg?w=300&amp;h=173 300w, https://chrisjarrintaylor.files.wordpress.com/2015/08/sfc.jpg?w=150&amp;h=87 150w, https://chrisjarrintaylor.files.wordpress.com/2015/08/sfc.jpg 494w" sizes="(max-width: 300px) 100vw, 300px"></a></p>
<p>I won’t go into details of the log file but the repaired file was related to the &#8220;Multilingual User Interface&#8221; (mui) files, which are translation files used to support different languages within windows. Mine in particular was mlang.dll.mui.</p>
<p>So, after a mild panic the fix was relatively straight forward……once I knew what the problem actually was!</p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/449/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/449/"></a> <img alt="" border="0" src="https://pixel.wp.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=449&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">SQLNorthEast Usergroup 2015 dates announced (preliminary)http://www.sqlservercentral.com/blogs/chriss-sql-blog/2015/01/13/sqlnortheast-usergroup-2015-dates-announced-preliminary/
Wed, 14 Jan 2015 04:40:31 UT/blogs/chriss-sql-blog/2015/01/13/sqlnortheast-usergroup-2015-dates-announced-preliminary/0http://www.sqlservercentral.com/blogs/chriss-sql-blog/2015/01/13/sqlnortheast-usergroup-2015-dates-announced-preliminary/#comments<p>Mike and I have been extremely busy over the Xmas period and we&#8217;ve finally sorted dates for our 2015 instalment of the SQLNorthEast SQL Server UserGroup (@SQLNE) in Newcastle. The great news is that after much negotiation we have managed to get agreement in principal to use the same venue for our events which is fantastic news!</p>
<p>Please see <a title="sqlne.com" href="http://www.sqlne.com" target="_blank">www.sqlne.com</a> for info on our next meeting and registration. Due to sqlpass website restrictions we cannot display all the dates for 2015 but a quick search on <a title="Eventbrite" href="https://www.eventbrite.co.uk/d/united-kingdom--newcastle/sql-server/" target="_blank">Eventbrite </a>will give you the relevant details.</p>
<p>The dates are as follows:</p>
<p style="padding-left:30px;">Feb: Tue 10th (with Chris Adkin &#8211; double session)<br>
March: Tue 24th (with Erin Stellato from SQLSkills and Peter Shaw)<br>
April: Tue 28th (with Neil Hambly)<br>
June: Tue 2nd (with Steve Powell)<br>
July: Tue 7th (with Annette Allen)<br>
Sept: Tue 8th (TBC)<br>
Nov: Tue 24th (TBC)</p>
<p>As you can see we have already lined up a number of fantastic speakers including a special remote session from the world class sqlskills Principal Consultant Erin Stellato!</p>
<p>2014 was a great year for us and due to the success of our second <a title="SQLRelay " href="http://www.sqlrelay.co.uk" target="_blank">SQLRelay </a>we&#8217;re going to be sending out a survey to find a bit more information about the needs and wants of our delegates to help us set our content for 2015. We have a very mixed bag of experience as well as SQL Server areas so any help or ideas we can get is a great help. If you wish to complete this now then you can find it at <a href="https://www.surveymonkey.com/s/5FBSSWZ" target="_blank">SurveyMonkey</a>.</p>
<p>If you have any thoughts or ideas regarding how we can improve then please get in touch via email or twitter and we&#8217;ll endeavour to incorporate it.</p>
<p style="padding-left:30px;"><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/423/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/423/"></a> <img alt="" border="0" src="https://pixel.wp.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=423&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">Merge csv files – quick PowerShell snippethttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2014/07/14/merge-csv-files-quick-powershell-snippet/
Tue, 15 Jul 2014 04:36:57 UT/blogs/chriss-sql-blog/2014/07/14/merge-csv-files-quick-powershell-snippet/2http://www.sqlservercentral.com/blogs/chriss-sql-blog/2014/07/14/merge-csv-files-quick-powershell-snippet/#comments<p><img data-attachment-id="933" data-permalink="https://chrisjarrintaylor.co.uk/2014/07/14/merge-csv-files-quick-powershell-snippet/csv/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2014/07/csv.png?w=142&#038;h=147" data-orig-size="776,800" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="csv" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2014/07/csv.png?w=142&#038;h=147?w=291" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2014/07/csv.png?w=142&#038;h=147?w=612" class=" wp-image-933 alignleft" src="https://chrisjarrintaylor.files.wordpress.com/2014/07/csv.png?w=142&#038;h=147" alt="csv" width="142" height="147" srcset="https://chrisjarrintaylor.files.wordpress.com/2014/07/csv.png?w=142&amp;h=147 142w, https://chrisjarrintaylor.files.wordpress.com/2014/07/csv.png?w=284&amp;h=294 284w, https://chrisjarrintaylor.files.wordpress.com/2014/07/csv.png?w=146&amp;h=150 146w" sizes="(max-width: 142px) 100vw, 142px">During a bit of work I&#8217;ve been doing this evening for <a href="http://www.sqlrelay.co.uk">SQLRelay</a>, I used something I have in my arsenal of PowerShell scripts which I thought I&#8217;d share because I love it&#8217;s simplicity. It&#8217;s nothing big and fancy but something that is extremely useful. Tasked with merging a large number of csv files there (as always) is a quick and easy way to do this with PowerShell:</p>
<pre class="brush: powershell; title: ; notranslate">
Get-ChildItem *.csv | ForEach-Object {Import-Csv $_} |
Export-Csv -NoTypeInformation WhateverYouWantToCallTheFile.csv
</pre>
<p><img data-attachment-id="930" data-permalink="https://chrisjarrintaylor.co.uk/2014/07/14/merge-csv-files-quick-powershell-snippet/posh_csv/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2014/07/posh_csv.png?w=612" data-orig-size="557,174" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="POSH_CSV" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2014/07/posh_csv.png?w=612?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2014/07/posh_csv.png?w=612?w=557" class=" size-full wp-image-930 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2014/07/posh_csv.png?w=612" alt="POSH_CSV" srcset="https://chrisjarrintaylor.files.wordpress.com/2014/07/posh_csv.png 557w, https://chrisjarrintaylor.files.wordpress.com/2014/07/posh_csv.png?w=150 150w, https://chrisjarrintaylor.files.wordpress.com/2014/07/posh_csv.png?w=300 300w" sizes="(max-width: 557px) 100vw, 557px"></p>
<p>There are ways to make this a little more dynamic which I will update the post with in the coming weeks&#8230;.</p>
<p>&nbsp;</p>
<p>&nbsp;</p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/419/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/419/"></a> <img alt="" border="0" src="https://pixel.wp.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=419&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">2013 in reviewhttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2013/12/31/2013-in-review/
Tue, 31 Dec 2013 18:19:51 UT/blogs/chriss-sql-blog/2013/12/31/2013-in-review/0http://www.sqlservercentral.com/blogs/chriss-sql-blog/2013/12/31/2013-in-review/#comments<p>The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.</p>
<p><a href="https://chrisjarrintaylor.co.uk/2013/annual-report/"><img alt="" src="https://i0.wp.com/www.wordpress.com/wp-content/mu-plugins/annual-reports/img/2012-emailteaser.png" width="100%"></a></p>
<p>Here&#8217;s an excerpt:</p>
<blockquote><p>The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about <strong>12,000</strong> times in 2013. If it were a concert at Sydney Opera House, it would take about 4 sold-out performances for that many people to see it.</p></blockquote>
<p><a href="https://chrisjarrintaylor.co.uk/2013/annual-report/">Click here to see the complete report.</a></p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/417/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/417/"></a> <img alt="" border="0" src="https://pixel.wp.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=417&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">Output SQL Server data from multiple tables to Tab Delimited text files using Powershellhttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2013/12/20/output-sql-server-data-from-multiple-tables-to-tab-delimited-text-files-using-powershell/
Fri, 20 Dec 2013 20:30:38 UT/blogs/chriss-sql-blog/2013/12/20/output-sql-server-data-from-multiple-tables-to-tab-delimited-text-files-using-powershell/3http://www.sqlservercentral.com/blogs/chriss-sql-blog/2013/12/20/output-sql-server-data-from-multiple-tables-to-tab-delimited-text-files-using-powershell/#comments<p><img data-attachment-id="937" data-permalink="https://chrisjarrintaylor.co.uk/2013/12/20/output-sql-server-data-from-multiple-tables-to-tab-delimited-text-files-using-powershell/tab/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2013/12/tab.png?w=178&#038;h=178" data-orig-size="512,512" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="tab" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2013/12/tab.png?w=178&#038;h=178?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2013/12/tab.png?w=178&#038;h=178?w=512" class=" wp-image-937 alignleft" src="https://chrisjarrintaylor.files.wordpress.com/2013/12/tab.png?w=178&#038;h=178" alt="tab" width="178" height="178" srcset="https://chrisjarrintaylor.files.wordpress.com/2013/12/tab.png?w=178&amp;h=178 178w, https://chrisjarrintaylor.files.wordpress.com/2013/12/tab.png?w=356&amp;h=356 356w, https://chrisjarrintaylor.files.wordpress.com/2013/12/tab.png?w=150&amp;h=150 150w, https://chrisjarrintaylor.files.wordpress.com/2013/12/tab.png?w=300&amp;h=300 300w" sizes="(max-width: 178px) 100vw, 178px">I had a request this morning for something I though was actually very simple:</p>
<p><strong>Client</strong>: &#8220;Can you extract all data for these particular tables including column headers to a tab delimited .txt file?&#8221;<br>
<strong>Chris</strong>: &#8220;Sure, no problem, I&#8217;ll just run bcp querying sys.tables using a COALESCE loop to output the statements&#8221;<br>
<strong>Client</strong>: &#8220;Top stuff, let me know when it&#8217;s done&#8221;</p>
<p>So, away I went generating my script which took a matter of minutes and run it&#8230;&#8230;.where&#8217;s the column headers? Bugger, forgot that bcp doesn&#8217;t output column headers without doing some funky stuff by creating a header record in a separate file and merging that with the file of data.</p>
<p>With this in mind I knew creating a SSIS package (or using export data to generate &#8211; very manual unless I delved into the realms of BIML) could do this but I thought I&#8217;d have a look at powershell invoking sqlcmd.</p>
<p>Again, this all seemed to be going very well until I came to outputting the data to a tab delimited .txt file. As far as I&#8217;m aware Powershell does not have an Export-Txt so I had to look into how I can use the Export-Csv to actualy output to .txt tab delimited as opposed to comma separated and found the parameter -delimiter &#8220;`t&#8221; &#8211; Excellent!!! Added this in and run the script&#8230;&#8230;&#8230;&#8230;and the first row consisted of &#8220;#TYPE System.Data.DataRow&#8221; &#8211; wft!?!?!?!?!</p>
<p>Quick search on my search engine of choice showed that there is a parameter that you can pass in to remove this from the export -NoTypeInformation.</p>
<p>Run it again with -NoTypeInformation and everything worked as expected apart from all column headers and data had quotes (&#8220;) around them which was not part of the requirement. Unfortunately (as far as I know) there is no switch, parameter or the likes that does this so I had to change the Export-Csv to ConvertTo-Csv and run a Replace on &#8216;&#8221;&#8216; with &#8221; which managed to do the trick.</p>
<p>I&#8217;ve included the script below which can be tailored to your needs:</p>
<pre class="brush: powershell; title: ; notranslate">
$server = 'ServerInstanceHere'
$database = 'DBNameHere'
$path = 'c:\work\ToDelete\'
$query = &quot;SELECT name FROM sys.tables WHERE name in (
'TableNameHere_1',
'TableNameHere_2'
--etc etc
)&quot;
$queryToOut = &quot;SELECT * FROM $TableName&quot;
#Get list of table names to output data
$Tables = invoke-sqlcmd -query $query -database $database -serverinstance $server
foreach ($Table in $Tables)
{
$TableName = $Table[&quot;name&quot;]
write-host -ForegroundColor Green &quot;Creating File $TableName.txt&quot;
invoke-sqlcmd -query $queryToOut -database $database -serverinstance $server | `
#Convert as opposed to Export to replace quotes if required
ConvertTo-Csv -NoTypeInformation -delimiter &quot;`t&quot; | `
ForEach-Object {$_ -Replace('&quot;','')} | `
Out-file $path$TableName.txt
#Export-Csv -NoTypeInformation -delimiter &quot;`t&quot; -path $path$TableName.txt
}
</pre>
<p>Apologies for the formatting but the powershell script tag doesn&#8217;t seem to format it the way I&#8217;m wanting it to so here is a screen shot of the code:</p>
<p><img data-attachment-id="939" data-permalink="https://chrisjarrintaylor.co.uk/2013/12/20/output-sql-server-data-from-multiple-tables-to-tab-delimited-text-files-using-powershell/posh_tab/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2013/12/posh_tab.png?w=612" data-orig-size="704,437" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" data-image-title="POSH_TAB" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2013/12/posh_tab.png?w=612?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2013/12/posh_tab.png?w=612?w=612" class="alignnone size-full wp-image-939 aligncenter" src="https://chrisjarrintaylor.files.wordpress.com/2013/12/posh_tab.png?w=612" alt="POSH_TAB" srcset="https://chrisjarrintaylor.files.wordpress.com/2013/12/posh_tab.png?w=612 612w, https://chrisjarrintaylor.files.wordpress.com/2013/12/posh_tab.png?w=150 150w, https://chrisjarrintaylor.files.wordpress.com/2013/12/posh_tab.png?w=300 300w, https://chrisjarrintaylor.files.wordpress.com/2013/12/posh_tab.png 704w" sizes="(max-width: 612px) 100vw, 612px"></p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/397/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/397/"></a> <img alt="" border="0" src="https://pixel.wp.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=397&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">SQL Server NorthEast – New Usergroup!!http://www.sqlservercentral.com/blogs/chriss-sql-blog/2013/11/19/sql-server-northeast-new-usergroup/
Wed, 20 Nov 2013 04:55:32 UT/blogs/chriss-sql-blog/2013/11/19/sql-server-northeast-new-usergroup/0http://www.sqlservercentral.com/blogs/chriss-sql-blog/2013/11/19/sql-server-northeast-new-usergroup/#comments<p>SQLBits in Nottingham was where it all began. A short conversation on whether there were any plans for a SQL usergroup in Newcastle with Richard Douglas (@SQLRich) and whether there was scope for me to begin setting one up quickly moved onto conversations with Chris Testa-O&#8217;Neill (@ctesta_oneill ) and eventually Jonathan Allen (@fatherjack). This became quite a lengthy chat regarding the ins, outs, ups, downs of setting up such a thing.</p>
<p>This was back in May. Four months of venue hunting, speaker negotiations and marketing led to the first ever SQL Usergroup in Newcastle &#8211; #sqlnortheast <img src="https://s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f642.png" alt="??" class="wp-smiley" style="height: 1em; max-height: 1em;"></p>
<p>The schedule was set up for Gavin Campbell and Neil Hambly to make their merry way up to the north east and give two fantastic sessions. So with venue sorted, speakers sorted, food sorted, attendees sorted, we were all set. Boooooom! Then the bombshell hits, a few days before the UG Neil anounces he can&#8217;t make it <img src="https://s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f626.png" alt="??" class="wp-smiley" style="height: 1em; max-height: 1em;"></p>
<p>With the first SQL UG in the north east hanging in the balance, up steps a very good friend of mine Chris McGowan (@ckwmcgowan), who was willing to make the trip from Manchester at such short notice and save the day! With 18 people regstered, this was about 17 more than I was expecting. With no initial indication as to the level of interest we could generate in the Northeast I was over the moon with the uptake. The integration between the group was phenominal and there was such a broad range of knowledge and skills ranging from hardcore sql internals DBA types to developers to Azure &#8211; made for great conversations.</p>
<p>So, September 3rd came along and surprisingly all seemed to be going well. Both speakers turned up on time, food turned up and most importantly 15 people turned up on the evening which was gobsmacking. All in all it turned out to be an extremely good evening / night. Few beers with everyone afterwards on the Quayside led to far too many beers with Chris and Gavin back at the hotel bar &#8211; wasn&#8217;t a pretty sight the next morning!</p>
<p>What an experience and from the excellent all round feedback received from the attendees, this is something that they hope will continue.</p>
<p>Michael Robson (@heymiky) and myself are currently trying to work out dates for next year and organise speakers. We&#8217;ve had a bit of a break from the UG due to taking on a leg of <a title="sqlrelay" href="http://www.sqlrelay.co.uk">sqlrelay</a> in Newcastle (November 25th) but we do have a &#8220;SQL on the Lash&#8221; evening session set up for December to end the year on a high.</p>
<p>I&#8217;ll be reporting back with the how it all goes with sqlrelay and with any further anouncements on dates for sqlnortheast UG in 2014.</p>
<p>Few pics from the session:</p>
<p><a href="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-6.jpg"><br>
</a> <a href="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-4.jpg"><img data-attachment-id="387" data-permalink="https://chrisjarrintaylor.co.uk/2013/11/19/sql-server-northeast-new-usergroup/photo-4/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-4.jpg?w=612" data-orig-size="2448,3264" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;}" data-image-title="photo (4)" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-4.jpg?w=612?w=225" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-4.jpg?w=612?w=612" class="aligncenter size-full wp-image-387" alt="photo (4)" src="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-4.jpg?w=612" srcset="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-4.jpg?w=612 612w, https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-4.jpg?w=1224 1224w, https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-4.jpg?w=113 113w, https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-4.jpg?w=225 225w, https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-4.jpg?w=768 768w" sizes="(max-width: 612px) 100vw, 612px"></a></p>
<p><a href="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg"><img data-attachment-id="388" data-permalink="https://chrisjarrintaylor.co.uk/2013/11/19/sql-server-northeast-new-usergroup/photo-5/" data-orig-file="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg?w=612" data-orig-size="3264,2448" data-comments-opened="1" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;}" data-image-title="photo (5)" data-image-description="" data-medium-file="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg?w=612?w=300" data-large-file="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg?w=612?w=612" class="aligncenter size-full wp-image-388" alt="photo (5)" src="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg?w=612" srcset="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg?w=612 612w, https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg?w=1224 1224w, https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg?w=150 150w, https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg?w=300 300w, https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg?w=768 768w, https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-5.jpg?w=1024 1024w" sizes="(max-width: 612px) 100vw, 612px"></a></p>
<p><a href="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-6.jpg"><img class="aligncenter" alt="photo (6)" src="https://chrisjarrintaylor.files.wordpress.com/2013/11/photo-6.jpg?w=497&#038;h=372" width="497" height="372"></a></p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/382/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/382/"></a> <img alt="" border="0" src="https://pixel.wp.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=382&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">Making sure your Triggers fire when they shouldhttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2013/03/04/making-sure-your-triggers-fire-when-they-should/
Mon, 04 Mar 2013 19:03:32 UT/blogs/chriss-sql-blog/2013/03/04/making-sure-your-triggers-fire-when-they-should/0http://www.sqlservercentral.com/blogs/chriss-sql-blog/2013/03/04/making-sure-your-triggers-fire-when-they-should/#comments<p>As some of you may be aware, triggers are not my favourite thing in the world but like most things, it does have its place.</p>
<p>Whilst onsite with one of my clients, one of the processes fires a trigger on insert which ultimately runs a SSRS subscription to email a report. All sounding fairly feasible so far. However, this process is also used as part of a batch process overnight which would run a separate insert statement (actually another stored procedure in another job step) instead of the &#8220;onDemand&#8221; insert. Ok, still doesn&#8217;t sound like too much of an issue.</p>
<p>Now, they started experiencing occasional failures of this job during the day with the error relating to the fact that the SSRS subscription job was being called when it already was running. Interesting, this in theory shouldn&#8217;t ever happen because the process either ran the jobs based on the batch process or the one off onDemand.</p>
<p>Stepping through the process, it led me to an AFTER INSERT trigger. Upon opening it I spotted the issue straight away. Something that as I&#8217;ve found over the years as a consultant, a lot of DBA&#8217;s and developers have failed to understand that (from <a title="MSDN" href="http://msdn.microsoft.com/en-us/library/ms189799(SQL.90).aspx">MSDN</a> ):</p>
<blockquote><p>These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.This is by design.</p></blockquote>
<p>So, the issue was that step 3 ran a procedure which ultimately ran an insert statement for the onDemand insert, step 4 ran a procedure to insert for the overnight batch process which as it happens doesn&#8217;t have any records to insert but will in fact fire the trigger to run the SSRS subscription again! There is a number of ways to fix this but I&#8217;ve tended to stick with a basic check of the &#8220;inserted&#8221; table for results and RETURN out if no records are there to process.</p>
<p>I&#8217;ve supplied a bit of test code below for people to try this out.</p>
<p>Lets create a test table and an audit table:</p>
<pre class="brush: sql; title: ; notranslate">
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable]
GO
CREATE TABLE [dbo].[TestTable]
(
TestTableID INT IDENTITY(1,1),
TestTableDescr VARCHAR(20)
)
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditTrigger]') AND type in (N'U'))
DROP TABLE [dbo].[AuditTrigger]
GO
CREATE TABLE [dbo].[AuditTrigger]
(
AuditTriggerID INT IDENTITY(1,1),
AuditTriggerDescr VARCHAR(20),
DateCreated DATETIME
)
GO
INSERT INTO dbo.TestTable (TestTableDescr)
VALUES ('Test1'), ('Test2'), ('Test3');
SELECT * FROM dbo.TestTable;
</pre>
<p>Now lets create the trigger with no checking:</p>
<pre class="brush: sql; title: ; notranslate">
USE [TempDB]
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trTestTable]'))
DROP TRIGGER [dbo].[trTestTable]
GO
CREATE TRIGGER [dbo].[trTestTable] ON [dbo].[TestTable]
AFTER INSERT
AS
BEGIN
--Log the fact the trigger fired
INSERT INTO [dbo].[AuditTrigger] (AuditTriggerDescr, DateCreated)
SELECT 'Trigger Fired', GETDATE()
END
GO
</pre>
<p>Test Inserting a record that exists:</p>
<pre class="brush: sql; title: ; notranslate">
--Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test1';
SELECT *
FROM [dbo].[AuditTrigger];
</pre>
<p>Test Inserting a record that doesn&#8217;t exist:</p>
<pre class="brush: sql; title: ; notranslate">
--Not a Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test4';
SELECT *
FROM [dbo].[AuditTrigger];
</pre>
<p>You&#8217;ll now see that there are 2 entries in the AuditTrigger table due to the fact that the trigger fired even though no records were actually valid to insert.</p>
<p>So, lets amend the trigger to check for valid inserts:</p>
<pre class="brush: sql; title: ; notranslate">
USE [TempDB]
GO
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trTestTable]'))
DROP TRIGGER [dbo].[trTestTable]
GO
CREATE TRIGGER [dbo].[trTestTable] ON [dbo].[TestTable]
AFTER INSERT
AS
BEGIN
--Check to see if any records were inserted
IF NOT EXISTS (SELECT 1 FROM INSERTED)
RETURN
--Log the fact the trigger fired
INSERT INTO [dbo].[AuditTrigger] (AuditTriggerDescr, DateCreated)
SELECT 'Trigger Fired', GETDATE()
END
GO
</pre>
<p>and test the inserts again:</p>
<p>Test Inserting a record that exists:</p>
<pre class="brush: sql; title: ; notranslate">
--Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test2';
SELECT *
FROM [dbo].[AuditTrigger];
</pre>
<p>Test Inserting a record that doesn&#8217;t exist</p>
<pre class="brush: sql; title: ; notranslate">
--Not a Valid Insert
INSERT INTO dbo.TestTable (TestTableDescr)
SELECT TestTableDescr
FROM dbo.TestTable
WHERE TestTableDescr = 'Test4';
SELECT *
FROM [dbo].[AuditTrigger];
</pre>
<p>No record will have been inserted with the final insert statement!</p>
<p>Lets clean up our tempdb:</p>
<pre class="brush: sql; title: ; notranslate">
USE [TempDB]
GO
--Clean up
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuditTrigger]') AND type in (N'U'))
DROP TABLE [dbo].[AuditTrigger]
GO
</pre>
<p>Hopefully this will help point out the misconception that triggers only fire when records are actually inserted <img src="https://s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f642.png" alt="??" class="wp-smiley" style="height: 1em; max-height: 1em;"></p>
<p>As per usual, I&#8217;d like to hear peoples thoughts/experiences on this topic.</p>
<div class="wpcnt">
<div class="wpa wpmrec">
<span class="wpa-about">Advertisements</span>
<div class="u"> <div style="padding-bottom:15px;width:300px;height:250px;float:left;margin-right:5px;margin-top:0px">
<div id="atatags-26942-5ab08400a58a8">
<script type="text/javascript">
__ATA.cmd.push(function() {
__ATA.initSlot('atatags-26942-5ab08400a58a8', {
collapseEmpty: 'before',
sectionId: '26942',
width: 300,
height: 250
});
});
</script>
</div></div> <div style="padding-bottom:15px;width:300px;height:250px;float:left;margin-top:0px">
<div id="atatags-114160-5ab08400a58df">
<script type="text/javascript">
__ATA.cmd.push(function() {
__ATA.initSlot('atatags-114160-5ab08400a58df', {
collapseEmpty: 'before',
sectionId: '114160',
width: 300,
height: 250
});
});
</script>
</div></div></div>
<div id="crt-2064025222" style="width:300px;height:250px;display:none !important;"></div>
<script type="text/javascript">
(function(){var c=function(){var a=document.getElementById("crt-2064025222");window.Criteo?(a.parentNode.style.setProperty("display","inline-block","important"),a.style.setProperty("display","block","important"),window.Criteo.DisplayAcceptableAdIfAdblocked({zoneid:388248,containerid:"crt-2064025222",collapseContainerIfNotAdblocked:!0,callifnotadblocked:function(){a.style.setProperty("display","none","important");a.style.setProperty("visbility","hidden","important")}})):(a.style.setProperty("display","none","important"),a.style.setProperty("visibility","hidden","important"))};if(window.Criteo)c();else{if(!__ATA.criteo.script){var b=document.createElement("script");b.src="//static.criteo.net/js/ld/publishertag.js";b.onload=function(){for(var a=0;a<__ATA.criteo.cmd.length;a++){var b=__ATA.criteo.cmd[a];"function"===typeof b&&b()}};(document.head||document.getElementsByTagName("head")[0]).appendChild(b);__ATA.criteo.script=b}__ATA.criteo.cmd.push(c)}})();
</script> <div id="crt-740114148" style="width:300px;height:250px;display:none !important;"></div>
<script type="text/javascript">
(function(){var c=function(){var a=document.getElementById("crt-740114148");window.Criteo?(a.parentNode.style.setProperty("display","inline-block","important"),a.style.setProperty("display","block","important"),window.Criteo.DisplayAcceptableAdIfAdblocked({zoneid:837497,containerid:"crt-740114148",collapseContainerIfNotAdblocked:!0,callifnotadblocked:function(){a.style.setProperty("display","none","important");a.style.setProperty("visbility","hidden","important")}})):(a.style.setProperty("display","none","important"),a.style.setProperty("visibility","hidden","important"))};if(window.Criteo)c();else{if(!__ATA.criteo.script){var b=document.createElement("script");b.src="//static.criteo.net/js/ld/publishertag.js";b.onload=function(){for(var a=0;a<__ATA.criteo.cmd.length;a++){var b=__ATA.criteo.cmd[a];"function"===typeof b&&b()}};(document.head||document.getElementsByTagName("head")[0]).appendChild(b);__ATA.criteo.script=b}__ATA.criteo.cmd.push(c)}})();
</script>
</div>
</div>DBCC CheckTable, Spatial Indexes and incorrect compatibility mode…..http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/11/19/dbcc-checktable-spatial-indexes-and-incorrect-compatibility-mode/
Tue, 20 Nov 2012 00:52:42 UT/blogs/chriss-sql-blog/2012/11/19/dbcc-checktable-spatial-indexes-and-incorrect-compatibility-mode/0http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/11/19/dbcc-checktable-spatial-indexes-and-incorrect-compatibility-mode/#comments<p>Just a very quick blog today regarding an issue that has arisen with one of my clients. During Integration it became apparent that one table in particular was failing during the weekly consistency checks, the error being output:</p>
<blockquote><p>DBCC results for &#8216;sys.extended_index_1696529623_384000&#8217;.</p>
<p>There are 313423 rows in 1627 pages for object &#8220;sys.extended_index_1696529623_384000&#8221;.</p>
<p>DBCC results for &#8216;schema.Table&#8217;.</p>
<p>There are 312246 rows in 12192 pages for object &#8220;schema.Table&#8221;.</p>
<p>Msg 0, Level 11, State 0, Line 0</p>
<p>A severe error occurred on the current command. The results, if any, should be discarded.</p>
<p>Msg 0, Level 20, State 0, Line 0</p>
<p>A severe error occurred on the current command. The results, if any, should be discarded.</p></blockquote>
<p>A bit of background. The server is running SQL Server 2008R2 SP1 CU2 and the database in question is still in compatibility 90 (SQL Server 2005). The table in question has a spatial index on a Geography column.</p>
<p>So, how do we fix this? Well there&#8217;s a couple of options.</p>
<ol>
<li>Change the compatibility to 100</li>
<li>Install SQL Server 2008R2 SP1 CU3&#8230;</li>
</ol>
<p>This is a documented issue (kb 2635827) and the fix can be found on<a title="Microsoft's Support Pages" href="http://support.microsoft.com/kb/2635827"> Microsoft&#8217;s Support Pages</a>.</p>
<blockquote><p>FIX: Access violation when you run a DBCC CHECKDB command against a database that contains a table that has a spatial index in SQL Server 2008 or in SQL Server 2008 R2</p></blockquote>
<p>As to which fix we deploy, well that&#8217;s for tomorrow&#8217;s fun and games <img src="https://s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f609.png" alt="??" class="wp-smiley" style="height: 1em; max-height: 1em;"></p>
<div class="wpcnt">
<div class="wpa wpmrec">
<span class="wpa-about">Advertisements</span>
<div class="u"> <div style="padding-bottom:15px;width:300px;height:250px;float:left;margin-right:5px;margin-top:0px">
<div id="atatags-26942-5ab08400ac2f1">
<script type="text/javascript">
__ATA.cmd.push(function() {
__ATA.initSlot('atatags-26942-5ab08400ac2f1', {
collapseEmpty: 'before',
sectionId: '26942',
width: 300,
height: 250
});
});
</script>
</div></div> <div style="padding-bottom:15px;width:300px;height:250px;float:left;margin-top:0px">
<div id="atatags-114160-5ab08400ac328">
<script type="text/javascript">
__ATA.cmd.push(function() {
__ATA.initSlot('atatags-114160-5ab08400ac328', {
collapseEmpty: 'before',
sectionId: '114160',
width: 300,
height: 250
});
});
</script>
</div></div></div>
<div id="crt-1707703881" style="width:300px;height:250px;display:none !important;"></div>
<script type="text/javascript">
(function(){var c=function(){var a=document.getElementById("crt-1707703881");window.Criteo?(a.parentNode.style.setProperty("display","inline-block","important"),a.style.setProperty("display","block","important"),window.Criteo.DisplayAcceptableAdIfAdblocked({zoneid:388248,containerid:"crt-1707703881",collapseContainerIfNotAdblocked:!0,callifnotadblocked:function(){a.style.setProperty("display","none","important");a.style.setProperty("visbility","hidden","important")}})):(a.style.setProperty("display","none","important"),a.style.setProperty("visibility","hidden","important"))};if(window.Criteo)c();else{if(!__ATA.criteo.script){var b=document.createElement("script");b.src="//static.criteo.net/js/ld/publishertag.js";b.onload=function(){for(var a=0;a<__ATA.criteo.cmd.length;a++){var b=__ATA.criteo.cmd[a];"function"===typeof b&&b()}};(document.head||document.getElementsByTagName("head")[0]).appendChild(b);__ATA.criteo.script=b}__ATA.criteo.cmd.push(c)}})();
</script> <div id="crt-2117688599" style="width:300px;height:250px;display:none !important;"></div>
<script type="text/javascript">
(function(){var c=function(){var a=document.getElementById("crt-2117688599");window.Criteo?(a.parentNode.style.setProperty("display","inline-block","important"),a.style.setProperty("display","block","important"),window.Criteo.DisplayAcceptableAdIfAdblocked({zoneid:837497,containerid:"crt-2117688599",collapseContainerIfNotAdblocked:!0,callifnotadblocked:function(){a.style.setProperty("display","none","important");a.style.setProperty("visbility","hidden","important")}})):(a.style.setProperty("display","none","important"),a.style.setProperty("visibility","hidden","important"))};if(window.Criteo)c();else{if(!__ATA.criteo.script){var b=document.createElement("script");b.src="//static.criteo.net/js/ld/publishertag.js";b.onload=function(){for(var a=0;a<__ATA.criteo.cmd.length;a++){var b=__ATA.criteo.cmd[a];"function"===typeof b&&b()}};(document.head||document.getElementsByTagName("head")[0]).appendChild(b);__ATA.criteo.script=b}__ATA.criteo.cmd.push(c)}})();
</script>
</div>
</div>Developing Microsoft SQL Server 2012 Databases (70-464) – My Thoughtshttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/10/30/developing-microsoft-sql-server-2012-databases-70-464-my-thoughts/
Wed, 31 Oct 2012 00:14:21 UT/blogs/chriss-sql-blog/2012/10/30/developing-microsoft-sql-server-2012-databases-70-464-my-thoughts/5http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/10/30/developing-microsoft-sql-server-2012-databases-70-464-my-thoughts/#comments<p>I finally pulled my finger out and took the last exam of the MCSE SQL Server 2012 &#8211; Data Platform certification last week and passed with a score of 876 which is pretty respectable in my opinion<img src="https://s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f609.png" alt="??" class="wp-smiley" style="height: 1em; max-height: 1em;"></p>
<p>The exam consisted of 3 sections:</p>
<li>1 &#8211; 6 Scenario based questions</li>
<li>2 &#8211; 7 Scenario based questions</li>
<li>3 &#8211; 32 Generic questions</li>
<p>
<p>So those of you who are clever enough to work out that&#8217;s a grand total of 45 questions. I&#8217;m pleased to say that once again the quality of the questioning was to Microsoft&#8217;s usual standard, not in terms of difficulty but more in terms of complete irrelevance! </p>
<p>I&#8217;m bound by NDA so can&#8217;t go into specifics but I&#8217;ll try and provide and example of this complete and utter irrelevance without giving the game away:</p>
<p>Q: What is your favourite holiday destination?</p>
<p>A (Select one of the following):</p>
<li>Green</li>
<li>Green with a bit of Yellow</li>
<li>Green with a bit of Blue</li>
<li>Sky Blue with Pink dots</li>
<p>
<p>Hmmmm, bit of a tricky one here. From what I can tell, none of the answers bear any relevance whatsoever to the question&#8230;&#8230;.hmmmmm&#8230;&#8230;..suppose I&#8217;ll have to take a random guess and hope that the answer I select is the one they&#8217;ve set as being correct!!!</p>
<p>It was just a good job i wasn&#8217;t on the threshold of pass/fail as this could potentially have been the difference. From the actual question, it was to be a simple answer but I suppose I&#8217;ll never know whether or not my random guess worked out or not<img src="https://s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f626.png" alt="??" class="wp-smiley" style="height: 1em; max-height: 1em;">. I just hope the comments I left regarding this issue are taken up and fixed so others don&#8217;t have the same issue.</p>
<p>All in all I felt comfortable throughout the exam but do know for a fact that my knowledge of Assemblies / CLR&#8217;s has slipped significantly and I need a refresher.</p>
<p>So that&#8217;s it. SQL Server 2012 MCSE done and dusted and no more exams&#8230;&#8230;well for the next 3 years at least. So whats next? Not sure, there is the MCSM exams but I have to be honest, I&#8217;ve got no formal certifications in SQL Server 2005/2008 but I know for a fact that I have far more knowledge and experience of its features than I do for SQL Server 2012 so I suppose I kind of backed up my original issue I have with these exams. Yes I studied and yes I did learn a lot from doing so but in no way shape or form would I class myself as an &#8220;Expert&#8221; in SQL Server 2012 &#8211; I don&#8217;t believe anyone could!</p>
<p>As per usual, any thoughts or comments are welcome.</p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/328/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/328/"></a> <img alt="" border="0" src="https://pixel.wp.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=328&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">Implementing a Data Warehouse with Microsoft SQL Server 2012 exam (70-463) – My Thoughtshttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/09/13/implementing-a-data-warehouse-with-microsoft-sql-server-2012-exam-70-463-my-thoughts/
Fri, 14 Sep 2012 04:45:28 UT/blogs/chriss-sql-blog/2012/09/13/implementing-a-data-warehouse-with-microsoft-sql-server-2012-exam-70-463-my-thoughts/1http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/09/13/implementing-a-data-warehouse-with-microsoft-sql-server-2012-exam-70-463-my-thoughts/#comments<p>Well I finally got around to completing the MCSA aspect of the SQL Server 2012 Certification and I&#8217;m pleased to say i passed with flying colours. As some of you may be aware I managed to nab and pass 3 of the Beta exams (70-461, 70-462 and 70-465) back in April and decided to see the MCSE through.</p>
<p>I really wasn&#8217;t sure how this exam was going to go as I&#8217;ve been working a lot recently with MDS 2012 and SSIS 2008 and revised the new 2012 features but went in with no real expectations. The exam consisted of 55 questions, again ranging from multiple guess, select the 3 things you&#8217;d do in order to a new feature i&#8217;ve not seen before and that is a drag n drop facility of a SSIS control flow which I thought was nifty. </p>
<p>The area I thought I&#8217;d struggle on was DQS but in fact found that aspect relatively simple, the difficult area for me was the &#8220;select the 3 things you&#8217;d do in order&#8221; relating to the new Project Deployment area of SSIS 2012. I&#8217;ve done a fair bit of &#8220;tinkering&#8221; with this over the last few months but its obvious I&#8217;m not as prolific as I thought as I found certain questions difficult to get my head around what it was suggesting in the answers. I obviously did ok in this area (according to the score sheet) but at the time i was sweating a bit.</p>
<p>Anyone wanting hints and tips, I obviously can&#8217;t go into detail but I&#8217;d definitely brush up on the new features of SSIS 2012!!! </p>
<p>Oh, and anyone wanting to know, the pass mark is 700 &#8211; none of the Beta exams told you this and I know some have said it was actually 800&#8230;&#8230;</p>
<p>Now onto 70-464 &#8211; Developing Microsoft SQL Server 2012 Databases, to complete the SQL Server 2012 MCSE certification!!!!</p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/314/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/314/"></a> <img alt="" border="0" src="https://pixel.wp.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=314&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">SSIS SCD vs MERGE Statement – Performance Comparisonhttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/07/03/ssis-scd-vs-merge-statement-performance-comparison/
Tue, 03 Jul 2012 23:24:54 UT/blogs/chriss-sql-blog/2012/07/03/ssis-scd-vs-merge-statement-performance-comparison/10http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/07/03/ssis-scd-vs-merge-statement-performance-comparison/#comments<p>I wouldn&#8217;t class myself as an expert in SSIS but I certainly know my way around but came across something today which I thought I&#8217;d share. As with a lot of things there are &#8220;many ways to skin a cat&#8221;, none of which is something I&#8217;ll go into at the moment but what i will concentrate on is updating columns in a table where the data has changed in the source.</p>
<p>One of the projects I&#8217;m currently working on requires this very process and when i set about doing so I created the T-SQL Merge statement to do the business. However, the question was raised as to why I didn&#8217;t use SSIS&#8217;s built in component Slowly Changing Dimension (SCD)? I didn&#8217;t really have an answer other than personal preference but decided to delve into it a bit further and compare the performance of each method.</p>
<p>As a test, I created a source table with a Key and Name column:</p>
<pre class="brush: sql; title: ; notranslate">
USE TempDB;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.iSource') AND type in (N'U'))
DROP TABLE dbo.iSource;
CREATE TABLE dbo.iSource
(
ID INT,
Name varchar(100)
);
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.iTarget') AND type in (N'U'))
DROP TABLE dbo.iTarget;
CREATE TABLE dbo.iTarget
(
ID INT,
Name varchar(100)
);
</pre>
<p>and populated it with some dummy data:</p>
<pre class="brush: sql; title: ; notranslate">
INSERT INTO dbo.iSource (ID,Name)
SELECT TOP 10000
ROW_NUMBER() OVER (ORDER BY t.object_id) AS rownumber
,'Name_'+convert(varchar(4),ROW_NUMBER() OVER (ORDER BY t.object_id))
FROM sys.tables t
CROSS JOIN sys.stats s;
INSERT INTO dbo.iTarget (ID,Name)
SELECT TOP 10000
ROW_NUMBER() OVER (ORDER BY t.object_id DESC) AS rownumber --Done in descending order
,'Name_'+convert(varchar(4),ROW_NUMBER() OVER (ORDER BY t.object_id))
FROM sys.tables t
CROSS JOIN sys.stats s;
SELECT ID, Name FROM iSource;
SELECT ID, Name FROM iTarget;
</pre>
<p>So we now have a source and target table with different Names and we&#8217;ll look to update the iTarget table with the information coming from iSource.</p>
<p><strong>Method 1 &#8211; MERGE Statement</strong></p>
<pre class="brush: sql; title: ; notranslate">
MERGE dbo.iTarget AS target
USING (
SELECT ID, Name
FROM dbo.iSource
) AS source (ID, Name)
ON (target.ID = source.ID)
WHEN MATCHED AND target.Name &lt;&gt; source.Name
THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (ID, Name)
VALUES (source.ID, source.Name);
</pre>
<p>Using this method simply in SSMS for simplicity, profiler output 2 rows for Batch Starting and Batch Completing, CPUTime of 125ms and <strong>Duration of 125ms</strong> and it updated 6678 records. Top stuff, as expected.</p>
<p><strong>Method 2 &#8211; SSIS SCD Component</strong><br>
I rebuilt the tables to put them back to where we started and set about creating the same thing in SCD setting ID as the business key and Name as the changing attribute and not setting inferred members, below is a screen dump of the outcome of this:</p>
<p><strong>BEFORE:</strong><br>
<a href="http://chrisjarrintaylor.files.wordpress.com/2012/07/scd_screengrab.png"><img src="http://chrisjarrintaylor.files.wordpress.com/2012/07/scd_screengrab.png?w=497" alt="" title="SCD_ScreenGrab" class="aligncenter size-full wp-image-301"></a></p>
<p>I clear down the profiler and run the ssis package and the outcome is quite astounding.</p>
<p><strong>DURING/AFTER:</strong><br>
<a href="http://chrisjarrintaylor.files.wordpress.com/2012/07/scd_screengrab_during.png"><img src="http://chrisjarrintaylor.files.wordpress.com/2012/07/scd_screengrab_during.png?w=497" alt="" title="SCD_ScreenGrab_During" class="aligncenter size-full wp-image-302"></a></p>
<p>The profiler output 13456 rows including 6678 rows of queries like this:</p>
<pre class="brush: sql; title: ; notranslate">exec sp_executesql N'SELECT [ID], [Name] FROM [dbo].[iTarget] WHERE ([ID]=@P1)',N'@P1 int',8</pre>
<p>as well as 6678 rows of queries similar to this:</p>
<pre class="brush: sql; title: ; notranslate">exec sp_execute 1,'Name_3304',3304</pre>
<p><strong>Total Duration of 37 seconds</strong> (yes that&#8217;s seconds not ms!!)&#8230;&#8230;.and this is on a table of only ~7k rows!</p>
<p>Well I&#8217;ll be damned, the SCD basically runs a cursor looping each record checking for a match on ID and updating that record if so. I can&#8217;t actually believe that MS have built a component which performs in this way.</p>
<p>So, to answer the question asked &#8221; why I didn&#8217;t use SSIS&#8217;s built in component Slowly Changing Dimension (SCD)?&#8221;, I now have a definitive answer, it doesn&#8217;t perform!</p>
<p>I&#8217;m sure SCD has its place but for me, the requirements and the datasets I&#8217;m working on I think I&#8217;ll stick with MERGE for now&#8230;.. <span class='wp-smiley wp-emoji wp-emoji-smile' title=':)'>:)</span></p>
<p>NOTE: This was done on SQL Server 2008R2 Developer Edition running on Windows 7 Ultimate, not sure if SQL Server 2012 has improved the SCD performance but I&#8217;ll leave that for another day.</p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/300/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/300/"></a> <img alt="" border="0" src="http://pixel.wp.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=300&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">It’s that time of year…..Exceptional DBA Awards 2012http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/06/25/its-that-time-of-yearexceptional-dba-awards-2012/
Tue, 26 Jun 2012 02:21:28 UT/blogs/chriss-sql-blog/2012/06/25/its-that-time-of-yearexceptional-dba-awards-2012/1http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/06/25/its-that-time-of-yearexceptional-dba-awards-2012/#comments<p>Being a 2011 finalist I felt I should try and rally all those who truly are exceptional to get their nominations in and quick sharp as the closing date is getting close.</p>
<p>I was lucky enough to be nominated for this award last year and wasn&#8217;t going to follow it through as I felt I didn&#8217;t really stand a chance but when I sat and thought about it, if someone is willing to think of you as being exceptional at what you do, enough so to nominate you then why not, what&#8217;s the worst that can happen!!??!!</p>
<p>The level of talent out the is phenomenal and the 4 guys I was up against last year are up there with the best in the world. Don&#8217;t let that put you off though, I feel that this award is very much focused towards those in the USA and not many actually make it through to the finals from the UK (Kevan Riley <a href="http://rileywaterhouse.co.uk/blog/" title="Blog" target="_blank">Blog</a> / <a href="https://twitter.com/#!/kevriley" title="Twitter" target="_blank">Twitter</a> and myself I think are the only two!) so I think we need to give a bigger push this year and try and get more than one finalist from the UK <span class='wp-smiley wp-emoji wp-emoji-smile' title=':)'>:)</span></p>
<p>If you haven&#8217;t been nominated by one of your peers then nominate yourself, there&#8217;s no rule saying you can&#8217;t and in fact Redgate encourage it.</p>
<p>Get entered, the questions answered and cross your fingers!</p>
<p>Good luck!!!!</p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/295/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/295/"></a> <img alt="" border="0" src="http://pixel.wp.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=295&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">And the results are in…..SQL Server 2012 beta examshttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/06/25/and-the-results-are-insql-server-2012-beta-exams/
Tue, 26 Jun 2012 02:09:29 UT/blogs/chriss-sql-blog/2012/06/25/and-the-results-are-insql-server-2012-beta-exams/6http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/06/25/and-the-results-are-insql-server-2012-beta-exams/#comments<p>Well after being a bit late in trying to book the beta exams I managed to get three of the five I needed for the MCSE data platform booked, taken and I&#8217;m pleased to say passed!</p>
<p>Unfortunately due to the fact I did 461, 462 and 465 means I don&#8217;t actually come away with any certification as I need 463 for the MCSA and then the 464 to complete the MCSE.</p>
<p>From what others in the field have said about 463, it&#8217;s very SSIS orientated which as it happens works out well for me as the project I&#8217;m currently working on is primarily SSIS so when things quieter down a bit I&#8217;ll look to get it booked. As for 464, I may very well look to batter that one out around the same time so it&#8217;s done n dusted.</p>
<p>I&#8217;ve never really been a big fan of Microsoft certification and my mindset hasn&#8217;t changed much. The questioning is still vague at times and in my opinion done in such a way that it&#8217;s a test of whether you can read a question and do what Microsoft believe is the best way to do things but I still don&#8217;t feel the questions always give enough information for you to give the best solution. In the real world there are a hell of a lot more questions I&#8217;d be asking in some of the scenarios before I could make a correct decision. </p>
<p>Anyway, enough whinging. Time to do some proper work <span class='wp-smiley emoji emoji-smile' title=':)'>:)</span></p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/293/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/293/"></a> <img alt="" border="0" src="http://stats.wordpress.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=293&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">Querying Microsoft SQL Server 2012 Beta exam (70-461 / 71-461) – My Thoughtshttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/04/13/querying-microsoft-sql-server-2012-beta-exam-70-461-71-461-my-thoughts/
Fri, 13 Apr 2012 18:30:30 UT/blogs/chriss-sql-blog/2012/04/13/querying-microsoft-sql-server-2012-beta-exam-70-461-71-461-my-thoughts/6http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/04/13/querying-microsoft-sql-server-2012-beta-exam-70-461-71-461-my-thoughts/#comments<p>Well I&#8217;ve now done the final SQL Server 2012 exam I managed to get a slot booked for. The Querying Microsoft SQL Server 2012 exam wasn&#8217;t going to be my strongest subject as I&#8217;m more of a DBA than Developer but i felt it went quite well.<br>
The exam consisted of 55 questions, varying in structure from multiple guess to drag n drop. There were only about 5 or 6 questions i left comments about relating to the content not being clear, typo&#8217;s or in one instance an actual mistake in the question so all in all a better setup than the Administrator exam I took first off (70-462 / 71-462). </p>
<p>The biggest issue I found was down to my own fault. I didn&#8217;t revise on the syntax of the new 2012 T-SQL functionality. Don&#8217;t get me wrong, I know i&#8217;ve got a lot of them right but with some, although I knew the answer was down to 2 of the 4, I didn&#8217;t know it well enough to be 100% certain as there was only 1 word different in the syntax which I&#8217;m a bit disappointed with&#8230;&#8230;..but no-one to blame but myself <img src="http://s0.wp.com/wp-includes/images/smilies/icon_smile.gif" alt=":)" class="wp-smiley"> </p>
<p>I&#8217;m still not sure whether the pass mark is 70% or 80% and hoping I&#8217;ve answered enough of the non-2012 questions correctly to scrape through.</p>
<p>As always, I’d be interesting to hear other peoples thoughts on any of the 2012 exams they&#8217;ve taken so far…..</p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/287/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/287/"></a> <img alt="" border="0" src="http://stats.wordpress.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=287&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">Designing Database Solutions for Microsoft SQL Server 2012 Beta exam (70-465 / 71-465) – My Thoughtshttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/04/06/designing-database-solutions-for-microsoft-sql-server-2012-beta-exam-71-465-my-thoughts/
Sat, 07 Apr 2012 01:37:58 UT/blogs/chriss-sql-blog/2012/04/06/designing-database-solutions-for-microsoft-sql-server-2012-beta-exam-71-465-my-thoughts/0http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/04/06/designing-database-solutions-for-microsoft-sql-server-2012-beta-exam-71-465-my-thoughts/#comments<p>After sitting the <a href="http://chrisjarrintaylor.co.uk/2012/04/04/administering-microsoft-sql-server-2012-databases-beta-71-462-my-thoughts/" target="_blank">Administering Microsoft SQL Server 2012 Databases Beta exam (71-462)</a> on Monday, I was still a little disappointed with Microsofts approach to questioning for these exams. So I went into this exam with pretty much the same mindset that the questions were going to be vague and in some cases completely wrong.</p>
<p>Much to my surprise, I found the questions in this exam far far better. The exam itself was split into sections. There were 44 in total, 26 the standard multiple choice and 5 further scenario based sections, each with either 3 or 4 questions. Section one was much the same as the 71-462 exam but I felt the questions were in the majority, more concise and in my opinion gave enough information to make a valid judgement when answering. I did leave a few comments as there were a few of the questions that could do with a bit more work and had a couple of typo&#8217;s.</p>
<p>The scenario sections again provided enough information to select the relevant answers, the only criticism of these sections were on question 2 of my second scenario, there was a major typo on the answers of question which didn&#8217;t pry me away from the answer but requires sorting.</p>
<p>Now, the main thing that really got me with this exam was the amount of SQL Azure questions in section one. Is was not mentioned as a skill measured so needs looking into in my opinion, either add it as a skill measured or remove it from the exam.</p>
<p>A much more enjoyable exam than the administrator, mainly due to the higher level of quality in the questioning resulting in far fewer comments being left and for me, I love the scenario based questions!!</p>
<p>As always, I&#8217;d be interesting to hear other peoples thoughts on any of the 2012 exams they’ve taken so far&#8230;..</p><br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/266/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/266/"></a> <img alt="" border="0" src="http://stats.wordpress.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=266&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">Administering Microsoft SQL Server 2012 Databases Beta (70-462 / 71-462) – My Thoughts…http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/04/04/administering-microsoft-sql-server-2012-databases-beta-71-462-my-thoughts/
Wed, 04 Apr 2012 14:00:00 UT/blogs/chriss-sql-blog/2012/04/04/administering-microsoft-sql-server-2012-databases-beta-71-462-my-thoughts/3http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/04/04/administering-microsoft-sql-server-2012-databases-beta-71-462-my-thoughts/#comments<p>On Monday I did my first Microsoft SQL Server exam since I did my SQL Server 2000 exams many moons ago and I can’t believe the quality of questioning hasn’t changed one bit! Don’t get me wrong, it wasn’t all plain sailing but for me, what made it difficult was the vagueness of the questions and in some cases, blatant mistakes! I understand the SQL Server 2012 exams are still in Beta but some of these errors are not simple spelling mistakes – although there were in fact quite a few typo’s in my exam.</p>
<p>As an example (without giving the game away), one question refers to SQL Server 2012 and SQL Server 2000, but the answers did not refer to SQL Server 2000 at all. I re-read the question over and over to see if I was missing something but I’m confident that it was a mistake in the question.</p>
<p>The exam itself consisted of 56 questions and I felt the allotted time was sufficient for this but what I didn’t find sufficient was the time allocated for leaving comments which from speaking to others in the field they&#8217;ve felt the same. During the exam I wrote comments down for around 25 of the questions in prep for leaving comments at the end but you’re only given 10 (or was it 15?) minutes to actually enter the comments into the system and with the machine (or could have been the application) being as slow as it was, this meant I had to type quicker than I’ve ever typed in my life <img src='http://s0.wp.com/wp-includes/images/smilies/icon_smile.gif' alt=':)' class='wp-smiley'> . </p>
<p>I didn’t get through all the comments so picked out the ones I felt needed highlighting the most and got those done first and managed to get through about 12 of them before the time ran out. I was a little disappointed with this as for me that time should be sufficient for examinees to relay their comments fully so Microsoft can take the points / mistakes on board and rectify them before they launch the live exams in June.</p>
<p>I was also a little surprised that I wasn’t given the test score there and then (have to wait until they actually go live), not quite sure why Microsoft have chosen to do it that way but I’m sure they have their reasons. I&#8217;m guessing its so people can&#8217;t relay an answers to others who are taking the exam but no reason was given so I can only speculate.</p>
<p>So, all in all a good test of your administrative knowledge but in my opinion there’s a lot of work to be done to rectify the issues before they go live. It would be interesting to hear other peoples thoughts on any of the 2012 exams they&#8217;ve taken so far&#8230;..</p>
<br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/252/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/252/"></a> <img alt="" border="0" src="http://stats.wordpress.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=252&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">How to output from invoke-sqlcmd to Powershell variablehttp://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/02/03/how-to-output-from-invoke-sqlcmd-to-powershell-variable/
Fri, 03 Feb 2012 22:30:13 UT/blogs/chriss-sql-blog/2012/02/03/how-to-output-from-invoke-sqlcmd-to-powershell-variable/1http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/02/03/how-to-output-from-invoke-sqlcmd-to-powershell-variable/#comments<p>Sorry for another Powershell post but I&#8217;ve been doing a lot of it recently and coming up with (what i think are) a few nifty tricks.</p>
<p>One of the issues I encountered recently was with Kerberos delegation whilst trying to automate Log Shipping. What I was trying to do was use an OPENROWSET query to run against the Primary and Secondary servers in order to obtain the Primary_id and Secondary_id in order to pass to the script to be ran on the monitor server. However, seeing as the environment was not setup for Kerberos I encountered the &#8220;double-hop&#8221; issue.</p>
<p>Enabling Kerberos delegation for the service account would be too high a risk without thorough testing so wasn&#8217;t an option in this instance so I decided to look into using invoke-sqlcmd against each of the servers to get the IDs required and pass it to the monitor script. </p>
<p>So how did I go about doing this you ask, well its actually really simple. After a bit of googling I came across this <a href="http://sqlblog.com/blogs/allen_white/archive/2009/08/14/a-couple-of-invoke-sqlcmd-issues.aspx" title="blog">blog by Allen White</a> which gave me a starting block.</p>
<p>Firstly, you have to amend your TSQL script to SELECT the parameter you want to output and use within the rest of the script, something like this:</p>
<p>TSQL snippet to be ran against the Primary Server:</p>
<p><pre class="brush: sql;">
--Cut down version of the script for readability
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N'$(Database)'
...
,@primary_id = @LS_PrimaryId OUTPUT --This is what we want
,@overwrite = 1
,@ignoreremotemonitor = 1
--Need to output this in order for powershell to take it and use it in the monitor script
SELECT @LS_PrimaryId as LS_PrimaryId
</pre></p>
<p>Do the same for the script to run on the secondary server but obviously for the secondary_id <img src='http://s0.wp.com/wp-includes/images/smilies/icon_smile.gif' alt=':)' class='wp-smiley'> </p>
<p>So, now you&#8217;ve setup the TSQL side of things, you need to then call these from Powershell and assign the output parameter to a Powershell variable like so:</p>
<p><pre class="brush: powershell;">
$script = &quot;LogShip_Primary.sql&quot;
$PrimaryID = Invoke-Sqlcmd -InputFile $ScriptLocation$script -Variable Database=$DatabaseName, etc etc etc -ServerInstance $PrimaryServer
$script = &quot;LogShip_Secondary.sql&quot;
$SecondaryID = Invoke-Sqlcmd -InputFile $ScriptLocation$script -Variable Database=$DatabaseName, etc etc etc -ServerInstance $SecondaryServer
</pre></p>
<p>So, relatively simple. Basically your setting the output to a Powershell variable. keeping things tidy, re-assign it to another variable and something to note is that the output is actually a DataTable object. Make sure you use the name of the alias you used in your last TSQL statement.</p>
<p><pre class="brush: powershell;">
$PID = $PrimaryID.LS_PrimaryId
$SID = $SecondaryID.LS_SecondaryId
</pre></p>
<p>Once this is done then you can use this in your script to run against the monitor server</p>
<p><pre class="brush: powershell;">
$script = &quot;LogShip_Monitor.sql&quot;
Invoke-Sqlcmd -InputFile $ScriptLocation$script -Variable Database=$DatabaseName, etc etc etc, PrimaryID=$PID, SecondaryID=$SID -ServerInstance $MonitorServer
</pre></p>
<p>And there you have it, nice n simple! All you then have to do is wrap it in a foreach loop for the databases you want to setup and a nice and simple automated logshipping build script.</p>
<p>Obviously I&#8217;ve omitted a lot of the setup / checking of scripts etc from this post as I don&#8217;t want to be doing all the work for you!</p>
<p>Enjoy <img src='http://s0.wp.com/wp-includes/images/smilies/icon_smile.gif' alt=':)' class='wp-smiley'> </p>
<br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/238/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/238/"></a> <img alt="" border="0" src="http://stats.wordpress.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=238&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">So then, what’s the definition of an object……..?http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/01/25/so-then-whats-the-definition-of-an-object/
Thu, 26 Jan 2012 01:30:26 UT/blogs/chriss-sql-blog/2012/01/25/so-then-whats-the-definition-of-an-object/3http://www.sqlservercentral.com/blogs/chriss-sql-blog/2012/01/25/so-then-whats-the-definition-of-an-object/#comments<p>Not blogged for a while due to client and project commitments but something which has surprised me when speaking with colleagues both past and present is that when I mention the built in function OBJECT_DEFINITION, the majority of DBA&#8217;s haven&#8217;t heard of it, never mind used it. So i felt it necessary to dust off the blog typing fingers and see if i can enlighten <img src='http://s0.wp.com/wp-includes/images/smilies/icon_smile.gif' alt=':)' class='wp-smiley'> </p>
<p>So, I though it be a good idea to enlighten a few people to how it can be used by giving real world examples.</p>
<p>Firstly, a short definition (no pun intended) from BOL (http://msdn.microsoft.com/en-us/library/ms176090.aspx) as to what exactly this function does:</p>
<blockquote><p>Returns the Transact-SQL source text of the definition of a specified object.</p></blockquote>
<p>Its as simple as that! </p>
<p>Pass in the Object_ID which it expects to be in the current database context and it spits out the text. I&#8217;ll show you a couple of examples of how it works in comparison to how I&#8217;ve seen the same thing done but by using sp_helptext as well as some of the other system tables.</p>
<p>I&#8217;ll not beat around the bush and get straight into a few examples and old skool alternatives as there&#8217;s not really much more i can say about the function itself:</p>
<p><strong>Example 1 &#8211; OBJECT_DEFINITION</strong><br>
<pre class="brush: sql;">SELECT OBJECT_DEFINITION(OBJECT_ID('usp_StoredProcedureName'))
</pre><br>
<br></p>
<p><strong>Example 2 &#8211; sp_helptext</strong><br>
<pre class="brush: sql;">EXEC sp_helptext 'usp_StoredProcedureName'
</pre><br>
<br></p>
<p><strong>Example 3 &#8211; Using system tables to search (this is a common way I&#8217;ve seen this done)</strong><br>
<pre class="brush: sql;">SELECT o.[name]
, o.type_desc
, sc.[text]
FROM sys.objects o
INNER JOIN syscomments sc ON o.[object_id] = sc.id
WHERE o.type_desc = 'SQL_STORED_PROCEDURE'
AND o.[name] = 'usp_StoredProcedureName'
</pre><br>
<br></p>
<p><strong>Example 4 &#8211; OBJECT_DEFINITION for multiple objects</strong><br>
<pre class="brush: sql;">SELECT [object_ID], [Name], OBJECT_DEFINITION([object_ID]) AS ProcText
FROM sys.procedures
</pre><br>
<br></p>
<p><strong>Example 5 &#8211; OBJECT_DEFINITION for multiple with filtering</strong><br>
<pre class="brush: sql;">SELECT [object_ID], [Name], OBJECT_DEFINITION([object_ID]) AS ProcText
FROM sys.procedures
WHERE OBJECT_DEFINITION([object_ID]) LIKE '%CATCH%'
</pre><br>
<br></p>
<p><strong>Example 6 &#8211; OBJECT_DEFINITION to Script out Procedures</strong><br>
<pre class="brush: sql;">
SET NOCOUNT ON;
DECLARE @strSQL NVARCHAR(MAX)
SET @strSQL = ''
SELECT @strSQL += OBJECT_DEFINITION([object_ID])+CHAR(10)+'GO'+CHAR(10)
FROM sys.procedures
SELECT @strSQL
</pre><br>
<br></p>
<p>Now this can be used for all programmability objects within SQL Server, not just procedures so the same works for Views, functions, triggers etc</p>
<p>Again from BOL here is a full list:</p>
<blockquote><p>C = Check constraint</p>
<p>D = Default (constraint or stand-alone)</p>
<p>P = SQL stored procedure</p>
<p>FN = SQL scalar function</p>
<p>R = Rule</p>
<p>RF = Replication filter procedure</p>
<p>TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)</p>
<p>IF = SQL inline table-valued function</p>
<p>TF = SQL table-valued function</p>
<p>V = View</p></blockquote>
<p>So there you have it, short n snappy blog today and I really hope that it helps give people a new insight into how to get object text.</p>
<br> <a rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/chrisjarrintaylor.wordpress.com/215/"><img alt="" border="0" src="http://feeds.wordpress.com/1.0/comments/chrisjarrintaylor.wordpress.com/215/"></a> <img alt="" border="0" src="http://stats.wordpress.com/b.gif?host=chrisjarrintaylor.co.uk&#038;blog=20428862&#038;post=215&#038;subd=chrisjarrintaylor&#038;ref=&#038;feed=1" width="1" height="1">