Search results matching tags 'Tips' and 'DBA'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Tips,DBA&orTags=0Search results matching tags 'Tips' and 'DBA'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Squishy Limits in SQL Server Express Editionhttp://sqlblog.com/blogs/kevin_kline/archive/2013/03/28/squishy-limits-in-sql-server-express-edition.aspxThu, 28 Mar 2013 12:19:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48447KKline<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">It's an old story you've probably heard before. &nbsp;Provide a free version of your software product with strict limitations on performance or other specific capabilities so that folks can give it a try without risk, while you minimize the chance of&nbsp;cannibalizing&nbsp;sales of your commercial products. &nbsp;Microsoft has take this strategy with&nbsp;<a href="http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx">SQL Server Express Edition</a>, not only to increase adoption in the student market but also to counter the threat of open-source (i.e. free) relational databases like MySQL for entry-level applications.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">One such limitation of SQL Server Express Edition is that it supports no more than 1GB of RAM for the instance. &nbsp;Of course, you could have many Express Edition instances on a single Windows server, each with its own 1GB of RAM.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">But what does that metric of 1GB of RAM actually mean? &nbsp;The key thing to remember is that the restriction is for&nbsp;<em><strong>buffer</strong><strong>&nbsp;cache.&nbsp;</strong></em><strong>&nbsp;</strong>Since SQL Server has many other caches, even when not counting the plan cache, there are plenty of other caches within SQL Server. &nbsp;(Run a query against&nbsp;<em>sys.dm_os_memory_clerks</em>&nbsp;if you'd like to see some of the others). &nbsp;Because only the buffer cache has the strict 1GB limitation, you can actually watch SQL Server Express Edition's memory working set size grow to around 1.4-1.5GB due to the other memory caches at play.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Pawel Potasinski, a SQL Server MVP from Poland (<a href="http://twitter.com/pawelpotasinski">Twitter</a>&nbsp;|&nbsp;<a href="http://sqlgeek.pl/">Blog</a>), once&nbsp;<a href="http://sqlgeek.pl/2010/08/23/pl-sql-server-limity-w-sql-server-2008-r2-express-edition/">posted an interesting repro</a>&nbsp;for this behavior:</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;padding-left:30px;"><span style="font-family:Consolas, Monaco, monospace;font-size:12px;line-height:18px;">-- Assess amount of databases resident in buffer cache</span></p><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">SELECT
CASE
WHEN database_id = 32767 THEN 'mssqlsystemresource'
ELSE DB_NAME(database_id)
END AS [Database],
CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [MB in buffer cache]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC;
GO</pre><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">-- Assess amount of tables resident in buffer cache
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
QUOTENAME(OBJECT_NAME(p.object_id)) AS [Object],
CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [MB In buffer cache]
FROM sys.dm_os_buffer_descriptors AS d
INNER JOIN sys.allocation_units AS u ON d.allocation_unit_id = u.allocation_unit_id
INNER JOIN sys.partitions AS p ON (u.type IN (1,3) AND u.container_id = p.hobt_id) OR (u.type = 2 AND u.container_id = p.partition_id)
WHERE d.database_id = DB_ID()
GROUP BY QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' + QUOTENAME(OBJECT_NAME(p.object_id))
ORDER BY [Object] DESC;
GO</pre><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">-- Fill up Express Edition's buffer allocation
IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL
DROP TABLE dbo.test;
GO</pre><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">CREATE TABLE dbo.test (col_a char(8000));
GO</pre><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">INSERT INTO dbo.test (col_a)
SELECT REPLICATE('col_a', 8000)
FROM sys.all_objects
WHERE is_ms_shipped = 1;</pre><pre style="font-size:12px;line-height:18px;font-family:Consolas, Monaco, monospace;padding-left:30px;">CHECKPOINT;
GO 100</pre><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;padding-left:30px;"><em>&nbsp;The bottom line for the hard memory limit of SQL Server Express Edition is "Yes, it's limited. &nbsp;But it's a squishy limit. Not a hard limit."</em></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;"><span style="line-height:19px;">Although your mileage may vary, I'd bet a dollar that you'll find more than 1GB in the active working set for your instance of SQL Server Express Edition. &nbsp;I am curious, however, if you're seeing much variation between versions and even service packs of SQL Server? &nbsp;Let me know if you try this out on more than one version and/or service pack level of SQL Server. &nbsp;Did it change much between versions? &nbsp;Let me know!</span></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">Enjoy,</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">-Kevin</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;"><a href="http://twitter.com/kekline">-Follow me on Twitter!</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;"><a href="http://twitter.com/kekline"></a><br><a href="https://plus.google.com/u/1/113032055249023350257?rel=author">Google Author</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;font-size:13.333333969116211px;line-height:18.99305534362793px;">&nbsp;</p>Learn More About SQL Server IO and Query Tuning in These Webcastshttp://sqlblog.com/blogs/kevin_kline/archive/2012/12/14/learn-more-about-sql-server-io-and-query-tuning-in-these-webcasts.aspxFri, 14 Dec 2012 18:50:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:46662KKline
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I'm doing two new webcasts next week on Wednesday, December 19th, one in the morning and the other after lunch.</p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;</p>
<h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">SSDs are a Game Changer for SQL Server Storage</h2>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">No, session is not exclusively about SSDs. &nbsp;But this is my first session on IO and storage tuning that emphasizes SSDs over hard disks. &nbsp;As Bob Dylan said "Times, they are a'changin'". &nbsp;This session on Wednesday, December 19th at 11:30 AM EST, sponsored by Astute Networks, takes you through all of the basics of storage and IO tuning, regardless of the underlying storage technology. &nbsp;I'll show you how SQL Server handles storage structures, how to identify IO activity on Windows and SQL Server, and best practices for minimizing IO bottlenecks. &nbsp;Register now for:<a title="Kevin Kline's Storage IO Best Practices for SQL Server" href="http://bit.ly/UcXYI3">&nbsp;Storage IO Best Practices for SQL Server and a New Approach to Solving Application Performance Issues</a>.</p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;</p>
<h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">Write Better SQL Queries</h2>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">The next webcast on Wednesday, December 19th at 2 PM EST, is with me, Aaron Bertrand &nbsp;(<a href="https://twitter.com/#!/AaronBertrand">Twitter&nbsp;</a>|&nbsp;<a href="http://sqlblog.com/blogs/aaron_bertrand/rss.aspx">Blog</a>)&nbsp;and SQLCruise Impresario &amp; Microsoft MVP Tim Ford &nbsp;(<a href="https://twitter.com/#!/sqlagentman">Twitter</a>&nbsp;|&nbsp;<a href="http://www.ford-it.com/sqlagentman/">Blog</a>)&nbsp;as we take you through the query tuning process, discussing important DMVs to use during query tuning, as well as demonstrating several essential query tuning techniques that every SQL developer should know. &nbsp;Not only are we presenting an hour of top quality technical content, we’ll also be giving away some cool prizes, including the grand prize of a paid registration for the upcoming&nbsp;<a target="_blank" href="http://elink.sqlsentry.net/c/1/?aId=67857085&amp;requestId=b34612-273953cd-e600-4a18-979a-a9f2ded860bd&amp;rId=lead-a407ed107f65de119513001e0b614992-c233a49718324979b0d8efc0614ff5d0&amp;ea=aunefuonetre=pbz=vagrepreir&amp;dUrl=http%3A%2F%2Fsqlcruise.com%2F2013-cruises%3F_cldee%3DbmhhcnNoYmFyZ2VyQGludGVyY2VydmUuY29t&amp;uId=0">SQLCruise Miami</a>, a $1,395 value! &nbsp;Register now for:&nbsp;<a title="SQL Server Query Tuning Best Practices, Hosted by Kevin Kline, Aaron Bertrand, and Tim Ford" href="http://bit.ly/UskPPm">SQL Server Query Tuning Best Practices, Hosted by Kevin Kline and Aaron Bertrand with special guest Tim Ford</a></p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;</p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I hope to see you at both of these sessions next week! &nbsp;Best regards,</p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-Kev</p>
<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a title="Kevin E. Kline on Twitter" href="http://twitter.com/kekline">-Follow me on Twitter!</a></p>Quick Tip - Speed a Slow Restore from the Transaction Loghttp://sqlblog.com/blogs/kevin_kline/archive/2012/11/14/quick-tip-speed-a-slow-restore-from-the-transaction-log.aspxWed, 14 Nov 2012 15:59:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:46209KKline<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">Here's a quick tip for you:</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">During some restore operations on Microsoft SQL Server, the transaction log redo step might be taking an unusually long time. &nbsp;Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations. &nbsp;To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement. &nbsp;For example, if you set MAXTRANSFERSIZE=1048576, it'll use 1MB buffers.</p><div style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><div align="left">If you change the MAXTRANSFERSIZE, keep an eye on the PerfMon objects for Buffer Manager and Readahead IO. &nbsp;You may also wish to keep an eye on LOGBUFFER wait stats.</div><div align="left"><br></div><div align="left">I'd love to hear your feedback. &nbsp;Have you tried this technique? &nbsp;Did it work as advertised? &nbsp;Did it require some changes to work on a specific version or edition?</div></div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><br></div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">Many thanks,</div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><br></div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-Kev</div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><br></div><div align="left" style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-<a title="Kevin E. Kline's Twitter Feed" href="http://twitter.com/kekline">Follow me on Twitter!</a></div>The Zombie PerfMon Counter That Never Dies! Quick Tiphttp://sqlblog.com/blogs/kevin_kline/archive/2012/10/08/the-zombie-perfmon-counter-that-never-dies-quick-tip.aspxMon, 08 Oct 2012 11:55:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:45480KKline<h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"></h2><h2>The PerfMon Counters That Just Won't Die</h2><div style="font-size:13px;font-weight:normal;"><br></div><img class="alignright size-medium wp-image-2093" title="zombie-baby1" width="300" height="296" style="border:1px solid black;cursor:default;float:right;font-size:13px;font-weight:normal;margin:2px;" src="http://kevinekline.com/wp-content/uploads/2012/10/zombie-baby1-300x296.jpg"><div style="font-size:13px;font-weight:normal;">One of the things that's simultaneously great and horrible about the Internet is that once something gets posted out in the ether, it basically never goes away. &nbsp;(Some day, politicians will realize this. &nbsp;We can easily fact check their consistency). &nbsp;Because of longevity of content posted to the Internet, a lot of performance tuning topics become "zombies". &nbsp;We shoot 'em in dead, but they keep coming back!</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">In other words, those old recommendations&nbsp;<em>were&nbsp;</em>a suggested best practices for long ago, for a specific version of SQL Server, but are now inappropriately for the newer version. &nbsp;It's not uncommon for me, when speaking at a conference, to encounter someone who's still clinging to settings and techniques which haven't been good practice since the days of SQL Server 2000. &nbsp;Here's an example of&nbsp;<a href="http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx">Microsoft SQL Server 2000 Best Practices that are very version-specific</a>.</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">So here's an example. &nbsp;The %Disk Time counter and the Disk Queue Length were heavily recommended as a key performance indicator for IO performance. &nbsp;SQL Server throws a lot of IO at the disks using scatter/gather to maximize the utilization of the disk-based IO subsystem. &nbsp;This approach leads to short bursts of long queue depths during checkpoints and readaheads for an instance of SQL Server. &nbsp;Sometimes the server workload is such that your disk can't keep up with the IO shoved at it and when that happens, you'll see long queue lengths too.&nbsp; The short burst scenario isn't a problem. &nbsp;The lengthening queue length scenario usually is a problem. &nbsp;&nbsp;So is that a good practice?</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;"><strong>In a word, not-so-much.</strong></div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">Those counters can still be of some use on an instance of SQL Server which only has one hard disk drive. &nbsp;But that's&nbsp;<em>exceedingly</em>&nbsp;rare these days. &nbsp;Why?</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">The PerfMon counter %Disk time is a bogus performance metric for several reasons. &nbsp;It does not take into account&nbsp;asynchronous&nbsp;I/O requests. &nbsp;It can't tell what the real performance profile is for an underlying&nbsp;&nbsp;RAID set may be, since they contain multiple disk drives. &nbsp;The PerfMon counter Disk Queue Length is also mostly useless, except on SQL Server's with a single physical disk, because the hard disk controller cache obfuscates how many IO operations are actually pending on the queue or not. &nbsp;In fact, some hard disks even have tiny write caches as well, which further muddies the water was to whether the IO is truly queued, in a cache somewhere between the OS and the disk, or has finally made it all the way to the&nbsp;<a href="http://en.wikipedia.org/wiki/Cmos">CMOS</a>&nbsp;on the disk.</div><div style="font-size:13px;font-weight:normal;"><br></div><h2>Better IO PerfMon Counters</h2><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">Instead of using those PerfMon counters, use the Ave Disk Reads /sec, Avg Disk Write /sec, and Avg Disk &nbsp;Transfers/sec&nbsp;to track the performance of disk subsystems. &nbsp;These counters track the average number of read IOs, write IOs, and combined read and write IOs to occured in the last second. &nbsp;Occassionally, I like to track the same metrics by volume of data rather than the rate of IO operations. &nbsp;So, to get that data, you may wish to give these volume-specific PerfMon counters a try:&nbsp;Avg Disk &nbsp;Transfer Bytes/sec, Ave Disk Read Bytes /sec, and Avg Disk Write Bytes/sec</div><div style="font-size:13px;font-weight:normal;"><br></div><h2>For SQL Server IO Performance, Use Dynamic Management Views (DMV)</h2><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">And unless you've been living in a cave, you should make sure to use SQL Server's Dynamic Management Views (DMVs) to check on IO performance for recent versions of SQL Server. &nbsp;Some of my favorite DMV's for IO include:</div><div style="font-size:13px;font-weight:normal;"><ul><li>Sys.dm_os_wait_stats</li><li>Sys.dm_os_waiting_tasks</li><li>Sys.dm_os_performance_counters</li><li>Sys.dm_io_virtual_file_stats</li><li>Sys.dm_io_pending_io_requests</li><li>Sys.dm_db_index_operational_stats</li><li>Sys.dm_db_index_usage_stats</li></ul></div><div style="font-size:13px;font-weight:normal;">Many of these DMVs are fully document in this Books Online article here at&nbsp;<a href="http://msdn.microsoft.com/en-us/library/ms187974.aspx">Microsoft SQL Server 2012&nbsp;Index Related Dynamic Management Views and Functions</a>.</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">So how are you tracking IO performance metrics? &nbsp;Which ones are you using?</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">I look forward to hearing back from you!</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">Enjoy,</div><div style="font-size:13px;font-weight:normal;"><br></div><div style="font-size:13px;font-weight:normal;">-Kev</div><div style="font-size:13px;font-weight:normal;"><p>-<a href="http://twitter.com/kekline">Follow me on Twitter!</a></p><p>&nbsp;</p><p>&nbsp;</p><div><br></div></div>Two New Slide Decks. Plus, the Week in Colorado.http://sqlblog.com/blogs/kevin_kline/archive/2012/08/20/two-new-slide-decks-plus-the-week-in-colorado.aspxMon, 20 Aug 2012 15:03:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44792KKline<p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a href="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2488.jpg"><img class="alignright wp-image-2027" title="Kevin and the SpringSQL Leadership" alt="" width="240" height="143" style="border:0px;cursor:default;float:right;" src="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2488-300x179.jpg"></a>I had the honor of traveling the great state of Colorado last week, speaking at the PASS chapters in&nbsp;<a title="Boulder, CO SQL Server Users Group" href="https://groups.google.com/forum/?fromgroups#!forum/boulder-sql-server-users-group">Boulder</a>,&nbsp;<a title="Colorado Springs, CO SQL Server Users Group" href="http://www.springssql.sqlpass.org/">Colorado Springs</a>, and&nbsp;<a title="Denver, CO SQL Server Users Group" href="http://denver.sqlpass.org/">Denver</a>. &nbsp;At all three events, we had a stellar attendance and, at least&nbsp;<a title="A Huge Crowd for the Denver SQL Server User Group!" href="http://img.ly/m6ZG">in Denver, broke all the records</a>&nbsp;in recent memory both in terms of overall attendance and in first-timers. &nbsp;Denver, in fact, was standing room only and had nearly 30 first time attendees. &nbsp;Great news! &nbsp;I also want to give a special shout-out of thanks and appreciation to&nbsp;Chris Shaw (<a href="https://twitter.com/#!/SQLShaw">Twitter</a>&nbsp;|&nbsp;<a href="http://chrisshaw.wordpress.com/feed/">Blog</a>) whose hard work and tenacity ensured that all of Colorado got to see me speak. From left to right, Gabriel Villa (<a title="Gabriel Villa on Twitter" href="http://twitter.com/extofer">Twitter</a>), me, Chris Shaw, and Rebecca Mitchell (<a title="Rebecca Mitchell on Twitter" href="http://twitter.com/sqlprincess">Twitter</a>). &nbsp;If it weren't for Chris, I wouldn't have been there. &nbsp;Thanks for putting in the time, amigo!</p><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">New Slide Decks!</h2><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">During the 3-day jaunt, I presented two of my more popular sessions. &nbsp;These are updated slide decks, in case you want to download them here:</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a title="End-to-End Troubleshooting for Microsoft SQL Server" href="http://kevinekline.com/wp-content/uploads/2012/08/UG-End-to-End-Troubleshooting.zip">UG - End-to-End Troubleshooting</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">and</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a title="Top 10 DBA Blunders on Microsoft SQL Server" href="http://kevinekline.com/wp-content/uploads/2012/08/UG-Top-10-SQL-Server-Administration-Mistakes.zip">UG - Top 10 SQL Server Administration Mistakes</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a href="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2492.jpg"><img class="alignright wp-image-2033" title="Kevin &amp; Steve Murchie" alt="" width="125" height="210" style="border:0px;cursor:default;float:right;" src="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2492-179x300.jpg"></a>Be sure to check in the Slides area of the website, if you want to see the links for SpeakerRate, and in the case of several of my presentations, white papers, video recordings, etc. It's the People that Matter</p><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">A Blast from the SQLPASS Past!</h2><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I've always tried to maintain the relationships I built with the founding members of the&nbsp;<a title="The Professional Association for SQL Server" href="http://www.sqlpass.org/">PASS</a>&nbsp;board of directors. &nbsp;After their time on the PASS board, almost all of them have moved on from SQL Server to other adventures. &nbsp;Pam Smith, the first president of the organization, is now a professor. &nbsp;Guy Brown, the second president, is now the director of IT at his same employer, rather than just SQL Server as when he was on the PASS board. &nbsp;A few, such as Kurt Windisch, a former VP of PASS, and my good friend&nbsp;Joe Webb (<a href="https://twitter.com/#!/joewebb">Twitter</a>&nbsp;|&nbsp;<a target="_blank" href="http://www.webbtechsolutions.com/blog">Blog</a>), are still active in the SQL Server space. &nbsp;One relationship that I've enjoyed over the years is with PASS' original Microsoft liaison and now a Denver-area software entrepreneur Steve Murchie (at right) running his own healthcare IT outfit. &nbsp;Steve has been a source of inspiration to me and also of great advice for all things startup-related. &nbsp;It was great to connect with Steve and catch up on his latest doings.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I also got to enjoy an evening out with the local attendees after the Denver user group meeting. &nbsp;It was great to hang out with folks there. &nbsp;I got to meet&nbsp;<a title="Kevin Cox on deck for 24HOP of SQLPASS.ORG" href="http://www.sqlpass.org/24hours/fall2012/SessionsbySchedule/SpeakerDetails.aspx?spid=480">Kevin Cox</a>&nbsp;(<a title="Kevin Cox's Twitter Feed" href="http://twitter.com/KevinCoxSQL">twitter</a>), a member of Microsoft's incredibly talented&nbsp;<a title="The Microsoft SQL Server Customer Advisory Team" href="http://www.sqlcat.com/">SQLCAT</a>&nbsp;group, and for whom I was a technical editor on a SQL Server v6.5 book back in the Neanderthal era. &nbsp;That shows just how old both Kevin and I actually are. &nbsp;Other cool folks that I got to meet included&nbsp;<a href="http://twitter.com/stevewake">Steve Wake</a>,&nbsp;<a href="http://twitter.com/mike_fal">Mike Fal</a>,&nbsp;<a href="http://twitter.com/marcbeacom">Marc Beacom</a>,&nbsp;<a href="http://twitter.com/jasonkassay">Jason Kassay</a>,&nbsp;<a href="http://twitter.com/jasonhorner">Jason Horner</a>&nbsp;and my ol' buddy,&nbsp;<a href="http://twitter.com/greeleygeek">Kelly the Greeley Geek</a>.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">On top of that, long-time SQL Server MVP and all-around awesome guy&nbsp;Steve Jones (<a href="https://twitter.com/#!/way0utwest">Twitter</a>&nbsp;|&nbsp;<a href="http://feeds.feedburner.com/sqlmusings">Blog</a>) visited. &nbsp;I kept him out way too late that night which, to be honest, isn't usually my style. &nbsp;But the good conversation propelled us on past midnight.</p><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">The Good Folks at SQL Server Professional and Windows IT Professional Magazines</h2><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;"><a href="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2486.jpg"><img class="alignright wp-image-2036" title="Kevin and the Ladies of SQLMag" alt="" width="240" height="143" style="border:0px;cursor:default;float:right;" src="http://kevinekline.com/wp-content/uploads/2012/08/IMAG2486-300x179.jpg"></a>I've written for SQL Server Professional (formerly the artist known as "SQLMag") in some form or another starting from my first cover article for them way back in the mid 1990's. &nbsp;My&nbsp;<a title="Kevin Kline's Tool Time column at SQL Server Professional Magazine" href="http://www.sqlmag.com/blogcontent/seriespath/tool-time-blog-16">Tool Time column</a>&nbsp;has been going strong there since, oh, around 2006 iirc. &nbsp;For most of the time I've known the folks at SQLMag, they were located in Loveland, CO but they were able to move to some incredibly nice digs just up the road in Fort Collins. &nbsp;In all the many years, I've written for them, I'd never been to their offices - until now. &nbsp;It was great to visit and break bread with Megan (to my right), Blair (across), and Jaylee (across and to my right)!</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I've always supported SQLMag and encourage you to subscribe. &nbsp;On top of the goodness already in the digital magazine, there are some neat developments coming down the pipeline with SQLMag which I think we'll all enjoy. &nbsp;Be sure to subscribe today! &nbsp;(You can click the badge on the left or simply go to&nbsp;<a title="SQL Server Professional Magazine" href="http://www.sqlmag.com/">http://www.sqlmag.com</a>).</p><h2 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;What's Next?</h2><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">My current travel schedule is pinned up on&nbsp;<a title="Kevin Kline's appearance schedule" href="http://kevinekline.com/2012/07/31/come-see-me-ill-probably-be-just-down-the-street-soon/">this blog post HERE</a>. &nbsp;However, I also know of a couple on-line appearances and probably a trip between the long gap between now and my next in-person appearance at the&nbsp;<a title="Orlando SQL Saturday 151" href="http://www.sqlsaturday.com/151/eventhome.aspx">Orlando SQL Saturday</a>&nbsp;at the end of&nbsp;September, where I'll also be teaching a pre-conference seminar (<a title="SQL Server Configuration and Tuning Seminar" href="http://www.eventbrite.com/event/3895236758?ref=ebtn">register HERE for the seminar</a>).</p><h3 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">SSWUG</h3><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">The first on-line event to note is my a presentation by&nbsp;<a title="SQL Server Worldwide User Group" href="http://www.sswug.org/">SSWUG</a>&nbsp;of my&nbsp;<a title="Kevin Kline and SSWUG bring you &quot;Leadership Skills for IT Professionals&quot;" href="http://www.vconferenceonline.com/event/home.aspx?id=769">Leadership Skills for IT Professionals video series</a>, starting on August 24th. &nbsp;Sign up using the hyperlink (note that a video plays immediately upon loading the webpage, in case you want to be ready to pause or stop it). &nbsp;You can also buy a DVD set of the 14 hours of leadership training content.</p><h3 style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">24HOP - The 24 Hours of PASS Event</h3><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">I'll also be speaking on the topic of influence in the next&nbsp;<a title="The 2012 24 Hours of PASS session schedule" href="http://www.sqlpass.org/24hours/fall2012/SessionsbySchedule.aspx">24 Hours of PASS coming up on September 20th and 21st</a>. &nbsp;Registration for the twenty-four hours of around the clock presentations is completely free and well worth your time. &nbsp;Check the schedule for the event and register! &nbsp;Even if you can only watch one or two sessions (or even zero sessions), be sure to register so that you'll automatically be notified when the sessions become available as streaming media.</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">Enjoy!</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-Kev</p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">-Follow me on&nbsp;<a title="Kevin Kline on Twitter" href="http://twitter.com/kekline">Twitter</a>,&nbsp;<a title="Kevin Kline on LinkedIn" href="http://linkedin.com/kekline">LinkedIn</a>, and&nbsp;<a title="Kevin Kline on Facebook" href="http://facebook.com/kekline">Facebook</a></p><p style="font-family:Georgia, 'Times New Roman', 'Bitstream Charter', Times, serif;line-height:19px;">&nbsp;</p>Must-Have Resources - SQL Server Backup &amp; Recoveryhttp://sqlblog.com/blogs/kevin_kline/archive/2011/12/08/Must_2D00_Have-Resources-_2D00_-SQL-Server-Backup-_2600_-Recovery.aspxThu, 08 Dec 2011 20:36:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:40232KKlineOne of the things that drives me crazy as I'm getting older is that my brain is losing the capacity to differentiate <em>version numbers</em>. As I speak travel around speaking with customers and at conferences, I find my self saying things like "I can't recall if this problem was fixed in SQL Server 2000 or 2005. But you don't have to worry about that any more." Or things like "That feature was added in SQL Server 2008 R2, eh, or was that version 6.5. DOH!" followed by a loud slapping sound as I whap my palm into my forehead.
The Internet doesn't forget either. Recommendations that were once helpful, if not outright essential, now are neutral or even downright <em>bad. </em>So now, whenever I put together new presentations, I always spend a lot of time in research, reassessing my knowledge on the topic. (That doesn't mean that I'll extemporaneously <em>say</em> it wrong, because I speak in an off-the-cuff and rapid style. But at least my notes are usually correct).
&nbsp;
<h2>Case In Point</h2>
Take backup and recovery (B&amp;R), for example. I've been writing about and performing B&amp;R for years. I'm even part of the team that builds the most popular B&amp;R tool in the SQL Server space. It's an extremely important part of what I do. And yet, even <em>after </em>spending a couple days re-researching topics (yet again), I still manage to get a few of the more specific details wrong because of changes over the years in the feature set. How so?
During an Expert's Perspective webcast last week (and available on-demand) covering the top backup and recovery mistakes on Microsoft SQL Server, I mis-explained the details concerning how SQL Server performs a differential backup. A very clever SC on our team who was also attending the webcast pointed out to me after the session that I'd described differentials working at an 8k page-level when, in fact, they work at the extend-level (that's a block of eight 8k pages). <strong><em>#FACEPALM!</em></strong>
When describing the differential backup I said that a bit was flipped on each page header and SQL Server would only back up those pages. Instead, whenever a page is changed a bit is recorded on the differential change map (1bit per extent), the backup process then queries these map pages and backs up those extents that have been marked as changed.
<ul>
<li>Watch the on-demand video of <a title="Quest Software webcast on Performance Tuning SQL Server for Backups and Restores" href="http://www.quest.com/webcast-ondemand/pain-of-the-week-performance-tuning-for-backups-and-restores813358.aspx" target="_blank">Performance Tuning SQL Server for Backups and Restores</a>, with me, Iain Kick, and Brent Ozar (<a title="Brent Ozar SQL Server certified master" href="http://www.brentozar.com/" target="_blank">blog </a>| <a title="Brent Ozar's Twitter Feed" href="http://twitter.com/brento" target="_blank">twitter</a>). Free, but registration required.</li>
<li>Watch the on-demand video of <a title="Quest Software webcast Incredibly Painful SQL Server Backup and Recovery" href="http://www.quest.com/webcast-ondemand/-experts-perspective-webcast-five-incredibly-painful-sql-server-backup816067.aspx" target="_blank">Ten Incredibly Painful SQL Server Backup and Recovery Mistakes</a>, with me and David Gugick.</li>
</ul>
<p style="text-align:center;"><a href="http://www.quest.com/webcast-ondemand/-experts-perspective-webcast-five-incredibly-painful-sql-server-backup816067.aspx"><img class="aligncenter size-medium wp-image-1846" title="EP, backup mistakes" alt="" src="http://kevinekline.com/wp-content/uploads/2011/12/EP-backup-mistakes-300x179.jpg" width="497" height="294"></a></p>
&nbsp;
<h2>The Research Reveals</h2>
The cool revelation here is not that I can admit my mistakes. The take-away from this blog post are all the great articles I read writing my latest slide deck. These blogs and articles were so good that I needed to spread the word. Add these to your <span style="text-decoration:underline;">must read</span> list:
<h3>Virtual Log Files must be tamed!</h3>
<ul>
<li><a title="SQL Server VLF Behavior Benchmarked" href="http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx" target="_blank">Performance impact: a large number of virtual log files Part I</a> and <a title="SQL Server VLF Behavior Benchmarked" href="http://sqlblog.com/blogs/linchi_shea/archive/2009/02/12/performance-impact-a-large-number-of-virtual-log-files-part-ii.aspx" target="_blank">Part II</a> by one of my favorite not-so-prolific bloggers, Linchi Shea (<a title="Linchi Shea's Blog" href="http://sqlblog.com/blogs/linchi_shea/default.aspx" target="_blank">blog</a>). The thing I love about Linchi's content is its rich benchmarking information. Few SQL Server experts out there routinely test the behavior and performance of specific SQL Server features like Linchi does.</li>
<li><a title="SQL Server Slow recovery times and slow performance due to Virtual Log Files" href="http://blogs.msdn.com/b/grahamk/archive/2008/05/16/slow-recovery-times-and-slow-performance-due-to-large-numbers-of-virtual-log-files.aspx" target="_blank">Slow recovery times and slow performance due to large numbers of Virtual Log Files</a>, by Graham Kent (<a title="Graham Kent's Blog" href="http://blogs.msdn.com/grahamk" target="_blank">blog</a>) of Microsoft Sweden, shows that VLFs impact not only the processing time of transactions but also B&amp;R processes.</li>
<li><a title="Lots of SQL Server VLFs are Bad!" href="http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/07/25/sql-2000-yes-lots-of-vlf-s-are-bad-improve-the-performance-of-your-triggers-and-log-backups-on-2000.aspx" target="_blank">Lots of VLFs are Bad</a>, by Tony Rogerson (<a title="Tony Rogerson's Blog" href="http://sqlblogcasts.com/blogs/tonyrogerson/default.aspx" target="_blank">blog</a> | <a title="Tony Rogerson's Twitter Feed" href="http://twitter.com/tonyrogerson" target="_blank">twitter</a>), was the article that first got me interested in this behavior.</li>
</ul>
<h3>Backup and Recovery Myth Busting</h3>
<ul>
<li><a title="SQL Server Pro Magazine's Michael K. Campbell" href="http://www.sqlmag.com/content2/topic/breaking-backup-chain-redux-eating-crow-141459/catpath/sql-server-2008-r2/seriespath/practical-sql-server-45#commentsAnchor" target="_blank">Breaking the Backup Chain - Redux (Or 'Eating Crow')</a>, by Michael K. Campbell (<a title="The Practical SQL Server Blog" href="http://www.sqlmag.com/blogcontent/seriespath/practical-sql-server-45" target="_blank">blog</a>), shows that out-of-band database backups break differentials not transaction log backups.</li>
<li><a title="Paul Randal's Blog" href="http://sqlskills.com/BLOGS/PAUL/post/BACKUP-WITH-COPY_ONLY-how-to-avoid-breaking-the-backup-chain.aspx" target="_blank">Backup with COPY_ONLY, How to Avoid Breaking the Backup Chain</a>, by the eminent Paul Randal (<a title="Paul Randal's SQL Server Blog" href="http://sqlskills.com/BLOGS/PAUL/" target="_blank">blog</a> | <a title="Paul Randal's Twitter Feed" href="https://twitter.com/#!/PaulRandal" target="_blank">twitter</a>), uses clear writing and first-class demos to illustrate the principle of breaking backup chains in Microsoft SQL Server. Other great blogs of a related nature from Paul include:</li>
<ul>
<li><a title="Paul Randal's 'Debunking a Couple Myths Around SQL Server Full Backup'" href="http://sqlskills.com/BLOGS/PAUL/post/Debunking-a-couple-of-myths-around-full-database-backups.aspx" target="_blank">Debunking a Couple Myths around Full Backup</a></li>
<li><a title="Paul Randal's 'The Importance of Validating SQL Server Backups'" href="http://sqlskills.com/BLOGS/PAUL/post/Importance-of-validating-backups.aspx" target="_blank">The Importance of Validating Backups</a></li>
<li><a title="Paul Randal's '30 Backup Myths'" href="http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx" target="_blank">30 Backup Myths</a></li>
</ul>
</ul>
<h3>Other Good Sources</h3>
And just in case you need a refreshing in the basics of SQL Server backup and recovery, check these out:
<ul>
<li><a title="Brent Ozar's Backup Best Practices" href="http://www.brentozar.com/sql/backup-best-practices" target="_blank">Backup best practices from Brent Ozar </a></li>
<li><a title="MSDN's 'SQL Server Backup 101'" href="http://msdn.microsoft.com/en-us/library/ms190374.aspx" target="_blank">Backup 101 - recovery models and transaction log management</a></li>
</ul>Simplifying CSV Data Loadshttp://sqlblog.com/blogs/kevin_kline/archive/2011/10/27/simplifying-csv-data-loads.aspxThu, 27 Oct 2011 17:55:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:39424KKline<P>Data files containing comma separated values, or CSV, are some of the most common data formats used for data representation and storage outside the database. &nbsp;When it comes to loading CSV data into the database, many options exist, however, few make it as simple as CSVexpress, powered by expressor software. &nbsp;I recently visited <A href="http://www.csvexpress.com/">www.csvexpress.com</A> to check out just how simple it could get. &nbsp;&nbsp;In short, <A href="http://www.csvexpress.com/">CSVexpress</A> offers a repeatable and quick way to load any CSV file into SQL Server (or any other database). &nbsp;&nbsp;For those whose data quality is not as pristine as it should be, CSVexpress also offers a wide variety of built-in functionality to repair the data issues. &nbsp;&nbsp;These are in addition to the data transformation components available out of the box, but let’s not get ahead of ourselves.</P>
<P><BR>The first thing I notice when I visit <A href="http://www.csvexpress.com/">CSVexpress</A> is that there are some video tutorials available on the main page. &nbsp;I found it pretty straightforward to load a CSV file into the database without watching the tutorials beforehand. &nbsp;However, by watching the tutorials I was able to learn more about some neat features and functions that I had not previously noticed.&nbsp;&nbsp;</P>
<P>For my test, I grabbed a simple CSV data file containing the following data:<BR>City,User_ID,Name,Street_Address,Status<BR>"Dallas",47,"Janet Fuller","445 Upland Pl.","Trial"<BR>"Lyon",38,"Andrew Heiniger","347 College Av.","Active"<BR>"Dallas",43,"Susanne Smith","2 Upland Pl.","Active"<BR>"Berne",22,"Bill Ott","250 - 20th Ave.","Active"<BR>"Boston",32,"Michael Ott","339 College Av.","Trial"<BR>"New York",41,"Bill King","546 College Av.","Deleted"<BR>"Oslo",45,"Janet May","396 Seventh Av.","Active"<BR>As you can see from the diagram below, the import of the data to create a schema was not difficult at all:<IMG alt="" src="https://lh3.googleusercontent.com/e1Wesda8y4TyPA_0Zv0pk76IRzf1YXmgbvKiBwLViZx_A-GSTQQ909Ihlv1IvR-1yexkfqtagtiNMSoqW53H5BTes7fUxHqd0gelY-P8tqWJ39v8SgU" width=780 height=320><BR>Once the schema is configured, I can create the following simple data flow to move data from my CSV input file to my target table in SQL Server:<IMG alt="" src="https://lh5.googleusercontent.com/-Sc-aKWVzCVWjCBRUhtgsdszTkA2KF7keBxE6Y9LlwPtF1ZH6RO9xmkSnJiTDRavSQJF7pyHM2NQH4b3PLjFNKKOVkWJaOQxEu7ewoQiMvlo6XKi4f8" width=321 height=90></P>
<P><IMG id=internal-source-marker_0.41670942602338795 alt="" src="https://lh3.googleusercontent.com/Pa2kSjhOQO1rJ32jpKYeCB_MNIJxORqbd6jQvRbLOm-WGXdHgI-fq75p5iO1fo4X9EblwaUxM9jo6pyJ-s0sXS5DaEa6MdcVTaYAVPm64c_FfGyLNJo" width=624 height=381><BR>While there are other tools available for performing similar tasks, CSVexpress makes it very simple and intuitive. &nbsp;However, as I mentioned earlier, where it starts getting really interesting is when you need to pre-process and clean-up the data prior to loading it. &nbsp;&nbsp;Whether it involves enriching the data from external data sources or web services, or identifying and repairing bad data, CSVexpress maintains a simple interface for all of that. &nbsp;</P>
<P><BR>The best part - it’s all free of charge. &nbsp;The version you can download from <A href="http://www.csvexpress.com/">www.csvexpress.com</A> is expressor’s free Community Edition. &nbsp;expressor also offers a licensed Desktop and Standard Edition with even more advanced features, which are available for a 30-day trial. &nbsp;&nbsp;As a matter of fact, at the end of November, expressor will be introducing Salesforce support into their commercial editions and <A href="http://www.csvexpress.com/">CSVexpress</A> will feature a 30-day trial version. &nbsp;You will be able to load Salesforce just as easily as if loading to SQL Server, or download your Salesforce contact, lead, and opportunity data and transform it before generating the right CSV output file (or files) that meets your daily, weekly, and monthly Excel reporting and analysis needs. &nbsp;Now that’s easy!</P>
<P>- Kev</P>Back up a single table in SQL Serverhttp://sqlblog.com/blogs/buck_woody/archive/2010/06/03/back-up-a-single-table-in-sql-server.aspxThu, 03 Jun 2010 12:51:34 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:25902BuckWoody<p>SQL Server doesn’t have an easy way to take a table backup, so I often use the bcp (Bulk Copy Program) to accomplish the same goal. I’ve mentioned this before, and someone told me when they tried it they couldn’t restore the table – ah the dangers of telling people half the information! I should have mentioned that you need to have a “format file” ready if the table does not exist at the destination. In my case I already had the table, in this person’s case they did not. The format file can be used to rebuild that table structure before the data is bcp’d in, and you can read more about it here: <a href="http://msdn.microsoft.com/en-us/library/ms191516.aspx">http://msdn.microsoft.com/en-us/library/ms191516.aspx</a></p> <p>There’s another way to back up a table, and that’s to create a Filegroup and place the table there. Then you can take a Filegroup backup to back up a single table.</p> <p>Of course, there are other methods of moving a single table’s data in an out, including SQL Server Integration Services and even the older Data Transformation Services, or simply by using hte SQLCMD or PowerShell utilities to run a query and just save the output to a file. In fact, these days I’m using a PowerShell script to build INSERT statements from that query. That could also easily be modified to create the table structure (or modify one if needed) quite easily.</p>Backup those keys, citizenhttp://sqlblog.com/blogs/buck_woody/archive/2010/04/20/backup-those-keys-citizen.aspxTue, 20 Apr 2010 12:14:50 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:24408BuckWoody<p>Periodically I back up the keys within my servers and databases, and when I do, I blog a reminder here. This should be part of your standard backup rotation – the keys should be backed up often enough to have at hand and again when they change.</p> <p>The first key you need to back up is the Service Master Key, which each Instance already has built-in. You do that with the <a href="http://msdn.microsoft.com/en-us/library/ms190337.aspx" target="_blank">BACKUP SERVICE MASTER KEY command, which you can read more about here</a>.</p> <p>The second set of keys are the Database Master Keys, stored per database, if you’ve created one. You can back those up with the <a href="http://technet.microsoft.com/en-us/library/ms174387.aspx" target="_blank">BACKUP MASTER KEY command, which you can read more about here</a>.</p> <p>Finally, you can use the keys to create certificates and other keys – those should also be backed up. <a href="http://msdn.microsoft.com/en-us/library/ms189586.aspx" target="_blank">Read more about those here</a>.</p> <p>Anyway, the important part here is the backup. Make sure you keep those keys safe!</p>PowerShell PowerPack Downloadhttp://sqlblog.com/blogs/buck_woody/archive/2010/04/07/powershell-powerpack-download.aspxWed, 07 Apr 2010 13:30:20 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:24123BuckWoody<p>I read <a href="http://redmondmag.com/articles/2010/04/01/pack-some-power-in-your-shell.aspx" target="_blank">Jeffery Hicks’ article in this month’s Redmond Magazine</a> on a new add-in for Windows PowerShell 2.0. It’s called the PowerShell Pack and it has a some great new features that I plan to put into place on my production systems as soon as I finished learning and testing them.</p> <p>You can <a href="http://code.msdn.microsoft.com/PowerShellPack" target="_blank">download the pack here if you have PowerShell 2.0</a>. I’m having a lot of fun with it, and I’ll blog about what I’m learning here in the near future, but you should check it out. The only issue I have with it right now is that you have to load a module and then use get-help to find out what it does, because I haven’t found a lot of other documentation so far.</p> <p>The most interesting modules for me are the ones that can run a command elevated (in <strong>PSUserTools</strong>), the task scheduling commands (in <strong>TaskScheduler</strong>) and the file system checks and tools (in <strong>FileSystem</strong>). There’s also a way to create simple Graphical User Interface panels (in ). I plan to string all these together to install a management set of tools on my SQL Server Express Instances, giving the user “task buttons” to backup or restore a database, add or delete users and so on. Yes, I’ll be careful, and yes, I’ll make sure the user is allowed to do that.</p> <p>For now, I’m testing the download, but I thought I would share what I’m up to. If you have PowerShell 2.0 and you download the pack, let me know how you use it.</p> <p style="margin:0in 0in 0pt;mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;" class="MsoNormal"><font face="Calibri"><b><i style="mso-bidi-font-style:normal;"><span style="color:maroon;font-size:10pt;mso-bidi-font-size:11.0pt;mso-bidi-font-family:calibri;mso-ansi-language:en;">Script Disclaimer, for people who need to be told this sort of thing: </span></i></b><b><i style="mso-bidi-font-style:normal;"><span style="color:maroon;font-size:10pt;mso-bidi-font-family:calibri;mso-ansi-language:en;"> <p></p> </span></i></b></font></p> <p style="margin:0in 0in 0pt;" class="MsoNormal"><i style="mso-bidi-font-style:normal;"><span style="color:maroon;font-size:10pt;mso-bidi-font-family:calibri;mso-ansi-language:en;"><font face="Calibri">Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.</font></span></i></p>