tag:blogger.com,1999:blog-13329238981932910042024-03-15T14:19:27.446+01:00SQLAdminSQL Server administration, infrastructure, automation, instrumentation etc.NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.comBlogger194125tag:blogger.com,1999:blog-1332923898193291004.post-53739887765737885852024-02-03T22:05:00.005+01:002024-02-03T22:06:42.104+01:00PowerShell Advanced Function Template<p>I have made this simple template to start a new PowerShell script file with a advanced function. It helps me to get the basics right from the beginning. Also it supports my effort to write all scripts with advanced functions.</p><p>
The code for the template on a advanced function is on SQLAdmin GitHub (<a href="https://github.com/NielsGrove/SQLAdmin/tree/master/Scripts/PowerShell%20Templates">link</a>).
</p>
<h2>
PowerShell script documentation</h2>
<p>To PowerShell is defined a syntax for comment-based help in scripts (<a href="https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_comment_based_help" title="Microsoft: about_Comment_Based_Help">about_Comment_Based_Help</a>). And there are many more keywords than I have used above, but my personal PowerShell style is covered with these keyword. Feel free to create your own script template with our own personal selection of standard keywords.<br />
Using only standard keywords ensure that your scripts can be presented in a central place with nice and useful documentation. And the presentation can be automatic updated without redundant documentation tasks.</p>
<p>
Michael Sorens has on SimpleTalk posted a series of great posts about automatic documentation on a library of PowerShell scripts. The first post is called "<a href="https://www.simple-talk.com/sysadmin/powershell/how-to-document-your-powershell-library/" title="SimpleTalk: How To Document Your PowerShell Library">How To Document Your PowerShell Library</a>" where the basics are presented in a brilliant way.</p>
<h2>
Advanced function</h2>
<p>If a script is evolving to become a central part of a process then you should elevate it by refactoring it to an advanced function.<br />
The template above is for an advanced function, but not all possibilities are included. The documentation on advanced functions (<a href="https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.core/about/about_functions_advanced" title="MSDN > PowerShell Reference > About Functions Advanced">about_Functions_Advanced</a>) and parameters on advanced functions (<a href="https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.core/about/about_functions_advanced_parameters" title="MSDN > PowerShell Reference > About Functions Advanced Parameters">about_Functions_Advanced_Parameters</a>).</p>
<p>A very important part of an advanced function is the CmdletBinding function attribute (<a href="https://msdn.microsoft.com/powershell/reference/5.1/Microsoft.PowerShell.Core/about/about_Functions_CmdletBindingAttribute" title="MSDN > PowerShell Reference > About Functions CmdletBindingAttribute">about_Functions_CmdletBindingAttribute</a>) that will make a function work like a compiled CmdLet. You can in most cases just use it blank and it will make a function work really nice as a CmdLet, but if you take some time to read and experiment you can create some awesome advanced functions. If you are looking into very large amounts of data you should take a look of the possibilities with CmdletBinding. More technical <code>CmdletBinding</code> is a <a href="http://sqladm.blogspot.dk/2017/05/powershell-accelerators.html" title="SQLAdmin > PowerShell accelerators">PowerShell accelerator</a> of the .NET class <code><a href="https://msdn.microsoft.com/en-us/library/system.management.automation.cmdletbindingattribute.aspx" title="MSDN Library: System.Management.Automation.CmdletBindingAttribute">CmdletBindingAttribute</a></code> that is a part of the namespace <code><a href="https://msdn.microsoft.com/en-us/library/system.management.automation.aspx" title="MSDN Library: System.Management.Automation Namespace">System.Management.Automation</a></code>.</p>
<p>One thing that you will benefit from rather quick is the use of <code>-Verbose</code> or <code>-Debug</code> where it will by passed on through advanced functions. Then you will get ectended information not only from your own code but also from real CmdLets and .NET objects.</p>
<p>There are several articles that covers various facets of advanced functions ok, but my personal reference is, beside the documentation, the book „<a href="https://www.manning.com/books/powershell-in-depth" title="PowerShell In Depth, 1st Edition">PowerShell In Depth</a>“ (e.g. <a href="https://manning-content.s3.amazonaws.com/download/9/dc11f5a-f96e-4ffc-96b0-1829b2307e88/PSiD_sample_ch32.pdf" title="The chapter is available as free download :-)">chapter 32</a>) by Don Jones (<a href="https://twitter.com/concentrateddon" title="twitter > @concentrateddon">@concentrateddon</a>), Richard Siddaway (<a href="https://twitter.com/RSiddaway" title="twitter > @RSiddaway">@RSiddaway</a>) and Jeffery Hicks (<a href="https://twitter.com/JeffHicks" title="twitter > @JeffHicks">@JeffHicks</a>). Look for latest edition.</p>
<h2>
PowerShell script module</h2>
<p>When you have refined the script, I think it is time to move the functionality to one or more PowerShell script modules. The general concepts about PowerShell modules are listed in the article „<a href="https://technet.microsoft.com/en-us/library/dd901839.aspx" title="MSDN Library: Windows PowerShell Module Concepts">Windows PowerShell Module Concepts</a>“ and short described in the MSDN article „<a href="https://msdn.microsoft.com/en-us/library/dd878324.aspx" title="MSDN Library: Understanding a Windows PowerShell Module">Understanding a Windows PowerShell Module</a>“. Right now I will focus on a script module as this text is about PowerShel scripting. There is a short and nice introduction in the MSDN article „<a href="https://msdn.microsoft.com/en-us/library/dd878340.aspx" title="MSDN Library: How to Write a PowerShell Script Module">How to Write a PowerShell Script Module</a>“.</p>
<p>In the script template above I have indicated how to dynamically import a script module placed in a given path. If the script module is not signed and the PowerShell Execution Policy is somehow restricted – which it should be! – you can't import a module placed outside the local computer, e.g. on a file share through a UNC path.</p>
<p>PowerShell script modules are defined and stored in psm1-files, but looks a lot like plain script files. If you use the script template above for a script module the module import and the final lines with invokation should be removed. The invokation should be in the script file importing the script module.</p>
<p>There are several benefits of using PowerShell script modules. Many of them you will see when you start using them...<br />
One thing that surprised me was the value of <a href="https://en.wikipedia.org/wiki/Unit_testing" title="Wikipedia: Unit testing">unit testing</a> the script code.</p>
<p>Michael Sorens (again) has on SimpleTalk written the great article „<a href="https://www.simple-talk.com/dotnet/.net-tools/further-down-the-rabbit-hole-powershell-modules-and-encapsulation/" title="SimpleTalk > .NET > Further Down the Rabbit Hole: PowerShell Modules and Encapsulation">Further Down the Rabbit Hole: PowerShell Modules and Encapsulation</a>“ on PowerShell modules and the surroundings with some very valuable guidelines.</p>
<h2>
History</h2>
2017-03-15 : Blog post created on SQLAdmin blog.<br />
2017-05-17 : Section about script documentation added. Inspired by comment from my old friend and colleague <a href="https://www.blogger.com/profile/15991115017499294057" title="blogger.com: Jakob Bindslet">Jakob Bindslet</a>.<br />
2017-06-14 : Section about PowerShell script module added.<br />
2017-07-04 : Section about PowerShell Advance function added.<br />
2017-11-10 : Blog post text migrated from SQLAdmin blog and code to Github.<br />
2024-02-03 : Blog post migrated (back) to SQLAdmin. Code migrated to SQLAdmin Github.
NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-16486355772196638082024-02-03T22:01:00.002+01:002024-02-03T22:01:54.259+01:00Path environmental variable with PowerShellSee defined paths<br />$env:path.Split(';')<br /><br />Add new path<br />[string]$JdbcAuthPath = 'C:\temp\Install-Manual\sqljdbc6220\sqljdbc_6.2\enu\auth\x64'<br />[string]$OldPath = [System.Environment]::GetEnvironmentVariable('Path')<br />[string]$NewPath = $OldPath + ';' + $JdbcAuthPath<br />[Environment]::SetEnvironmentVariable('Path', $NewPath, [System.EnvironmentVariableTarget]::Machine)<br /><div><br /></div><div>Test new path</div><div>(new session)</div><br />Remove path<br />[string]$OldPath = [System.Environment]::GetEnvironmentVariable('Path')<br />[string]$NewPath = $OldPath.Replace($JdbcAuthPath, $null)<br />[Environment]::SetEnvironmentVariable('Path', $NewPath, [System.EnvironmentVariableTarget]::Machine)<br /><div><br /></div>Test new path<br />(new session)<br /><br />DSC Environment Resource<br />
<p>(https://docs.microsoft.com/en-us/powershell/dsc/environmentresource) </p>
<h2>History</h2>
<p>2024-02-03 Migrated from Azure Admin blog.</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0tag:blogger.com,1999:blog-1332923898193291004.post-20685024010240738652024-01-28T10:18:00.000+01:002024-01-28T10:18:20.586+01:00Integrated Security = SSPI<p> The term <code>Integrated Security = SSPI</code> is a element in a connection string for an application to get access to a data source using a OLEDB or SqlClient provider. Usually it is just used because it is recommended by someone trustworthy.</p>
<p>SSPI is an abbreviation to Security Support Provider Interface which is a general implementation of the Generic Security Services Application Program Interface (GSSAPI). The interesting thing is that SSPI makes it possible to get the most secure authentication in a given context. In Windows it is at least a Kerberos or NTLM authentication. When possible you will get the more secure - and faster - Kerberos authentication. This is done by a negotiation, where some parameters like rank can be configured in Windows. The Windows configuration can be through Group Policies, both local and domain.</p>
<p>In relation to SSPI Kerberos and NTLM are Security Support Providers (SSPs) speaking Windows lingo. The documentation from Microsoft says that you can write our own SSP, but I couldn't find many details on the subject.</p>
<p>Speaking about Microsoft documentation the SSPI documentation as spred across Windows Server documentation on Windows Authentication with "<a href="https://learn.microsoft.com/en-us/windows-server/security/windows-authentication/security-support-provider-interface-architecture" target="_blank">Security Support Provider Interface Architecture</a>" and Windows App Development on Remote Procedure Call (RPC) with "<a href="https://learn.microsoft.com/en-us/windows/win32/rpc/security-support-provider-interface-sspi-" target="_blank">Security Support Provider Interface (SSPI)</a>". In general I find it somewhat difficult to find documentation on the involved components in a login and authentication process.</p>
<p>Using SSPI is done with different parameters and values on each driver or provider.<br/>
With the SqlClient the keyword to the <a href="https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnection.connectionstring" target="_blank"><code>ConnectionString</code></a> property is "<code>Integrated Security</code>" and the value is "<code>SSPI</code>" - which is „strongly recommended“ by Microsoft. Please notice that with SqlClient 5 the namespace is changed from <code>System.Data.SqlClient</code> to <a href="https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient"><code>Microsoft.Data.SqlClient</code></a> as the SqlClient component is removed from the .NET framework and is now available as a NuGet package.<br/>
With the ODBC driver the keyword is "<code>Trusted_Connection</code>" and the value is "<code>Yes</code>" which is an example on the big variations on both keywords and values. If you are using a driver from another vendor than Microsoft or a driver for another environment you should be very aware on these details as they can really bite you.</p>
<p> </p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-78890067480590131972024-01-23T10:23:00.000+01:002024-01-23T10:23:17.806+01:00PowerShell SMO by SqlServer module<p>Install SqlServer module:</p>
<code>Install-Module -Name sqlserver</code>
<p>Typical warning:</p>
<code>Untrusted repository<br/>
You are installing the modules from an untrusted repository. If you trust this repository, change its<br/>
InstallationPolicy value by running the Set-PSRepository cmdlet. Are you sure you want to install the modules from 'PSGallery'?<br/>
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is "N"): y</code>
<p><br /></p>
<p>See installed module:</p>
<code>Get-Module -Name SqlServer -ListAvailable</code>
<p><br /></p>
<p>Import SqlServer module in PowerShell session:</p>
<code>Import-Module -Name SqlServer</code>
<p><br /></p>
<p>Create SMO Server object:</p>
<code>$smoServer = New-Object Microsoft.SqlServer.Management.Smo.Server '(local)\SSDB2019'</code>
<p>See SMO Server object information:</p>
<code>$smoServer.Information</code>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-7195696134735872852024-01-01T15:22:00.000+01:002024-01-01T15:22:09.480+01:00Top-ten posts 2023<p>2023 was – again – a year with far less posts than planned. Fortunately some elder posts still generate some activity on this blog.</p>
<p>The top-ten 2023 post list is something like <a href="https://sqladm.blogspot.com/2023/01/top-ten-posts-2022.html" target="_blank">Top-ten posts 2022</a>.</p>
<table><tbody>
<tr><th>Rank</th><th>Move</th><th>Rank 2022</th><th>Title</th><th>Views</th><th>Created</th></tr>
<tr><td>1</td><td>0</td><td>1</td><td><a href="https://sqladm.blogspot.com/2017/07/sqlbulkcopy-with-powershell.html">SqlBulkCopy with PowerShell</a></td><td>1.950</td><td>2017-07-30</td></tr>
<tr><td>2</td><td>>9</td><td>>10</td><td><a href="High performance script timer">High performance script timer</a></td><td>814</td><td>2020-01-03</td></tr>
<tr><td>3</td><td>+1</td><td>4</td><td><a href="https://sqladm.blogspot.com/2008/08/iso-8601-date-formatting-using.html">ISO 8601 date formatting using PowerShell</a></td><td>479</td><td>2008-08-14</td></tr>
<tr><td>4</td><td>>7</td><td>>10</td><td><a href="https://sqladm.blogspot.com/2012/06/iso-4217-currency-list.html">ISO 4217 Currency list</a></td><td>425</td><td>2012-06-26</td></tr>
<tr><td>5</td><td>+3</td><td>8</td><td><a href="https://sqladm.blogspot.com/2010/02/sql-agent-schedule-owner.html">SQL Server Agent schedule owner</a></td><td>360</td><td>2010-02-08</td></tr>
<tr><td>6</td><td>>5</td><td>>10</td><td><a href="https://sqladm.blogspot.com/2018/09/team-foundation-server-on-single-server.html">Team Foundation Server on a single server</a></td><td>350</td><td>2018-09-19</td></tr>
<tr><td>7</td><td>-1</td><td>6</td><td><a href="https://sqladm.blogspot.com/2014/10/dbcc-checkdb-with-powershell.html">DBCC CHECKDB with PowerShell</a></td><td>306</td><td>2014-10-09</td></tr>
<tr><td>8</td><td>>3</td><td>>10</td><td><a href="https://sqladm.blogspot.com/2012/09/start-shared-hta-with-powershell.html">Start shared HTA with PowerShell</a></td><td>271</td><td>2012-09-13</td></tr>
<tr><td>9</td><td>>2</td><td>>10</td><td><a href="https://sqladm.blogspot.com/2011/01/vlf-count.html">VLF count</a></td><td>247</td><td>2011-01-11</td></tr>
<tr><td>10</td><td>0</td><td>10</td><td><a href="https://sqladm.blogspot.com/2014/01/audit-log-for-analysis-services.html">Audit Log for Analysis Services</a></td><td>204</td><td>2014-01-08</td></tr>
</tbody></table>
<p>This year there are several new items on the Top-ten. My personal biggest surprise is the item on <a href="https://en.wikipedia.org/wiki/ISO_4217" title="Wikipedia: ISO 4217">ISO 4217</a>.<br />
But – still my favority ISO is <a href="https://en.wikipedia.org/wiki/ISO_8601">8601</a>. The oldest post is more than ten years old, which is I find both interesting and disturbing.</p>
<p>The posts in this blog are personal notes rather than public presentations. This is why I am fine with the low number of views.</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-4468739263368276732023-11-02T19:28:00.001+01:002023-11-02T19:28:55.806+01:00Activate AMD-V in Gigabyte BIOS<p> I have replaced my old 1st generation Threadripper with a new Ryzen 9. It was surprisingly smooth and works really good.</p><p>The only thing I had to take a minute to fix was a error when starting VMware Workstation about AMD-V not being enabled as required.</p><p>Going through the menus in the BIOS on the Gigabyte motherboard I found nothing about "AMD-V". But a quick search on AMD-V revealed that <a href="https://en.wikipedia.org/wiki/X86_virtualization#AMD_virtualization_(AMD-V)" target="_blank">AMD-V</a> has been published as AMD Secure Virtual Machine (SVM). And this I could find in the BIOS menus:</p><p></p><ol style="text-align: left;"><li>During startup press <Delete> to enter the BIOS.</li><li>Select "Advanced Mode (F2)".</li><li>Go to "Advanced CPU Settings".</li><li>In the Tweeker menu you pick "SVM Mode" and select "Enabled". Then press <Enter>.</li><li>Select "Save & Exit" then click "Save & Exit Setup" and finally "Yes".</li></ol>Now VMware Workstation works like a charm.<p></p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0tag:blogger.com,1999:blog-1332923898193291004.post-35104995163265166652023-09-21T10:54:00.001+02:002023-09-21T11:16:17.275+02:00Sandbox Windows Server in VMware Workstation<p>To create a sandbox installation in VMware Workstation I usually have to start with a Windows Server. I am using <a href="https://www.vmware.com/nordics/products/workstation-pro.html" target="_blank">VMware Workstation Pro</a> as it makes it possible to build more complex sandbox installations with multiple servers like SQL Server AlwaysOn Availability Groups.</p>
<p>This is a description of the installation and configuration of a general Windows Server to be used in different roles. When the configuration is completed I can clone the virtual machine to build the desired sandbox.</p>
<p>First I download the ISO installation image from Microsoft. Currently it is Windows Server 2023 Standard Edition. To use it I also have to get a license key from my Visual Studio subscription.</p>
<p>Then a new virtual machine is created on the ISO image with the license key. The VM is created with 16 GB memory, 4 vCores and 64 GB hard disk. After installation Windows Update is executed.</p>
<h3>Configurations</h3>
<ul>
<li><i>Network adapter</i>: Change to paravirtual adapter „vmxnet3“. This is a off-line operation I have <a href="https://sqladm.blogspot.com/2019/03/vmxnet3-network-adapter.html" title="SQLadm blog: vmxnet3 network adapter">described</a> earlier.</li>
<li><i>Network adapter</i>: Do not allow to turn off device to save energy.</li>
<li><i>Network adapter</i>: Show hidden devices in Device Manager and remove old Intel Gigabit Network Connection.</li>
<li><i>Keyboard</i>: Add Danish keyboard to English profile and remove English keyboard. This is as I live in Denmark.</li>
<li><i>Location</i>: Change to UK and set to UTC to avoid daylight savings etc.</li>
<li><i>Server Manager</i>: Do not start automatic.</li>
<li><i>Print Spooler</i>: Stop and disable service.</li>
</ul>
<p>Script to do all this will be in SQLAdmin <a href="https://github.com/NielsGrove/SQLAdmin/blob/master/Infrastructure/Sandbox/WindowsServer.ps1" title="SQLAdmin Github: WindowsServer.ps1">Github</a>.</p>
NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-61490341363216443492023-08-08T12:31:00.003+02:002023-08-08T12:31:57.594+02:00Being Effective<p>Being Effective is often said to be a good thing. And naturally we have an idea of what is effective. But in english there are three different words on different aspects of being effective:</p>
<h3>Effectiveness</h3>
<p>"Doing the right thing". That is getting the desired result. I usually think of this as not goofing around but focus on the main task.</p>
<h3>Efficiency</h3>
<p>"Doing things right". That is without waste of time, materials, energy and other resources. And still with success.</p>
<h3>Efficacy</h3>
<p>"Doing with maximum response". Usually this term is used in pharmacology when maximum response is achieved.</p>
<p>In Danish we only have one word on being effective (Effektiv)...</p><p>The three terms are also discussed in the article "<a href="https://nesslabs.com/efficacy-effectiveness-efficiency" target="_blank">The difference between efficacy, effectiveness and efficiency</a>".</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-9061791284898989492023-07-27T08:27:00.001+02:002023-07-27T09:42:18.980+02:00Migrate from HTA to SPA<p>Microsoft <a href="https://en.wikipedia.org/wiki/HTML_Application" target="_blank" title="wikipedia: HTML Application">HTML Application</a> (HTA) technology is based on Internet Explorer. And this is obsolete and will soon be removed. A HTA is running with mshta.exe, which is existing outside Internet Explorer and a default part of Windows 11. But as it is based on Internet Explorer we must face that HTA is going away too. A solid indicator is that the HTA documentation at Microsoft is no longer maintained and placed in a historical <a href="https://learn.microsoft.com/en-us/previous-versions//ms536471(v=vs.85)" target="_blank" title="MSDN redirect">path</a>.</p>
<p>HTA is a quick and neat way to build a nice and dynamic GUI for local tools. This has made HTA rather popular for many years, and there are a lot of solutions based on HTA. So migrating from HTA will be a significant task to many.</p>
<p>You can take the opportunity to migrate to a more complete technology stack like .Net with C#, but there will be a lot new tools to learn. Some might consider to go with PowerShell, but both WinForms and WPF are quite complex with PowerShell in comparison to dynamic HTML in HTA.</p>
<h3>Single-Page Application</h3>
<p>Another suggestion I have seen (<a href="https://learn.microsoft.com/en-us/answers/questions/403154/what-is-the-future-of-mshta-exe-since-internet-exp" title="Microsoft: What is the future of mshta.exe since Internet Explorer is officially going away?" target="_blank">link</a>) as a replacement to HTA is <a href="https://en.wikipedia.org/wiki/Single-page_application" target="_blank" title="wikipedia: Single-Page Application">Single-Page Application</a> (SPA), where it is possible to use dynamc HTML and make the solution local on the client.</p>
<p>This works fine on very simple things. It is even possible to make a sensible filestructure for the SPA with external script and style files. But when you want to work with external resources like a database or the content of a file you hit rock buttom!</p>
<p>With HTA you can access external resources through <a href="https://en.wikipedia.org/wiki/ActiveX" target="_blank" title="wikipedia: ActiveX">ActiveX</a> models or objects like <a href="https://learn.microsoft.com/en-us/sql/ado/microsoft-activex-data-objects-ado" target="_blank" title="Microsoft: ActiveX Data Objects">ActiveX Data Objects</a> (ADO) or FileSystemObject. And modern web browsers can't handle ActiveX. There is a Internet Explorer setting in Microsoft Edge, but that will most likely be removed.<br />I think that the real problem to Microsoft here is security, as ActiveX has given a lot of trouble on the internet. The COM technology that ActiveX is build on is still solid and widespread.</p>
<p>The conclusion must be that HTA can't be migrated to SPA as most HTAs are working on external resources like files or databases.<br />If I find a good replacement to HTA I will let you know.</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-15046532080734080582023-05-17T21:06:00.016+02:002023-05-17T21:21:41.482+02:00Phytagoras hypotenuse with PowerShell<p>To get the hypotenuse of a right angle triangle with Pythagoras formular using the two Methods <a href="https://learn.microsoft.com/en-us/dotnet/api/system.math.sqrt" target="_blank">Sqrt</a> and <a href="https://learn.microsoft.com/en-us/dotnet/api/system.math.pow" target="_blank">Pow</a> of the .NET <a href="https://learn.microsoft.com/en-us/dotnet/api/system.math" target="_blank">System.Net</a> class with PowerShell it is possible in just one line:</p>
<code>[System.Math]::Sqrt( [System.Math]::Pow(11.68, 2) + [System.Math]::Pow(7.24, 2) )</code>
<p>Which will give the implicit formatted result</p>
<code>13.7419067090415</code>
<p>Actually you do not have to spell out the namespace <a href="https://learn.microsoft.com/en-us/dotnet/api/system" target="_blank">System</a> as it is implicit to PowerShell. But I like to spell it out as a kind of documentation.</p>
<p>This is a example on using .NET features direct with PowerShell. Also I think it shows that you have to know .NET as a PowerShell user.</p>
<p>The numbers are from the dimensions in <a href="https://en.wikipedia.org/wiki/Millimetre" target="_blank">mm</a> of a RJ45 plug. So now you know the inner size of the hole to drill to make it fit.</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-43976828788712307572023-02-14T20:20:00.001+01:002023-02-15T08:07:52.887+01:00Add SQL Agent job step with tokens in output file name<p> If you create a new SQL Agent job step with tokens in output file name using the stored procedure <a href="https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-transact-sql" target="_blank"><code>sp_add_jobstep</code></a> or want to add a filename with tokens to an existing job step using the stored procedure <a href="https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-jobstep-transact-sql" target="_blank"><code>sp_update_jobstep</code></a> you will get a error like</p>
<code style="color: red;"><pre>A fatal scripting error occurred.
Variable SQLLOGDIR is not defined.</pre></code>
<p>In this case I was trying to use the token <code>SQLLOGDIR</code>, but it does not matter which token.</p>
<p>Many users of Ola Hallengren (<a href="https://www.linkedin.com/in/olahallengren/" target="_blank">LinkedIn</a>) Maintenance Solution (<a href="https://ola.hallengren.com/" target="_blank">link</a>) had similar errors. Actually Ola is aware of the challenge - he just forgot to promote his solution ;-)</p>
<p>When you use the stored procedure <code>sp_add_jobstep</code> you with that call the internal stored procedure <code>sp_add_jobstep_internal</code>. You can see the definition of both procedures in the system database <code>msdb</code> with SQL Server Management Studio (SSMS).</p>
<p>The root-challenge is that somewhere along the path from calling <code>sp_add_jobstep</code> to setting the output file name on the job step the content of the parameter is interpreted. But at that point the execution is outside SQL Agent, and the tokens are not available.</p>
<p>The trick - that Ola forgot to promote - is to build a string with each token element seperated.</p>
<code style="color: blue;"><pre>DECLARE @_token_log_directory NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(SQLLOGDIR))';
DECLARE @_token_job_name NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(JOBNAME))';
DECLARE @_token_date NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(DATE))';
DECLARE @_token_time NVARCHAR(MAX) = N'$' + N'(ESCAPE_SQUOTE(TIME))';
DECLARE @_output_file_name NVARCHAR(MAX) = @_token_log_directory + N'\' + @_token_job_name + N'_' + @_token_date + N'_' + @_token_time + N'.txt';</pre></code>
<p>This string can then be used as parameter value to the parameter <code>@output_file_name</code> in the stored procedures to a SQL Agent job step.</p>
<code style="color: blue;"><pre>EXECUTE msdb.dbo.sp_update_jobstep @job_name=N'IndexOptimize - Db1.Schema1.Tbl1',
@step_id=1,
@output_file_name= @_output_file_name;</pre></code>
<p>If you dig into Olas code to install the maintenance solution and search for the string „@TokenLogDirectory“ you will see that Ola build this string element by element. With careful seperation of $-sign and token names.</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-24178115529768373882023-01-27T02:20:00.003+01:002023-01-27T02:22:09.466+01:00UDL-file connection to SQL Server<p>UDL is a component in Windows named Universal Data Link. To be more specific it is a OLE DB tool that is a connection defined in a UDL-file a with a GUI to edit the connection.</p><p>The file is usually crated as a empty file with the type ".udl".</p><p>The GUI has three tabs as follows:</p><h4 style="text-align: left;">Provider</h4><p>A list of local available OLE DB providers for various sources like SQL Server components. The default provider is "Microsoft OLE DB Provider for SQL Server". But the provider "Microsoft OLE DB Driver for SQL Server" is the latest SQL Server provider which I will continue with here as I would recommend using it.</p><p>The provider "SQL Server Native Client" should be avoided as it is deprecated.</p><h4 style="text-align: left;">Connection</h4><p>Basic connection configuration points are available here for different types of connection on the given provider. Some typical configuration points are:</p><p></p><ul style="text-align: left;"><li><i>Server name</i>: The server name can be short or long (FQDN) and can also be with protocol and port information. If you try to select a server the UDL GUI will try to scan the network for a server.</li><li><i>Log on</i>: The default is unfortunately "SQL Server Authentication" but I would recommend "Windows Authentication" in general. This also has a field for Server SPN if you want Kerberos authentication.<br />There are other Active Directory log on that could be relevant to more special situations. These are not available with other providers.</li><li><i>Database</i>: A drop-down filed where a database can be entered or selected. If you want to select a database the UDL GUI will try to connect to the server.<br />A more special situation is the possibility to connect to a database file as a attached database. This is sometimes used in more dynamic development situations.</li></ul>Also there is the button "Test Connection" where the text explains quite well what the button does.<p></p><h4 style="text-align: left;">Advanced</h4><p>This tab will only show some other configuration points. The three tabs together will not show all configuration points.</p><h4 style="text-align: left;">All</h4><p>This is the access to all configuration points on the given provider. Some point for timeout, encryption or Application Name are only available in UDL GUI here.</p><h3 style="text-align: left;">Edit file</h3><p>After entering the values and saving them by clicking OK you can edit the UDL-file with a text editor. This way you can see a working example of a connection string with the given provider and configuration values. This can be quite handy when working on a effective connection string or a string with specific features.</p><h3 style="text-align: left;">Reference</h3><p>Microsoft Docs: "<a href="https://learn.microsoft.com/en-us/sql/connect/oledb/help-topics/data-link-pages" target="_blank">Universal Data Link (UDL) configuration</a>"</p><p>Microsoft Docs: "<a href="https://learn.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server" target="_blank">Microsoft OLE DB Driver for SQL Server</a>"</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-3269783436885171822023-01-01T10:34:00.000+01:002023-01-01T10:34:52.479+01:00Top-ten posts 2022<p>2022 was a year with far fewer posts than expected. But there still were some activity on this blog.</p>
<p>The top-ten 2022 is a little different from <a href="https://sqladm.blogspot.com/2022/01/top-ten-posts-2021.html" title="SQLAdmin Top-ten 2021">top-ten 2021</a>.</p>
<table>
<tbody><tr>
<th>Rank</th>
<th>Move</th>
<th>Rank 2021</th>
<th>Title</th>
<th>Views</th>
<th>Created</th>
</tr>
<tr>
<td>1</td>
<td>0</td>
<td>1</td>
<td>SqlBulkCopy with PowerShell</td>
<td>1530</td>
<td>2017-07-30</td>
</tr>
<tr>
<td>2</td>
<td>>9</td>
<td>>10</td>
<td>Move table to filegroup</td>
<td>1390</td>
<td>2015-09-22</td>
</tr>
<tr>
<td>3</td>
<td>+3</td>
<td>6</td>
<td>Audit Log for Analysis Services</td>
<td>476</td>
<td>2014-01-08</td>
</tr>
<tr>
<td>4</td>
<td>-2</td>
<td>2</td>
<td>ISO 8601 date formatting using PowerShell</td>
<td>439</td>
<td>2008-08-14</td>
</tr>
<tr>
<td>5</td>
<td></td>
<td>N/A</td>
<td>Windows Server 2022 preview on VMware Workstation</td>
<td>391</td>
<td>2021-03-10</td>
</tr>
<tr>
<td>6</td>
<td>-3</td>
<td>3</td>
<td>DBCC CHECKDB with PowerShell</td>
<td>384</td>
<td>2014-10-09</td>
</tr>
<tr>
<td>7</td>
<td>-3</td>
<td>4</td>
<td>xp_instance_regwrite syntax</td>
<td>150</td>
<td>2013-09-10</td>
</tr>
<tr>
<td>8</td>
<td>0</td>
<td>8</td>
<td>SQL Server Agent schedule owner</td>
<td>140</td>
<td>2010-02-08</td>
</tr>
<tr>
<td>9</td>
<td>>2</td>
<td>>10</td>
<td>Generate sqlcmd statements</td>
<td>93</td>
<td>2010-02-10</td>
</tr>
<tr>
<td>10</td>
<td>>1</td>
<td>>10</td>
<td>Sandbox Active Directory in VMware Workstation</td>
<td>82</td>
<td>2018-12-28</td>
</tr>
</tbody></table>
<p>Some posts I have updated since the creation, and then the history is described in the post itself.<br/>The oldest post is more than ten years old, and that I personally find quite satisfying.<br/>There is more than ten times views difference between some posts which is significant.</p>
<p>The blog is more a personal collection of refined notes than public presentations. This is one of the reasons that I am fine with the limited amount of views.</p>
NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0tag:blogger.com,1999:blog-1332923898193291004.post-82785222608973916002022-11-29T22:38:00.008+01:002022-11-30T08:45:20.015+01:00MSDTC Operations<p>Microsoft <a href="https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms684146(v=vs.85)" target="_blank">Distributed Transaction Coordinator</a>, abbreviated MSDTC or just DTC, is a somewhat aged but still very essential technology from Microsoft.</p>
<p>As a SQL Server DBA I have primarily met MSDTC under the <a href="https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine" target="_blank">Linked Server</a> functionality. Here MSDTC serves the technical transactions between the SQL Server installation where the linked server is defined and the target of the linked server on another Windows Server. The MSDTC usage is implicit when using a linked server, primarily as the linked server is based on the <a href="https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms722784(v=vs.85)" target="_blank">OLEDB</a> technology.</p>
<p>As a side note this is why you will often see OLEDB waits in SQL Server when working over a linked server.</p>
<h3 style="text-align: left;">Graphical User Interface</h3>
<p>The primary GUI for MSDTC is the – also – elder Microsoft Management Console (MMC) component named "Component Services". This is a COM+ snap-in for MMC. If you are using a Danish Windows then the component is named "Komponenttjenester". It is not easy to find in-depth documentation on COM+ or MMC, and the voluminous (five-volume) "COM+ Developers´s Reference Library" is unfortunately no longer available in the book stores, that is either as new or used.</p><p>Usually you are working on the local MSDTC. You might find the path in Component Services long, but here it is:</p>
<ol style="text-align: left;"><li>Console Root</li><li>Component Services</li><li>Computers</li><li>My Computer</li><li>Distributed Transaction Coordinator</li><li>Local DTC</li></ol>
<p>When you right-click Local DTC you have access to the properties of the Local DTC with three configuration areas in separate tabs. The first tab is on MSDTC tracing configuration.</p>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_mYIzNpJNz9EPQO-vtyxLf4ka_m39N2_ch7JFKsJt8jB67WFHwtpSbhSvsZ7hF4LB-8xIvXCClk2g4EmUgCwo392TJuSop8xQIXxfEMIvcbCl7eQ8dJ3NH-VJn8IynHU80YOpCfpinhWtBQIJzgy-5qRX_kFejy8gSD5KhYeEZVWHa-CMByJ6pahaWg/s513/MSDTC_Properties_1Tracing.png" style="margin-left: 1em; margin-right: 1em;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_mYIzNpJNz9EPQO-vtyxLf4ka_m39N2_ch7JFKsJt8jB67WFHwtpSbhSvsZ7hF4LB-8xIvXCClk2g4EmUgCwo392TJuSop8xQIXxfEMIvcbCl7eQ8dJ3NH-VJn8IynHU80YOpCfpinhWtBQIJzgy-5qRX_kFejy8gSD5KhYeEZVWHa-CMByJ6pahaWg/s320/MSDTC_Properties_1Tracing.png" /></a>
<p>The second tab is on MSDTC logging configuration.</p>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2lyuQC1dNHvtBn-KzCJgfI5smivugrFoh-ITS68wqfNrtR1AMSGbX_nACIwEBJAdtecxl-xmuwH2FrNeknzbvKujgYBbSe31bXUhbOdBB0HJJYiLea-gfXDVqEYD79wF8zTNueWxnpmbLL28Laby9eOgPHNDGq81CBGubvIeR-SpEppM4xtWE6nyQ_g/s514/MSDTC_Properties_2Logging.png" style="margin-left: 1em; margin-right: 1em;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2lyuQC1dNHvtBn-KzCJgfI5smivugrFoh-ITS68wqfNrtR1AMSGbX_nACIwEBJAdtecxl-xmuwH2FrNeknzbvKujgYBbSe31bXUhbOdBB0HJJYiLea-gfXDVqEYD79wF8zTNueWxnpmbLL28Laby9eOgPHNDGq81CBGubvIeR-SpEppM4xtWE6nyQ_g/s320/MSDTC_Properties_2Logging.png" /></a>
<p>The third and last tab is on MSDTC security configuration.</p>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCGEuVhomwHwsFA3tq0zoC8uKnXbf7l__mrXO9MaQONoVTQ3QrF6ZbN7qni0repnjn1Huvz9t3HNt1fUy2fa3xC3ayQgGgKZmXnZZu6ZSfXq6WMxhVT4SJVnSYfCROAcrRJlIOrwncQFk-6l0ARsrX9tkmBuhcbTIZ6xeFLEpm-1CU9TwasL-5iZsdtg/s511/MSDTC_Properties_3Security.png" style="margin-left: 1em; margin-right: 1em;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCGEuVhomwHwsFA3tq0zoC8uKnXbf7l__mrXO9MaQONoVTQ3QrF6ZbN7qni0repnjn1Huvz9t3HNt1fUy2fa3xC3ayQgGgKZmXnZZu6ZSfXq6WMxhVT4SJVnSYfCROAcrRJlIOrwncQFk-6l0ARsrX9tkmBuhcbTIZ6xeFLEpm-1CU9TwasL-5iZsdtg/s320/MSDTC_Properties_3Security.png" /></a>
<p>All the examples are with default values.</p>
<p>When expanding Local DTC you see two items:</p>
<ul style="text-align: left;"><li>Transaction List</li><li>Transaction Statistics</li></ul>
<p>The first item Transaction List will give you a simple list of active transactions. But there is not much more you can see on each transaction or the transaction history.</p><p>The second item Transaction Statistics give you a rather simple graphical presentation of a few measures. There are no possibilities to drill-down or get other other details.</p>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjLTQzMaJrcMy9yiZ8QJgobfIvfXQgImJ_5gCVGtmAEn0iH2WiKEGxgt_L5Hyno5SUviIp44-pYwDN5fX9-WX6pYlDMcgmgYk20ON1-Q7EsAc7ihevNcmPu6IXea9SbM8h8UdCmjXcsdOemzxOrW06YiCUCTLUocgLI1i-fTINbKaYRFJlRMBtGhWSow/s386/MSDTC_Trans_Stats.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjLTQzMaJrcMy9yiZ8QJgobfIvfXQgImJ_5gCVGtmAEn0iH2WiKEGxgt_L5Hyno5SUviIp44-pYwDN5fX9-WX6pYlDMcgmgYk20ON1-Q7EsAc7ihevNcmPu6IXea9SbM8h8UdCmjXcsdOemzxOrW06YiCUCTLUocgLI1i-fTINbKaYRFJlRMBtGhWSow/s320/MSDTC_Trans_Stats.png" /></a>
<p>I think that the limited monitoring shows the age of the MSDTC component.</p>
<h3 style="text-align: left;">MSDTC Log Files</h3>
<p>Is a standard Windows installation the MSDTC log files are in</p>
<p><code>C:\Windows\System32\MsDtc</code></p>
<p>The log files are in a proprietary binary Microsoft format, with no local tools to read the log files.</p>
<p>You can open the MSDTC log files with the command line tool <a href="https://learn.microsoft.com/en-us/windows-hardware/drivers/devtest/tracefmt" target="_blank">tracefmt.exe</a>. But you will have to download <a href="https://developer.microsoft.com/en-us/windows/downloads/windows-sdk/" target="_blank">Windows SDK</a> to get this tool. With Windows SDK you will get <code>tracefmt.exe</code> in both 32- and 64-bit editions in these (default) locations:</p>
<ul style="text-align: left;"><li><code>C:\Program Files (x86)\Windows Kits\10\bin\10.0.22621.0\x64</code></li><li><code>C:\Program Files (x86)\Windows Kits\10\bin\10.0.22621.0\x86</code></li></ul>
<p>The paths are examples from the current Windows SDK version.</p>
<p>Using <code>tracefmt.exe</code> is not complicated but also not just point-and-click. Read the documentation and spend some time with the syntax to get a output you can use.</p>
<h3 style="text-align: left;">MSDTC Application Error</h3>
<p>Some errors will surface in Windows Application Event Log like this example.</p>
<p style="font-style: italic;">Log Name: Application<br />Source: Microsoft-Windows-MSDTC Client 2<br />Date: 28-11-2022 07:34:19<br />Event ID: 4879<br />Task Category: CM<br />Level: Warning<br />Keywords: Classic<br />User: N/A<br />Computer: SQLDB42.sqladmin.lan<br />Description:<br />MSDTC encountered an error (HR=0x80000171) while attempting to establish a secure connection with system SQLDB666.
</p>
<h3 style="text-align: left;">MSDTC in AlwaysOn Availability Groups</h3>
<p>As SQL Server AlwaysOn Availability Groups are based on Windows Failover Cluster, among others, you will have to go through some configuration details to get a robust transaction even during a failover. There are some basic documentation like <a href="https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-availability-group-for-distributed-transactions" target="_blank" title="Microsoft Documentation: Configure distributed transactions for an Always On availability group">Configure distributed transactions for an AlwaysOn availability group</a>, but I really think you will have to do some more reading, build a sandbox and try several solutions thoroughly before going live.</p><p>This could be important if you are <strike>working</strike> wrestling with an application build with a high-level framework like .NET <a href="https://learn.microsoft.com/en-us/ef/" target="_blank">Entity Framework</a> or Java <a href="https://hibernate.org/" target="_blank">Hibernate</a>, as such frameworks tend to use client-side transactions and then rely on distributed transactions.</p>
NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-17179196146819679352022-08-10T21:27:00.006+02:002024-01-20T09:57:46.750+01:00SqlClient 5.0<p>The .NET data provider SqlClient for SQL Server has been released in version 5.0 for general availability. This is a new major version so you are advised to check the changes, especially the breaking changes.</p>
<p>At first there are added support for TDS 8.0, but that will not affect most developers or their products. Anyway there is a breaking change with this on the Encrypt property. A quick solution in many cases is to add the keyword <code><a href="https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnectionstringbuilder.trustservercertificate" target="_blank" title="Microsoft Learn: SqlConnectionStringBuilder.TrustServerCertificate Property">TrustServerCertificate</a></code> with the value <code>true</code> to the connection string. This will look like this:<br />
<code>…Integrated Security=SSPI;TrustServerCertificate=true;…</code><br />
Using <code>TrustServerCertificate</code> is also described in the Microsoft documentation „<a href="https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax" target="_blank" title="Microsoft Learn: Connection String Syntax">Connection String Syntax</a>“ in the section „<a href="https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax#using-trustservercertificate" target="_blank" title="Microsoft Learn: Connection String Syntax > Using TrustServerCertificate">Using TrustServerCertificate</a>“.</p>
<p>But the added support for SPN on server and failover will be interesting to many working on critical systems using SQL Server AlwaysOn Availability Groups and Kerberos authentication.</p>
<p>I think that the most significant change is the breaking change on added dependency on the Microsoft.SqlServer.Server package. You should really take a detailed look on this if you are working with SqlClient based systems. Even if you are not a developer or architect but "only" Database Administrator or DevOps Engineer. The migration from the namespace <code>Microsoft.Data.SqlClient.Server</code> in the .NET framework to the namespace <code>Microsoft.SqlServer.Server</code> in the SqlClient package will require some sort of refactoring code.</p>
<p>See more in the blog post "<a href="https://techcommunity.microsoft.com/t5/sql-server-blog/released-general-availability-of-microsoft-data-sqlclient-5-0/ba-p/3592710" target="_blank">Released: General Availability of Microsoft.Data.SqlClient 5.0</a>" and the <a href="https://github.com/dotnet/SqlClient/blob/main/release-notes/5.0/5.0.0.md" target="_blank">Release Notes</a> on GitHub.</p>
<h2>History</h2>
<p>2022-08-10 : Post created.<br />2024-01-20 : Update with <code>TrustServerCertificate</code>.</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-60593752826299952672022-07-15T15:36:00.001+02:002022-07-15T15:36:45.176+02:00Execute on a subset of databases<p>Sometimes it is necessary to execute a command or a statement on a subset of the databases in a instance. A quick solution is to generate the commands in a <code><a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql" target="_blank">SELECT</a></code> on <code><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql" target="_blank">sys.database</a></code> and then execute the output.<//p>
<p>But when this has been done a few times for a recurring task the solution becomes boring and tiresome. And then a more automated solution is required.</p>
<p>Automating the entire statement build and execxution over a cursor and using <code><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql" target="_blank">sp_executesql</a></code> looks like a nice T-SQL solution.</p>
<code><pre>DECLARE @DatabaseID AS INT;
DECLARE @DatabaseName AS NVARCHAR(50);
DECLARE @DatabaseCursor AS CURSOR;
SET @DatabaseCursor = CURSOR LOCAL FAST_FORWARD FOR
SELECT name, database_id
FROM sys.databases
WHERE databases.name LIKE 'wso2am!_%' ESCAPE ('!');
OPEN @DatabaseCursor;
FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @BackupSql NVARCHAR(4000) = N'BACKUP DATABASE [' + @DatabaseName + N'] TO DISK =''R:\BACKUP\' + @DatabaseName + '.bak'' WITH COPY_ONLY;';
DECLARE @Msg NVARCHAR(2047) = CONVERT(nchar(27), SYSUTCDATETIME()) + N'Z Backup [' + @DatabaseName + N']...'
RAISERROR (@Msg, 0,0) WITH NOWAIT;
DECLARE @BeginTime DATETIME2 = SYSUTCDATETIME();
EXECUTE master.dbo.sp_executesql @stmt = @BackupSql;
DECLARE @EndTime DATETIME2 = SYSUTCDATETIME();
SET @Msg = N' Duration = ' + CAST(DATEDIFF(s , @BeginTime, @EndTime) AS NVARCHAR(42)) + N's';
RAISERROR (@Msg ,0,0) WITH NOWAIT;
FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
END
CLOSE @DatabaseCursor;
DEALLOCATE @DatabaseCursor;</pre></code>
<p>The example above is part of cloning a subset of databases. I hope that you can use this in other situations.</p>
NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-77840436855798892752022-07-11T10:23:00.000+02:002022-07-11T10:23:08.039+02:00RAISERROR with UNIQUEIDENTIFIER<p>The T-SQL type Uniqueidentifier is not a formatting possibility for at <code><a href="https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql" target=”_blank”>RAISERROR</a></code> message. But converting the value to a string is a quick fix. This example is based on the Uniqueidentifier output from the procedure <code><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobschedule-transact-sql" target="_blank">sp_add_jobschedule</a></code>.</p>
<pre><code>DECLARE @_schedule_uid UNIQUEIDENTIFIER = N'7f6a5df0-a68f-4b07-992f-b7595958061f';
DECLARE @_schedule_code NVARCHAR(50) = CONVERT(NVARCHAR(50), @_schedule_uid);
RAISERROR ( N'Schedule UID = %s', 0,0, @_schedule_code ) WITH NOWAIT;</code></pre>
<p>The output is then</p>
<pre><code>Schedule UID = 7F6A5DF0-A68F-4B07-992F-B7595958061F</code></pre>
<p>A small detail is that with the conversion the value is also converted to upper-case.</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-73496254118216342332022-05-31T22:15:00.003+02:002023-01-01T15:32:47.882+01:00Descriptions<p>Databases and objects in databases on SQL Server can be described with the Extended Property <code>MS_Description</code>. This description can be read by and used with Visual Studio, which gives a handy access to detailed documentation.</p>
<p>Unfortunately does instances and server-level objects like logins not have Extended Properties. So these elements can not have a description direct attached. There are some alternatives you can consider for these elements such like custom registry keys, file in root directory. But please don't create a custom table in the master database!</p>
<h4 style="text-align: left;">Description Lifecycle</h4>
<p>A description can be created with the stored procedure <code><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql" target="_blank">sp_addextendedproperty</a></code>. The procedure has eight parameters to control the name, value and level in the object hierarchy of the extended property. The value of the description has the type <code><a href="https://docs.microsoft.com/en-us/sql/t-sql/data-types/sql-variant-transact-sql" target="_blank">sql_variant</a></code>, and the size can not be more than 7,500 bytes.</p>
<p>Later the description can be changed with the stored procedure <code><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-updateextendedproperty-transact-sql" target="_blank">sp_updateextendedprocedure</a></code>.</p><p>Sometimes a tool or a clone can require the description to be removed. This can be done with the stored procedure <code><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-dropextendedproperty-transact-sql" target="_blank">sp_dropextendedproperty</a></code>.</p>
<p>Extended Properties can be viewed through the catalog view <code><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/extended-properties-catalog-views-sys-extended-properties" target="_blank">sys.extended_properties</a></code> or the function <code><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-listextendedproperty-transact-sql" target="_blank">sys.fn_listextendedproperty</a></code>.</p>
<h5 style="text-align: left;">Example on table description</h5>
<pre>EXECUTE sys.sp_updateextendedproperty
@name=N'MS_Description', @value=N'List of SQL Server collations',
@level0type=N'SCHEMA',@level0name=N'sqladmin',
@level1type=N'TABLE',@level1name=N'collation';</pre>
<h4 style="text-align: left;">SQL Server Management Studio</h4>
<p>In SQL Server Management Studio (SSMS) the description on a object can be set as described above. But there are two more ways to add a description to a object. They are short described below, but the order they are mentioned in does not imply any ranking of the possibilities.<br />
The first way to add a description is in the SSMS table design tool. Right-click on a object in the Object Explorer and click Design. Show the properties of the object with another right-click or press Alt+Enter. Then you can edit the Description property.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJbkW5u3jMCqyFk6Uo4cKmWGecBuIbazIHMHtDbdXeulYpTBbR5TjaLwtLX0QxbgMbsknFSeOAwpA8QDIrQUvTemTvGn_aXsTCyCFkyFHYl5DCFaCIkKtR-gw6VimH5pgEUSWigbVWt9AwuKoAhlepZknkbgtIfQz2Ttk-bs5T1Baaw35TYeadzIVcgg/s1249/tbl_design.png" title="Table design property"><img border="0" data-original-height="205" data-original-width="1249" height="53" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJbkW5u3jMCqyFk6Uo4cKmWGecBuIbazIHMHtDbdXeulYpTBbR5TjaLwtLX0QxbgMbsknFSeOAwpA8QDIrQUvTemTvGn_aXsTCyCFkyFHYl5DCFaCIkKtR-gw6VimH5pgEUSWigbVWt9AwuKoAhlepZknkbgtIfQz2Ttk-bs5T1Baaw35TYeadzIVcgg/s320/tbl_design.png" width="320" /></a></div><br /><br />
The second way to add a description is in the Database Diagrams of a given database. In here you can access the properties of a objects and edit the Description property.<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg90kBSZldRpo9YpTuOM3nUFLusIH8kca2G0ze1dB7ROLYokLBhPWKAecbm_jmPZ4rmZVY5m1nbFeBqvplM8oGmnRP7txZGlp8hzo60yCJgf84peGV1KMypDounXgKuZNid9xjErF-eyyr12J5hN4vffvyTxjcylawlS6n3Kl-wB0WEW1Hnh4dZAW1hgg/s894/db_diag_prop.png" title="Database Diagram property"><img border="0" data-original-height="178" data-original-width="894" height="64" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg90kBSZldRpo9YpTuOM3nUFLusIH8kca2G0ze1dB7ROLYokLBhPWKAecbm_jmPZ4rmZVY5m1nbFeBqvplM8oGmnRP7txZGlp8hzo60yCJgf84peGV1KMypDounXgKuZNid9xjErF-eyyr12J5hN4vffvyTxjcylawlS6n3Kl-wB0WEW1Hnh4dZAW1hgg/s320/db_diag_prop.png" width="320" /></a></div><br /><p></p>
<h4 style="text-align: left;">Visual Studio</h4>
<p>In SQL Server Management Studio (SSMS) extended properties are in the object properties. Here each extended property like <code>MS_Description</code> can be created, viewed, changed and deleted.</p>
<p>A Visual Studio data project can be created on a existing database or with a new database. Open the design of a table and the description in <code>MS_Description</code> is in the properties window, usually down to the right in Visual Studio.</p>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRYyXY_Jdri9xZY9Wr4fUEBTMnX3Bmp22-ZmBycGcRH0zmvkTijPbahBMt8Nisk5b6yjRixRgEzkVwK0_j5tIf6LMYsakkUeX4XCcxV67xm9jIntcnIY95uBzYSE5faMOgOFXPy5kk9tny5Tgvnd49_3GTvbyNly1ENPCOU7ZLarLoA03N7JvD31km8A/s454/table_description.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="241" data-original-width="454" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRYyXY_Jdri9xZY9Wr4fUEBTMnX3Bmp22-ZmBycGcRH0zmvkTijPbahBMt8Nisk5b6yjRixRgEzkVwK0_j5tIf6LMYsakkUeX4XCcxV67xm9jIntcnIY95uBzYSE5faMOgOFXPy5kk9tny5Tgvnd49_3GTvbyNly1ENPCOU7ZLarLoA03N7JvD31km8A/s16000/table_description.png" title="Table description." /></a></div><br />
<p>In the example above the table "collation" has the description "SQL Server collations", that is placed in the extended property MS_Description of the table.</p><p>Actually the value of the description can also be handled (create/change) in a Visual Studio data project.</p>
<h4 style="text-align: left;">Data Classifications</h4>
<p>When Microsoft introduced data classification with SQL Server 2012 this information was stored in extended properties. But with SQL Server 2016 this information is in (hidden) system tables.</p>
<h4 style="text-align: left;">Discussion</h4>
<p>I can only recommend that you put the extended property <code>MS_Description</code> on each database and every object in the databases. At first it wil be a disturbance and somewhat annoying. But when you databases get in production, have lived for a few years and new developers og DBAs are to keep the database alive, then you (hopefully) will experience the benefits. That be in SSMS, Visual Studio or a 3rd part tool.</p>
<p>As a start you could add MS_Description to your static code test. At least on database, data objects and functional object. Later you can extend the test to cover more technical parts of the database like filegroups or users.</p><h4 style="text-align: left;">History</h4><p>2023-01-01 : SSMS section added.<br />2022-05-31 : Entry created.</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-70436342963306086412022-05-05T11:24:00.001+02:002022-05-05T11:24:14.484+02:00Restore AdventureWorks on SQL Server 2019<p> There are several SQL Server sample databases from Microsoft, and one of the easiest to start and still somewhat up-to-date is AdventureWorks. There are various versions and editions of the database, and they are described in the Microsoft document "<a href="https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure" target="_blank">AdventureWorks sample databases</a>".</p><p>This is an example on how to restore AdventuresWorks on a SQL Server 2019 Database Engine instance.</p><p>Before you start restoring you have to make the backup file available to the instance. This can be done by copying the file to the default backup location.</p><p>Then you can start the restore and some minor configurations to make the database available as below.</p>
<p></p>
<pre><code>
USE [master];
RESTORE DATABASE [AdventureWorks] FROM DISK = N'R:\BACKUP\AdventureWorks2019.bak' WITH
MOVE N'AdventureWorks2017' TO N'R:\DATA\AdventureWorks.mdf',
MOVE N'AdventureWorks2017_log' TO N'R:\LOGS\AdventureWorks_log.ldf',
NORECOVERY, STATS = 5;
GO
RESTORE DATABASE [AdventureWorks] WITH RECOVERY;
GO
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 150;
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa];
GO
ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME=N'AdventureWorks2017', NEWNAME=N'AdventureWorks');
ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME=N'AdventureWorks2017_log', NEWNAME=N'AdventureWorks_log');
GO
EXECUTE [AdventureWorks].sys.sp_updateextendedproperty
@name=N'MS_Description',
@value=N'AdventureWorks Sample OLTP Database' ;
GO
-- Option
ALTER DATABASE [AdventureWorks] SET QUERY_STORE = ON;
ALTER DATABASE [AdventureWorks] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO
</code></pre>
<p></p>
<p>The configurations are not necessary to make the database available to you, but they are nice to make it general available and functional on SQL Server 2019. Configuring Query Store on the database is optional, but could be handy on a non-production instance.</p>
NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-67884382022023216932022-04-01T11:03:00.000+02:002022-04-01T11:03:14.001+02:00Check for pending restart<p> Windows pending restart is unfortunately not a singular registration, but is in three differen places in the Windows Registry:</p><p></p><ul style="text-align: left;"><li>HKLM: SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired</li><li>HKLM: System\CurrentControlSet\Control\Session Manager, PendingFileRenameOperations</li><li>HKLM: SOFTWARE\CapaSystems\RestartStatus, Reboot</li></ul>The last one is specific for the Capa (<a href="https://www.capasystems.dk/">CapaSystems.dk</a>) systems, that I have met in a IT infrastructure.<div><p></p></div>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-62925215770805040072022-03-25T10:22:00.001+01:002022-03-25T10:22:44.299+01:00xp_dirtree (T-SQL)<p>It is possible to get the content of a directory from T-SQL with the Extended Stored Procedure (XP) <code>xp_dirtree.</code> This only requires the SQL Server service account to have access to the directory and have browse rights.</p>
<p>Syntax:<br />
<code>EXECUTE master.dbo.xp_dirtree <Directory>, <Depth>, <IsFile></code><br />
<code><Directory></code> nvarchar(???): Full path (name) of directory. Can be UNC path. The value can end with '\' or not.<br />
<code><Depth></code> int: Depth of list in directory.<br />
<code><IsFile></code> int: Show if row is on a file. If the value in non-zero then the result will show if a row is in a file.</p>
<p>The result is a table with the three columns <code>subdirectory</code>, <code>depth</code> and <code>file</code> where<br />
<code>subdirectory</code> is the name of the subdirectory or file in the given directory.<br />
<code>depth</code> is the depth of the directory/file in the given directory. One ('1') is the root of the current directory.<br />
<code>file</code> indicates if the directory is a file ('1') or not ('0'). If not a file then it is a directory.</p>
<p>Example:<br />
<code>EXECUTE master.dbo.xp_dirtree N'D:\MSSQL\Backup', 1, 1;</code><br />
List (backup) files in folder.</p><p>
</p><p>Unfortunately this Extended Stored Procedure is not documentd by Microsoft. Like many other XP's...</p>
<p></p><p></p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337127.365862963821158 -22.5879129 83.986330636178849 47.7245871tag:blogger.com,1999:blog-1332923898193291004.post-30809708146657208022022-01-12T15:47:00.000+01:002022-01-12T15:47:10.820+01:00Using sp_executesql with PowerShell<p> The system extended stored procedure <code>sp_executesql</code> is recommended in general to execute dynamic SQL to protect from SQL injection attack. Using <code><a href="https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql" target="_blank">sp_execute</a></code> in PowerShell is like using any other stored procedure in PowerShell. But I prefer to do it in ADO.NET instead of using dynamic CmdLets like <code><a href="https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd" target="_blank">Invoke-SqlCmd</a></code>. The reason is that I have a rather tight control with the connectivity and can make the call really fast.</p>
<p>The example below is a test on if a database exists with the given name. I have chosen to implement the test with a <code><a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql" target="_blank">COUNT()</a></code> function call so that the result always is the same type (integer) no matter if the database exists or not. Also <code>COUNT()</code> is usually very effective in the statement execution.</p>
<p>First I give the database name to look for:</p>
<code>$DbName = 'SSISDB'</code>
<p>Then I prepare the call and set up the connection to the SQL Server installation:</p>
<code><pre>$SqlInstanceName = '(local)\MSSQL2019'
$CnnStr = "Server=$SqlInstanceName;Integrated Security=SSPI"
$SqlStatement = 'SELECT COUNT(*) AS [db_count] FROM [master].[sys].[databases] WHERE [name]=@dbname;'
$SqlParameters = '@dbname sysname'
$CnnSql = New-Object System.Data.SqlClient.SqlConnection
$CnnSql.ConnectionString = $CnnStr
$CnnSql.Open()</pre></code>
<p>Then I call sp_executesql with the query and get the result. I close the connection as soon as possible to release the session resources:</p>
<code><pre>$CmdSql = New-Object System.Data.SqlClient.SqlCommand
$CmdSql.Connection = $CnnSql
$CmdSql.CommandText = '[master].[sys].[sp_executesql]'
$CmdSql.CommandType = [System.Data.CommandType]::StoredProcedure
$CmdSql.Parameters.Add('@stmt', [System.Data.SqlDbType]::NVarChar, -1).Value = $SqlStatement
$CmdSql.Parameters.Add('@params', [System.Data.SqlDbType]::NVarChar, 500).Value = $SqlParameters
$CmdSql.Parameters.Add('@dbname', [System.Data.SqlDbType]::NVarChar, 128).Value = $DbName
$Rdr = $CmdSql.ExecuteReader()
while ($Rdr.Read()) { $DbCount = $Rdr['db_count'] }
$Rdr.Close()
$CnnSql.Close()</pre></code>
<p>Afterwards I can work with the result from the query:</p>
<code>"Db Count: $DbCount"</code>
<p>This example can be used for calling any stored procedure.</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337227.365862963821158 -22.587912799999998 83.986330636178849 47.7245872tag:blogger.com,1999:blog-1332923898193291004.post-16294110154730984082022-01-07T11:33:00.002+01:002022-01-07T12:15:02.989+01:00Windows 11 on VMware Workstation<p> With VMware Workstation version 16.2 it is prepared for Windows 11. There are more details in the blog post "<a href="https://blogs.vmware.com/workstation/2021/10/workstation-16-2-now-available.html" target="_blank">Workstation 16.2 Now Available</a>". But with the increased requirements for Windows 11 there are some configuration you have to do before installation.</p><p></p><ol style="text-align: left;"><li>Set firmware type to UEFI and enable secure boot. The is required to enable TPM. The setting is in Options > Advanced: Firmware type.</li><li>Set encryption on the virtual machine. This is required to enable TPM. The setting is in Options: Access Control. You have to generate a password for the VM, but that is quite straight forward.</li><li>Add Trusted Platform Module (TPM). This is required by Windows 11.</li></ol>With these configurations in place the Windows 11 installation is quite similar to the Windows 10 installation.<p></p><div>If you plan to use the Windows 11 installation for usage without internet access, you should complete the installation with a local offline account. This is done during the final parts of the installation where you are expected to log on.</div>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337227.365862963821158 -22.587912799999998 83.986330636178849 47.7245872tag:blogger.com,1999:blog-1332923898193291004.post-20445336073703194292022-01-01T12:22:00.000+01:002022-01-01T12:22:01.623+01:00Top-ten posts 2021<p>Looking back at 2021 not only as another year with the Covid-19 pandemic. For various reasons it has only been a year with fewer posts than planned. </p><p>The top-ten from 2021 is very much like the top-ten list from 2020 (<a href="https://sqladm.blogspot.com/2021/01/top-ten-posts-2020.html">link</a>).</p><p></p><ol style="text-align: left;"><li><a href="https://sqladm.blogspot.com/2017/07/sqlbulkcopy-with-powershell.html">SqlBulkCopy with PowerShell</a> (2017-07-30)</li><li><a href="https://sqladm.blogspot.com/2008/08/iso-8601-date-formatting-using.html">ISO 8601 date formatting using PowerShell</a> (2008-08-14)</li><li><a href="https://sqladm.blogspot.com/2014/10/dbcc-checkdb-with-powershell.html">DBCC CHECKDB with PowerShell</a> (2014-10-09)</li><li><a href="https://sqladm.blogspot.com/2010/09/xpinstanceregwrite-syntax.html">xp_instance_regwrite syntax</a> (2013-09-10)</li><li><a href="https://sqladm.blogspot.com/2017/04/t-sql-formatted-duration-t-sql.html">T-SQL formatted duration</a> (2017-04-29)</li><li><a href="https://sqladm.blogspot.com/2014/01/audit-log-for-analysis-services.html">Audit Log for Analysis Services</a> (2014-01-08)</li><li><a href="https://sqladm.blogspot.com/2012/09/start-shared-hta-with-powershell.html">Start shared HTA with PowerShell</a> (2012-09-13)</li><li><a href="https://sqladm.blogspot.com/2010/02/sql-agent-schedule-owner.html">SQL Server Agent schedule owner</a> (2010-02-08)</li><li><a href="https://sqladm.blogspot.com/2014/03/powershell-messagebox.html">PowerShell MessageBox</a> (2014-03-15)</li><li><a href="https://sqladm.blogspot.com/2019/03/windows-storage-spaces.html">Windows Storage Spaces</a> (2019-03-27)</li></ol>The date on each post is the creation date. Some posts I have updated since the creation, and then the history is described in the post itself. The oldest post is more than ten years old, and that I personally find quite satisfying.<p></p><p>Still I do not have many readers, That is fine with me as the purpose of this blog is more a personal collection of refined notes. Looking at the number of views on each post there is a rather big difference. The #1 post has about 20 times more views than post #10.</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337227.365862963821158 -22.587912799999998 83.986330636178849 47.7245872tag:blogger.com,1999:blog-1332923898193291004.post-4710051790368027682021-11-21T00:57:00.000+01:002021-11-21T00:57:05.620+01:00PowerShell Class<p>With PowerShell 5 we were given the possibility to create more "real" custom classes.</p><p>I have some notes, trials and personal examples in SQLAdmin Github (<a href="https://github.com/NielsGrove/SQLAdmin/tree/master/Scripts/Powershell%20class" target="_blank">link</a>).</p>NielsGrovehttp://www.blogger.com/profile/02098378173525245170noreply@blogger.com0Copenhagen, Denmark55.6760968 12.568337227.365862963821158 -22.587912799999998 83.986330636178849 47.7245872