<?xml version="1.0" encoding="UTF-8"?><rss version="2.0"
	xmlns:content="http://purl.org/rss/1.0/modules/content/"
	xmlns:wfw="http://wellformedweb.org/CommentAPI/"
	xmlns:dc="http://purl.org/dc/elements/1.1/"
	xmlns:atom="http://www.w3.org/2005/Atom"
	xmlns:sy="http://purl.org/rss/1.0/modules/syndication/"
	xmlns:slash="http://purl.org/rss/1.0/modules/slash/"
	>

<channel>
	<title>SQL Server Consulting &#8211; Straight Path Solutions</title>
	<atom:link href="http://straightpathsql.com/feed/" rel="self" type="application/rss+xml" />
	<link>https://straightpathsql.com/</link>
	<description>Database Managed Services and Pain Relief. Sleep at night!</description>
	<lastBuildDate>Fri, 20 Nov 2026 15:24:35 +0000</lastBuildDate>
	<language>en-US</language>
	<sy:updatePeriod>
	hourly	</sy:updatePeriod>
	<sy:updateFrequency>
	1	</sy:updateFrequency>
	

<image>
	<url>https://straightpathsql.com/wp-content/uploads/2026/03/cropped-straight-path-solutions-logo-square-32x32.png</url>
	<title>SQL Server Consulting &#8211; Straight Path Solutions</title>
	<link>https://straightpathsql.com/</link>
	<width>32</width>
	<height>32</height>
