How to enable MSDTC XA transactions with Powershell

Question: I've read your post on Enable XA transactions for SQL Server , which outlines how to enable XA transactions via the Computer Services module. I'd like to integrate the enabling of XA transactions as part of a scripted SQL Server installation, whoch includes enabling XA transactions for some applications. I'm using Powershell to develop the script . How can I see the current value and also change the value to enable XA ? Answer: It is possible to access and set the MSDTC XA Transactions values using Powershell . You need to make sure your current login has the... Read more →

Powershell Script - Backup SQL databases to a remote drive

Question: Do you have a powershell script to backup SQL databases on a single SQL Server Instance , and direct them to a network share or a local drive? Also , I'd like an email notification with a screen output sent to my email box. The purpose of the script is not to replace the current backup scripting but is for ad-hoc uses Answer: There are plenty of different ways to manage the process of backing up SQL Server databases with Powershell. In this script , the powershell cmdlet Invoke-sqlcmd is used to connect to a sql server , and... Read more →

How to search a different Active Directory Domain with Powershell Get-ADGroupMember

Question: I'm using the Powershell Get-ADGroupMember to iterate through various Groups and return the members. This is working OK - as long as I'm searching in AD groups which are on the same domain server as the server I'm currently logged on. For example - I've logged onto the domain : mydomain1.admin.net . I have authority to check AD groups in this domain. But if I then attempt to do a search on another domain e.g mydomain2.admin.net , even though the domain is in the same AD farm , the Get-ADGroupMember will not find the group. To make things more... Read more →

How create a drive to a network share with Powershell New-PSDrive

Question: I'm trying build some Powershell scripts to automate and standardize the SQL Server patching process. One of the steps is to create a temporary drive with a dedicated letter . This drive should be mapped to a network share. That way the executable , which exists on the network , will run as if on the local server. How can I create the new drive , mapped to a network share in Powershell? Answer: Powershell has a few different cmdlets for this purpose. I like using New-PSDrive . The New-PSDRive allows you to nominate a drive letter , point... Read more →

SQL Server , Meltdown and Spectre FAQ

I'm getting various questions about the Spectre and Meltdown impact on SQL Server. I'll start listing the most popular questions and the response. There is a lot of activity in progress across all the communities about mitigating the impact of Meltdown and Spectre . Typically there will be patching by different vendors and this needs to be managed properly. Where is the Microsoft SQL Server guideance documentation for Meltdown and Spectre ? The main link I reference is Protect SQL Server from attacks on Spectre and Meltdown side-channel vulnerabilities . The main advice is start planning for patching. There is... Read more →