</image> 
	<item>
		<title>SQL Server Case of the Week: Log Shipping Fails on SQL Server 2025 When the Binaries Aren’t on C:\</title>
		<link>https://straightpathsql.com/sql-server-case-of-the-week-log-shipping-fails-on-sql-server-2025-when-the-binaries-arent-on-c/</link>
					<comments>https://straightpathsql.com/sql-server-case-of-the-week-log-shipping-fails-on-sql-server-2025-when-the-binaries-arent-on-c/#respond</comments>
		
		<dc:creator><![CDATA[Sandra Delany]]></dc:creator>
		<pubDate>Fri, 20 Nov 2026 11:00:00 +0000</pubDate>
				<category><![CDATA[Troubleshooting]]></category>
		<category><![CDATA[case of the week]]></category>
		<category><![CDATA[log shipping]]></category>
		<category><![CDATA[SQL Server 2025]]></category>
		<category><![CDATA[sql server migration]]></category>
		<guid isPermaLink="false">https://straightpathsql.com/?p=5464</guid>

					<description><![CDATA[<p>Quick Summary The log shipping restore job will fail on SQL Server 2025 if the SQL Server binaries were installed on any drive other than the C:\ drive. Context A client had SQL Server 2025 installed for a migration. I set up log shipping, and the restore job failed. The backup and copy jobs on ... <a title="SQL Server Case of the Week: Log Shipping Fails on SQL Server 2025 When the Binaries Aren’t on C:\" class="read-more" href="https://straightpathsql.com/sql-server-case-of-the-week-log-shipping-fails-on-sql-server-2025-when-the-binaries-arent-on-c/" aria-label="Read more about SQL Server Case of the Week: Log Shipping Fails on SQL Server 2025 When the Binaries Aren’t on C:\">Read more</a></p>
<p>The post <a href="https://straightpathsql.com/sql-server-case-of-the-week-log-shipping-fails-on-sql-server-2025-when-the-binaries-arent-on-c/">SQL Server Case of the Week: Log Shipping Fails on SQL Server 2025 When the Binaries Aren’t on C:\</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></description>
										<content:encoded><![CDATA[
<h2 id="h-quick-summary" class="wp-block-heading"><strong>Quick Summary</strong></h2>



<p class="wp-block-paragraph">The log shipping restore job will fail on SQL Server 2025 if the SQL Server binaries were installed on any drive other than the C:\ drive.</p>



<h2 id="h-context" class="wp-block-heading"><strong>Context</strong></h2>



<p class="wp-block-paragraph">A client had SQL Server 2025 installed for a migration. I set up log shipping, and the restore job failed. The backup and copy jobs on the source server were fine. Only the restore job failed.</p>



<h2 id="h-the-problem" class="wp-block-heading"><strong>The Problem</strong></h2>



<p class="wp-block-paragraph">After setting up log shipping, the restore job failed with this error:</p>



<blockquote class="wp-block-quote is-layout-flow wp-block-quote-is-layout-flow">
<p class="wp-block-paragraph">Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly &#8216;Microsoft.SqlServer.ConnectionInfo, Version=17.100.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91&#8217; or one of its dependencies. The system cannot find the file specified&#8230;at Microsoft.SqlServer.Management.LogShipping.LogShipping.Main(String[] args)</p>
</blockquote>



<p class="wp-block-paragraph">I’ve set up log shipping dozens (maybe hundreds) times, and I’ve never seen this error.</p>



<h2 id="h-the-investigation" class="wp-block-heading"><strong>The Investigation</strong></h2>



<p class="wp-block-paragraph">Like the error said, log shipping wanted assembly version <strong>17.100.0.0</strong>, but the only version I could find registered was <strong>17.0.0.0</strong>.</p>



<p class="wp-block-paragraph">My first thought was that something was out of date, so I installed the latest cumulative update and the latest version of SSMS 22. That did <strong>not</strong> fix it.</p>



<p class="wp-block-paragraph">I used PowerShell to look for <strong>Microsoft.SqlServer.ConnectionInfo.dll</strong> under <strong>C:\Program Files\Microsoft SQL Server\170</strong>. It was there, version <strong>17.100.94.0</strong>, in the <strong>Setup Bootstrap\SQL2025\x64</strong> folder. I also checked which versions of the SMO assemblies existed in the GAC. They were there too, but they were not being loaded.</p>



<p class="wp-block-paragraph">The files existed, but log shipping could not find them. Why not?</p>



<p class="wp-block-paragraph">I brought in coworkers, Buck Woody and Mike Lynn. Buck did some research. His findings point to <strong>sqllogship.exe.config</strong>. SQL Server 2025 appears to have bumped the SMO and ConnectionInfo assembly versions to 17.100.0.0, and the config file seems to resolve the DLL locations relative to the install path. That would explain why a non-default install drive causes problems.</p>



<p class="wp-block-paragraph">Buck also found a possible alternative: edit <strong>sqllogship.exe.config</strong>, located in <strong>&lt;SQL_Install_Dir&gt;\170\Tools\Binn</strong>, and add an assembly binding redirect from version 17.100.0.0 to 17.0.0.0. I did <strong>not</strong> try this one, since changing that config file could introduce other issues.</p>



<h2 id="h-the-fix" class="wp-block-heading"><strong>The Fix</strong></h2>



<p class="wp-block-paragraph">Mike found the fix. Copy the <strong>MDS5xSMO</strong> folder from the C: drive to the same path on the drive where the binaries are installed:</p>



<p class="wp-block-paragraph"><strong>From:</strong> C:\Program Files\Microsoft SQL Server\170\Shared\MDS5xSMO<br><strong>To:</strong> H:\Program Files\Microsoft SQL Server\170\Shared\MDS5xSMO</p>



<p class="wp-block-paragraph">We had to create the <strong>Shared</strong> directory on the H:\ drive first, then copy just that folder. This worked because H:\ is where the SQL Server binaries were installed for that instance.</p>



<h2 id="h-lesson-takeaway" class="wp-block-heading"><strong>Lesson/Takeaway</strong></h2>



<ol start="1" class="wp-block-list">
<li><strong>Check where the binaries are installed.</strong> If SQL Server 2025 binaries are not on the C:\ drive and log shipping restore fails with a missing <strong>Microsoft.SqlServer.ConnectionInfo</strong> 17.100.0.0 error, copying the <strong>MDS5xSMO</strong> folder to the matching path on the binaries drive fixed it for me.</li>



<li><strong>Patching did not fix this.</strong> Installing the latest CU and the latest SSMS did not resolve the error.</li>



<li><strong>I did not find any Microsoft documentation on this issue.</strong> Hopefully this saves you some time.</li>
</ol>



<h2 id="h-the-straight-path-team-and-skills" class="wp-block-heading">The Straight Path Team and Skills</h2>



<p class="wp-block-paragraph">This case shows one of many reasons why database migrations can experience unexpected issues. If you are considering upgrading your version of SQL Server, the Straight Path team can help make the migration as painless and cost-effective as possible.</p>



<p class="wp-block-paragraph"></p>



<hr class="wp-block-separator has-alpha-channel-opacity"/>



<p class="wp-block-paragraph"><em>Straight Path Solutions is a team of nearly 20 SQL Server experts trusted by 120+ clients. We treat our clients&#8217; environments as if they were our own because our job depends on your success. Making your life pain-free in the land of SQL is the rewarding part of our work. If you&#8217;re planning a migration, dealing with backup concerns, or just want to sleep better knowing someone&#8217;s watching your environment, <a href="https://straightpathsql.com/sql-server-consultant/contact-us/">reach out to us</a>.</em></p>



<p class="wp-block-paragraph"><em>This post is part of our <a href="https://straightpathsql.com/archives/category/case-of-the-week/">Case of the Week</a> series, real SQL Server issues and lessons from the field.</em></p>



<hr class="wp-block-separator has-alpha-channel-opacity"/>



<p class="wp-block-paragraph"></p>
<p>The post <a href="https://straightpathsql.com/sql-server-case-of-the-week-log-shipping-fails-on-sql-server-2025-when-the-binaries-arent-on-c/">SQL Server Case of the Week: Log Shipping Fails on SQL Server 2025 When the Binaries Aren’t on C:\</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://straightpathsql.com/sql-server-case-of-the-week-log-shipping-fails-on-sql-server-2025-when-the-binaries-arent-on-c/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Are blogs still relevant? #tsql2sday</title>
		<link>https://straightpathsql.com/are-blogs-still-relevant-tsql2sday/</link>
					<comments>https://straightpathsql.com/are-blogs-still-relevant-tsql2sday/#respond</comments>
		
		<dc:creator><![CDATA[Mike Walsh]]></dc:creator>
		<pubDate>Tue, 10 Nov 2026 17:52:57 +0000</pubDate>
				<category><![CDATA[Community & Events]]></category>
		<category><![CDATA[Database Administration]]></category>
		<category><![CDATA[#tsql2sday]]></category>
		<guid isPermaLink="false">https://straightpathsql.com/?p=5445</guid>

					<description><![CDATA[<p>Straight Path&#8217;s own Jeff Iannucci is the host this month of T-SQL Tuesday and he asked a cheeky sort of question &#8211; I mean &#8211; it&#8217;s a blog carnival (that&#8217;s what we used to call them back in the day isn&#8217;t it?) Anyway that&#8217;s his question &#8211; are Blogs still relevant in this advanced, AI ... <a title="Are blogs still relevant? #tsql2sday" class="read-more" href="https://straightpathsql.com/are-blogs-still-relevant-tsql2sday/" aria-label="Read more about Are blogs still relevant? #tsql2sday">Read more</a></p>
<p>The post <a href="https://straightpathsql.com/are-blogs-still-relevant-tsql2sday/">Are blogs still relevant? #tsql2sday</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></description>
										<content:encoded><![CDATA[
<p class="wp-block-paragraph">Straight Path&#8217;s own <a href="https://desertdba.com/t-sql-tuesday-204-are-blogs-still-relevant/">Jeff Iannucci is the host this month of T-SQL Tuesday</a> and he asked a cheeky sort of question &#8211; I mean &#8211; it&#8217;s a blog carnival (that&#8217;s what we used to call them back in the day isn&#8217;t it?) Anyway that&#8217;s his question &#8211; are Blogs still relevant in this advanced, AI fueled, age of content everywhere and folks skipping thinking and content being sprayed from everywhere. Well, he didn&#8217;t say that part &#8211; but look around&#8230;</p>



<figure class="wp-block-image size-full"><img decoding="async" width="150" height="150" src="https://straightpathsql.com/wp-content/uploads/2010/07/TSQL2sDay150x150.jpg" alt="" class="wp-image-2012"/></figure>



<p class="wp-block-paragraph">Sooooo&#8230;.</p>



<h2 id="h-are-blogs-still-relevant-as-we-wrap-up-2026" class="wp-block-heading">Are Blogs Still Relevant As We Wrap Up 2026?</h2>



<p class="wp-block-paragraph"><strong>Yeah. </strong>I think they are. And I&#8217;m not just saying that because we have an active (ish) blog here at Straight Path. And I don&#8217;t think I&#8217;m saying that because I&#8217;ve been blogging myself since 2009, no actually since <a href="https://straightpathsql.com/first-entry/">12/31/2008 apparently</a>. My first post said I&#8217;d &#8220;always post only about things that meet the following criteria:&#8221;</p>



<ul class="wp-block-list">
<li>It is something I spent time researching/investigating to discover the tidbit</li>



<li>I haven’t bumped into a lot of posts about the same topic</li>



<li>I would have found this to be a useful blog post</li>



<li>This is knowledge that I will probably want to find in the future</li>



<li>It is a trackback to someone else’s blog that provided benefit to me</li>
</ul>



<p class="wp-block-paragraph">Remember trackbacks? </p>



<p class="wp-block-paragraph">I don&#8217;t know &#8211; I didn&#8217;t really expect to consult &#8211; at least not full-time. And I never expected to wind up running a 22 person company doing SQL Server DBA services, consulting, [CDO/Chief AI Officer/Data Architecture/Cloud Architecture &#8211; thanks Buck] work, and now PostgreSQL work. But I at least tried to make the posts we share be something we&#8217;ve learned from time we&#8217;ve fixed or researched and find interesting. We try and be useful in our posts. And I&#8217;ve often found our blog posts when troubleshooting something. <strong>So I guess 18 years later (18 years.. WHAT THE LIVING HECK.. ) we&#8217;re still mostly on mission.</strong></p>



<p class="wp-block-paragraph">But that doesn&#8217;t really answer the question&#8230; Are they relevant? <br><br>I think so. I think you can right now ask AI any question you like and probably find <strong><em>an answer</em></strong>. My 4th or 5th post in January of 2009 was called &#8220;<a href="https://straightpathsql.com/empirical-evidence/">Empirical Evidence</a>&#8220;</p>



<p class="wp-block-paragraph">In that post I spoke of how fast answers can come from sources like, you know, Newsgroups and Experts Exchange (HA-HA-HA). And how I was seeing folks out there bumping into bad situations from taking the first piece of advice and running with it. It was my attempt to warn folks against running with scissors in that speedy information era of NNTP, Blog Posts, and Answer sites like Experts Exchange&#8230;.<br><br>Wellllpppppp&#8230;  Thanks to the <a href="https://en.wikipedia.org/wiki/That_Hideous_Strength" target="_blank" rel="noreferrer noopener">NICE</a> folks at the AI companies &#8211; our brave new world is sort of  the same &#8211; it&#8217;s just faster. When I was a kid &#8211; I learned &#8211; a LOT &#8211; about a lot of topics &#8211; by taking the encyclopedia for a letter off the shelf (My mom got a good deal, we didn&#8217;t have a lot of money at all, were on free/reduced lunch but thanks to the magic of credit, installment plans and folks who took pity on us &#8211; we got a set from Britannica) and I&#8217;d just read. Learn. I&#8217;d get taken up by a random topic and then hit the library, use the card catalog and get some books. Sometimes I&#8217;d read them there, sometimes I&#8217;d take them home&#8230; Anyway &#8211; learning was fun &#8211; and it still is.  <strong>Folks can still choose to learn today. </strong>Folks can still research an answer before applying it (if they want&#8230; and they should want that.) </p>



<p class="wp-block-paragraph">But &#8211; you can just go to your favorite search browser today, type a question, skip the results and do what the robots tell you to do. And at an increasing rate, that first answer is more likely to be right &#8211; but they are still spitting out the most probably right answer &#8211; based on their corpus of learning materials (answers, books, posts they&#8217;ve pilfered&#8230;) <strong>The answer that is most probably to be right &#8211; for some situations. </strong></p>



<p class="wp-block-paragraph">But when you take that answer you:</p>



<ol class="wp-block-list">
<li>Don&#8217;t learn.</li>



<li>May break something (we have a new mug that I love and on it is printed &#8220;We repair what your AI fixed.&#8221; &#8211; we use AI &#8211; we aren&#8217;t luddites &#8211; but we have now seen enough examples of folks who thought the world was falling apart because an LLM told them it was &#8211; or the opposite. We&#8217;ve seen someone use a vibe-coded tool and not have it do what they thought it would and now they need those troubleshooting skills I blogged about in those early blog days.</li>



<li>Speaking of that &#8211; You don&#8217;t develop troubleshooting skills.</li>



<li>Don&#8217;t build context for the pros/cons and ifs and whens.</li>
</ol>



<p class="wp-block-paragraph">I&#8217;m not saying that blogs are the only place to get that. But there are a lot of great blog posts out there and many of them keep coming out &#8211; and they try and teach, share, show, explain the nuance and help bring you along. They help satisfy that curiosity drive we all have and help fuel you to want to learn more.</p>



<p class="wp-block-paragraph">Yeah. Blogs are still relevant. </p>



<p class="wp-block-paragraph">I&#8217;ll also add that a few of our hires were hired in large part thanks to blogs &#8211; either the blogs they read when we asked that question, the blogs they have and info they shared, or because we were connected on a mutual Blog, like when we hired Jeff, because he responded to one of our notes in Brent&#8217;s monthly &#8220;Who is hiring in the SQL Server community&#8221; blog posts that he shares &#8211; not to get anything out us &#8211; but to help.</p>



<p class="wp-block-paragraph"><strong>But they aren&#8217;t relevant to everyone. </strong>Some folks just want to coast around, let the robots on the machine do the work and answer the questions for them. You do you &#8211; but I think that&#8217;s a career limiting move and I&#8217;m probably not going to want to work with you. You need to demonstrate a desire to grow and learn and find answers &#8211; not just trust the first crap that an LLM pops up. You need to demonstrate a desire to grow and learn from what you&#8217;ve read and change. Again &#8211; blogs aren&#8217;t the only way to demonstrate that &#8211; but in our technical community? They are a great start.<br><br>Read. Be curious. Listen. Read some blog posts. Write some even.<br><br>They are relevant to me. And because I&#8217;m in charge of this small company as long as the team keeps me around &#8211; they&#8217;re relevant to us. And the day they aren&#8217;t? Maybe the question should be rephrased &#8220;Are humans still relevant?&#8221; But that will be written in binary or astromech and it will be the things the agents are asking each other. <em>Maybe they already are&#8230;</em></p>
<p>The post <a href="https://straightpathsql.com/are-blogs-still-relevant-tsql2sday/">Are blogs still relevant? #tsql2sday</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://straightpathsql.com/are-blogs-still-relevant-tsql2sday/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Reflections on four years with sp_CheckSecurity</title>
		<link>https://straightpathsql.com/reflections-on-four-years-with-sp_checksecurity/</link>
					<comments>https://straightpathsql.com/reflections-on-four-years-with-sp_checksecurity/#respond</comments>
		
		<dc:creator><![CDATA[Jeff Iannucci]]></dc:creator>
		<pubDate>Mon, 09 Nov 2026 15:38:06 +0000</pubDate>
				<category><![CDATA[DBA Tools & Automation]]></category>
		<category><![CDATA[SQL Server Security]]></category>
		<category><![CDATA[Free Tools]]></category>
		<category><![CDATA[sp_CheckSecurity]]></category>
		<guid isPermaLink="false">https://straightpathsql.com/?p=5441</guid>

					<description><![CDATA[<p>In recent weeks I&#8217;ve been conducting a bit more of our own internal SQL Server Security Checks than usual for our clients. This shouldn&#8217;t be a surprise, what with all the stories about &#8220;rouge AI&#8221; breaches and with Microsoft releasing A LOT more security fixes in their recent Windows and SQL Server updates. In all ... <a title="Reflections on four years with sp_CheckSecurity" class="read-more" href="https://straightpathsql.com/reflections-on-four-years-with-sp_checksecurity/" aria-label="Read more about Reflections on four years with sp_CheckSecurity">Read more</a></p>
<p>The post <a href="https://straightpathsql.com/reflections-on-four-years-with-sp_checksecurity/">Reflections on four years with sp_CheckSecurity</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></description>
										<content:encoded><![CDATA[
<p class="wp-block-paragraph">In recent weeks I&#8217;ve been conducting a bit more of our own internal SQL Server Security Checks than usual for our clients. This shouldn&#8217;t be a surprise, what with all the stories about &#8220;rouge AI&#8221; breaches and with Microsoft releasing A LOT more security fixes in their recent Windows and SQL Server updates.</p>



<p class="wp-block-paragraph">In all these Security Checks we use our <a href="https://straightpathsql.com/sp_check/sp_checksecurity/">sp_CheckSecurity</a> stored procedure to collect a big chunk of the findings. I wrote the original version of this tool a while back, and out of curiosity I wanted to see how long ago that was. It turns out I created the first working version of the tool four years ago this week. Wow, four years! And the things that I have found using that tool could, as my late stepfather might say, make your eyes water.</p>



<p class="wp-block-paragraph">Inspired by my colleague Andy Kelly&#8217;s post <a href="https://straightpathsql.com/introducing-sp_checkactivity/">introducing the sp_CheckActivity</a> tool that he&#8217;s been working on in various forms for years, I thought I would share a bit about the history and intention of sp_CheckSecurity in a similar manner.</p>



<h2 id="h-how-did-sp-checksecurity-come-to-be" class="wp-block-heading"><strong>How did sp_CheckSecurity come to be?</strong></h2>



<p class="wp-block-paragraph">According to my notes, in October of 2022 I was meeting with a client discussing some findings from some internal Health Checks we had recently done for their SQL Server instances. And my notes from the conversation indicated that they &#8220;definitely want to focus on reviewing security&#8221; for those instances.</p>



<p class="wp-block-paragraph">At the time, Straight Path didn&#8217;t have a specific product for reviewing SQL Server security. We had scripts for reviewing individual items, and there were (and still are) some security-related checks in free tools like <a href="https://www.brentozar.com/blitz/">Brent Ozar&#8217;s sp_Blitz</a>, but the client indicated they wanted a deeper review to find vulnerabilities. So, after speaking with Mike Walsh about this, I wrote a script.</p>



<p class="wp-block-paragraph">I had never even remotely considered myself a SQL Server security expert, so I consulted with my colleagues here at Straight Path, primarily with Mike Walsh and Mike Lynn, and put together a list of about 25 things to check. The checks were for things like logins with elevated permissions, condition of the sa login, and public permissions. I put them all in my T-SQL script that would return three columns: Issue, Database (if applicable), and Description. That&#8217;s it.</p>



<p class="wp-block-paragraph">As embarrassing as it now to have used such a simple script, the client was pleased with the findings since there were quite a few things that were actionable. I took that feedback and kept working to add more possible vulnerabilities to the script.</p>



<p class="wp-block-paragraph">The interesting thing is that the more I started looking into finding additional vulnerabilities, the more I wanted to keep finding them. Throughout 2023 I sought out blog posts and sessions about SQL Server vulnerabilities and security recommendations, absorbing years&#8217; worth of posts from <a href="https://andreas-wolter.com/en/blog-en/">Andreas Wolter&#8217;s blog</a> and being shocked by <a href="https://passdatacommunitysummit.com/video-library/watch-out-for-permission-hijacking/">Erland Sommarskog&#8217;s session on permissions hijacking</a>. The more I learned, the more checks I added to my script.</p>



<p class="wp-block-paragraph">But at the same time, the more fearful I became for folks. I know security can be a less appealing topic, and I have noticed security sessions aren&#8217;t always well attended. And when I had the opportunity to speak with folks personally about security, there were only a handful that would discuss the topic in any depth.</p>



<p class="wp-block-paragraph">That&#8217;s not a slight against anyone, but rather a note about how it isn&#8217;t always front of mind for database folks. One of the reasons might be that we can measure performance improvements to stored procedures with all kinds of metrics, but it can be difficult to quantify to a supervisor what we&#8217;ve done to protect the data. I mean, if you tell your supervisor you removed three orphaned users with elevated permissions and changed a group from db_owner to only have read, write, and execute permissions&#8230;you might get a nod, if you&#8217;re lucky. More likely you&#8217;ll just get a blank stare.</p>



<h2 id="h-why-did-we-release-a-public-versions" class="wp-block-heading"><strong>Why did we release a public versions?</strong></h2>



<p class="wp-block-paragraph">In 2023 I added more checks, made a supplemental PowerShell script to collect more info for the Security Check reports, and completed these reports for mostly new clients. And what I saw made me really…concerned.</p>



<ul class="wp-block-list">
<li>There was the client that had over 90 members of the sysadmin group. On several instances.</li>
</ul>



<ul class="wp-block-list">
<li>Another had DOMAIN\Users in the sysadmin group.</li>
</ul>



<ul class="wp-block-list">
<li>Too many clients that had logins with a password that was the same as the login. Or the word &#8220;password&#8221;. And plenty of those logins were also in the sysadmin role.</li>
</ul>



<ul class="wp-block-list">
<li>I&#8217;ve found SQL Server service accounts in DOMAIN\Administrators.</li>
</ul>



<ul class="wp-block-list">
<li>I&#8217;ve found the public role with unexpected permissions like BACKUP DATABASE and ALTER ANY DATABASE.</li>
</ul>



<ul class="wp-block-list">
<li>And I&#8217;ve found <em>way too many</em> linked servers using the sa login or some other member of the sysadmin role.</li>
</ul>



<p class="wp-block-paragraph">Every new client seemed to have at least one instance that had some sort of &#8220;What the Fudge?!?&#8221; finding that left me…deeply concerned. So I spoke with Mike Walsh and our COO Evan Corbett in early 2024 about releasing a version of my script as a publicly available stored procedure, with the intention of helping other folks who may have inherited SQL Server instances to find and review any WTF-type findings in their own environment. Since there was no similar publicly-available tool, they both agreed this would be the right thing to do.</p>



<p class="wp-block-paragraph">By that point the script had evolved to have more columns, but I changed the stored procedure version to mimic my favorite thing about sp_Blitz: a column with a URL so people can read more about any given finding. Of course, this meant I needed to create the content for those URLs and pages, and after a few weeks of work I had those on our website for nearly all the 40 or so issues we were checking.</p>



<p class="wp-block-paragraph">We arrived at the name &#8220;sp_CheckSecurity&#8221; since we had a phrase on our website that said &#8220;We sell green checkmarks!&#8221; We wanted to emphasize the &#8220;Check&#8221; as a sort of brand on which we could build more tools. As I recall, in that same conversation I volunteered that after releasing sp_CheckSecurity I would start working on <a href="https://straightpathsql.com/sp_check/sp_checkbackup/">sp_CheckBackup</a>, since backups are crucial to recovery after any security-related event.</p>



<p class="wp-block-paragraph">In April of 2024 sp_CheckSecurity was released, and around the same time I created a &#8220;Defending SQL Server&#8221; session to present the most frequent findings I had seen when conducting Security Checks for clients. I have modified this session over the years to match frequent findings and newer checks, and have continued to present the session as recently as last month.</p>



<h2 id="h-what-issues-do-i-keep-seeing" class="wp-block-heading"><strong>What issues do I keep seeing?</strong></h2>



<p class="wp-block-paragraph">If you haven&#8217;t seen that session (you can find a version from 2025 on <a href="https://www.youtube.com/watch?v=E4UwC0_Ue1Y">YouTube</a>), I can tell you that in my hour presentation I try to cover the most dangerous and easily resolvable findings I have seen over the last 4 years. Even though there are close to 100 checks in sp_CheckSecurity now, the biggest issues I keep seeing are almost always the same.</p>



<ul class="wp-block-list">
<li><strong>The sa login is enabled.</strong> This is especially troubling when I create a SQL Audit or Extended Event and find that the login is not only enabled, but also used by applications or even individual users. (Note: I know <a href="https://andreas-wolter.com/en/202512_renaming-sql-servers-sa-account/">Andreas recommends leaving the sa login enabled with a humongous password</a> and using it as a honeypot, but that plan only works if someone is monitoring the usage of the account.)</li>
</ul>



<ul class="wp-block-list">
<li><strong>The logins that have CONTROL SERVER permissions.</strong> These are almost always from some software vendor, whom I can only presume didn&#8217;t want to raise concerns by adding the login used by their application to the sysadmin role…so they did the next best thing.</li>
</ul>



<ul class="wp-block-list">
<li><strong>The numerous members of the sysadmin role.</strong> Someone has to be in the role, but I keep seeing 10 or 20 or more members of this group on so many instances that I think some database admins are just lazy.</li>
</ul>



<ul class="wp-block-list">
<li><strong>The numerous members of the db_owner role.</strong> Speaking of permissions from lazy admins, this is probably the most common. Folks must not realize that members of this group can easily escalate permissions to the owner of the database &#8211; and that&#8217;s usually a member of the sysadmin role. Seriously, scroll back up and watch that session from Erland that I linked and you&#8217;ll be…deeply concerned about this and other database roles.</li>
</ul>



<ul class="wp-block-list">
<li><strong>Databases with TRUSTWORTHY enabled.</strong> If folks knew what this does, you would never actually enable this setting. It&#8217;s often the golden ticket of privilege escalation.</li>
</ul>



<ul class="wp-block-list">
<li><strong>The SQL Server service accounts in the sysadmin role or the local Windows Administrator role.</strong> 99% of the time the service accounts will get all the permissions they need by adding or changing them in SQL Configuration Manager. If folks used that instead of the Services control panel then they wouldn&#8217;t need to grant these other excessive privileges.</li>
</ul>



<ul class="wp-block-list">
<li><strong>The SQL Server service accounts using NT AUTHORITY\SYSTEM.</strong> I&#8217;ve heard and read lots of folks express concern about whether xp_cmdshell is enabled. Here&#8217;s the thing: anyone in the sysadmin role can enable xp_cmdshell at any time, so you&#8217;re better off worrying about (1) who has permissions to use xp_cmdshell and (2) limiting the permissions to what can be executed with xp_cmdshell. And using NT AUTHORITY\SYSTEM allows way more permissions than your SQL Server services need.</li>
</ul>



<ul class="wp-block-list">
<li><strong>The SQL Server instances that have never gotten updates.</strong> In the last two months Microsoft&#8217;s updates for SQL Server have had something like 60-80 security fixes, depending on which version you are running. It&#8217;s 2026 and too many folks still can&#8217;t figure out how to schedule and apply updates on a monthly or even quarterly cadence.</li>
</ul>



<ul class="wp-block-list">
<li><strong>Linked Servers using fixed logins in the sysadmin role.</strong> Let&#8217;s just say that I have learned to despise linked servers with the fire of a thousand suns.</li>
</ul>



<ul class="wp-block-list">
<li><strong>Failure to review failed logins.</strong> This goes back to what I said previously about disabling the sa login. Folks aren&#8217;t even noticing the hundreds or thousands of failed logins <em>already</em> captured in their SQL Server Error Log, which hopefully are from misconfigured credentials and not a brute force attack.</li>
</ul>



<h2 id="h-what-s-next" class="wp-block-heading"><strong>What&#8217;s next?</strong></h2>



<p class="wp-block-paragraph">I&#8217;ve enjoyed getting feedback on sp_CheckSecurity over these last few years, and I hope it&#8217;s helping database professionals correct vulnerabilities and protect their organization from threats. I&#8217;m still reading blog posts from Andreas and <a href="https://www.red-gate.com/simple-talk/collections/fabiano-amorims-complete-guide-to-sql-server-security/">Fabiano Amorim</a> and <a href="https://vladdba.com/">Vlad Drumea</a> and others with great interest, and looking for any sessions from them or <a href="https://thatinfradba.com/schedule.php">Danny de Haan</a> or <a href="https://www.linkedin.com/in/thomasgrohser/">Thomas Grohser</a> or some other security-minded folks. And of course my colleagues, a group that now includes our Chief Data Officer <a href="https://straightpathsql.com/team-member/buck-woody/">Buck Woody</a>, keep me on my toes by asking me &#8220;Does sp_CheckSecurity check for (something it doesn&#8217;t yet check for)?&#8221;</p>



<p class="wp-block-paragraph">Even though I&#8217;ve written five <a href="https://github.com/Straight-Path-Solutions/sp_Check">sp_Check</a> tools, sp_CheckSecurity was the first. (Well, <em>actually</em> I wrote a script that became the core of <a href="https://straightpathsql.com/sp_check/sp_checktempdb/">sp_CheckTempdb</a> years earlier, but nevermind that.) And now that I&#8217;ve been updating and using it for four years, I&#8217;ve got a huge fondness for it. The other tools are useful and can help folks discover some nasty problems with their SQL Server instances, but I&#8217;d like to think sp_CheckSecurity can not only help you find and correct vulnerabilities, but that in doing so it can help you to protect your entire organization.</p>
<p>The post <a href="https://straightpathsql.com/reflections-on-four-years-with-sp_checksecurity/">Reflections on four years with sp_CheckSecurity</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://straightpathsql.com/reflections-on-four-years-with-sp_checksecurity/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Introducing sp_CheckActivity</title>
		<link>https://straightpathsql.com/introducing-sp_checkactivity/</link>
					<comments>https://straightpathsql.com/introducing-sp_checkactivity/#respond</comments>
		
		<dc:creator><![CDATA[Andrew Kelly]]></dc:creator>
		<pubDate>Mon, 02 Nov 2026 11:00:00 +0000</pubDate>
				<category><![CDATA[DBA Tools & Automation]]></category>
		<category><![CDATA[SQL Server Performance]]></category>
		<category><![CDATA[Free Tools]]></category>
		<category><![CDATA[sp_CheckActivity]]></category>
		<category><![CDATA[SQL Server]]></category>
		<guid isPermaLink="false">https://straightpathsql.com/?p=5434</guid>

					<description><![CDATA[<p>We are releasing a new FREE tool for you to use with your SQL Server instances. Our sp_CheckActivity page explains the requirements, parameters, download instructions, and more, and should answer most questions about using the tool. In this post, Andrew Kelly explains a bit more about why he created it, and how he uses it. ... <a title="Introducing sp_CheckActivity" class="read-more" href="https://straightpathsql.com/introducing-sp_checkactivity/" aria-label="Read more about Introducing sp_CheckActivity">Read more</a></p>
<p>The post <a href="https://straightpathsql.com/introducing-sp_checkactivity/">Introducing sp_CheckActivity</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></description>
										<content:encoded><![CDATA[
<p class="wp-block-paragraph"><em>We are releasing a new FREE tool for you to use with your SQL Server instances. Our <a href="https://straightpathsql.com/sp_check/sp_checkactivity/">sp_CheckActivity</a> page explains the requirements, parameters, download instructions, and more, and should answer most questions about using the tool. In this post, Andrew Kelly explains a bit more about why he created it, and how he uses it.</em></p>



<h2 id="h-what-is-sp-checkactivity-and-why-did-i-create-it" class="wp-block-heading"><strong>What is sp_CheckActivity, and why did I create it?</strong></h2>



<p class="wp-block-paragraph">I initially created the first version of this utility many years ago as an alternative to the immensely popular tool developed by Adam Machanic called <a href="http://whoisactive.com/">sp_WhoIsActive</a>. I wanted something that would suit most of my everyday needs for a snapshot type of view of the current activity on a SQL Server instance.</p>



<p class="wp-block-paragraph">I know what you are thinking: why couldn’t I just use sp_WhoIsActive?&nbsp;</p>



<p class="wp-block-paragraph">Well, I have used it for many years and in some cases still do when a special need arises. The main motivator for creating sp_CheckActivity was not for lack of features but…it’s more like there can be too many.</p>



<p class="wp-block-paragraph">sp_WhoIsActive has always been a great tool and still is. Adam did a fantastic job all those years ago when he gave the SQL Server community a tool like no other at the time. I am sure it is used by many tens (maybe even hundreds) of thousands of users around the world, and that is a testament to just how useful and powerful it is to this day.</p>



<p class="wp-block-paragraph">However, I found that for my daily work environment it could be a little too intensive on very busy systems. These were 200+ core machines with 4 to 8TB of memory and thousands of users and today’s servers can dwarf even those. What we found was during the peak periods of activity, sp_WhoIsActive could take many minutes to return the results even with mostly default parameters. As such, I needed a way to capture similar data with less overhead and faster results and thus my custom tool was born.</p>



<p class="wp-block-paragraph">Did it speed up the results? Under low to medium workloads there is little noticeable difference in most cases. However, under more extreme workloads I found it could return results in a fraction of the time such as seconds vs minutes. I purposely didn’t list exact specifications here as workloads vary dramatically and YMMV. Why is this, you may ask?&nbsp; In a nutshell, it is because sp_CheckActivity simply does not have as wide a range of functionality as sp_WhoIsActive, and in some cases does not dig as deep into the DMV’s that can give us so much information as to what is happening.</p>



<h2 id="h-what-were-the-design-goals" class="wp-block-heading"><strong>What were the design goals?</strong></h2>



<p class="wp-block-paragraph">Before I started to develop the tool, I wrote down what metrics I needed to be reported on for most of my daily activity monitoring needs and started from there. Again, keeping in mind that this was never meant to be a full replacement for Adam’s tool that has so much capability. I was looking for lower overall overhead that still reported on the metrics I most cared about day to day.</p>



<p class="wp-block-paragraph">An example of this is the ability of sp_WhoIsActive to report wait times by thread. While that is awesome and can certainly be useful, I found in most cases I did not need to go down to that level, and thus did not want the extra overhead it took to get that information.</p>



<p class="wp-block-paragraph">You will notice sp_CheckActivity has a similar look and feel, if you will, to sp_WhoIsActive. But it was written from scratch to meet my immediate needs, and much of that same data is collected and reported on although not always in the same way. For instance, I report current and historical TempDB usage in GBs instead of pages.</p>



<p class="wp-block-paragraph">I never intended this tool to be public, but I have received many requests to do so and, well, here we are. And more recently I was asked to add functionality which I agreed to, since it did not add any significant overhead and was able to make the tool even more useful for other folks. The most recent additions (outlined by their parameter names) are listed below.</p>



<p class="wp-block-paragraph">Note that none of these are run by default and thus do not incur any additional overhead unless specifically invoked.</p>



<h2 id="h-what-is-included-with-the-new-functionality" class="wp-block-heading"><strong>What is included with the “new functionality”?</strong></h2>



<p class="wp-block-paragraph"><strong>@ShowProgress</strong> &#8211; specifically returns results for any operation in which the % Complete metric is populated in the sys.dm_exec_requests DMV. Using @ShowProgress not only shows the % Complete value, but also how long it has been running and the estimated completion time in hh:mm:ss.&nbsp; According to the Microsoft documentation these are the types of command in which this information is exposed, so we can take action:</p>



<p class="wp-block-paragraph"><code>ALTER INDEX REORGANIZE</code><br><code>BACKUP DATABASE</code><br><code>DBCC CHECKDB</code><br><code>DBCC CHECKFILEGROUP</code><br><code>DBCC CHECKTABLE</code><br><code>DBCC INDEXDEFRAG</code><br><code>DBCC SHRINKDATABASE</code><br><code>DBCC SHRINKFILE</code><br><code>RECOVERY</code><br><code>RESTORE DATABASE</code><br><code>ROLLBACK</code><br><code>TDE ENCRYPTION</code></p>



<p class="wp-block-paragraph"><strong>@ShowBlocking</strong> &#8211; this is a simplified but useful option to show any blocking that is occurring with the Lead Blockers separated from the sessions being blocked. This serves me well in the majority of blocking cases I deal with.</p>



<p class="wp-block-paragraph"><strong>@ShowTopProc</strong> – normally the results will show the individual statement that is currently running along with the Batch / Procedure / UDF in which it was immediately called from. With this option it will also show the outermost object vs. just the parent object. This can help find the calling chain when you have lots of nested calls.</p>



<h2 id="h-summary" class="wp-block-heading"><strong>Summary</strong></h2>



<p class="wp-block-paragraph">If you are already familiar with sp_WhoIsActive, please try sp_CheckActivity on its own terms. Think of it as a general-purpose lighter-weight tool for daily activity monitoring tool rather than a direct replacement. It should fit many environments, but you may may need a specific functionality this may not have and that is fine. No single tool fits every need otherwise we would not be here today.</p>



<p class="wp-block-paragraph">I would like to hear what features you’d find useful and I would love to hear about them. You can leave a comment in this post, and if you discover a bug or would like to request a feature you can note it on the <a href="https://github.com/Straight-Path-Solutions/sp_Check/issues">Issues page in the GitHub repository</a>. However, please keep in mind the original design goals were to be relatively lightweight, so we want to ensure this doesn’t get bloated over time.</p>



<p class="wp-block-paragraph">I hope many of you find this tool useful.</p>



<p class="wp-block-paragraph"></p>
<p>The post <a href="https://straightpathsql.com/introducing-sp_checkactivity/">Introducing sp_CheckActivity</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://straightpathsql.com/introducing-sp_checkactivity/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Updates for all sp_Check tools – October 2026</title>
		<link>https://straightpathsql.com/updates-for-all-sp_check-tools-october-2026/</link>
					<comments>https://straightpathsql.com/updates-for-all-sp_check-tools-october-2026/#respond</comments>
		
		<dc:creator><![CDATA[Jeff Iannucci]]></dc:creator>
		<pubDate>Wed, 28 Oct 2026 22:40:35 +0000</pubDate>
				<category><![CDATA[Company News]]></category>
		<category><![CDATA[DBA Tools & Automation]]></category>
		<category><![CDATA[Free Tools]]></category>
		<category><![CDATA[sp_check]]></category>
		<category><![CDATA[sp_checkAG]]></category>
		<category><![CDATA[sp_CheckBackup]]></category>
		<category><![CDATA[sp_CheckHealth]]></category>
		<category><![CDATA[sp_CheckSecurity]]></category>
		<category><![CDATA[sp_CheckTempdb]]></category>
		<guid isPermaLink="false">https://straightpathsql.com/?p=5424</guid>

					<description><![CDATA[<p>We&#8217;ve been busy updating our suite of sp_Check tools, but…we forgot to tell you about that. And by we I mean me, so this post from me is to rectify that issue. Speaking of issues, we have added a bunch of new issues to check for in sp_CheckSecurity recently. Fabiano Amorim and Andreas Wolter have ... <a title="Updates for all sp_Check tools – October 2026" class="read-more" href="https://straightpathsql.com/updates-for-all-sp_check-tools-october-2026/" aria-label="Read more about Updates for all sp_Check tools – October 2026">Read more</a></p>
<p>The post <a href="https://straightpathsql.com/updates-for-all-sp_check-tools-october-2026/">Updates for all sp_Check tools – October 2026</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></description>
										<content:encoded><![CDATA[
<p class="wp-block-paragraph">We&#8217;ve been busy updating our suite of sp_Check tools, but…we forgot to tell you about that. And by we I mean me, so this post from me is to rectify that issue.</p>



<p class="wp-block-paragraph">Speaking of issues, we have added a bunch of new issues to check for in sp_CheckSecurity recently. <a href="https://www.red-gate.com/simple-talk/author/fabiano-amorim/">Fabiano Amorim</a> and <a href="https://andreas-wolter.com/en/blog-en/">Andreas Wolter</a> have been doing great work writing about SQL Server security issues, and we have tried to keep up with their findings by including new checks in sp_CheckSecurity. We would HIGHLY recommend updating your copy of that tool if you haven&#8217;t done so recently.</p>



<p class="wp-block-paragraph">We&#8217;ve also added checks to our other tools, as well as made some parameter changes. And we added a MATRIX.md file so you can see which checks are in which tools.</p>



<p class="wp-block-paragraph">For a full list of features, here are the recent&nbsp;Release Notes. Note that the 3-digit numbers relate to the new CheckIDs. Enjoy!</p>



<h2 id="h-sp-checkag" class="wp-block-heading"><a href="https://github.com/Straight-Path-Solutions/sp_Check/tree/main/sp_CheckAG">sp_CheckAG</a></h2>



<p class="wp-block-paragraph">&nbsp; 2026.9.1</p>



<ul class="wp-block-list">
<li>&nbsp;Formatting changes only.</li>
</ul>



<p class="wp-block-paragraph">&nbsp; 2026.10.1</p>



<ul class="wp-block-list">
<li>&nbsp;New check: 380 Endpoint encryption (Medium), for an endpoint that uses RC4 or no encryption.</li>



<li>&nbsp;Fix: the error log temporary tables are widened to avoid truncation errors.</li>
</ul>



<h2 id="h-sp-checkbackup" class="wp-block-heading"><a href="https://github.com/Straight-Path-Solutions/sp_Check/tree/main/sp_CheckBackup">sp_CheckBackup</a></h2>



<p class="wp-block-paragraph">&nbsp; 2026.9.1</p>



<p class="wp-block-paragraph">&nbsp; &#8211; New checks:</p>



<ul class="wp-block-list">
<li>&nbsp;219 Backups not encrypted (Medium, SQL Server 2014 and later)</li>



<li>&nbsp;220 Backups written to a local drive (Medium)</li>
</ul>



<p class="wp-block-paragraph">&nbsp; &#8211; Both use a single read of the last 7 days of full backups from msdb.</p>



<p class="wp-block-paragraph">&nbsp; 2026.10.1</p>



<ul class="wp-block-list">
<li>Fix (certificate checks 210 and&nbsp;212): COLLATE DATABASE_DEFAULT is now applied when building the certificate details text. This fixes &#8220;Cannot resolve collation conflict&#8221; (Msg&nbsp;446) when msdb and the install database have different collations, for example on servers hosting SSRS databases.</li>



<li>Fix: the error log temporary tables (#FailedBackups, #ErrorLog) are widened to avoid truncation errors.</li>
</ul>



<h2 id="h-nbsp-sp-checkhealth" class="wp-block-heading"><strong>&nbsp; </strong><a href="https://github.com/Straight-Path-Solutions/sp_Check/tree/main/sp_CheckHealth">sp_CheckHealth</a></h2>



<p class="wp-block-paragraph">&nbsp; 2026.9.1</p>



<p class="wp-block-paragraph">&nbsp; &#8211; New checks:</p>



<ul class="wp-block-list">
<li>&nbsp;358 SPN registration failed (High)</li>



<li>&nbsp;375 SQLAgentOperatorRole membership (High)</li>



<li>&nbsp;378 Always Encrypted column master key on database server (High)</li>



<li>&nbsp;633 Default trace disabled (High)</li>
</ul>



<p class="wp-block-paragraph">&nbsp; 2026.10.1</p>



<ul class="wp-block-list">
<li>&nbsp;Result sets: at the default @Mode = 99 it now returns two result sets, instance information followed by problem findings.</li>



<li>&nbsp;Header row: the CheckID 0 header row is in the findings set, not the instance information.</li>



<li>&nbsp;Other modes: @Mode = 1 returns instance information only, @Mode = 0 returns findings only, and @Mode = 11 is unchanged.</li>
</ul>



<p class="wp-block-paragraph">&nbsp; &#8211; Fixes:</p>



<ul class="wp-block-list">
<li>&nbsp;The error log temporary tables are widened (ProcessInfo nvarchar(255), text nvarchar(max), LogDate datetime2), which fixes Msg 8152 on long log lines.</li>



<li>&nbsp;CheckID 720 no longer raises the &#8220;Null value is eliminated by an aggregate&#8221; warning, because the CASE expressions now have ELSE 0. Results are unchanged.</li>
</ul>



<h2 id="h-nbsp-sp-checksecurity" class="wp-block-heading"><strong>&nbsp; </strong><a href="https://github.com/Straight-Path-Solutions/sp_Check/tree/main/sp_CheckSecurity">sp_CheckSecurity</a></h2>



<p class="wp-block-paragraph">&nbsp; 2026.9.1</p>



<p class="wp-block-paragraph">&nbsp; &#8211; New checks (Security):</p>



<ul class="wp-block-list">
<li>&nbsp;&nbsp;&nbsp;357 NTLM authentication in use (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;358 SPN registration failed (High)</li>



<li>&nbsp;&nbsp;&nbsp;359 Legacy password hash algorithm (High)</li>



<li>&nbsp;&nbsp;&nbsp;360 SQL authentication in use (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;361 Guest user has CONNECT permission (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;362 Contained database users (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;363 Application roles detected (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;364 Certificate or asymmetric key mapped login (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;365 Server role membership with escalation path (High)</li>



<li>&nbsp;&nbsp;&nbsp;366 Custom server role with elevated permissions (High)</li>



<li>&nbsp;&nbsp;&nbsp;367 BUILTIN\Administrators exists as a login (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;368 ALTER ANY SERVER AUDIT granted to login (High)</li>



<li>&nbsp;&nbsp;&nbsp;369 External scripts enabled (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;370 ALTER SETTINGS granted to login (High)</li>



<li>&nbsp;&nbsp;&nbsp;371 Unsigned assembly with elevated permission set (High)</li>



<li>&nbsp;&nbsp;&nbsp;372 Assembly creation permission granted to login (High)</li>



<li>&nbsp;&nbsp;&nbsp;373 Trusted assembly registered (High)</li>



<li>&nbsp;&nbsp;&nbsp;374 Agent proxy accessible to non-sysadmin (High)</li>



<li>&nbsp;&nbsp;&nbsp;375 SQLAgentOperatorRole membership (High)</li>



<li>&nbsp;&nbsp;&nbsp;376 Logon trigger (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;377 Extended stored procedure registered (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;378 Always Encrypted column master key on database server (High)</li>



<li>&nbsp;&nbsp;&nbsp;379 SQL Server Audit not capturing security actions (Medium)</li>
</ul>



<p class="wp-block-paragraph">&nbsp; &#8211; New checks (other categories):</p>



<ul class="wp-block-list">
<li>&nbsp;&nbsp;&nbsp;202 Backups without checksum (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;219 Backups not encrypted (Medium, SQL Server 2014 and later)</li>



<li>&nbsp;&nbsp;&nbsp;220 Backups written to a local drive (Medium)</li>



<li>&nbsp;&nbsp;&nbsp;633 Default trace disabled (High)</li>
</ul>



<p class="wp-block-paragraph">&nbsp; &#8211; Changed checks:</p>



<ul class="wp-block-list">
<li>&nbsp;354 is now &#8220;Server-level permissions granted to login&#8221;. It covers ALTER ANY LOGIN, IMPERSONATE ANY LOGIN, ALTER ANY SERVER ROLE, ALTER ANY CREDENTIAL and ALTER ANY LINKED SERVER.</li>



<li>&nbsp;355 was &#8220;ALTER ANY LOGIN permissions&#8221;. It is now &#8220;Orphaned database user with elevated permissions&#8221; (High).</li>



<li>&nbsp;309 (CLR enabled) is now High. On SQL Server 2017 and later it flags CLR enabled with clr strict security off, even when SSISDB exists. On 2016 and earlier it still excludes servers with SSISDB.</li>



<li>&nbsp;Modes: @Mode = 1 is now instance information only, and the new @Mode = 2 returns High-importance items only. Modes 0 and 99 are unchanged in meaning.</li>



<li>&nbsp;Output: the Importance column is now numeric (0 to&nbsp;3) rather than text like &#8220;1 &#8211; High&#8221;. Instance information labels no longer have leading spaces.</li>
</ul>



<p class="wp-block-paragraph">&nbsp; 2026.10.1</p>



<ul class="wp-block-list">
<li>&nbsp;New check: 380 Endpoint encryption (Medium). It flags a database mirroring endpoint using RC4 or no encryption,  including mixed AES RC4 settings.</li>



<li>&nbsp;Instance information: the current state (Importance&nbsp;0) of ten settings now appears: 310, 311, 319, 341, 342, 343, 345, 347, 348,&nbsp;349.</li>



<li>&nbsp;Issues: those ten now appear as findings only when enabled, except 310 (remote admin connections), which appears only when disabled. Their importance is unchanged.</li>



<li>&nbsp;Registry reads: the Hide Instance, Extended Protection and Force Encryption reads happen once, and the Extended Protection read now uses @no_output.</li>



<li>&nbsp;Fix: the error log temporary tables are widened (ProcessInfo nvarchar(255), text nvarchar(max), LogDate datetime2). That avoids &#8220;String or binary data would be truncated&#8221; on long log lines.</li>
</ul>



<h2 id="h-sp-checktempdb" class="wp-block-heading"><a href="https://github.com/Straight-Path-Solutions/sp_Check/tree/main/sp_CheckTempdb">sp_CheckTempdb</a></h2>



<p class="wp-block-paragraph">&nbsp; 2026.9.1</p>



<ul class="wp-block-list">
<li>&nbsp;Formatting changes only.</li>
</ul>



<p class="wp-block-paragraph">&nbsp; 2026.10.1</p>



<ul class="wp-block-list">
<li>&nbsp;New check: 743 Large version store (Medium, High at 50% or more). It names the oldest open snapshot transaction when one exists.</li>



<li>&nbsp;New parameter: @VersionStorePercent, default 25, with a 1 GB minimum reserved, clamped to&nbsp;100.</li>
</ul>



<p class="wp-block-paragraph"></p>
<p>The post <a href="https://straightpathsql.com/updates-for-all-sp_check-tools-october-2026/">Updates for all sp_Check tools – October 2026</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://straightpathsql.com/updates-for-all-sp_check-tools-october-2026/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>We Repair What Your AI Fixed: Where AI Belongs in Your Automation</title>
		<link>https://straightpathsql.com/we-repair-what-your-ai-fixed-where-ai-belongs-in-your-automation/</link>
					<comments>https://straightpathsql.com/we-repair-what-your-ai-fixed-where-ai-belongs-in-your-automation/#respond</comments>
		
		<dc:creator><![CDATA[David Seis]]></dc:creator>
		<pubDate>Thu, 22 Oct 2026 17:28:55 +0000</pubDate>
				<category><![CDATA[Database Administration]]></category>
		<guid isPermaLink="false">https://straightpathsql.com/?p=5398</guid>

					<description><![CDATA[<p>We&#8217;ve all tasted the fruit. AI is awesome. It is astounding what it can do, how much it can do, and how correct it can be. It is also equally scary and world-impacting, and we can all feel the ground turning a little fluid under our feet. I don&#8217;t have big answers, but I do ... <a title="We Repair What Your AI Fixed: Where AI Belongs in Your Automation" class="read-more" href="https://straightpathsql.com/we-repair-what-your-ai-fixed-where-ai-belongs-in-your-automation/" aria-label="Read more about We Repair What Your AI Fixed: Where AI Belongs in Your Automation">Read more</a></p>
<p>The post <a href="https://straightpathsql.com/we-repair-what-your-ai-fixed-where-ai-belongs-in-your-automation/">We Repair What Your AI Fixed: Where AI Belongs in Your Automation</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></description>
										<content:encoded><![CDATA[
<h2 id="h-we-ve-all-tasted-the-fruit" class="wp-block-heading">We&#8217;ve all tasted the fruit.</h2>



<p class="wp-block-paragraph">AI is awesome. It is astounding what it can do, how much it can do, and how correct it can be. It is also equally scary and world-impacting, and we can all feel the ground turning a little fluid under our feet. I don&#8217;t have big answers, but I do have some automation-related opinions to share with you right now, and they come down to one idea: use AI to help build your automation, not to <em>be </em>your automation.</p>



<h5 id="h-ai-is-non-deterministic" class="wp-block-heading">AI is non-deterministic</h5>



<p class="wp-block-paragraph">As data people, we should all know what this means. Given the same data and the same prompt, AI can&#8217;t be trusted to do the same thing, make the same recommendation, or reach the same analysis twice. Even if it does today, the next model update can change its behavior without you changing anything. Reliable decision making should be based on reliable inputs.</p>



<h5 id="h-ai-is-a-black-box" class="wp-block-heading">AI is a black box</h5>



<p class="wp-block-paragraph">At the lowest levels, AI is still very much un-auditable. It is a black box according to the people developing it and the people trying to understand it. We can ask it why it did something, but its answer is just more generated text, not a record of what actually happened inside. We can see what it did, but we can&#8217;t verify why, and that means it can&#8217;t be audited the way a script can.</p>



<h5 id="h-ai-isn-t-accountable" class="wp-block-heading">AI isn&#8217;t accountable</h5>



<p class="wp-block-paragraph">In my own experience, and in the many horror stories on LinkedIn, AI still isn&#8217;t following the rules we try to set for it. It seems to have short-term memory loss when it comes to guardrails. It can&#8217;t be trusted to follow the rules, and even when it does follow them and still gets something wrong, it can&#8217;t be held accountable.</p>



<h5 id="h-ai-is-so-easy-to-use" class="wp-block-heading">AI is so easy to use</h5>



<p class="wp-block-paragraph">For many years, some of the worst SQL Server deployments have been described as someone clicking &#8220;next, next, finish&#8221; through the installer. Some AI usage is that same issue on jet fuel. It is so easy to hand it your own credentials, let it access the data, show it the schema, and give it the script. And in most cases, it will do an OK job of giving you something that does what you asked, but the problem is the same as next, next, finish: AI is going to implement what you ask for. The less you know, the higher the chance you aren&#8217;t asking for the best implementation.</p>



<h2 id="h-what-does-all-this-mean-for-you-and-your-automation" class="wp-block-heading">What does all this mean for you and your automation?</h2>



<p class="wp-block-paragraph">The more complex the requirements, the more resilient the solution needs to be, and the more an experienced person needs to be involved in engineering it. AI is a powerful force multiplier. An experienced engineer with a well-designed specification can burn through an implementation at blinding speed. The hardest parts of development are now design and testing. Design means being clear and detailed in what you ask for, with zero ambiguity as the target. Testing means having an environment to test the output in, and people experienced enough to know when the output is wrong, too complex, or was led in the wrong direction.</p>



<p class="wp-block-paragraph">The size of the project matters too. A simple automation to collect, store, and report on data doesn&#8217;t need an entire team of reviewers. However, we are seeing a boom in teams trying to use AI to replace enterprise-level software without the experience to confirm that the output is enterprise-ready for resilience, reliability, and security, among other things. Some are letting AI make major changes on their SQL Servers, which is why we recently had mugs made with &#8220;<a href="https://www.linkedin.com/feed/update/urn:li:activity:7509215856841691136/">We repair what your AI fixed</a>&#8221; on the side. Picture a transaction log filling up a drive. The AI switches the database to SIMPLE recovery, shrinks the log, and reports the problem fixed. It also just broke your log backup chain, and point-in-time recovery is gone until someone notices. The fallout of vibe-coded implementations at enterprise scale shows up again and again in technical groups and in the news.</p>



<h4 id="h-so-how-should-you-use-ai-in-your-automation" class="wp-block-heading">So how should you use AI in your automation?</h4>



<p class="wp-block-paragraph">I&#8217;ve rambled enough. I&#8217;m an automation guy, and I love encouraging you to find ways to save time. In this post I want to be clear that AI is a powerful tool, but implementation should be careful and intentional. Here are a few guidelines for how I would start bringing AI into your automated tasks.</p>



<p class="wp-block-paragraph">One distinction before the list: AI can help you build almost any of these processes. These guidelines are about whether AI should <em>be</em> the process, making a judgment call every time it runs.</p>



<p class="wp-block-paragraph"><strong>Should nots:</strong></p>



<ol start="1" class="wp-block-list">
<li>Don&#8217;t use AI where you need deterministic output.&nbsp;Fetching data, sorting or organizing it, building a report with pass/fail counts: you don&#8217;t want two different answers from the same data. Let AI help write the script, then let the script produce the output.</li>



<li>Don&#8217;t use AI for processes that need auditing.&nbsp;AI can&#8217;t be held responsible, so don&#8217;t ask it to interpret something and then make a change to a production database. It won&#8217;t always make the right decision, make the same decision, or follow the rules. And if the scope is narrow enough that AI could never get it wrong, regular old scripting is cheaper anyway.</li>



<li>Don&#8217;t use AI for things you can&#8217;t undo.&nbsp;You don&#8217;t want to be up the creek when AI makes an irreversible decision, like reducing backup retention, turning off your restore test job, or running REPAIR_ALLOW_DATA_LOSS on your behalf.</li>
</ol>



<p class="wp-block-paragraph"><strong>Coulds:</strong></p>



<ol start="1" class="wp-block-list">
<li>Use AI to help with specs and well-designed, well-tested development.&nbsp;Have it poke holes in your spec, generate code against it, and write the tests, then run everything in a lab before it gets anywhere near production.</li>



<li>Use AI to interpret Windows, SQL Server, and application error messages during incident response.&nbsp;Make it cite the log entries it&#8217;s drawing from so you can check its work.</li>



<li>Use AI to triage and route.&nbsp;It can read the tone and urgency of tickets and alerts, suggest who should handle them, and loop in the right people.</li>



<li>Use AI to summarize and explain.&nbsp;Turn a 200-finding health check into a top five your manager will actually read, or get a second opinion on an execution plan.</li>
</ol>



<p class="wp-block-paragraph">The pattern behind all of the coulds: AI proposes, a person approves, a script executes.</p>



<h4 id="h-where-i-d-draw-the-lines" class="wp-block-heading">Where I&#8217;d draw the lines</h4>



<p class="wp-block-paragraph">Here&#8217;s how I&#8217;d sort some common DBA tasks. Most land in more than one column, and that&#8217;s the point: the script does the work, AI helps build it and make sense of it, and a person owns the decisions that can&#8217;t be undone.</p>



<figure class="wp-block-table"><table class="has-fixed-layout"><thead><tr><td><strong>Task</strong></td><td><strong>Script it</strong></td><td><strong>Let AI help with</strong></td><td><strong>A person decides</strong></td></tr></thead><tbody><tr><td>Collecting and storing server data</td><td>dbatools on a schedule</td><td>Writing the collection script</td><td>What to collect, how often, where it is saved, how it is viewed</td></tr><tr><td>Health check report with pass/fail counts</td><td>Same rules, every run</td><td>A plain-English summary</td><td>What pass/fail means, who the recipients are, when it is run </td></tr><tr><td>Nightly backups</td><td>On a schedule</td><td>Reviewing the job setup</td><td>Where backups belong, what RPO/RTO is needed for the business</td></tr><tr><td>Backup retention changes</td><td>Once approved</td><td>Drafting the change</td><td>Again RTO/RPO, long term backup retention strategy</td></tr><tr><td>Restore testing</td><td>Test-DbaLastBackup on a schedule</td><td>Summarizing the results</td><td>What target to restore backups on, who gets the results</td></tr><tr><td>Incident response</td><td>Gathering Windows, SQL Server, and app logs</td><td>Interpreting errors, with sources</td><td>The business rules for investigating, prior examples, what to do with the findings</td></tr><tr><td>Ticket and alert triage</td><td>Routing rules you define</td><td>Reading urgency and suggesting an owner</td><td>Who is assigned the ticket, defining what is ticketable</td></tr><tr><td>Patching</td><td>Update-DbaInstance</td><td>Summarizing release notes</td><td>The window and go/no-go</td></tr><tr><td>Permission changes</td><td>From an approved request</td><td>Drafting the script</td><td>Approving it, determining the permission rules, least privilege.</td></tr><tr><td>Corruption</td><td>DBCC CHECKDB on a schedule</td><td>Explaining the output</td><td>Anything involving REPAIR_ALLOW_DATA_LOSS,</td></tr><tr><td>Building new automation</td><td>The finished product</td><td>Spec review, code, and tests</td><td>Design and test sign-off, permissions, business requirements.</td></tr></tbody></table></figure>



<p class="wp-block-paragraph">Notice that AI never shows up alone in a row that changes production.</p>



<h4 id="h-final-thoughts" class="wp-block-heading">Final Thoughts</h4>



<p class="wp-block-paragraph">We&#8217;ve all tasted the fruit, and nobody is going back, me included. AI is going to keep getting better, and it&#8217;s already a powerful force multiplier for anyone building automation. But it multiplies whatever you bring to it. Bring experience, a clear spec, and a lab to test in, and you&#8217;ll build faster than you ever have. Bring next, next, finish, and you&#8217;ll also get there faster, just somewhere you didn&#8217;t want to go.</p>



<p class="wp-block-paragraph">Use AI to help design, write, and test your automation. Let the finished, reviewed, version-controlled script do the work. Keep a person on anything that can&#8217;t be undone.</p>



<p class="wp-block-paragraph">I love automation and the time it saves for me, the <a href="https://straightpathsql.com/about/">Straight Path team</a>, and our customers. The less time we spend on things that can be automated, the more time we have for things that can&#8217;t be. Used carefully and intentionally, AI gets us there faster. And if your AI has already fixed something, you know where to find us. We have mugs.</p>



<p class="wp-block-paragraph"></p>
<p>The post <a href="https://straightpathsql.com/we-repair-what-your-ai-fixed-where-ai-belongs-in-your-automation/">We Repair What Your AI Fixed: Where AI Belongs in Your Automation</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://straightpathsql.com/we-repair-what-your-ai-fixed-where-ai-belongs-in-your-automation/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>If You Are Moving to Postgres, Start Here</title>
		<link>https://straightpathsql.com/if-you-are-moving-to-postgres-start-here/</link>
					<comments>https://straightpathsql.com/if-you-are-moving-to-postgres-start-here/#respond</comments>
		
		<dc:creator><![CDATA[Amy Abel]]></dc:creator>
		<pubDate>Mon, 19 Oct 2026 21:05:00 +0000</pubDate>
				<category><![CDATA[DBA as a Service]]></category>
		<category><![CDATA[PostgreSQL]]></category>
		<category><![CDATA[Growing]]></category>
		<category><![CDATA[learning]]></category>
		<category><![CDATA[Postgres]]></category>
		<guid isPermaLink="false">https://straightpathsql.com/?p=5385</guid>

					<description><![CDATA[<p>This week I went back through my PostgreSQL notes and listed what is different between SQL Server and Postgres. These are the things I would pay close attention to when doing a migration. Most migration issues are not caused by the data. They come from assuming Postgres works the same way as SQL Server. Case ... <a title="If You Are Moving to Postgres, Start Here" class="read-more" href="https://straightpathsql.com/if-you-are-moving-to-postgres-start-here/" aria-label="Read more about If You Are Moving to Postgres, Start Here">Read more</a></p>
<p>The post <a href="https://straightpathsql.com/if-you-are-moving-to-postgres-start-here/">If You Are Moving to Postgres, Start Here</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></description>
										<content:encoded><![CDATA[
<p class="wp-block-paragraph">This week I went back through my PostgreSQL notes and listed what is different between SQL Server and Postgres. These are the things I would pay close attention to when doing a migration.</p>



<p class="wp-block-paragraph">Most migration issues are not caused by the data. They come from assuming Postgres works the same way as SQL Server.</p>



<h2 id="h-case-sensitivity" class="wp-block-heading">Case Sensitivity</h2>



<p class="wp-block-paragraph" id="h-one-thing-i-learned-is-that-postgres-does-not-handle-object-names-the-same-way-as-sql-server-if-you-create-an-object-with-quotes-postgres-keeps-the-exact-case-if-you-leave-the-quotes-off-postgres-converts-the-name-to-lowercase-string-comparisons-are-another-thing-i-would-check-a-lot-of-sql-server-environments-are-case-insensitive-by-default-postgres-is-usually-case-sensitive-unless-you-set-that-up-yourself">One thing I learned is that Postgres does not handle object names the same way as SQL Server. If you create an object with quotes, Postgres keeps the exact case. If you leave the quotes off, Postgres converts the name to lowercase. String comparisons are another thing I would check. A lot of SQL Server environments are case insensitive by default. Postgres is usually case sensitive unless you set that up yourself.</p>



<p class="wp-block-paragraph">I would check table names, column names, schema names, application code, ORM mappings, and anything using quoted identifiers. If possible, I would stick with lowercase object names.</p>



<h2 id="h-indexes" class="wp-block-heading">Indexes</h2>



<p class="wp-block-paragraph">Indexes do not always translate the same way when you migrate them over. In SQL Server, the clustered index controls the table order. Postgres stores tables as heaps. Postgres does have a CLUSTER command, but it only rewrites the table once. New rows do not stay in that order.</p>



<p class="wp-block-paragraph">I would review included columns, filtered indexes versus partial indexes, fill factor, statistics, and query plans. Recreating the same indexes does not mean you will get the same performance.</p>



<h2 id="h-postgresql-versions" class="wp-block-heading">PostgreSQL Versions</h2>



<p class="wp-block-paragraph">Postgres releases a major version about once a year and supports each version for five years. I have found a lot of companies running older versions, so I would check the version early in the migration. I would look at end-of-life dates, features the application needs, extension support, upgrade paths, and what the cloud provider offers.</p>



<p class="wp-block-paragraph">As of late 2026, PostgreSQL 13 is already out of community support. PostgreSQL 14 reaches the end of community support in November 2026.</p>



<h2 id="h-extensions" class="wp-block-heading">Extensions</h2>



<p class="wp-block-paragraph" id="h-in-a-previous-post-i-talked-about-alternatives-to-sql-server-agent-a-lot-of-extra-postgres-features-come-from-extensions-one-thing-i-learned-is-that-not-every-extension-is-available-everywhere-i-would-check-whether-the-cloud-provider-supports-it-whether-it-needs-superuser-access-whether-it-needs-extra-configuration-and-what-can-be-used-if-it-is-not-something-available-managed-postgres-services-do-not-all-offer-the-same-extensions">In a previous post, I talked about alternatives to SQL Server Agent. A lot of extra Postgres features come from extensions. One thing I learned is that not every extension is available everywhere. I would check whether the cloud provider supports it, whether it needs superuser access, whether it needs extra configuration, and what can be used if it is not something available. Managed Postgres services do not all offer the same extensions.</p>



<h2 id="h-managed-versus-self-managed" class="wp-block-heading">Managed Versus Self-Managed</h2>



<p class="wp-block-paragraph">Managed and self-managed Postgres are not the same. Managed services usually size some settings for you. Self-managed servers often start with conservative defaults. I would review shared buffers, work memory, maintenance work memory, connection limits, and autovacuum.</p>



<p class="wp-block-paragraph">I was surprised by how low some of the default memory settings can be. Fresh installs can still have shared_buffers set around 128 MB.</p>



<p class="wp-block-paragraph">I would also check the number of connections used by the application. Postgres uses a process for each connection. Applications that open hundreds of connections will probably need a connection pooler.</p>



<h2 id="h-job-scheduling" class="wp-block-heading">Job Scheduling</h2>



<p class="wp-block-paragraph" id="h-jobs-are-not-sql-server-agent-for-years-people-scheduled-postgres-jobs-with-cron-pg-cron-makes-it-easier-to-schedule-sql-inside-postgres-but-it-is-not-a-full-replacement-for-sql-server-agent-pg-cron-runs-sql-not-shell-scripts-if-the-instance-is-down-the-job-does-not-run-nbsp">Jobs are not SQL Server Agent. For years, people scheduled Postgres jobs with cron. pg_cron makes it easier to schedule SQL inside Postgres, but it is not a full replacement for SQL Server Agent. pg_cron runs SQL, not shell scripts. If the instance is down, the job does not run.&nbsp;</p>



<p class="wp-block-paragraph" id="h-other-choices-include-pg-timetable-and-cloud-schedulers-before-cutover-i-would-list-every-maintenance-job-backup-job-etl-job-and-monitoring-job-i-would-make-sure-each-job-has-a-replacement-and-test-it-before-the-migration">Other choices include pg_timetable and cloud schedulers. Before cutover, I would list every maintenance job, backup job, ETL job, and monitoring job. I would make sure each job has a replacement and test it before the migration.</p>



<h2 id="h-stored-procedures" class="wp-block-heading">Stored Procedures</h2>



<p class="wp-block-paragraph" id="h-if-the-environment-uses-a-lot-of-stored-procedures-i-would-budget-time-for-them-postgres-has-procedures-and-functions-they-are-not-written-like-t-sql-i-would-review-syntax-transactions-error-handling-dynamic-sql-temporary-tables-and-how-results-come-back-multiple-result-sets-from-one-procedure-are-common-in-sql-server-they-do-not-move-cleanly-to-postgres">If the environment uses a lot of stored procedures, I would budget time for them. Postgres has procedures and functions. They are not written like T-SQL. I would review syntax, transactions, error handling, dynamic SQL, temporary tables, and how results come back. Multiple result sets from one procedure are common in SQL Server. They do not move cleanly to Postgres.</p>



<h2 id="h-identity-columns-and-sequences" class="wp-block-heading">Identity Columns and Sequences</h2>



<p class="wp-block-paragraph" id="h-sql-server-identities-do-not-always-translate-directly-postgres-has-identity-serial-and-standalone-sequences">SQL Server identities do not always translate directly. Postgres has IDENTITY, SERIAL, and standalone sequences.</p>



<h2 id="h-data-types" class="wp-block-heading">Data Types</h2>



<p class="wp-block-paragraph" id="h-some-sql-server-data-types-do-not-have-a-direct-match-in-postgres-i-would-check-money-uniqueidentifier-rowversion-tinyint-bit-datetime-types-sql-variant-hierarchyid-and-clr-types-each-one-needs-to-be-mapped-and-tested-before-the-migration-also-rowversion-is-not-a-date-or-time-type">Some SQL Server data types do not have a direct match in Postgres. I would check money, uniqueidentifier, rowversion, tinyint, bit, datetime types, sql_variant, hierarchyid, and CLR types. Each one needs to be mapped and tested before the migration. Also, rowversion is not a date or time type.</p>



<h2 id="h-linked-servers" class="wp-block-heading">Linked Servers</h2>



<p class="wp-block-paragraph" id="h-linked-servers-do-not-exist-in-postgres-the-usual-replacements-are-postgres-fdw-and-dblink-i-would-also-check-what-each-linked-server-is-being-used-for-replacing-the-connection-does-not-mean-the-existing-queries-will-work-without-changes">Linked servers do not exist in Postgres. The usual replacements are postgres_fdw and dblink. I would also check what each linked server is being used for. Replacing the connection does not mean the existing queries will work without changes.</p>



<h2 id="h-database-mail" class="wp-block-heading">Database Mail</h2>



<p class="wp-block-paragraph" id="h-postgres-does-not-have-a-direct-replacement-for-database-mail-if-the-application-or-a-job-depends-on-it-i-would-plan-a-replacement-before-cutover">Postgres does not have a direct replacement for Database Mail. If the application or a job depends on it, I would plan a replacement before cutover.</p>



<h2 id="h-cross-database-queries" class="wp-block-heading">Cross-Database Queries</h2>



<p class="wp-block-paragraph" id="h-sql-server-databases-on-the-same-instance-can-query-each-other-postgres-databases-are-isolated-a-lot-of-migrations-use-schemas-in-one-postgres-database-instead-of-creating-several-databases-i-would-search-the-sql-server-code-for-three-part-names-before-deciding-how-to-set-up-the-postgres-databases-and-schemas">SQL Server databases on the same instance can query each other. Postgres databases are isolated. A lot of migrations use schemas in one Postgres database instead of creating several databases. I would search the SQL Server code for three-part names before deciding how to set up the Postgres databases and schemas.</p>



<h2 id="h-security-and-permissions" class="wp-block-heading">Security and Permissions</h2>



<p class="wp-block-paragraph" id="h-i-would-review-roles-role-inheritance-authentication-service-accounts-and-search-path-settings-postgres-roles-belong-to-the-whole-cluster-the-search-path-controls-which-objects-postgres-finds-when-the-schema-name-is-left-out">I would review roles, role inheritance, authentication, service accounts, and search_path settings. Postgres roles belong to the whole cluster. The search_path controls which objects Postgres finds when the schema name is left out.</p>



<h2 id="h-collation" class="wp-block-heading">Collation</h2>



<p class="wp-block-paragraph" id="h-collation-needs-to-be-checked-before-the-move-sorting-unique-constraints-and-string-comparisons-may-not-match-what-sql-server-was-doing-this-can-change-query-results-even-when-the-data-moved-correctly">Collation needs to be checked before the move. Sorting, unique constraints, and string comparisons may not match what SQL Server was doing. This can change query results even when the data moved correctly.</p>



<h2 id="h-temporary-tables" class="wp-block-heading">Temporary Tables</h2>



<p class="wp-block-paragraph" id="h-postgres-temporary-tables-belong-to-the-session-they-can-also-be-set-to-drop-when-the-transaction-commits-there-is-no-global-temporary-table-like-a-sql-server-table-i-would-also-check-connection-pooling-a-temporary-table-created-on-one-connection-will-not-be-there-if-the-next-statement-uses-another-connection">Postgres temporary tables belong to the session. They can also be set to drop when the transaction commits. There is no global temporary table like a SQL Server <code>##table</code>. I would also check connection pooling. A temporary table created on one connection will not be there if the next statement uses another connection.</p>



<h2 id="h-date-functions" class="wp-block-heading">Date Functions</h2>



<p class="wp-block-paragraph" id="h-getdate-getutcdate-dateadd-datediff-and-datepart-all-need-postgres-replacements-i-would-also-check-how-the-application-handles-time-zones-postgres-has-timestamp-with-time-zone-and-timestamp-without-time-zone">GETDATE(), GETUTCDATE(), DATEADD(), DATEDIFF(), and DATEPART() all need Postgres replacements. I would also check how the application handles time zones. Postgres has timestamp with time zone and timestamp without time zone.</p>



<h2 id="h-vacuum-and-bloat" class="wp-block-heading">Vacuum and Bloat</h2>



<p class="wp-block-paragraph" id="h-postgres-keeps-old-row-versions-until-vacuum-cleans-them-up-long-running-transactions-can-stop-vacuum-from-removing-those-rows-this-can-leave-tables-and-indexes-bloated-i-would-check-autovacuum-settings-long-running-transactions-and-tables-with-a-lot-of-updates-or-deletes">Postgres keeps old row versions until vacuum cleans them up. Long-running transactions can stop vacuum from removing those rows. This can leave tables and indexes bloated. I would check autovacuum settings, long-running transactions, and tables with a lot of updates or deletes.</p>



<h2 id="h-query-hints" class="wp-block-heading">Query Hints</h2>



<p class="wp-block-paragraph" id="h-a-lot-of-sql-server-hints-do-not-exist-in-postgres-nolock-forceseek-and-index-hints-need-another-approach-nolock-is-also-the-wrong-way-to-think-about-postgres-postgres-uses-mvcc-so-readers-and-writers-usually-do-not-block-each-other">A lot of SQL Server hints do not exist in Postgres. NOLOCK, FORCESEEK, and INDEX hints need another approach. NOLOCK is also the wrong way to think about Postgres. Postgres uses MVCC, so readers and writers usually do not block each other.</p>



<h2 id="h-high-availability" class="wp-block-heading">High Availability</h2>



<p class="wp-block-paragraph" id="h-always-on-availability-groups-do-not-translate-directly-to-postgres-i-would-review-replication-failover-read-replicas-backups-and-restore-tests-postgres-failover-often-uses-streaming-replication-with-other-tools-or-a-cloud-high-availability-service-i-would-test-failover-and-restores-before-the-migration-i-would-not-assume-the-cloud-service-handles-everything">Always On Availability Groups do not translate directly to Postgres. I would review replication, failover, read replicas, backups, and restore tests. Postgres failover often uses streaming replication with other tools or a cloud high-availability service. I would test failover and restores before the migration. I would not assume the cloud service handles everything.</p>



<h2 class="wp-block-heading">My Thoughts&nbsp;</h2>



<p class="wp-block-paragraph">Most migration issues are not caused by the data. They come from assuming Postgres works the same way as SQL Server.</p>



<p class="wp-block-paragraph">These are the things I would check before the move. It is better to find them while you still have time to fix them than after the migration.</p>



<p class="wp-block-paragraph"></p>
<p>The post <a href="https://straightpathsql.com/if-you-are-moving-to-postgres-start-here/">If You Are Moving to Postgres, Start Here</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://straightpathsql.com/if-you-are-moving-to-postgres-start-here/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>The accidental DBA is learning PostgreSQL (and why you should never stop being a beginner)</title>
		<link>https://straightpathsql.com/the-accidental-dba-is-learning-postgresql-and-why-you-should-never-stop-being-a-beginner/</link>
					<comments>https://straightpathsql.com/the-accidental-dba-is-learning-postgresql-and-why-you-should-never-stop-being-a-beginner/#respond</comments>
		
		<dc:creator><![CDATA[Bruce Campbell]]></dc:creator>
		<pubDate>Thu, 08 Oct 2026 18:38:00 +0000</pubDate>
				<category><![CDATA[DBA as a Service]]></category>
		<category><![CDATA[PostgreSQL]]></category>
		<category><![CDATA[Growing]]></category>
		<category><![CDATA[learning]]></category>
		<category><![CDATA[Postgres]]></category>
		<guid isPermaLink="false">https://straightpathsql.com/?p=5387</guid>

					<description><![CDATA[<p>I got started in SQL Server and databases as a bit of an accident several years ago. I worked at a company where I was the primary application support person and dabbled in SSMS to review issues when a customer’s loan didn’t go through. As the age-old joke goes, I knew just enough to be ... <a title="The accidental DBA is learning PostgreSQL (and why you should never stop being a beginner)" class="read-more" href="https://straightpathsql.com/the-accidental-dba-is-learning-postgresql-and-why-you-should-never-stop-being-a-beginner/" aria-label="Read more about The accidental DBA is learning PostgreSQL (and why you should never stop being a beginner)">Read more</a></p>
<p>The post <a href="https://straightpathsql.com/the-accidental-dba-is-learning-postgresql-and-why-you-should-never-stop-being-a-beginner/">The accidental DBA is learning PostgreSQL (and why you should never stop being a beginner)</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></description>
										<content:encoded><![CDATA[
<p class="wp-block-paragraph">I got started in SQL Server and databases as a bit of an accident several years ago. I worked at a company where I was the primary application support person and dabbled in SSMS to review issues when a customer’s loan didn’t go through. As the age-old joke goes, I knew just enough to be dangerous. While I was there, I decided to keep learning about SQL from the DBA team and when that company went out of business, I was able to pivot and apply for DBA roles, and I eventually found my way here to Straight Path. Part of how I was able to do that was that I was able to see the writing on the wall and learn that there was opportunity in SQL and data.</p>



<p class="wp-block-paragraph">With that being said, we at Straight Path pride ourselves on proactivity and helping customers to determine what they might need. So, as you may have guessed by the title, this is a blog post about PostgreSQL and some things I’ve learned about it and why I never want to stop being a beginner.</p>



<p class="wp-block-paragraph">If you’re new to us, Straight Path is a SQL and Database consulting firm started by our CEO Mike Walsh. We’re now at 22 employees and we have backgrounds in performance tuning, migration work, security, and just general DBA knowledge spread out across our Senior and Support teams. There isn’t much we can’t handle as an organization. Our new CDO, Buck Woody, in the spirit of proactivity has seen the writing on the wall as well and knows that our next logical step was Postgres since it’s getting more popular than ever. DBAs and organizations around the world are turning to Postgres for its customization, its easy licensing, and resource management.</p>



<p class="wp-block-paragraph">I’ve built my career in Microsoft but my OS of choice for my personal projects is Pop!_OS, which is based on Ubuntu and is part of the Free and Open Source Software (FOSS) project. I learned about Linux and the different operating systems built around it when I was fresh out of high school, through my uncle who had been using Linux almost since its inception. I was amazed that so many humans across the world could come together on a project like this and that Linux was responsible for so many things in our lives. Now it’s on billions of devices and runs critical infrastructure that we depend on. I’m saying all of this to emphasize that it’s my passion for being a beginner that got me into Linux in the first place, and that same passion brought me to being a DBA, and now it’s helping me to learn Postgres.</p>



<p class="wp-block-paragraph">As the title suggests, I didn’t set out to be a DBA, I originally went to school for programming. That job I mentioned earlier hired me partially for my ability to read code. My boss at the time assumed that if I could read C# then I must have been able to read whatever language the company’s software was written in and that would help me in my support role. He wasn’t totally wrong because that light programming background allowed me to talk with the developers and try to understand what we were building. It helped me make relationships that I still maintain today and that ability to be a beginner let me ask questions. I asked questions about everything because it’s how I soak up information. Asking those questions eventually got me pointed to the DBA team because I wanted to know why something wasn’t working, and development eventually pointed the finger at the DBAs. As we all know, DBA does stand for “Default Blame Accepter”.</p>



<p class="wp-block-paragraph">I started working closely with them and learning anything and everything I could. Eventually that company was going under and I needed to find new work and I found Straight Path. I wanted to take the DBA knowledge I had to the next level because I wanted to be a beginner somewhere else and before I knew it, I was. I was learning the ropes of SQL Agent, jobs and maintenance plans, users and access, and how to troubleshoot issues. Fast forward 5 years and I’ve grown into my role as a DBA where I maintain a list of my own clients and I assist our Senior team with their clients where I can. I focus on proactive work and maintaining a well performing environment while also looking for opportunities to enhance security. The world is only getting less secure these days, and I started all this by being a beginner at something. &nbsp;</p>



<p class="wp-block-paragraph">Here I am now, relearning a lot of those same principles but in a new environment with new challenges and new goals. Postgres is a great tool and even though it can do so much for your environment and your workflows, it may or may not be the right tool for your needs.</p>



<p class="wp-block-paragraph">Postgres is also considered FOSS, and it’s maintained by a dedicated group of people around the world and provided for free in most cases. That philosophy is also part of what brought me to learning it and wanting to use it, as it was like getting to learn about Linux with my uncle all over again.</p>



<p class="wp-block-paragraph">So, this is a roundabout way of saying that you shouldn’t ever stop being a beginner at something. I took my passion for learning and started with Windows 98 as a kid, then in my teens learned about Linux, then as an adult I found my career with Microsoft software, and now we’re going back to Linux to build out this Postgres plan. More on that to come!</p>
<p>The post <a href="https://straightpathsql.com/the-accidental-dba-is-learning-postgresql-and-why-you-should-never-stop-being-a-beginner/">The accidental DBA is learning PostgreSQL (and why you should never stop being a beginner)</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://straightpathsql.com/the-accidental-dba-is-learning-postgresql-and-why-you-should-never-stop-being-a-beginner/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>Backup a second… this isn’t a SQL Server post? My journey into PostgreSQL backups</title>
		<link>https://straightpathsql.com/backup-a-second-this-isnt-a-sql-server-post-my-journey-into-postgresql-backups/</link>
					<comments>https://straightpathsql.com/backup-a-second-this-isnt-a-sql-server-post-my-journey-into-postgresql-backups/#respond</comments>
		
		<dc:creator><![CDATA[Mike Lynn]]></dc:creator>
		<pubDate>Tue, 29 Sep 2026 15:39:50 +0000</pubDate>
				<category><![CDATA[DBA as a Service]]></category>
		<category><![CDATA[PostgreSQL]]></category>
		<category><![CDATA[Growing]]></category>
		<category><![CDATA[learning]]></category>
		<category><![CDATA[Postgres]]></category>
		<guid isPermaLink="false">https://straightpathsql.com/?p=5383</guid>

					<description><![CDATA[<p>I have been working with SQL Server since 2005, and while I love SQL Server, I also love to learn.&#160; One of the platforms I’ve been hearing more about lately is Postgres.&#160; It is being referenced more in blog posts, conference sessions, and I even noticed some SQL Server engineers at Microsoft moving over to ... <a title="Backup a second… this isn’t a SQL Server post? My journey into PostgreSQL backups" class="read-more" href="https://straightpathsql.com/backup-a-second-this-isnt-a-sql-server-post-my-journey-into-postgresql-backups/" aria-label="Read more about Backup a second… this isn’t a SQL Server post? My journey into PostgreSQL backups">Read more</a></p>
<p>The post <a href="https://straightpathsql.com/backup-a-second-this-isnt-a-sql-server-post-my-journey-into-postgresql-backups/">Backup a second… this isn’t a SQL Server post? My journey into PostgreSQL backups</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></description>
										<content:encoded><![CDATA[
<p class="wp-block-paragraph">I have been working with SQL Server since 2005, and while I love SQL Server, I also love to learn.&nbsp; One of the platforms I’ve been hearing more about lately is Postgres.&nbsp; It is being referenced more in blog posts, conference sessions, and I even noticed some SQL Server engineers at Microsoft moving over to the Postgres team.&nbsp;</p>



<p class="wp-block-paragraph">Those couple of events piqued my interest so a few months ago I dipped my toes into the Postgres pool.&nbsp; I started this adventure looking for ways to apply my 20+ years in SQL Server to Postgres so that I can start to help people who are also new to the platform.&nbsp; One of the very first things I learned about in SQL Server was backups, so I thought it would be fitting to start there with Postgres.</p>



<p class="wp-block-paragraph">Most of the readers of this post likely know how SQL Server backups work.&nbsp; You can create a backup of an individual database with a full backup, a differential backup, and a log backup.&nbsp; You can then use those backup files to restore the database onto the same server or a different server.</p>



<p class="wp-block-paragraph">With Postgres, there are two categories of backups: a logical backup, and a physical backup.&nbsp; The logical backup is essentially an export of one or more objects in the database to a file.&nbsp; The contents of that file could have anything from the scripts to create the objects to the insert statements to reload the data into a table.&nbsp; But that is all you can really do with a logical backup &#8211; create a copy of something in a database and restore it somewhere else.&nbsp; Useful for certain tasks, but this cannot be used for other administrative tasks like point in time recovery or building physical standby replicas.</p>



<p class="wp-block-paragraph">A physical backup is where you would start if you needed to perform a point in time restore of a database.&nbsp; Comparing this to SQL Server this is where you’d restore a full backup, then maybe a diff backup, and then roll log backups until you get to your desired end time.&nbsp;</p>



<p class="wp-block-paragraph">One of the big differences between SQL backups and physical Postgres backups is with SQL Server you backup one database; with a physical Postgres backup, you capture the entire Postgres cluster.&nbsp; One of the reasons for this is that all the databases on the Postgres cluster share the same Write-Ahead Log (WAL) file. Because all databases share the WAL file, everything on the cluster is needed for Postgres to guarantee a database is transactionally consistent.&nbsp; If there were objects missing from the cluster, it could cause failures while transactions are being replayed from the WAL file backups.</p>



<p class="wp-block-paragraph">This was a big difference for me when comparing SQL Server and Postgres. With SQL Server I only need to worry about a couple database files and if there is space on the server, but with Postgres there is so much more to take into consideration when preparing to do a point in time restore of a database.&nbsp;</p>



<p class="wp-block-paragraph">Looking forward to learning more about the differences between the two platforms and if there are any similarities.</p>
<p>The post <a href="https://straightpathsql.com/backup-a-second-this-isnt-a-sql-server-post-my-journey-into-postgresql-backups/">Backup a second… this isn’t a SQL Server post? My journey into PostgreSQL backups</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://straightpathsql.com/backup-a-second-this-isnt-a-sql-server-post-my-journey-into-postgresql-backups/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
		<item>
		<title>We Finished SOC 2 Type I (What this is and isn&#8217;t)</title>
		<link>https://straightpathsql.com/we-finished-soc-2-type-i-what-this-is-and-isnt/</link>
					<comments>https://straightpathsql.com/we-finished-soc-2-type-i-what-this-is-and-isnt/#respond</comments>
		
		<dc:creator><![CDATA[Mike Walsh]]></dc:creator>
		<pubDate>Mon, 28 Sep 2026 17:09:13 +0000</pubDate>
				<category><![CDATA[DBA as a Service]]></category>
		<category><![CDATA[Buying DBA Services]]></category>
		<category><![CDATA[Compliance]]></category>
		<category><![CDATA[security]]></category>
		<category><![CDATA[SOC2]]></category>
		<guid isPermaLink="false">https://straightpathsql.com/?p=5371</guid>

					<description><![CDATA[<p>We received our official and final SOC 2 Type I report from Linford &#38; Company, an independent CPA firm, covering our Managed SQL Server Services as of September 15, 2026 today. I&#8217;m proud of it. I&#8217;m also a little wary of how these announcements usually go, because I&#8217;ve read a lot of vendor press releases ... <a title="We Finished SOC 2 Type I (What this is and isn&#8217;t)" class="read-more" href="https://straightpathsql.com/we-finished-soc-2-type-i-what-this-is-and-isnt/" aria-label="Read more about We Finished SOC 2 Type I (What this is and isn&#8217;t)">Read more</a></p>
<p>The post <a href="https://straightpathsql.com/we-finished-soc-2-type-i-what-this-is-and-isnt/">We Finished SOC 2 Type I (What this is and isn&#8217;t)</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></description>
										<content:encoded><![CDATA[
<p class="wp-block-paragraph">We received our official and final SOC 2 Type I report from Linford &amp; Company, an independent CPA firm, covering our Managed SQL Server Services as of September 15, 2026 today. I&#8217;m proud of it. I&#8217;m also a little wary of how these announcements usually go, because I&#8217;ve read a lot of vendor press releases that make a SOC 2 report sound like a force field. So I want to walk through what we actually did, what the report says, what it doesn&#8217;t say, and why we&#8217;re already on the hook for the next one.</p>



<h3 id="h-no-client-asked-us-to-do-this" class="wp-block-heading">No client asked us to do this</h3>



<p class="wp-block-paragraph">That&#8217;s the first thing I want people to know. Nobody made us. We&#8217;ve been doing this since 2011 and we&#8217;ve passed plenty of vendor questionnaires without a SOC report. Maybe the desire to do a couple less vendor questionnaires motivated me, but then I learned that we&#8217;ll still get them &#8211; but I also learned that our managed service provider for IT and MS365 and security will also fill those out for me. So that&#8217;s still a win.</p>



<p class="wp-block-paragraph">When you hire us, you&#8217;re handing us the keys to the kingdom. Our DBAs get privileged access to SQL Servers that hold patient records, member accounts, customer orders, payroll (the stuff that keeps your business running and keeps people&#8217;s lives in order). We help clients get through audits and security reviews all the time. It felt a little hypocritical to keep asking you to trust our answers when we&#8217;d never had anyone outside the company check them and keep watching.</p>



<h3 id="h-what-we-actually-did" class="wp-block-heading">What we actually did</h3>



<p class="wp-block-paragraph">We&#8217;re a 22-person company. Hiring a full-time CISO didn&#8217;t make sense for our size, and the &#8220;buy a compliance platform and click through the checklist&#8221; route didn&#8217;t give me what I wanted, which was someone who knew our business telling me where we were weak. I don&#8217;t want a rubber stamp &#8211; this was not a cheap expense &#8211; and it is a new ongoing line item &#8211; the managed security services and the annual audit we&#8217;ll have to keep our SOC2 Type II up once we (hopefully) achieve that in 6 months is an expense. The extra time to do some things, the process, the forced patching reboots. It&#8217;s all a cost &#8211; but it&#8217;s worth it.</p>



<p class="wp-block-paragraph">So we leaned on <a href="https://www.mstech.com/">Mainstay Technologies</a>, who already run our managed IT and security monitoring. Erin Mealey guided the SOC 2 work start to finish, and internally Evan Corbett (our COO) drove the follow-through while Buck Woody brought a lot of hard-won data security and incident response experience from his roles at Microsoft for the past nearly 20 years.</p>



<p class="wp-block-paragraph">The work itself was a lot of turning things we already did into things we could prove we do: documenting controls, formalizing policies, running every vendor through the same review, security training with evidence behind it, and testing our response plans. We also pulled our monitoring platform, Skopos, and the software we write into scope, because that&#8217;s part of how we serve clients and leaving it out would have made the report look tidier than reality.</p>



<p class="wp-block-paragraph">I expected more misery than we got. The part I figured would be pure box-checking was the disaster tabletop exercises. It wasn&#8217;t. Walking through those scenarios surfaced real risks, and we changed some of our approaches and policies because of them. Turns out we had some homework. I&#8217;d rather find that out in a conference room than at 2 a.m. on a client&#8217;s server.</p>



<h3 id="h-what-a-soc-2-type-i-actually-is" class="wp-block-heading">What a SOC 2 Type I actually is</h3>



<p class="wp-block-paragraph">SOC 2 is an examination framework from the AICPA. An independent CPA firm looks at a company&#8217;s description of its system and its controls against a set of criteria. We were examined against the Security criteria, which is the required baseline for every SOC 2.</p>



<p class="wp-block-paragraph">A Type I is a snapshot <strong><em>only</em></strong>. It says that on a specific date, our controls were in place and suitably designed to meet those criteria.</p>



<p class="wp-block-paragraph">I spent years as a firefighter and EMT, so here&#8217;s how I think about it. A Type I is the inspector walking the building and confirming the smoke detectors are installed, wired correctly, and in the right rooms. A Type II is the inspector coming back and checking the logs to see that they were tested every month and actually went off when there was smoke, and the folks followed the evacuation plan months later.</p>



<h3 id="h-what-it-doesn-t-mean" class="wp-block-heading">What it doesn&#8217;t mean</h3>



<p class="wp-block-paragraph">This is the part I care about most, because transparency matters, it&#8217;s the whole point behind the Buying DBA Services guide posts I&#8217;ve been sharing. Don&#8217;t let our &#8220;SOC2 Achieved!&#8221; woo you into letting your guard down. Even when we hopefully pass our Type II audit.</p>



<ul class="wp-block-list">
<li><strong>It isn&#8217;t a certification.</strong> There&#8217;s no such thing as being &#8220;SOC 2 certified,&#8221; even though you&#8217;ll see vendors say it everywhere. It&#8217;s an attestation report from a CPA firm. We&#8217;ll try hard not to call it a certification, and if you catch us doing it, tell us.</li>



<li><strong>It doesn&#8217;t prove our controls worked over time.</strong> That&#8217;s what Type II is for, and we&#8217;re not there yet.</li>



<li><strong>It doesn&#8217;t mean nothing bad can happen.</strong> No report eliminates risk. Anyone who tells you otherwise is selling something.</li>



<li><strong>It doesn&#8217;t cover your servers.</strong> The report is about how Straight Path runs. We don&#8217;t host your data in our environment. The security of your SQL Servers is still a shared job, and the report even lists things clients are expected to do on their side (auditors call these complementary user entity controls, apparently). We&#8217;ll help you with those, but they&#8217;re yours. We think the time is right for you to have a PAM tool, as much as the DBA in me cringes typing it, but that&#8217;s your decision and a CUEC suggests it &#8211; we can tell you how others have done it but this is up to your security team.</li>



<li><strong>It doesn&#8217;t replace your due diligence.</strong> It gives you better evidence to work with. You should still ask us hard questions, ask our competitors hard questions, get to know the team, and decide if we&#8217;re the right fit.</li>



<li><strong>It doesn&#8217;t mean we changed who we are.</strong> We&#8217;re still the same bunch of SQL Server geeks. We just have more evidence behind the promise and more documentation, process and checkboxes for the team to follow behind the scenes.</li>
</ul>



<h3 id="h-why-it-s-still-worth-it" class="wp-block-heading">Why it&#8217;s still worth it</h3>



<p class="wp-block-paragraph"><strong><em>The</em></strong> <strong><em>SOC 2 (or ISO27001) journey</em></strong> <strong><em>can absolutely be an expensive paperwork exercise that makes a company look secure without making it more secure. I&#8217;ve seen that happen.</em></strong></p>



<p class="wp-block-paragraph">What made it worth it for us came from a few places:</p>



<p class="wp-block-paragraph"><strong>We found our unknown unknowns.</strong> We already had solid basics in place (MFA, background checks, managed devices, active monitoring, phishing tests, perimeter security, see something say something policies, etc.). The value was in the questions we didn&#8217;t know to ask ourselves, and the tabletop drills were the best example. Or pushing the same questions and process on our own vendors was a good takeaway &#8211; rigidity up and down.</p>



<p class="wp-block-paragraph"><strong>The team&#8217;s defaults changed.</strong> I&#8217;ve watched people make more secure-by-default decisions without anyone prompting them. This team was already mature and security already mattered here, but now I can see it in how everyday choices get made.</p>



<p class="wp-block-paragraph"><strong>Rules for everyone make life easier.</strong> When we were five people, a lot of how we worked lived in my head and a couple of other heads. Having clear policies that apply to everyone, me included, has made things simpler than the early days.</p>



<p class="wp-block-paragraph"><strong>Your vendor reviews get easier.</strong> If you&#8217;re a credit union, you&#8217;re expected to do real due diligence on third-party providers and your examiners will ask about it. If you&#8217;re in healthcare, you&#8217;re asking vendors how they protect PHI. Now we can hand you independent evidence instead of a well-written questionnaire answer alone.</p>



<p class="wp-block-paragraph"><strong>The monitoring already proved itself once.</strong> Before the formal SOC 2 work even started, some activity on my laptop tripped an automated response falsely. My laptop got isolated, my account got locked, and Mainstay&#8217;s security team called me. It turned out to be harmless. I was annoyed for about an hour, and then really glad, because that&#8217;s exactly what I want to happen when something looks wrong.</p>



<h3 id="h-why-we-re-already-in-the-type-ii-phase" class="wp-block-heading">Why we&#8217;re already in the Type II phase</h3>



<p class="wp-block-paragraph"><em>A Type I tells you we designed things well on one day. That&#8217;s a real milestone, <strong>but it&#8217;s a starting line</strong>. The question anyone serious about vendor risk will ask next is &#8220;did you keep doing it?&#8221;</em> <em>Even &#8220;are you going to keep doing it diligently forever?&#8221;</em></p>



<p class="wp-block-paragraph">Yeah we are &#8211;> Our six-month Type II observation period with Linford is now underway or about to be (not officially sure how it works &#8211; but it&#8217;s next and we are waiting 6 months to have enough of a period to catch us doing something wrong, and move it to 2027 so we can go annual after that.) During that window we have to operate the controls we described, keep the evidence, and then let the auditors test random samples across the whole period.<strong><em> If a control slips, the report will say so, we can&#8217;t do anything about it. That&#8217;s the point of it.</em></strong></p>



<h3 id="h-why-annual-audits-are-in-our-budget" class="wp-block-heading">Why annual audits are in our budget</h3>



<p class="wp-block-paragraph">This wasn&#8217;t a one-time project, and we&#8217;ve budgeted it that way. After the Type II, we plan to be examined every year.</p>



<p class="wp-block-paragraph">A few reasons:</p>



<ol class="wp-block-list">
<li><strong>Reports age.</strong> A report that covers a period that ended a year ago tells you less and less every month. Auditors themselves recommend producing them annually so there&#8217;s no gap between reporting periods.</li>



<li><strong>Your reviews are annual.</strong> Most of our regulated clients re-review their vendors every year. We should have something current to hand them.</li>



<li><strong>Discipline decays without an outside deadline.</strong> I know myself alllllll too well. Good habits hold up a lot better when someone independent is coming back to check. Outside accountability helps. Plus it makes the process the bad guy, not me with all our process and procedures and security.</li>



<li><strong>It matches how we already do business.</strong> We don&#8217;t lock clients into long-term contracts, because we think we should have to earn your business every quarter. It would be a little strange to then ask you to trust a snapshot from years ago.</li>
</ol>



<p class="wp-block-paragraph">It&#8217;s a line item now, like insurance and training. I&#8217;m okay with that.</p>



<h3 id="h-what-this-means-if-you-work-with-us-or-are-thinking-about-it" class="wp-block-heading">What this means if you work with us (or are thinking about it)</h3>



<p class="wp-block-paragraph">If you&#8217;re a client and your compliance team wants to see the report, reach out to us and we&#8217;ll get it to you, you&#8217;ll see it on your next vendor questionnaire round if you are one of the many who send the links to them. If you&#8217;re evaluating us, ask for it, and ask us anything else too. What we train on, what happens when something goes wrong, what our policies actually say. <strong>Those are fair questions, and you should ask them of every vendor who touches your data.</strong></p>



<p class="wp-block-paragraph">We really care about the data we look after. Behind every row in those tables are people, families, customers, and livelihoods. This report is one piece of evidence that we mean it. The rest we&#8217;ll keep earning the same way we always have &#8211; earning the next renewal by proving ourselves to be a part of your team, not just another vendor.</p>



<p class="wp-block-paragraph">Have a SQL Server environment you want an honest conversation about? <a target="_blank" rel="noopener" href="https://straightpathsql.com/contact">Let&#8217;s talk.</a></p>
<p>The post <a href="https://straightpathsql.com/we-finished-soc-2-type-i-what-this-is-and-isnt/">We Finished SOC 2 Type I (What this is and isn&#8217;t)</a> appeared first on <a href="https://straightpathsql.com">SQL Server Consulting - Straight Path Solutions</a>.</p>
]]></content:encoded>
					
					<wfw:commentRss>https://straightpathsql.com/we-finished-soc-2-type-i-what-this-is-and-isnt/feed/</wfw:commentRss>
			<slash:comments>0</slash:comments>
		
		
			</item>
	</channel>
</rss>
