Travis Gan

PowerShell for SharePoint

2025-04-07T07:00:00.001-05:00

SharePoint has been introduced since a long ago and has evolved over time. This post attempts to gather different methods of interacting SharePoint with PowerShell.

SharePoint Management Shell

There are two SharePoint Mangement Shell PowerShell modules. One for SharePoint online and another one for SharePoint Server.

SharePoint Online

This is a Windows PowerShell module to manage SharePoint Settings for organization or site collection level. Useful for SharePoint administrator and site admin. The module is Microsoft.Online.SharePoint.PowerShell and the command naming convention starts with SPO as its noun, as example

Install-Module -Name Microsoft.Online.SharePoint.PowerShell -Scope CurrentUser
Connect-SPOService -Url https://contoso-admin.sharepoint.com -Credential admin@contoso.com

List of SharePoint Online PowerShell commands and its documentation

SharePoint Server

The PowerShell cmdlets comes along with SharePoint Server installation. Useful for SharePoint administrator. To use the SharePoint cmdlets (provided as snap-in, not module) in Windows PowerShell, register the snap-in manually, Add-PSSnapin Microsoft.SharePoint.PowerShell. If it is executed from SharePoint Managemetn Shell, it is automatically registered. Command naming convention usually starts with SP as its noun, as example

Add-PSSnapin Microsoft.SharePoint.PowerShell
Get-SPSite

List of SharePoint Server PowerShell commands and its documentation

PnP PowerShell

This is a PowerShell module with more than 600 commands to interact with SharepPoint Online, Microsoft Teams, Microsoft Entra ID, and etc. It is an open source and community provided library under Microsoft 365 platform community initiative. This module is useful for various team including SharePoint admnistrator, developer, power user and etc. The module is PnP.PowerShell and the command naming convention starts with PnP, as example

Install-Module PnP.PowerShell -Scope CurrentUser
Connect-PnPOnline

List of PnP PowerShell commands and its documentation

Microsoft Graph PowerShell

This is a PowerShell module (recommended PowerShell core, can be used in Windows PowerShell) that acting as API wrapper for Microsoft Graph APIs. Microsoft Graph API cover various aspects from Azure Entra ID, to other Microsoft services like SharePoint, Exchange, Outlook and etc. This module is useful for SharePoint admin, developer and power user. The main module is Microsoft.Graph and for SharePoint specific submodule is Microsoft.Graph.Sites. The command naming convention starts with Mg, as example

Install-Module Microsoft.Graph -Scope CurrentUser 
Connect-MgGraph

List of SharePoint command in Microsoft Graph and its documentation

PowerShell with CSOM / API

SharePoint .NET client-side object model (CSOM) and REST API could be used in PowerShell as .NET object or wrapped as PowerShell command

The CSOM are available to download via SharePoint Online Client Component SDK or SharePoint .NET nuget package.

as example to add/use CSOM (after the CSOM dll been downloaded and location the identified)

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

$Context = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl)

SharePoint REST API V1 (documentation)
SharePoint REST API V2 (documentation)
SharePoint Sites Microsoft Graph REST API (documentation)

Microsoft Certified

2022-11-23T23:40:00.041-06:00

It has been a while since I last pursued a Microsoft certification.

After a few years working as a developer, I focused primarily on SQL Server data platform and business intelligence. I learned from work experiences, Microsoft articles as well as various blogs from the awesome SQL Server communities. To test my level of knowledge and exposure in this field, I also took the challenge of certification for Microsoft Certified Solution Expert for Data Platform and Business Intelligence.

As my role at work continue to evolve, I have been wearing multiple hats and at time switch hat. I found myself heavily involved in Azure AD, Okta, Azure cloud resources, Azure Devops, Github, App / API Gateway, etc.. in addition to SQL Server administration.

So again, I thought it might be a good idea to take up the challenge to the certification in these areas to gauge my knowledge and learn something new that I might not have exposed to previously. At the same time, also reap the benefit of increasing credibility to existing or new employers. Win win.

For those who have not taken Microsoft certification for a while, Microsoft has make some changes on their websites and certification path. Microsoft now also provided free online study material in addition to instructor-led class. You can find the newest information at this Microsoft Learn certification site. In addition to this, at the moment, Microsoft also offers Enterprise Skills Initiative to organization for their employees to learn and take the certification examination for free.

I reviewed different Microsoft certifications and here are 3 that I felt that I have the experience or/and interested in.

Microsoft Azure Administrator (more details)
Manage Azure identities and governance
Implement and manage storage
Deploy and manage Azure compute resources
Configure and manage virtual networking
Monitor and maintain Azure resources

Azure Solutions Architect Expert (more details)
Design identity, governance, and monitoring solutions
Design data storage solutions
Design business continuity solutions
Design infrastructure solutions

DevOps Engineer Expert (more details)
Develop an instrumentation strategy
Develop a Site Reliability Engineering (SRE) strategy
Develop a security and compliance plan
Manage source control
Facilitate communication and collaboration
Define and implement continuous integration
Define and implement a continuous delivery and release management strategy

After few months of study and exploration, I am glad that in addition to the MSCE for Data Platform and Business Intelligence, I am now certified as Azure Administrator Associate, Azure Solutions Architect Expert and DevOps Engineer Expert.

Happy holiday!

Access Office 365 Exchange Online Mailbox with Client Credential Flow

2022-11-02T07:30:00.020-05:00

As of this writing, Microsoft has disabled the basic authentication to Exchange Online, the alternate more secure method to authenticate and access Exchange Online is via Client Credential flow using OAuth token. OAuth (OIDC) Client Credential flow is typically used for background (eg. windows services or daemon) process without interaction with user.

Microsoft provides different APIs to access Exchange Online. To authenticate against these APIs, we need OAuth access token with appropriate permissions when invoking these APIs. And this type of access token is obtained from Azure AD.

A little background, in order to access Microsoft resource eg. Azure, Exchange Online, SharePoint via Microsoft APIs, the entry point via an identity (or service principal). In this case, it would be by creating an Azure AD app registration.

In Azure AD, an API is granted either with delegated permission or application permission. When using Client Credential flow in Azure AD, the access token is acquired as application identity with only the application permission (delegated permission is not used since there is no user). The scope used during access token request is with *./default scope, for example https://graph.microsoft.com/.default or https://outlook.office365.com/.default depending on which API you are using.

To configure the access restriction of the application, we will need the Exchange Online PowerShell module (ExchangeOnlineManagement). It is the Microsoft Exchange Online administrative interface to Microsoft Exchange Online. To install the PowerShell module,

Install-Module -Name ExchangeOnlineManagement -Scope CurrentUser

Connect to the organization Microsoft Exchange Online, enter the credential in the interactive prompt

Connect-ExchangeOnline

Depending if your account has the appropriate role in the organization eg. global admin, exchange admin, etc. it appears to subsequently downloads dynamic module to user path like this, C:\Users\abc\AppData\Local\Temp\tmpEXO_xyzabc10.a12\ where the module seems to contain similar cmdlets like the ones in PowerShell module.

To locate the dynamic module, you can lookup some cmdlet eg.

Get-Command Get-ServicePrincipal

With all the tools and some background covered, here are the two examples of using OAuth to access Office 365 Exchange Online mailbox. One using Legacy Office 365 Exchange Online API to access and read mails of a mailbox via IMAP/POP, and another using Graph API to send mail as the user. Please be aware these are not the only ways, but simply an example. Microsoft provide several other ways to achieve to read and send mails with OAuth.

In order to access the APIs, we need a valid access token with appropriate permission (scope) from Azure AD. First we need to create a application service principal via app registration.

Login to Azure portal, go to Azure Active Directory. Select app registration and select new registration.

Since the application will be using client credential flow, the default option without any redirect URI is sufficient.

Next we will need to create a Client Secret. Select certificates & secrets on the left menu and click on Client secrets and create a new client secret. Keep the Client Secret for later use.

Next we need to grant API permission to the application. Select API Permissions on the left menu, and add a permission.

For Graph API, select Microsoft Graph, click on Application Permissions.

Search for mail, and check Mail.Send. Notice that the permission will allow the application to send mail as any user. We will restrict its access via application access policy described later.

For Office 365 Exchange Online API, select APIs my organization uses tab, search for office and select Office 365 Exchange Online. click on Application Permissions.

Search for and check IMAP.AccessAsApp (if you need to access via POP, check POP.AccessAsApp).

Grant admin consent to the permissions added.

We need to get the ApplicationID (ClientID) and ObjectID of the application service principal for later use. Go to Enterprise Applications on the left menu

Search for the application and note down the ObjectID and ApplicationID.

The app registration is now completed. If needed, you can verify if the access token could be obtained. It is important to know ahead which API you are trying to invoke so you can obtain an access token with correct audience (aud claim in the JWT). For Graph API, use https://graph.microsoft.com/.default as the scope; And for Office 365 Exchange Online API, use https://outlook.office365.com/.default as the scope. Here is an example acquiring an access token for Graph API usage.

$tenantid = 'your tenant id'
$clientid = 'your app client id'
$clientsecret = 'your app client secret'

$token = Invoke-RestMethod -Method Post -ContentType 'application/x-www-form-urlencoded' -Uri "https://login.microsoftonline.com/$tenantid/oauth2/v2.0/token" -Body @{
    client_id = $clientid
    client_secret = $clientsecret
    scope = 'https://graph.microsoft.com/.default'
    grant_type = 'client_credentials'
}

$token.access_token

You can copy/paste to view the decoded access token (JWT) here. See the below example of the aud claim and scope with Mail.Send role (permission) configured earlier.

Legacy Office 365 Exchange Online APIs
In the API permission, we granted IMAP.AccessAsApp or POP.AccessAsApp access to the application service principal. This allows the application to access IMAP or POP. However, the application can't access any mailbox yet. And to allow it to access specific mailbox, we first need to add the service principal in exchange online and then grant that service principal permission (delegation) to the mailbox.

Create an exchange service principal. Use the AppId and ObjectId from the Azure AD application registration.

New-ServicePrincipal -AppId c33aa7**-****-****-****-******ffca0d -ServiceId c21b49**-****-****-****-******f33f47 -Organization 'tenant id' -DisplayName mailtest

To grant permission for service principal to a mailbox (add delegation),

Add-MailboxPermission -Identity "usera@emaildomain.com" -User c21b49**-****-****-****-******f33f47 -AccessRights FullAccess

To check the existing permission (delegation) on a mailbox,

Get-MailboxPermission -Identity "usera@emaildomain.com"

More information to configure for IMAP/POP can be found here.

To verify the IMAP access with the application, you can use this PowerShell script written by one of the Microsoft employee for verification.

$tenantid = 'tenant id'
$clientid = 'c21b49**-****-****-****-******f33f47'
$clientsecret = '***************************'
$maibox = 'usera@emaildomain.com'

.\Get-IMAPAccessToken.ps1 -tenantID $tenantid -clientId $cliendid -clientsecret $clientsecret -targetMailbox $mailbox

Graph API
For Graph API, understand that granting API Application Permission usually allow it access to all tenant resources. For example, Mail.Send permission allows it to send mail as any user; Mail.Read permission allows it to read mail in all mailboxes. To restrict it to specific mailbox / security group, creates an Application Access Policy.

Note, as of this writing, if there is no application access policy existed, the get application access policy cmdlet returns an error.

Get-ApplicationAccessPolicy

Write-ErrorMessage : Ex6F9304|Microsoft.Exchange.Configuration.Tasks.ManagementObjectNotFoundException|The operation couldn't be performed because object 'OU=abcefg.onmicrosoft.com,OU=Microsoft Exchange Hosted Organizations,DC=ABCDE123456,DC=PROD,DC=OUTLOOK,DC=COM\*' couldn't be found on 'AABBCC112233000.ABCDE123456.PROD.OUTLOOK.COM'. At C:\Users\abc\AppData\Local\Temp\tmpEXO_xyzabc10.a12\tmpEXO_xyzabc10.a12.psm1:1113 char:13 + Write-ErrorMessage $ErrorObject $IsFromBatchingRequest + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [Get-ApplicationAccessPolicy], ManagementObjectNotFoundException + FullyQualifiedErrorId : [Server=ABCDEFGHIJ123,RequestId=abcd-efgh-3e96-e79a-123456789012,TimeStamp=Sat, 31 S ep 2022 25:02:47 GMT],Write-ErrorMessage

To add application access policy to restrict application access to certain mailbox or security group, use the AppId during the Azure AD application registration. The PolicyScopeGroupId is the mailbox or security group that can be only access by the app.

New-ApplicationAccessPolicy -AccessRight RestrictAccess -AppId c33aa7**-****-****-****-******ffca0d -PolicyScopeGroupId usera@emaildomain.com -Description "restrict mailtest access"

To test the policy against the restricted account or/and other account, the AccessCheckResult returns if the effective access is denied or granted.

Test-ApplicationAccessPolicy -AppId c33aa7**-****-****-****-******ffca0d -Identity usera@emaildomain.com

From the experience, even thought the test command responded the testing result of the policy, in practical the policy could take more than 1 hour to be effective. From personal experience, it takes about an hour.

To list all existing application access policy,

Get-ApplicationAccessPolicy

Please note that the Application Access Policy only applicable to certain Microsoft Graph application permission. As of the time of writing,

Mail.Read
Mail.ReadBasic
Mail.ReadBasic.All
Mail.ReadWrite
Mail.Send
MailboxSettings.Read
MailboxSettings.ReadWrite
Calendars.Read
Calendars.ReadWrite
Contacts.Read
Contacts.ReadWrite

Exchange Web Services permission scope: full_access_as_app.

More information can be found here.

GitHub Create Private Fork of a Public Repository

2022-08-17T07:00:00.007-05:00

When making a fork of a GitHub public repository, the fork repository is set up with public visibility. However, under certain circumstances, it may be desirable to make the fork repository with private or internal (in an organization) visibility.

Here is one way to indirectly 'forking' a repository with with private or internal visibility.

First creates a new repo (private-repo.git) in Github through the ui or cli.

Then, clone a public repo to local.
--bare option is use to make a copy all branches and tags without mapping them to the remotes origin

Then, push the local to the private repo
--mirror option is use to push all refs

Remove the local copy of public repo .git directory.

git clone --bare https://github.com/original_user/public-repo.git

cd public-repo.git
git push --mirror https://github.com/your_user/private-repo.git

cd ..
rm -rf public-repo.git

To perform any changes,

First clone the private copy to local.
Make changes and push the changes up to private repo.

git clone https://github.com/your_user/private-repo.git
cd private-repo

git commit
git push origin master

For any subsequent changes on the private repo, pull it down to local

git pull origin # or git pull origin branch

# or using fetch/merge

git fetch origin
git merge origin/master # or intended remote branch

To update from public repo to private repo.
First add the public repo to remote. Now there should be two remotes.
One for origin pointing to the private repo (your_user), and one as public pointing to public repo (original_user)

Then pull the latest codes from public to local
Then push to the private repo

To also update all the tags from the public repo to private repo
fetch the public repo tags
push the tags to private repo

cd private-repo
git remote add public https://github.com/original_user/public-repo.git

# check list of remote
git remove -v

# Creates a merge commit
git pull public master 

git push origin master

git fetch public --tags
git push origin --tags

# if there is a need to remove the local tag to let public tag take precendent
git tag -d <tag>

# if there is a need to remove the remote tag
git push origin --delete <tag>

Create Sharepoint Site with Azure Function using PowerShell

2021-04-08T23:22:00.010-05:00

Business Case

Create SharePoint site based on user requirement without SharePoint Admin manually performing the task.

Design

A SharePoint List is created and shared with designated users to enter the desired SharePoint site criteria. Using an Azure function with PnP PowerShell module (compatible with PowerShell core in Azure function) to periodically check if any new entry in the SharePoint List and create SharePoint site based on the user entered criteria.

Create a SharePoint List

Create a new SharePoint List. In this example, it is named as NewSiteList.

Create necessary fields/columns to capture new SharePoint site criteria.

Create an account to access SharePoint List

Create an account in Azure AD which would be used by the Azure function to access the SharePoint List. Similar account is also used to create SharePoint site.

Grant account permission

Grant the account read (and write access in needed to update the list) access to the list.

Check the setting where user can create site or set site creation permission accordingly.

Create Azure function

Select code and PowerShell Core as runtime stack with PowerShell 7.0

Store user credential in Azure Key Vault

Azure Key Vault is used to store the credential safely. In order for Azure function to access the Secret in the Key vault, an identity is created for Azure function, and the identity is used for granting access permission in Key vault.

Under identity, on System assigned tab, create system managed identity by selecting On for the status. Copy the Object ID for later use.

Create Key Vault.

Create a Secret each for user and password

Copy the secret identifier for user and password

Grant Get access to Secret for the Azure function system managed identity created earlier

Create new application settings each for the user and password in the Azure function.

Back to Azure function, under Configuration, create new application setting with a name and the secret identifier created previously.

The format of the value is

@Microsoft.KeyVault(SecretUri=<secret identifier>)

Network Traffic Capture Without Installing Any Software

2019-04-25T07:00:00.000-05:00

From time to time, there is a need to capture network traffic for troubleshooting on server. Some network tool like Wireshark is pretty popular for network capturing. However, these tool often need additional installation on the server, and depending on your security team rules, it may take days to be installed on the server.

There is an alternate way to capture network traffic on Windows OS without additional software installation. Using Netsh trace. Most of you may familiar with Netsh for different type of common purpose like firewall, http listener, network interface info, etc. Netsh could be also used to collect network trace.

Open the elevated command prompt / powershell,

netsh trace -?

It shows the list of parameter purpose, examples and other useful information on netsh trace

To simply start a trace,
netsh trace start capture=yes tracefile=c:\nettrace-example.etl

To stop the trace,
netsh trace stop

These are the trace files generated.

You can copy the trace file to a computer that has netmon (network monitor) installed. You may download the software here. Presumably you can at least install the software easily on your computer.

Open netmon and read the trace file.

You may see the parser issue on the description.

For those who paid close attention during the netmon installation, it also prompted to install the parser. However, the parser is not configured to be active by default. To configure the parser, go to tools

Select Windows, and click Set As Active

Now the description is more useful and ready for troubleshooting and analysis.

That's it! Happy troubleshooting. More information on netmon filter could be found here.

Inserting and Hosting 551 Millions Records Cheaply - Case Study

2019-02-11T23:04:00.000-06:00

Recently I worked on a pet project with cloud implementation. There was a dump file in text format with 551 million line of records which needs to be stored in a way where filtered results could be quickly returned from a query, and with a goal of low cost implementation.

The dataset is static. Large number of records. No relational requirement. The storage and querying has to be fast and low cost. Azure table storage seems to be a good candidate for this purpose. Azure table storage is a NoSQL key value store and is very cheap.

The dataset (with some Azure table metadata) is estimated to be around 50 GB when it is stored in Azure table storage. With the choice of locally redundant storage (LRS), the price of the dataset storage is ~50 GB * $0.07 / GB per month = ~$3.50 per month. For the data transfer calculation, Microsoft charges $0.00036 / 10,000 transaction for table (that includes read, write, delete). If it was to insert 551 million record transactions, it would cost $19.84. But we could better. More on that later.

Although the application and API used against the Azure table would be made public, this pet project is more an educational project for myself so I don't expect large traffic (way less than 10,000). The operating transaction cost would be minimal.

Back to the text file. It contains 551 million records. To increase the insert performance into Azure table storage, here are the implemented strategy.

- Provisioned an Azure VM in the same region. Network latency and throughput is much better compare with personal computer.
- Batched 100 records (max limit) in a transaction whenever possible.
- Built some logic to insert records parallelly. 24 consoles were used to process different records simultaneously.
- Turned Nagle off to increase throughput for table insert.

Azure VM
The process is mostly compute intensive. Fsv2, Fs, F Azure VM type are the compute optimized VM. In this case, Fsv2 (2 vcpu, 4 GB RAM, 4 data disks, 4000 max iops, $75.89 / month) and standard HDD disk was selected.

Multiple records in batch
Azure table storage has a maximum of 100 records limit in a batch operation. All records in the batch must have same partition key. Batching not only increase insert performance, it also save cost. One batch operation is considered as a transaction. If all batches are 100 records, 551 millions records insert cost would be only $0.1984! In this case, due to the parallel logic put in place, average about 5 out of 6 batches contain 100 records.

Parallel Logic
Single process would be boring and inefficient. The process do need to read the text file line by line and insert into Azure table storage. It doesn't seem like there is any native parallel process of reading file and performing the insert. Especially consider the file is around 25 GB in size and the VM has only 4 GB of memory. I thought of a logic where it could have multiple powershell process its own unique section line by line simultaneously with minimum memory required. I picked 24, just an arbitrary number but with previous experience. It does consume the 2 vcpu on VM around 90% the entire time. Good.

Turned off Nagle
Microsoft storage team has an article of how the Nagle algorithm affects the Azure table storage insert performance. Personally has seen quite a significant improvement for Azure queue by turning off Nagle. [System.Net.ServicePointManager]::UseNagleAlgorithm = $false

Once these are setup with a new Azure table created, I ran the powershell to process the records and insert into the Azure table. With 24 process running simultaneously, it inserts around 12k batch transaction / minute. Estimating ~90 records per batch transaction, that is average of 18,000 records insert / second.

The total times for inserting 551 millions records into Azure table took about 8 and 1/2 hours.

Setup cost (including get server up and script ready),
Azure VM ~ $1.46
Azure table (storage + transaction) - $3.23
Bandwidth - $0.04
Total initial setup cost: $4.73

VM is only needed for this initial setup process and is decommissioned right after.

Operating cost (including all website components in addition to Azure table)
Azure table (storage) ~ $3
Azure table transaction (it would need to be > 270k transaction to be charged $0.01) - $0
Azure functions (as backend API, $0.20 / million execution, 1 million execution free / month) - $0
Azure static website (angular app size is small < 850 KB) ~ $0
Outbound Bandwidth (First 5 GB is free, between 5GB - 10TB, $0.087 / GB. Estimate application returns around 100 byte per each request) ~ $0
Total monthly operating cost: ~$3

Potential improvements. I could simplify the parallel logic to further increase the ratio of 100 records batches. Also increase the degree of parallelism (eg. 48 or more simultaneous process) along VM with more vpcu to further reduce the duration.

Windows Guy Learning Linux - Basic Command for Linux

2018-06-24T23:00:00.000-05:00

If you have been working mostly with Microsoft technology stacks, switching from Windows and learning Linux could be an exciting and daunting journey. So far my journey to Linux has been fun and I love it!

Linux has GUI but mostly (almost entirely) for server and application administration are performed using command line. If you are on Windows workstation, PuTTY software could be used to ssh into Linux server.

Here are some basic and commonly used commands. Hopefully the list is useful for you. Have fun learning!

Terminal
ctrl+c stop the command.
ctrl+d log out from current terminal (just like the command exit)
ctrl+u erase the current line. It clear from the cursor to the beginning of line
ctrl+y redo current line text from ctrl+u
ctrl+l clear screen (or use clear command). Just like cls in windows
ctrl+z send the process to background. Useful when you have a long running process and need to work on terminal. type fg to bring it back

Not that useful on windows keyboard
ctrl+a move the cursor to beginning of line. Or just use keyboard home button
ctrl+e move the cursor to end of line. Or just use keyboard end button

Tips: tab button helps automatically complete the command or file. Just like in cmd or powershell.

Help / Information
man user manual or help for command. eg. man ls show details and usage of ls command. To get out of man page, type q

Commonly use command
pwd current folder path
ls list directory content. eg.
ll is an alias of ls -l. It shows long listing format
ls -al includes showing long listing format including file starting with . (dot). This file is treated as hidden file.

Use ll command to list file / directory permission. More permission info here. For example,

drwxr-xr-x (a directory)
-rw-r--r-- (a file)

r as read permission
w as modify permission
x as execute file or view content of directory permission

1st character indicates a file or directory. d as directory, - as file
2-4 characters indicate the permission of owner of the file/directory
5-7 characters indicate the permission of the group of the file/directory
8-10 characters indicate the permission of all other users

chown user1.group1 <file/folder> change ownership of files/directories. User, group, other (every users). Use -R as recursive for folder permission change
chmod 644 <file/folder> change file/folder permission. eg. If this is a file, 6 as binary is 110, 4 as binary as 100. The file permission would be -rw-r--r--

sudo execute a command as superuser or another user
su change user

cd change current directory. Just like windows

cd - (dash), cd into previous folder (folder before the previous cd command)
cd ~ (tilde), ~ refer to home directory. cd into home directory
cd / (slash), / is the root directory
md make (create) new directory
rm remove folder or file. eg. rm <file>.. rm -r <folder> (r is to remove directory and its contents recursively)
cp copy file/folder
scp secure copy file/folder. eg copying files between hosts using ssh. eg. scp file.txt username@destination_host:/folder/subfolder
touch <file> create empty file. Or use text editor to create new file with content
> output to file. eg. ls > abc.txt
>> append to file. eg. ls >> abc.txt
find <directory> -name <file/folder> search file/folder in the directory. use -iname for insensitive search
locate <file> quickly search through files index db on the system (maybe not up to date)
file <file> show file type (eg. examine if a file is text or archive file)
grep search matching pattern or regular expression. Very useful for matching pattern. Eg. using in conjunction with ps -ef | grep java, to find java process

Archive/Compress tar archive utility (to zip or/and compress). Eg.
tar -cvf test.tar test1 (to archive test1 folder into a tar file. -c as archive, -v for verbose and -f for file
tar -xvf test.tar (to extract all files from archive.tar)
tar -cvzf test.tar.gz test1 (to archive test1 folder and compress into tar.gz file) -z for compress
tar -xvzf test.tar.gz (to extract and uncompress archive.tar.gz)

View / modify file content
cat <file> concatenate and show all contents
less <file> show content one page at a time. Scroll forward with f (or page down button) and scroll backward with b (or page up button).
less -p <pattern> <file> find pattern in the file
nano simple text editor. kind of like notepad. nano is pretty straight forward. For help, press ctrl+G. The ^ (caret) refers to ctrl key. M refer to alt key
vi more advanced (could be unintuitive for beginner) text editor. vi has two modes of operation: command mode and insert mode

press Esc key to put vi in command mode
press i key to put vi in insert mode

vi command mode,
dd delete line
yy copy line. Use mouse to select the word for
p paste
u undo
ctrl+r redo
1G beginning of first line (or :0)
G$ end of last line
:/searchword find word (n for next found word, shift+n for previous)
:%s/<searchword>/<replaceword>/g find searchword and replace with replaceword
:q exit

:q! exit and cancel any change

:wq save and exit

System (storage, process, services, etc)
uname -a show all system information eg linux version
df -h display file system disk space usage. -h for human readable size (eg. MB, GB instead of bytes)
du -sh <folder> display file usage of a folder. -s for summary and -h for human readable size
ps -ef display a snapshot of current process. -ef to show all process using standard syntax
top display linux processes. Like task manager in windows. Example,
h for help
z highlight
c show absolute path
k to kill process pid
u filter user

systemctl -at service systemctl is used to manage services. Show all services
systemctl -t service --state=active Show only active services
systemctl status <service> show service status (eg. enabled, active)
sudo systemctl start <service> start specific service
sudo systemctl stop <service> stop specific service

More administrative commands could be found on this page.

Microsoft Recertification

2015-12-31T14:33:00.001-06:00

Year 2016 is just around the corner. People are in holiday mood and in fact a lot of folks are on vacation at this time around. Project usually take this slow period into account and avoid any heavy personnel involvement and introduce changes only where necessary. This slow time of the year could be a great opportunity to expand knowledge and skills, and possibly look into certification / recertification.

Around 2012, Microsoft implemented a recertification requirement for Microsoft Certified Solution Expert (MCSE) certification and Microsoft Certified Solution Developer (MCSD). As oppose to the 'retired' certificates (eg. MCITP SQL Server) that doesn't expire (but will be changed to legacy status), the new MCSE certification expires in 3 years (MCSD in 2 years) and require recertification to maintain active status.

All MCSE recertification could be completed by passing the respective recertification exam. For example, Exam 70-469 for recertification for MCSE Data Platform. Besides taking the recertification exam, there is another option Microsoft announced early this year (2015) for recertification through Microsoft Virtual Academy (MVA). However this option is currently (as of end of 2015) available for only certain MCSE. That includes,

MCSE: Data Platform
MCSE: Business Intelligence
MCSE: Communication
MCSE: Messaging
MCSE: SharePoint

For recertification through MVA, it requires passing all the module assessment for the course. Below is an example showing the modules required for MCSE Data Platform.

So which option is right for you?

Recertification through recertification exam usually require only passing 1 exam. The exam cost $150 USD. The exam usually takes 3-4 hours. Certain exam is available at certified test center throughout the word. Certain exam is also available as online proctored exam delivery. To prepare for the exam, exam taker could take advantage of practice test, prep videos or even taking instructor-led training with Microsoft learning partners. Also, Microsoft offers discount or Second Shot (free retake) to these exam from times to times. There is a promotion offered currently for certain exam scheduled between July 12, 2015 to Jan 12, 2016.

For recertification through MVA, since not all MCSE (or MCSD) is currently available, this maybe an option only for certain folks, at least for now. As seen in the example above, there are multiple modules. Each module consists of multiple parts with videos, presentation and assessment. All assessment required to be completed. MVA courses are available online and you can complete each module at your own pace. There isn't any monetary cost to sign up with MVA, viewing the module content as well as taking the assessment. In addition to taking the required module for recertitication, there are many modules for anyone to learn and expand their knowledge. Keep in mind that upon completion of MVA course, you need to submit your MVA user name and Microsoft Certification ID (MC ID) to certquest@microsoft.com for them to verify and update your transcript. There is also MVA community to provide support and helps for your questions.

So, pick the option you seem more suitable for you. I chose the MVA route this time around as I wanted to explore this new option and the modules offered. My experience has been that while some modules emphasize on targeted expertise and new features, some modules also broaden to different areas as well as bring awareness of new product (eg. machine learning, azure platform). And yes, I am recertified as MCSE Data Platform and MCSE Business Intelligence.

Happy New Year!

SQL Replication - Subscriber On Different Domain

2015-06-29T14:16:00.002-05:00

Setting up SQL Server replication with publisher / distributor on one domain and subscriber on another domain could be tricky. It could be even more interesting when these servers have the same host name. This blog post discuss issues encountered and the steps of addressing them.

Preparation
In this example, two servers with SQL Server installed are setup in Microsoft Azure. Both using different cloud service (different domain) but within the same virtual network. In Microsoft Azure, when creating a virtual machine in a cloud service, Azure assigns the VM to a subdomain of cloudapp.net. Here is the server information.

The first VM (Server A)
Host Name: SQL2014
Domain: CloudLan.d10.internal.cloudapp.net
IP address: 10.0.0.5

The second VM (Server B)
Host Name: SQL2014
Domain: MoonLan.d10.internal.cloudapp.net
IP address: 10.0.0.4

There are both hosted on the same virtual network.

A database, TEST has been previously setup with a publication (PUB_C) and configured for transactional replication. Refer to this post for setup.

Communication Verification

The FQDN (Fully Qualified Domain Name) is required to be used for these two servers communication as they are on the different domain.

Attempt to ping Server B (with its FQDN) from Server A successful. Verified that they are able to communicate to each other. Connecting to Server B default SQL server instance from Server A successful with the FQDN of Server B.

Perform the same test and verify the connecting status on Server B connecting to Server A default SQL Server instance successful. So far so good. Each server is able to communicate to the other server as long as server FQDN is used.

Replication - Adding subscription
On SQL Server A, attempt to add SQL Server B as subscriber to this publication

In this example, the Push subscription method is used

Connecting the Server B default SQL Server instance with the FQDN since it is on different domain

Error!

Cannot connect to SQL2014.MoonLan.d9.internal.cloudapp.net.

------------------------------
ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, 'SQL2014'. (Replication.Utilities)

If we read the error message, it states that the actual server name 'SQL2014' is required. It appears that the subscriber server name (server B) used should be the host name of the server, rather than the FQDN name. Or is it?

Let's investigate. On Server B, run this command,

SELECT @@SERVERNAME;
GO
SELECT *
FROM sys.servers;
GO

The sys.servers system table stores the local SQL Server instance name (when server_id = 0) and linked servers (if any, for server_id > 0). The current result for local instance is SQL2014.

A word of caution for the steps below. You do not need to change the sys.servers usually unless the entry in sys.servers is different from the server host name. You may just need to add an alias on SQL Server A (Go to 'Add an Alias for SQL Server B' section below). More information on the conclusion section below.

To verify if SQL replication subscriber error is referring to the host name or this entry in the sys.servers, if we change this record into SQL2014B (with the B),

EXEC sp_dropserver 'SQL2014'
GO
EXEC sp_addserver 'SQL2014B', local;
GO

Notice that @@SERVERNAME does not reflect the change. Let's restart the SQL Server service on Server B. Now both @@SERVERNAME and the sys.servers return SQL2014B. A reminder that, the server name (host name) has not been changed. It is still SQL2014.

$env:COMPUTERNAME

Result returns as SQL2014

Setting up an Alias for SQL Server B
Back to Server A. We need to connect to the SQL Server B instance (SQL2014B) on Server B (SQL2014). One way to accomplish this successfully is to create a SQL Server alias for SQL Server instance (SQL2014B) on Server A. SQL Server Alias provides alternate name to be used to connect to the target SQL Server. Adding SQL Server alias can be done with SQL Native Client in the SQL Server configuration manager, and in this case on Server A.

Note that SSMS is running in 32-bit, while the SQL Server in this case is installed with 64-bit version. While the replication agent process is called from SQL Server agent, the replication process run in 64-bit. So, in this case we will setup an alias in both 32-bit and 64-bit SQL Native Client configuration.

Alias: SQL2014B (Same as the record in sys.servers on SQL Server B)
Port: 1433 (SQL Server default port since it's default instance)
Protocol: TCP/IP
Server: SQL2014.MoonLan.d10.internal.cloudapp.net (This is Server B FQDN)

Now back to addition the replication publication subscriber screen. Enter SQL2014B.

Now it works.

Observation
From we just observed, the server name enter during the subscriber addition needs to be same as the local server entry in the sys.servers of the subscriber. It has nothing to do with the host name of the subscriber server. The local server entry may differs from the host name due to host name (computer name) changes after SQL Server installation. Or like this case it was changed intentionally.

If you didn't setup the alias in 32-bit SQL Native Client configuration, you may continue to encounter the error during setup. Vice versa, if you didn't setup the alias in 64-bit SQL Native Client configuration on 64-bit SQL Server, you may be to complete adding the subscriber, but replication encounter error during synchronization to subscriber.

Conclusion
To regroup, the example above address two issues.

- First, as we see that during the subscriber addition, the server name entered needs to be the same as the local server entry in the sys.servers of the subscriber.
- The second issue in this example is that the host name is the same on both servers, SQL2014 with different FQDN (this issue is not common)

To address the first issue, all we have to do is to create an alias (the entry as in sys.servers, usually same as its server host name) for SQL Server B on Server A (the connecting side). Setting an alias redirect the SQL communication of the alias to the actual SQL Server instance (with FQDN).

However, to address the second issue in this example, it becomes a little tricky. We could change the record in sys.servers of the SQL Server B to be something different (like we saw, SQL2014B) without changing the host name of the server. However, having a different name for SQL Server default instance and the server host name usually is not preferable. It could cause a lot of confusion and potential break some codes and setting configured previously. This may works when it is not an option to change the host name. With that said, always try to have the default SQL Server instance name similar as the server host name.

Note. Changing the host name (compute name) of the server does not change the entry in the sys.servers. You will need to perform the sp_dropserver and sp_addserver steps as shown above to change the SQL Server instance name in sys.servers to match the host name.

This example setup the Push subscription from SQL Server A. As a result, the alias of SQL Server B is setup on the SQL Server A. If you are setting Pull subscription from SQL Server B, the alias of SQL Server A will need to be setup on SQL Server B. Just remember that the alias (of the receiving side) is setup on the connecting side (the connection initializing side).

SQL Server Replication - Case Study & Implementation

2015-06-25T07:00:00.000-05:00

This post discuss of evaluating a business case, with designing and implementing SQL Server replication in a step-by-step tutorial to address the requirement.

Business Case
There are two SQL Servers. One hosted on Server A and one hosted on Server B. These two servers are stand alone server on a similar network but not joined to the same domain. Each SQL Server has a database with similar list of tables. Within this list of tables, a set of tables (A_Tbl*) have read/write transactions performed on the database hosted on SQL Server A, with its data also available on SQL Server B for read only purpose. Similarly, there are another set of tables (B_Tbl*) in the database that have read/write transactions performed on SQL Server B, with the data also available on SQL Server A for read only purpose. Any data change occurred on any table of that database on one SQL Server should be promptly reflected on the other SQL Server.

SQL Server replication stand out to be a good solution to address this business and technical requirement. Replication topology could includes servers that are not in the same domain. The servers do not need to be in a cluster environment or require any domain account. SQL Server replication also provides option for replicating selected objects (tables) to another SQL Server. The receiving database on the other SQL Server is available for use even when new data is being replicated over.

Design & Implementation
Transactional replication is chosen to allows data to be replicated in a continuous manner. The replication topology design in this case have each publisher utilize its own distributor for its publication. The distributor is hosted on the same server as the publisher. Push subscription method is chosen on each server to push the publication articles to another server. A local Windows account will be created and used for replication agent process account as well as connecting to SQL Server.

SQL Server transactional replication is implemented by multiple agents, there are snapshot agent, log reader agent and distribution agent. Here are some description and requirement of each agent.

Snapshot agent
- Prepare snapshot files (schema, script, indexes, data, etc), and record synchronization status in distribution database. Snapshot files are used to initiate subscriber for transactional replication and also used for other replication
- Run at distributor
- Connect to publisher either with a Windows account or SQL account. Connecting account at least db_owner database role in publication database
- Connect to distributor with a Windows account (process account). Process account at least db_owner database role in distribution database. Process account has write permission to snapshot share

Log reader agent
- Monitor transaction log on publication database and copy transaction marked for replication to distribution database
- Run at distributor
- Connect to Publisher either with a Windows account or SQL account. Windows account will be used in this case. The same Windows account (connecting account) with at least db_owner database role in publication database
- Connect to Distributor with a Windows account (process account). Process account with at least db_owner database role in distribution database

Distributor agent (Push subscription)
- Move snapshot in transaction stored in distribution database to the subscriber
- Run at distributor
- Connect to distributor with a Windows account (process account). Process account at least db_owner database role in distribution database. Process account has read permission to snapshot share. Process account a member of PAL (Publication Access List)
- Connect to subscriber with either with a Windows account or SQL account. Windows account will be used in this case. The same Windows account (connecting account) with at least db_owner database role in subscription database

This is the design layout,

As the distributor and publisher of each publication are hosted on the same server, for simplicity, one local Windows account is used for snapshot agent, log reader agent and distributor agent on each server. As both servers are not on the same domain, one Windows account with similar name and password will be used for authentication purpose.

Preparation
In this example, we will create a database called TEST on each SQL Server. The actual server name in the example is SQL2008R2 (acted as Server A) and SQL2008R2A (acted as Server B). Each server only have one default SQL Server instance.

The SQL Server agent service startup type on each server has been changed to automatic. A firewall rule to allow TCP port 1433 has been created on each server for SQL Server communication.

Lets create the database and tables. Connect to SQL Server A and run the database and table creation script. Insert some data into A_tbl tables

USE master;
GO
CREATE DATABASE TEST;
GO
USE TEST;
GO
CREATE TABLE [dbo].[A_tbl1] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[A_tbl2] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[A_tbl3] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl1] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl2] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl3] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
INSERT INTO dbo.A_tbl1 VALUES ('Hello'), ('Hi');
INSERT INTO dbo.A_tbl2 VALUES ('Morning'), ('Night');
INSERT INTO dbo.A_tbl3 VALUES ('SQL'), ('ORACLE');
GO

On SQL Server B, create the database and same tables. Insert some data into B_tbl tables

USE master;
GO
CREATE DATABASE TEST;
GO
USE TEST;
GO
CREATE TABLE [dbo].[A_tbl1] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[A_tbl2] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[A_tbl3] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl1] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl2] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
CREATE TABLE [dbo].[B_tbl3] (
 [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
 [Desc] [varchar](50) NULL ) 
GO
INSERT INTO dbo.B_tbl1 VALUES ('Yes'), ('No');
INSERT INTO dbo.B_tbl2 VALUES ('Food'), ('Drink');
INSERT INTO dbo.B_tbl3 VALUES ('See ya'), ('Bye');
GO

On Server A,
Creates a Windows account for replication agents through computer management
Account: sqlreplication

Grant the Windows account created read / write access to its snapshot replication folder (on its server). In this example, the default location is used. The replication folder is designated during replication setup.

eg. C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata

Create the account in the SQL Server A for the Windows account just created. Grant the local Windows account db_owner role on the publication database (in this case also a subscription database for other publication), TEST database.

On Server B,
Perform the same action like Server A.

Creates a Windows account with the same name and same password like Server A for replication agents through computer management
Account: sqlreplication

Grant the Windows account just created with read / write access to its snapshot replication folder (on its server). In this example, the default location is used. The replication folder is designated during replication setup.

eg. C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\repldata

Create the account in the SQL Server B for the Windows account just created. Grant the local Windows account db_owner role on the publication database (in this case also a subscription database for other publication), TEST database.

SQL Replication Implementation - Publication A
Now we are ready to setup the replication. Let's setup the publication A (PUB_A) on SQL Server A for the three articles (three tables A_tbl1, A_tbl2, A_tbl3). Keep in mind that we will be setting up with push subscription to SQL Server B.

This example mainly use the SSMS GUI for all the replication tasks. So, ready for the screenshot?

Distributor hosted on the same server.

This is where we define the snapshot folder that we grant the sqlreplication Windows account the read write permission.

Use transactional replication

Selecting the A_tbl1, A_tbl2, and A_tbl3 for this publication

We will initiate the snapshot after we finish all the configuration

Using sqlreplication account on Server A for snapshot agent. We have previously configured the account with db_owner role in the TEST database.

Both snapshot and log reader agent using the same local Windows account.

Name this publication as PUB_A

There is one more thing to do here. The sqlreplication Windows account needs to be granted the db_owner role in the newly created distribution database.

Now that the publication has been setup on SQL Server A, lets setup the subscriber (SQL Server B) for this publication. On SQL Server A, creates new subscription

Run all agents at distributor for push subscription.

Add SQL Server B as subscriber to this publication (PUB_A)

Run distribution agent with the sqlreplication local Windows account. Connect to subscriber (SQL Server B) with the Server B sqlreplication local Windows account. This will only work when both local Windows account has the same name and password.

Synchronization (or replication) run continuously

Select initialize when to immediately. This will start the Snapshot agent when the subscription is setup. The snapshot agent will generate the snapshot and synchronization will follow after.

Now with both the publication and subscriber setup.

Go to SQL Server B and verify the three A_tbl tables have updated with the data. Data show up. Great! Let's insert a record at SQL Server A.

USE TEST;
INSERT INTO A_tbl2 VALUES ('Noon');

Check the synchronization status if the new record has sent to the subscriber.

Synchronization completed

Go to SQL Server B. Check the table A_tbl2, new record 'noon' show up.

SQL Replication Implementation - Publication B
The first publication (PUB_A) has been completed. Now we just need to setup the other publication (PUB_B) at the SQL Server B. The steps are similar like the one above.

Here are some of the difference,

Select the B_tbl tables only for this publication

The local Windows account used is the sqlreplication Windows on Server B (NOT Server A), although the name is the same. See the sql2008r2a (with the a)?

The publication named as PUB_B

On subscriber setup, here are some of the difference.

Connecting to SQL Server A to add it as subscriber

Similarly, setup the Server B sqlreplication local Windows account (NOT the server A) as the process account.

Insert a record to B_tbl1 on SQL Server B,

USE TEST;
INSERT INTO B_tbl1 VALUES ('MayBe');

Verify the synchronization status,

Go to SQL Server A, check the B_tbl1 tables.

It has been verified that two publications successfully replicated to each other. Mission accomplished.

Powershell Password Encryption & Decryption

2015-06-19T07:00:00.003-05:00

One of the common task in PowerShell script design and execution is credential encryption requirement. Some privileged account is used and its credential need to pass to the script in order to access resources. It becomes crucial especially when the execution tasks are being delegated to other users or being automated. As storing the password as clear text is huge security risk and the last thing desired, here in this blog post we discuss a few options on storing the credential securely.

If account credential could be avoided being stored in the script, attempt that first. If the predefined / privileged account used is a Windows account (eg. local or domain account), and its process is executed through some task like Windows task scheduler (or PowerShell scheduled job), the privileged account could be setup as the running account of the task and let Windows handles the credential encryption securely during the setup. When the delegated user initiate the task on demand or the task is executed in a schedule automatically, the predefined account as the running account is used to access all the required resources with Windows integrated authentication.
Or assigning with PowerShell

$principal = New-ScheduledTaskPrincipal -UserId DomainA\TestUser
Set-ScheduledTask -TaskName TestingTask -Principal $principal

However often cases different Windows account(s) or third party software account(s) are used to access different type of resource within the execution. In these cases, it is likely that there will be a need to manage the account credential encryption ourselves.

PowerShell provides some native command for encryption. The commonly use command is the ConvertTo-SecureString and ConvertFrom-SecureString.

Take an example, this script is to obtain some server information of a remote server. However, the user who run the PowerShell script does not have the access to a remote server.

Get-WmiObject -Class win32_OperatingSystem -ComputerName RemoteServerA

As the executing account (user Windows account) doesn't have enough privilege, the access denied error is encountered

Get-WmiObject : Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
At line:1 char:2
+ Get-WmiObject -Class win32_OperatingSystem -ComputerName RemoteServerA
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Get-WmiObject], UnauthorizedAccessException
+ FullyQualifiedErrorId : System.UnauthorizedAccessException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

To incorporate a predefined Windows account which has been granted permission to the server to obtain this information,

$User = 'TestUser'
$Password = 'B@dPassw0rd!'
$SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
$UserCred = New-Object System.Management.Automation.PSCredential ($User, $SecurePassword)
Get-WmiObject -Class win32_OperatingSystem -ComputerName RemoteServerA -Credential $UserCred

With the password in plain text, we would need to encrypt the password here.

The ConvertTo-SecureString cmdlet converts the password into a System.Security.SecureString object. This object represents text that should be kept confidential, such as removing it from the computer memory when no longer needed.

To pre-encrypt the password, we could use the same command and hard code the encrypted password in the script, save it in another file, in the registry or some other places.

Store encrypted password in a file (txt file)
To save the encrypted password text into a file,

$User = 'TestUser'
$Password = 'B@dPassw0rd!'
$Password | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString | Out-File C:\encrypted.txt

Note: The Force flag is optional starting in PowerShell 7.

ConvertFrom-SecureString cmdlet convert a secure string (System.Security.SecureString) to an encrypted standard string using the Windows Data Protection API (DPAPI) using user account as default (we will discuss some issues with this default setting later in this post) OR with an encryption key if provided. The encrypted password text (eg. '01000000d08c9ddf0115d1118c7a00c04fc29...') is then save into a file.

A better approach is not displaying the password in clear text at all,

$UserCred = Get-Credential
$UserCred.Password | ConvertFrom-SecureString | Out-File C:\encrypted.txt

The Get-Credential prompt for user name and password as directly save into a secure PSCredential object.

Decrypt encrypted password in a file (txt file)
With the password encrypted as stored in the file, now the script simply have to extract the encrypted password and pass it to the PSCredential object,

$User = 'TestUser'
$SecurePassword = Get-Content C:\encrypted.txt | ConvertTo-SecureString
$UserCred = New-Object System.Management.Automation.PSCredential ($User, $SecurePassword)
Get-WmiObject -Class win32_OperatingSystem -ComputerName RemoteServerA -Credential $UserCred

Store encrypted password in a file using Export-Clixml (xml file)
Another option is to save it as xml file. We could save the PSCredential object with both user and encrypted password to a xml file.

Get-Credential | Export-Clixml -Path C:\encrypted.xml

Decrypt encrypted password in a file using Import-Clixml (xml file)
To load the xml directly back into a PSCredential object

$UserCred = Import-Clixml -Path C:\encrypted.xml
Get-WmiObject -Class win32_OperatingSystem -ComputerName RemoteServerA -Credential $UserCred

Note that the ConvertTo-SecureString converts the encrypted password text into a SecureString object. A lot of (but not all) Windows and 3rd party PowerShell cmdlet utilize the SecureString object for authentication purpose like PSCredential object or Compellent Get-SCConnection. In general, the password does not need to be decrypted back to plain text as string object as it is exposed in the memory until it is removed by the garbage collector.

However, some cmdlet does not utilize the SecureString and require password in plain text. The SecureString object in this case will need to be decrypted into to plain text. If the account information has been constructed into a PSCredential object, the password could be extracted in plain text,

$Password = $UserCred.GetNetworkCredential().Password

OR starting in PowerShell 7

$Password = $UserCred.Password | ConvertFrom-SecureString -AsPlainText

Store encrypted password in a file for 3rd party password (txt file)
In the case of encrypting a third party account password,

$SecurePassword = Read-Host -AsSecureString
$SecurePassword | ConvertFrom-SecureString | Out-File C:\encrypted.txt

Decrypt encrypted password in a file for 3rd party password (txt file)
Decrypt 3rd party password into plain text by converting the SecureString into Binary String object,

$User = 'TestUser'
$SecurePassword = Get-Content C:\encrypted.txt | ConvertTo-SecureString
$Marshal = [System.Runtime.InteropServices.Marshal]
$Bstr = $Marshal::SecureStringToBSTR($SecurePassword)
$Password = $Marshal::PtrToStringAuto($Bstr)
$Marshal::ZeroFreeBSTR($Bstr)

The last line ZeroFreeBSTR is to clear the unmanaged memory. OR starting in PowerShell 7

$User = 'TestUser'
$SecurePassword = Get-Content C:\encrypted.txt | ConvertTo-SecureString
$Password = $SecurePassword | ConvertFrom-SecureString -AsPlainText

Store encrypted password in registry
Another method of saving encrypted password is to save it in registry. The example below uses HKCU (HKEY_CURRENT_USER) hive which the registry only applicable to the current user. You may want to use other more appropriate hives (eg. HKLM) for all other users access.

$SecurePasswordText = 'B@dPassw0rd!' | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString
New-Item -Path HKCU:\Software\Test -Value $SecurePasswordText

$UserCred = Get-Credential
New-Item -Path HKCU:\Software\Test -Value ($UserCred.Password | ConvertFrom-SecureString)

OR storing both user name and password in the registry,

New-Item -Path HKCU:\Software\Test
New-ItemProperty -Path HKCU:\Software\Test -Name User -Value ($UserCred.UserName)
New-ItemProperty -Path HKCU:\Software\Test -Name Password -Value ($UserCred.Password | ConvertFrom-SecureString)

To decrypt encrypted password stored in registry
With the password encrypted as stored in the registry, here is how to extract it for PSCredential in the script,

$User = 'TestUser'
$SecurePassword = (Get-ItemProperty -Path HKCU:\Software\Test).'(Default)' | ConvertTo-SecureString
$UserCred = New-Object System.Management.Automation.PSCredential ($User, $SecurePassword)
Get-WmiObject -Class win32_OperatingSystem -ComputerName RemoteServerA -Credential $UserCred

$User = (Get-ItemProperty -Path HKCU:\Software\Test).User
$SecurePassword = (Get-ItemProperty -Path HKCU:\Software\Test).Password | ConvertTo-SecureString
$UserCred = New-Object System.Management.Automation.PSCredential ($User, $SecurePassword)
Get-WmiObject -Class win32_OperatingSystem -ComputerName RemoteServerA -Credential $UserCred

Issue with default encryption using user account
By default, ConvertTo-SecureString cmdlet uses current user's password to generate an encryption key, and it is stored within the user profile (eg. %Userprofile%\Application Data\Microsoft\Crypto\RSA\User SID for RSA key). The encryption key is then used to encrypt the intended string. The same user's user profile is created independently on different computer. Unless the particular person's user account has been set as roaming profile, the encryption key on his user profile on one computer does not synchronize with his user profile on another computer. This creates some issues. If a person pre-encrypt the password on his computer, then deploy that to another server. First, the encrypted password text can't be decrypted because the encryption key is not present on the person' user profile of the server. Needless to say, other users won't be able to decrypt the password as well because they don't have the encryption key. This is the error received.

ConvertTo-SecureString : Key not valid for use in specified state.
At line:1 char:336
+ ... bf6ff4d7ae3" | ConvertTo-SecureString
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [ConvertTo-SecureString], CryptographicException
+ FullyQualifiedErrorId : ImportSecureString_InvalidArgument_CryptographicError,Microsoft.PowerShell.Commands.Conv
ertToSecureStringCommand

If it is only to resolve the issue locally on one server, a task could be setup in a way that always use the same running account which perform execute the script. This way, we could encrypt the predefined account password using the running account. As the running account has the encryption key stored in its user profile, it is able to decrypt the password during task execution regardless who initiated the task. However, that also means a person will be the developer of the script, the admin of the server, and have access to the running account credential.

To perform encryption with a running account, we could login to the server as the running account, or use the RunAs command in our own remote session to open a PowerShell session as the running account to perform the encryption,

runas /profile /user:RunningAcctA powershell.exe

Note that running account profile needs to be loaded in order to store the encryption key as discussed earlier. /Profile is a default parameter. The /profile parameter is listed as an explicit example.

In the PowerShell session. use whoami to identify the account of that session. Once verified, generate the encrypted password file from that session.

This trick applies to using LocalSystem account as well. The easiest way to run a PowerShell session as LocalSystem is using PsExec by Mark Russinovich from Microsoft. You can find this download from this Windows internal link or I have some PsExec example here in this post as well toward the middle section.

psexec -s -i powershell.exe

Keep in mind that if the script is migrated to other server, the encryption step need to be re-performed again as the previous encryption key generated only store in the running account profile of that particular server.

Custom Encryption Key
One other way to address the multiple server and different user issue is to use a specific encryption key. ConvertTo-SecureString cmdlet allows a key to be provided for the encryption. The valid encryption key lengths are 16, 24, and 32 bytes. With the use of encryption key, it allows the encrypted password to be decrypted on different server with different account.

To generate a valid key, we could use RNGCryptoServiceProvider class to generate random number for the key.

$EncryptKey = New-Object Byte[] 16  #An example of 16 bytes key
[Security.Cryptography.RNGCryptoServiceProvider]::Create().GetBytes($EncryptKey)

In this example, a simple 1 to 16 array is used as the key.

[byte[]] $EncryptKey = (1..16)   #An example of a simple key (1. 2, 3,...,14, 15, 16)
$UserCred = Get-Credential
$UserCred.Password | ConvertFrom-SecureString -Key $EncryptedKey | Out-File C:\encrypted.txt

In the script,

[byte[]] $EncryptedKey = (1..16)
$User = 'TestUser'
$SecurePassword = Get-Content C:\encrypted.txt | ConvertTo-SecureString -Key $EncryptedKey
$UserCred = New-Object System.Management.Automation.PSCredential ($User, $SecurePassword)
Get-WmiObject -Class win32_OperatingSystem -ComputerName RemoteServerA -Credential $UserCred

However, by hard cording the encryption key in the script, it exposes the key and allows unintended person to potentially decrypt the encrypted password. There are a few options to manage the key.

Store the key in a file with access privilege granted only to intended user or executing/service account, this should be done with the encrypted password file as well. For example,

$User = 'TestUser'
$SecureKey = Get-Content C:\Key.txt | ConvertTo-SecureString
$SecurePassword = Get-Content C:\encrypted.txt | ConvertTo-SecureString -SecureKey $SecureKey
$UserCred = New-Object System.Management.Automation.PSCredential ($User, $SecurePassword)
Get-WmiObject -Class win32_OperatingSystem -ComputerName RemoteServerA -Credential $UserCred

Just a reminder that if the process is going to be executed by a delegated user, the user account will need the read access to the encrypted password and key file. This method prevent unintended person from obtaining the key. However, if the intent is to prevent the delegated user to obtain the password, this method would not be sufficient. One way to address this is setup a task / service to execute the script with a service account (executing account) and only that account has the read access (through Access Control List, ACL) to the encrypted password and key file. When the delegated user initiates the task / service, the process utilizes the service (executing account) to access the files. This limits the exposure of the encrypted password and key file only to the service account.

There are other option like using a certificate to encrypt the key file. Dave Wyatt has a good post of this here. The way it works is the user needs to have a private key of the certificate in order to decrypt the encryption key. This option faces the similar concern like the key file.

Using DPAPI ProtectedData Class for encryption
DPAPI ProtectedData class provides another method to encrypt and decrypt data. As we discussed earlier that ConvertTo-SecureString uses user account by default, this Protected class provide encryption options as CurrentUser or LocalMachine (LocalSystem profile). For scenario like having delegated users to run the script on the server, the predefined account password could be encrypted with LocalMachine option (scope) and any user could decrypt the password on that machine.

$Password = "B@dPassw0rd!"
$PasswordBytes = [System.Text.Encoding]::Unicode.GetBytes($Password)
$SecurePassword = [Security.Cryptography.ProtectedData]::Protect($PasswordBytes, $null, [Security.Cryptography.DataProtectionScope]::LocalMachine)
$SecurePasswordStr = [System.Convert]::ToBase64String($SecurePassword)

To decrypt the encrypted password,

#$SecurePasswordStr
$SecureStr = [System.Convert]::FromBase64String($SecurePasswordStr)
$StringBytes = [Security.Cryptography.ProtectedData]::Unprotect($SecureStr, $null, [Security.Cryptography.DataProtectionScope]::LocalMachine)
$PasswordStr = [System.Text.Encoding]::Unicode.GetString($StringBytes)

Note that ProtectedData class is returning byte array object (and later convert to string) as opposed to SecureString object. If other cmdlet need the SecureString as its parameter, the password will need to be converted to the SecureString object.

Use SecretManagement and SecretStore (or other 3rd party extension vault) PowerShell module
Microsoft released these two new modules in 2021. SecretManagement module help user to manage secrets that are stored across vaults (local or remote). SecretStore module is a cross-platform local extension vault supported in all environment as PowerShell 7. The SecretStore stores the secrets locally for the current user and uses .NET Core cryptographic APIs to encrypt file content. Even without vault password option, it still encrypt the secrets but the key is stored in current user location.

The modules required to be installed first and some configuration.

Install-Module Microsoft.PowerShell.SecretManagement, Microsoft.PowerShell.SecretStore -Scope CurrentUser
Register-SecretVault -Name SecretStore -ModuleName Microsoft.PowerShell.SecretStore -DefaultVault

# Setting Secret Store Password. It prompts to enter the vault password since not provided
Set-SecretStorePassword

Setting up a secret

# Setting a secret. If not provided, it will promot to enter in secure format
Set-Secret -Name TestUserPassword -Secret 'B@dPassw0rd!'

To retrieve the password, first to unlock the vault. The vault stay unlocked within the PasswordTimeOut setting (default 15 minutes).

# Unlock the vault with the vault password. Provide valut password as SecureString. 
# If not provided, it prompts to enter vault password
Unlock-SecretStore

Get-Secret -Name TestUserPassword -AsPlainText

There are so much more to these SecretManagement and SecretStore (and also 3rd party vault) modules. More details in another post.

Query Hint and Plan Guide

2015-05-05T05:00:00.000-05:00

Query execution plan generated for a SQL query significantly affect the query performance. Parameter sniffing, indexes, statistics and other factors are taken into consideration for SQL Server query optimizer to produce an optimal query plan for the execution within a timely manner. Sometimes, due to certain scenarios and limitation, the query plan generated may be suboptimal. There are multiple ways to address this problem, like modifying the way the query is written, or using query hint or even plan guides under certain circumstances. This blog post discuss an example of using a query hint as well as plan guide.

First, this is a contrived stored procedure to retrieve some sales and product information based on the user input of the product number. It has a LIKE operator in the where condition to allows a specific product number or wildcard value.

CREATE PROCEDURE sp_ProductOrderDetail 
(
  @ProductNumber nvarchar(25),
  @RecodOffset int,
  @RecordNumber int
)
AS
BEGIN
SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;
END
GO

Turn on IO and Time statistics

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

The performance comparison below is tested against warm cache where the data already loaded in memory. Execute first query to return result from the first 1000 records of all the frame (FR% as ProductNumber). Before the execution, we clear the query plan cache (not the data in memory) for better comparison later on.

DBCC FREEPROCCACHE; --Remove the query plan cache (not the data)
GO

EXEC sp_ProductOrderDetail @ProductNumber = 'FR%', @RecodOffset = 0, @RecordNumber = 1000;
GO

The query executed less than 1 second. The query is written to also allow specific product number (SO-B909-L). Let's remove the previous query plan cache (not the data in memory).

DBCC FREEPROCCACHE; --Remove the query plan cache (not the data)
GO

EXEC sp_ProductOrderDetail @ProductNumber = 'SO-B909-L', @RecodOffset = 0, @RecordNumber = 1000;
GO

Notice that the execution plan is different from the previous query plan? SQL Server optimizer comes up with this query plan with the parameter value sniffed (SO-B909-L). Instead of scanning the respective sales header and sales details records and join with respective product, it identifies the product record first and join it with respective sales details and sales header. This may due to the records in scope is much smaller since the exact product number is provided.

As SQL Server reuse previously cached query plan, executing the first parameter with FR% yield this performance,

EXEC sp_ProductOrderDetail @ProductNumber = 'SO-B909-L', @RecodOffset = 0, @RecordNumber = 1000;
GO

The performance of the query went from less than 1 second to about 25 seconds! First, there is significant IO involved with this query plan for this parameter value, and also multiple of operation spilled into tempdb which makes the query run much slower.

The query was designed to allow parameter value that have very different scope. One for specific value that usually yield small result set, but another for pattern with a wildcard that usually return much larger result set.

There could be multiple options to address this. Often, the stored procedure may be rewritten in a way to allow different query plan to be used according to parameter value. Since this post is about query hint and plan guide, let's explore those method. One of the query hint is OPTIMIZE FOR UNKNOWN. OPTIMIZE FOR UNKNOWN hint asks SQL Server optimizer to use statistical data instead of the initial parameter values (sniffed value) when the query is compiled and optimized. It produces a consistent query plan regardless of what the initial value was used (sniffed).

Since the parameter value provided yield a different query plan as noticed previously, could the OPTIMIZE FOR UNKNOWN query hint help here?

ALTER PROCEDURE sp_ProductOrderDetail 
(
  @ProductNumber nvarchar(25),
  @RecodOffset int,
  @RecordNumber int
)
AS
BEGIN
SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY
OPTION (OPTIMIZE FOR UNKNOWN);
END

Now, execute the stored procedure with either @ProductNumber = 'SO-B909-L' or @ProductNumber = 'FR%' produces the same query plan.

DBCC FREEPROCCACHE; 
GO
EXEC sp_ProductOrderDetail @ProductNumber = 'SO-B909-L', @RecodOffset = 0, @RecordNumber = 1000;
GO

DBCC FREEPROCCACHE; 
GO
EXEC sp_ProductOrderDetail @ProductNumber = 'FR%', @RecodOffset = 0, @RecordNumber = 1000;
GO

It looks good on @ProductNumer = 'FR%' performance, but the performance on @ProductNumber = 'SO-B909-L' suffers with this plan. It went from about 1 second to around 20 seconds! One scenario becomes better in the expense of the other one. Not good.

As we have noticed previously that these two types of parameter value passed yield very different query plans to obtain the most optimal performance, it seems like it is best not to use the query plan cached from the initial parameter value, but rather utilize an optimal plan depending on the provided parameter values.

OPTION (RECOMPILE) is a query hint to discard query plan previously generated and force the query optimizer to recompile a new query plan the next time the same query is executed. Let's see how it works here.

ALTER PROCEDURE sp_ProductOrderDetail 
(
  @ProductNumber nvarchar(25),
  @RecodOffset int,
  @RecordNumber int
)
AS
BEGIN
SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY
OPTION (RECOMPILE);
END

Now SQL Server optimizer generates an optimal query plan for parameter value ProductNumer = 'FR%' or @ProductNumber = 'SO-B909-L' respectively regardless the initial value used. Both different parameter value provided perform as expected with the optimal duration seen previously.

One important note. As OPTION (RECOMPILE) recompile and regenerate new query plan on each execution, there is additional CPU overhead involved. Depending on how expensive is the query plan and the frequency the stored procedure is executed, RECOMPILE may or may not be a suitable solution Also, since the cache plan is discarded after execution, it may not show up on SQL Server DMV for monitoring or troubleshooting purpose.

Now we learn that OPTION (RECOMPILE) is useful for the query to perform optimally for different parameter value, what happen if the query could not be modified due to multiple reasons, like the stored procedure is provided by vendor or ORM generated query?

SQL Server plan guides could be useful in these type of scenario when we have our hand tied trying to address the query performance. Let's see how it works.

First remove the query hint on the stored procedure.

ALTER PROCEDURE sp_ProductOrderDetail 
(
  @ProductNumber nvarchar(25),
  @RecodOffset int,
  @RecordNumber int
)
AS
BEGIN
SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;
END

Using UI to create a plan guide for this query in sp_ProductOrderDetail stored procedure.

Or use SQL Statement to create the plan guide.

USE [AdventureWorks2014]
GO

EXEC sp_create_plan_guide @name = N'[PlanGuide_sp_ProductOrderDetailPlanGuide]', 
@stmt = N'SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;', 
@type = N'OBJECT', 
@module_or_batch = N'[dbo].[sp_ProductOrderDetail]', 
@hints = N'OPTION (RECOMPILE)'
GO

Execute the stored procedure with different parameter and examine the query plan. One way to verify if the plan guide is being used on the execution by right clicking on the SELECT node, click properties and examine the plan guide name value.

What about if the SQL query is not a stored procedure but rather a query from ORM application. Something like this,

sp_executesql 
N'SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;', 
N'@ProductNumber nvarchar(25), @RecodOffset int, @RecordNumber int', 
@ProductNumber = 'SO-B909-L', @RecodOffset = 0, @RecordNumber = 1000;

OR with sql_prepare

DECLARE @p1 int
EXEC sp_prepare @p1 output, N'@ProductNumber nvarchar(25), @RecodOffset int, @RecordNumber int', 
N'SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;';

EXEC sp_execute @p1, 'SO-B909-L', @RecodOffset = 0, @RecordNumber = 1000;
--EXEC sp_unbindefault @p1;

From the verification step earlier, the query plan generated is not using the query hint specified in the plan guide. Plan guide only applied when very specific conditions are met like in this case both the scope as well as the SQL statement. The plan guide created earlier is only scoped for the sp_ProductOrderDetail stored procedure. A proper plan guide needs to be created for this SQL query.

USE [AdventureWorks2014]
GO

EXEC sp_create_plan_guide @name = N'[PlanGuide_Query1]', 
@stmt = N'SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal
FROM sales.SalesOrderHeaderEnlarged s
JOIN sales.SalesOrderDetailEnlarged d
 ON d.SalesOrderID = s.SalesOrderID
JOIN Production.Product p
 ON p.ProductID = d.ProductID
WHERE p.ProductNumber LIKE @ProductNumber
ORDER BY s.SalesOrderNumber 
OFFSET @RecodOffset ROWS
FETCH NEXT @RecordNumber ROWS ONLY;', 
@type = N'SQL', 
@params = N'@ProductNumber nvarchar(25), @RecodOffset int, @RecordNumber int', 
@hints = N'OPTION (RECOMPILE)'
GO

Now, the SQL query execute with the OPTION (RECOMPILE) query as specified in the plan guide. The setting also shows that plan guide is being used.

To view the existing plan guide from UI or using sys.plan_guides dynamic management view

Plan guide is only available on enterprise edition. Plan guide should be always used sparingly and only under very special circumstances.

Hopefully you find this post on query hint and the plan guide helpful.

Query Performance Tuning Example

2015-03-30T05:00:00.000-05:00

As DBA, application performance issue and complaints often thrown our way. It is often fun to troubleshoot those performance issue. When it has been identified that the problem lies on SQL Server, it get even more exciting. Here is one of the example of how I tuned a query a while ago and also some lessons learned.

I received a request to identify bottlenecks of an application where its users has reported in a survey that they have been experienced slow application response in their daily operations. Without any information of which specific module or functionality that experience the 'problem', I did some digging and notice there were different types of queries and stored procedures developed in this application to serve both operational as well as a reporting purpose.

Using one of my own query below, I look up the most run (Order by COUNT) query as well as the one that take the longest times (Order by TIME).

DECLARE @TopNumber smallint = 20;
DECLARE @OrderByField varchar(20) = 'COUNT';

--Top 10 CPU Usage Query
--Declare @TopNumber smallint = 10;
--DECLARE @OrderByField varchar(20) = 'CPU';

-- CPU   - avg_cpu_time
-- TIME   - avg_elapsed_time
-- READ   - avg_physical_read + avg_logical_read
-- WRITE  - avg_logical_write
-- COUNT  - cache_ob_lookup
-- RECOMPILE  - avg_ob_recompile
-- PARALLEL  - avg_over_parallel

WITH query_stat AS 
(SELECT TOP(@TopNumber)
  SUM(qt.plan_generation_num) / SUM(cp.usecounts) AS avg_ob_recompile,
  SUM(cp.usecounts) AS total_exec_count,
  SUM(qt.total_worker_time) / SUM(qt.execution_count) / 1000 AS avg_cpu_time,
  SUM(qt.total_elapsed_time) / SUM(qt.execution_count) / 1000 AS avg_elapsed_time,
  SUM(qt.total_logical_writes) / SUM(qt.execution_count) AS avg_logical_write,
  SUM(qt.total_logical_reads) / SUM(qt.execution_count) AS avg_logical_read,
  SUM(qt.total_physical_reads) / SUM(qt.execution_count) AS avg_physical_read,
  (SUM(qt.total_worker_time) - SUM(qt.total_elapsed_time)) / SUM(qt.execution_count) / 1000 AS avg_over_parallel,
  MIN(qt.sql_handle) AS sql_handle,
  qt.query_hash
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
INNER JOIN sys.dm_exec_query_stats AS qt WITH (NOLOCK) ON
 cp.plan_handle = qt.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qt.sql_handle) st 
GROUP BY qt.query_hash
ORDER BY 
 (CASE @OrderByField 
  WHEN 'CPU' THEN SUM(qt.total_worker_time) / SUM(qt.execution_count)
  WHEN 'READ' THEN SUM(qt.total_logical_reads + total_physical_reads) / SUM(qt.execution_count)
  WHEN 'WRITE' THEN SUM(qt.total_logical_writes) / SUM(qt.execution_count)
  WHEN 'COUNT' THEN SUM(cp.usecounts)
  WHEN 'TIME' THEN SUM(qt.total_elapsed_time) / SUM(qt.execution_count)
  WHEN 'RECOMPILE' THEN SUM(qt.plan_generation_num) / SUM(cp.usecounts)
  WHEN 'PARALLEL' THEN (SUM(qt.total_worker_time) - SUM(qt.total_elapsed_time)) / SUM(qt.execution_count)
 END) DESC
)

SELECT TOP(@TopNumber)  
 DB_NAME(st.dbid) AS database_name,
 OBJECT_NAME(st.objectid, st.dbid) AS proc_name,
 cp.cacheobjtype,
 cp.objtype,
 qs.avg_ob_recompile,
 total_exec_count,
 avg_cpu_time,
 avg_elapsed_time,
 avg_logical_write,
 avg_logical_read,
 avg_physical_read,
 avg_over_parallel,
 SUBSTRING(st.text, (qt.statement_start_offset/2) + 1,
   ((CASE statement_end_offset 
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qt.statement_end_offset 
     END - qt.statement_start_offset)/2) + 1) AS sql_text, 
 st.text AS ob_text,
 ph.query_plan
FROM query_stat AS qs
INNER JOIN sys.dm_exec_query_stats AS qt WITH (NOLOCK) ON
 qs.query_hash = qt.query_hash AND
 qs.sql_handle = qt.sql_handle
INNER JOIN sys.dm_exec_cached_plans cp WITH (NOLOCK) ON
 cp.plan_handle = qt.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qt.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qt.plan_handle) AS ph
OPTION (RECOMPILE);

By analyzing the two different set of data, I was able to find some correlation and identified some frequently called slow queries. I decided to focus on the 'operational' queries as that is more aligned with the user complaint, With some help from developer to filter out the reporting queries, I was able to identify one stored procedure that appear to have significant impact on user operational task experience.

Here is the target, dbo.SPBuildFastIndex

The stored procedure appears to insert new entry into a table when there a new entry, and in addition to that, it could also be executed to delete all entries in the table and reinsert all data. This stored procedure is one of the most run stored procedure with higher than average duration for its execution. From the system cached data, the stored procedure executed around 3000 times a day. From the statistics, it appears that there are certain SQL statements within the stored procedure take an average of 20 seconds to complete in production and some instances of that certain entries take between 1-2 minutes to complete.

ALTER PROCEDURE dbo.SPBuildFastIndex (@ObjectID int)
AS
...
INSERT INTO FastIndexTbl
(LocID, FastIndexOTID, TransID, NameData, Info, Timestamp)
(
 SELECT 
  t.LocID, 
  100, 
  t.TransID, 
  dbo.NameStr(b.FirstName, b.MiddleName, b.LastName, b.CompanyName),
  CONVERT(varchar(50),d.DetailNumber) + ', ' + 
  ISNULL(dbo.NameStr(i.FirstName, i.MiddleName, i.LastName, NULL), 'No Data'), 
  ISNULL(CAST(o.OpsDate AS datetime), ISNULL(t.TransDate, '1/1/1900'))
 FROM Trans t 
 INNER JOIN Detail d ON t.TransID=d.TransID
 INNER JOIN Ind i ON d.DetailInd=i.IndID
 INNER JOIN Acct a ON t.AcctID=a.AcctID
 INNER JOIN Ind b ON b.IndID=a.CustID
 INNER JOIN [Ops] o ON d.DetailID=o.DetailID
 WHERE 
  d.DetailTypeID = 100  
  AND dbo.NameStr(b.FirstName, b.MiddleName, b.LastName, b.CompanyName) IS NOT NULL
  AND t.TransID = CASE WHEN @ObjectID = 0 THEN t.TransID ELSE @ObjectID END
);

I picked the slow SQL Statement out for more isolated analysis and execute the select statement with the ObjectID parameter specified (eg. 100001),

--Turn on statistics to display IO and time information of the execution
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

I execute these commands below to clear the cache each time I made changes for better comparison

--Delete cache and data in memory for comparison of each change
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

Here is the execution plan of the query (view from SQL Sentry Plan Explorer). Click on the picture for larger view.

Here is some of the IO and time stat from cold cache.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Acct'. Scan count 1, logical reads 22357, physical reads 20, read-ahead reads 22739, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Ops'. Scan count 1, logical reads 12541, physical reads 27, read-ahead reads 12555, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Detail'. Scan count 1, logical reads 21372, physical reads 24, read-ahead reads 21437, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Trans'. Scan count 1, logical reads 53846, physical reads 76, read-ahead reads 53872, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Ind'. Scan count 2, logical reads 282086, physical reads 205, read-ahead reads 141176, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 34203 ms, elapsed time = 43402 ms

Better view from Statistics Parser by Richie Rump.

Here is the IO and time statistics from warm cache (data available in memory)

The duration of reading the data from memory reduce around 10 seconds.

The elapsed time for the warm cache likely to illustrate what is experiencing by end user. However, I would be using the cold cache data for better comparison with other improvement options.

Below were a few methods I tried to identify the level of improvements.

Index implementation
The IO stat as well as the execution plan show that the dbo.Ind table returns and process large number of pages. Mainly because this table is wide and there is no useful narrower index to be used to fulfill this query. Other index scan operators are chosen likely due to the number of rows required to return for process. Implementing some indexes could help reduce number of pages required and returned.

I examined the tables for redundant index as well as reusable index. I found sp_BlitzIndex from Brent Ozar Unlimited makes the index examination easier and faster. I cleaned up some redundant indexes and decided to create some indexes. Eg. on dbo.ind table.

CREATE INDEX [IX_Ind_IndlID_Includes]
ON [dbo].[Ind] ([IndID]) INCLUDE ([FirstName], [MiddleName], [LastName], [CompanyName]);

The duration reduce around 10 seconds with the index implementation from cold cache. Total pages read reduces from ~392K to 47K.

Scalar User Defined Function and Inline Table Valued Function

The SQL statement uses a scalar user defined function (UDF) dbo.NameStr. The function is as innocent as concatenating first, middle, last name and sometimes company name into one string.

Understanding that this function is implemented to make code maintenance simpler and avoid the need to rewrite the same script every times when there is a need to concatenate the name into one string/column.

CREATE FUNCTION [dbo].[NameStr]
(  
  @FirstName varchar(50), @MiddleName varchar(50), @LastName varchar(50), @CompanyName varchar(100) = ''
)
RETURNS varchar(150)
AS
BEGIN

DECLARE @ret varchar(150)

IF IsNull(@CompanyName,'') = ''
  SET @ret = @LastName + CASE WHEN IsNull(@FirstName, '') <> '' THEN ', ' ELSE '' END + @FirstName + CASE WHEN @MiddleName is NULL THEN '' ELSE ' ' + @MiddleName END
ELSE
  SET @ret = @CompanyName

RETURN @ret 

END

However, scalar function has some disadvantages.

1) When scalar UDF is used in a query, SQL Server invoke this function as a separate module for each row within the iterator. This introduces overhead from each invocation of the module of each row
2) Scalar UDF estimated cost is 0 which is not true and it affects how SQL optimizer choose its optimal plan
3) Scalar UDF does not use parallelism in a plan

SQL Profiler trace show that the scalar function is invoked and executed for each row.

SQL plan show that the cost of the function is 0 which is not true

All the execution plan so far has been with no parallelism involved.

There are few ways to remedy this particular case,

1) Rewrite query to include the logic in the query itself, or
2) Replace with inline table valued function

If the decision is to use inline table valued function (TVF), an inline TVF can be created with very similar syntax as the existing scalar UDF, but instead of returning a scalar value, it returns a table with single value (in this case). Please refer to reference for the exact syntax. Here is an example of a inline TVF for this purpose,

CREATE FUNCTION [dbo].[NameStrTable]
( 
  @FirstName varchar(50), @MiddleName varchar(50), @LastName varchar(50), @CompanyName varchar(100) = ''
)
RETURNS TABLE
AS
RETURN 
…
);

Replace any dbo.NameStr(FirstName, MiddleName, LastName, Company) with (SELECT Name FROM dbo.NameStrTable(FirstName, MiddleName, LastName, Company))

SELECT 
 t.LocID, 
 100, 
 t.TransID, 
 (SELECT Name FROM dbo.NameStrTable(b.FirstName, b.MiddleName, b.LastName, b.CompanyName)),
 CONVERT(varchar(50),d.DetailNumber) + ', ' + 
 ISNULL((SELECT Name FROM dbo.NameStrTable(i.FirstName, i.MiddleName, i.LastName, NULL)), 'No Data'), 
 ISNULL(CAST(o.OpsDate AS datetime), ISNULL(t.TransDate, '1/1/1900'))
FROM Trans t 
INNER JOIN Detail d ON t.TransID=d.TransID
INNER JOIN Ind i ON d.DetailID=i.IndID
INNER JOIN Acct a ON t.AcctID=a.AcctID
INNER JOIN Ind b ON b.IndID=a.CustID
INNER JOIN [Ops] o ON d.DetailID=o.DetailID
WHERE 
 d.DetailTypeID = 100  
 AND (SELECT Name FROM dbo.NameStrTable(b.FirstName, b.MiddleName, b.LastName, b.CompanyName)) IS NOT NULL
 AND t.TransID = CASE WHEN @ObjectID = 0 THEN t.TransID ELSE @ObjectID END;

The execution plan below show that SQL Server optimizer chose a parallel plan for this execution. In this case the degree of parallelism is 4.

The duration reduces about 30 seconds from cold cache. The execution was performed without any new index implemented previously.

From the SQL Profiler, it appears that SQL Server does not invoke the inline TVF for each row like scalar UDF and hence avoid the overheads that scalar UDF were experiencing.

The combination of having the function in line with the query for better cost estimation, parallel plan and prevention of overhead cost of invoking the function for each row allow the query to perform much faster.

~~Parameter Sniffing~~ CASE Condition and Parameter Embedding Optimization
I will get to the reason why I cross out parameter sniffing in a minute.

If you remember, at the beginning of the review, we discussed about the purpose of this stored procedure. In order to build the fast index to speed up search lookup, any new entry entered executed this stored procedure to insert a record into the search index, in addition this stored procedure could be also used build a new one from scratch.

The key part is that this stored procedure is serving two purposes. In order to do that, there is the CASE statement at the search condition (where clause)

t.TransID = CASE WHEN @ObjectID = 0 THEN t.TransID ELSE @ObjectID END;

When a zero is passed in as ObjectID parameter, the query filter all rows in sales table along with other condition (e.g. other joins and condition); If a specific ObjectID is passed in, it only filter that specified sales item in sales table along with other condition (e.g. other joins and search condition).

There are about 1.5 million rows in sales table. When the ObjectID parameter is zero, SQL Server needs to read all these 1.5 mil rows (if this is the only search condition) along with the relevant data required from other tables. SQL Server is likely to choose a scan operator along with merge or hash joins as they are more efficient due to the large rows to be processed. On the other hand, if a specified ObjectID parameter value is specified (non-zero), SQL Server only need to read this one row along with the data required from other tables. Since the SaleID is a clustered key, SQL Server is likely to choose a seek operator along with nested joins which is more efficient for small row set.

Since this two search conditions are applied as a CASE statement, SQL Server would need to evaluate this two conditions and choose a plan that is good enough for both conditions. In this case, the plan shown previously was chosen. Mostly scan operators along with merge and hash joins.

So what is the performance difference of these two plans? Without any index implementation and function replacement, here is the statistics with current plan (statistics same with what shown previously)

Plan SQL Server chose,

With optimal plan,

Wow. the huge performance difference between these two execution plans. This optimal execution plan takes less than a second. Even without any new indexes, the CPU and IO demand is very low compare to the current plan as it is able to seek that particular record through the index seek operation.

As we know now that the current plan is not an optimal plan for single sales item due to the CASE statement, there are a few ways to remedy it. Before that, I want to discuss the topic on the parameter sniffing. When a parameter is passed in the first time, SQL Server evaluates the parameter to choose and generate a compiled plan, execute with that plan and then cache it for reuse. Parameter sniffing occurs when the plan that was chosen and cached is not optimal for subsequent parameter passed in. This usually occur when the data is not evenly distributed in the table. It may seem like the ObjectID with zero was passed in the first time and caused SQL Server to generate a less optimal plan for subsequent specified ObjectID execution. However, this is not the case here as SQL Server evaluates both condition in the CASE statement to generate a plan, regardless if the zero or specified value (non-zero) is passed in the first time. In addition, since the SaleID is an unique column, it allows accurate cardinality estimation and the same compiled plan should be optimal for different ObjectID value as long as it is not a zero.

Now, let’s explore different ways to remedy this.

1) OPTION (RECOMPILE)
2) Rewrite separate static query with IF statement
3) Rewrite as dynamic query with IF statement

OPTION (RECOMPILE)

SELECT 
 t.LocID, 
 100, 
 t.TransID, 
 dbo.NameStr(b.FirstName, b.MiddleName, b.LastName, b.CompanyName),
 CONVERT(varchar(50),d.DetailNumber) + ', ' + 
 ISNULL(dbo.NameStr(i.FirstName, i.MiddleName, i.LastName, NULL), 'No Data'), 
 ISNULL(CAST(o.OpsDate AS datetime), ISNULL(t.TransDate, '1/1/1900'))
FROM Trans t 
INNER JOIN Detail d ON t.TransID=d.TransID
INNER JOIN Ind i ON d.DetailInd=i.IndID
INNER JOIN Acct a ON t.AcctID=a.AcctID
INNER JOIN Ind b ON b.IndID=a.CustID
INNER JOIN [Ops] o ON d.DetailID=o.DetailID
WHERE 
 d.DetailTypeID = 100  
 AND dbo.NameStr(b.FirstName, b.MiddleName, b.LastName, b.CompanyName) IS NOT NULL
 AND t.TransID = CASE WHEN @ObjectID = 0 THEN t.TransID ELSE @ObjectID END
OPTION (RECOMPILE);

Adding the OPTION (RECOMPILE) at the end of the statement force SQL Server to regenerate a new plan based on the parameter for every execution. In addition, it applies parameter embedding optimization that allows SQL parser to fully evaluating the CASE statement. Hence generating an optimal plan for both zero parameter and parameter with specified value. However, there is CPU overhead to recompile and generate a plan, and it may not be desirable especially for query that is executed frequently.

Static query with IF statement,

IF @ObjectID <> 0
BEGIN
 SELECT 
  t.LocID, 
  100, 
  t.TransID, 
  dbo.NameStr(b.FirstName, b.MiddleName, b.LastName, b.CompanyName),
  CONVERT(varchar(50),d.DetailNumber) + ', ' + 
  ISNULL(dbo.NameStr(i.FirstName, i.MiddleName, i.LastName, NULL), 'No Data'), 
  ISNULL(CAST(o.OpsDate AS datetime), ISNULL(t.TransDate, '1/1/1900'))
 FROM Trans t 
 INNER JOIN Detail d ON t.TransID=d.TransID
 INNER JOIN Ind i ON d.DetailInd=i.IndID
 INNER JOIN Acct a ON t.AcctID=a.AcctID
 INNER JOIN Ind b ON b.IndID=a.CustID
 INNER JOIN [Ops] o ON d.DetailID=o.DetailID
 WHERE 
  d.DetailTypeID = 100  
  AND dbo.NameStr(b.FirstName, b.MiddleName, b.LastName, b.CompanyName) IS NOT NULL
  AND t.TransID = @ObjectID;
END
ELSE
BEGIN
  SELECT 
  t.LocID, 
  100, 
  t.TransID, 
  dbo.NameStr(b.FirstName, b.MiddleName, b.LastName, b.CompanyName),
  CONVERT(varchar(50),d.DetailNumber) + ', ' + 
  ISNULL(dbo.NameStr(i.FirstName, i.MiddleName, i.LastName, NULL), 'No Data'), 
  ISNULL(CAST(o.OpsDate AS datetime), ISNULL(t.TransDate, '1/1/1900'))
 FROM Trans t 
 INNER JOIN Detail d ON t.TransID=d.TransID
 INNER JOIN Ind i ON d.DetailInd=i.IndID
 INNER JOIN Acct a ON t.AcctID=a.AcctID
 INNER JOIN Ind b ON b.IndID=a.CustID
 INNER JOIN [Ops] o ON d.DetailID=o.DetailID
 WHERE 
  d.DetailTypeID = 100  
  AND dbo.NameStr(b.FirstName, b.MiddleName, b.LastName, b.CompanyName) IS NOT NULL
END

This method separates the two logics and allow SQL Server to generate, use and cache different plan for different search conditions. Once the compiled plan is cached, it could be reused again for next execution. This method may require the same select and join statements to be rewritten twice.

Dynamic query with IF statement

DECLARE @sql nvarchar(max),
  @parameter nvarchar(4000);

SELECT @parameter = '@transid int';

SELECT @sql = 
 `SELECT 
  t.LocID, 
  100, 
  t.TransID, 
  dbo.NameStr(b.FirstName, b.MiddleName, b.LastName, b.CompanyName),
  CONVERT(varchar(50),d.DetailNumber) + ', ' + 
  ISNULL(dbo.NameStr(i.FirstName, i.MiddleName, i.LastName, NULL), 'No Data'), 
  ISNULL(CAST(o.OpsDate AS datetime), ISNULL(t.TransDate, '1/1/1900'))
 FROM Trans t 
 INNER JOIN Detail d ON t.TransID=d.TransID
 INNER JOIN Ind i ON d.DetailInd=i.IndID
 INNER JOIN Acct a ON t.AcctID=a.AcctID
 INNER JOIN Ind b ON b.IndID=a.CustID
 INNER JOIN [Ops] o ON d.DetailID=o.DetailID
 WHERE 
  d.DetailTypeID = 100  
  AND dbo.NameStr(b.FirstName, b.MiddleName, b.LastName, b.CompanyName) IS NOT NULL`;

  AND t.TransID = CASE WHEN @ObjectID = 0 THEN t.TransID ELSE @ObjectID END

IF @ObjectID <> 0 SELECT @sql = @sql + ' AND t.TransID = @transid;';
ELSE SELECT @sql = @sql + ';';

EXEC sp_executesql @sql, @parameter, @ObjectID;

This method separates the two search condition logics and allow SQL Server to generate, use and cache different plan (using sp_executesql) for different search conditions. Once the compiled plan is cached, each statement doesn't required to be recompiled again for next execution. The dynamic query allows the reuse of the same select and join statement during coding. However, the use of dynamic query may add another layer of complexity of coding and troubleshooting.

Observation
Index Implementation
As we have seen that some indexes do improve the performance for current plan (optimized for CASE condition) from cold cache. It reduces the number of pages required, that translates to lower IO and with that, it indirectly reduces the memory demand.

For this particular stored procedure, it does not appear that these new indexes will significantly improve the query performance that we are experiencing in production especially after the data have been cached in memory. However with an optimal query plan, some covering indexes could be helpful.

Replacing Scalar Function
The prevention of using parallel plan and the significant overhead incurred on scalar UDF could severely affect the performance when a large number of rows are involved like in this case. Although parallel plan is not always better than serial plan, in the case of large number of rows, it could be beneficial for multiple threads to split up the works. With small row set, the scalar UDF may not have significant impact as we have seen with the optimal plan.

From the observation, replacing scalar UDF would improve the performance in production especially when large number of rows are involved. The only thing is that the function is being used in many different stored procedures. As not all stored procedure will replace it with UDF, changing this stored procedure to use a new UDF mean there will be two separate functions to maintain. May not be a big deal but all developers will need to take note of that.

Replacing CASE statement
The CASE statement at the search condition appears to be the main culprit of low performance of this query. SQL optimizer has chosen and generated a plan that is suboptimal for parameter with specified value (non-zero), which is most of the cases here.

Since the situation is not caused by inaccurate cardinality estimation from uneven data distribution according to the parameter, recompilation on every execution may not be desired. There is only a need for two search condition; one with specified value and one for returning all rows. By rewriting the query with two separate logics allow optimal cached plan for each type of execution, and also prevent the overhead of recompiling the plan to generate an optimal plan for each execution.

Lesson Learned
When performing query tuning, the query itself should be examined closely before performing any index changes. Index does help in many scenarios but should not be the first approach. Often, including this scenario, the performance of the query could be significantly improved just by changing the way it is written. With better understanding on different limitation and constraints (eg. scalar function) and how SQL Server Optimizer works would definitely help.

One Important Aspect - Mentor

2015-02-14T16:12:00.003-06:00

Looking back to my education and career journey, there were times when I felt like it was totally stagnant, and other times of leap and bound moment. I have came to realize the importance of a mentor.

I have been fortunate to have some great teachers helping me to build a strong foundation in math, engineering and logical type of subjects in my school years. I know my stuff and it wasn't hard to get a good grade in these fields.

When it comes to my choice of educational advancement oversea, that was my first taste of lack of concrete direction. There were some information on the internet and material provided in university. However, there all seem no difference to me at that time and I wasn't sure what to look for. It wasn't too long until I met the then head of degree transfer program who I worked part time for. He shared some of his experience, led me to some relevant resources based on my question and what I was trying to achieve. The next thing happened was I picked up my bag and flied to United States myself.

Fast forwarding to a few years ago when I was a manufacturing engineer. I found strong interest in .net and database development during company transitioning to MES (Manufacturing Enterprise System). I was surrounded by engineers and occasionally some IT folks, but I couldn't seem to able to find a path that would take me to the next level in this new field. Eventually I took an opportunity to become a .Net developer in a small company which allow me also work closely with database.

I learned as I work, through online tutorial and documentation, books and blogs. Despite the success of replacing company website and internal application with wide acceptance, there wasn't really anyone I could get help or work with on discussing different development approach, performance improvement methods and other things that would potentially reveal a much efficient and effective way.

Things started to change when I discovered user group. .NET user group, C# user group, SQL user group, I attended them all as much as I could. There are so many dedicated and passionate folks on the technology and everyone are there to learn, help and share knowledge. Wow!

Another best thing I came about is the SQL community. I stumbled upon a few blogs and they blew my mind. Folks at BrentOzar unlimited, awesome people at sqlskills and many many more who actively and voluntarily share and help others.

On the awesome community spirit of SQL community, Paul Randal at SQLSkills who recently voted for most desired mentor, is offering to mentor a few people. Personally, I feel that he would be one of the ideal mentor for me. Reasons are some of our similar technical backgrounds, his previous and current involvement as Microsoft employee and manager, community contributor, consultant and a business owner in SQL server field, and last but not least his principles on time management with his 50% traveling and the other half for his family. Although I know a few great resources where I could learn and improve my technical skills, I constantly have questions in some other aspects which keep resurfacing for more decisive direction. Questions like generalist vs specialist, consultant vs employee and others. This may seems like common questions that has been widely addressed, but I am looking into something more specific to my own and some insight from someone in the same field personal experience.

Whoever he picked as mentee would surely be lucky ones!

Although these guys were not my mentors, I have learned so much from them through their blog, videos and interactions. They not only improve my technical skill set, it changes the way I view work and career advancement. Work is so much fun when everyone share their knowledge and make everyone better.

Last note, from personal experience, regardless if a person is in school, workforce, or business, having right mentor play an important role and has great impact to the mentee. By sharing his/her personal experience and valuable insight with guidance and resources, often times broaden the vision and expand the horizon for the mentee which he/she would never thought of. On the other side, it is important for the mentee to willing to open for other opinions and to have some idea of his/her goal and what he/she is trying achieve in order for the mentoring process to be effective.

Update: Class of 2015! I can't imagine a person (busy consulting owner) volunteers to mentor the whole class! But here I present you, Paul Randal!

Execute SSIS remotely - PowerShell

2015-02-02T06:00:00.000-06:00

Since SQL Server 2012, Microsoft introduce a new way of interacting and storing SSIS package. SSIS packages are frequently executed in scheduling format often through SQL Server agent jobs. Today blog post will be focusing on remotely calling SSIS package stored in this new SSISDB Catalog.

SSISDB Catalog is the new center point of working with these objects including SSIS Projects, Packages and its parameter and environment. These objects are stored in the SSISDB database. The database is automatically created when Integration Service Catalog is created. Interaction with objects stored in SSISDB Catalog is performed mainly through the SSIS Catalog UI or calling the provided stored procedures in SSISDB.

There are few methods to remotely call the SSIS packages in SSISDB Catalog. One method is through Microsoft.SqlServer.Management.IntegrationServices Namespace, or utilize the SSISDB stored procedures. Please beware that executing SSIS stored in msdb, SSIS package store or file system are different.

The below PowerShell example utilize the SSISDB stored procedures. It first executes the SSISDB catalog.create_execution stored procedure with all the parameter values to create an instance of execution, and subsequently executes the catalog.start_execution stored procedure to start the particular execution instance just created.

function Invoke-SSISPackage {
    [CmdletBinding(DefaultParametersetName="SSPI")] 
    param(
        [parameter(Mandatory=$true, Position=0)]
        [string]$SQLInstance,

        [parameter(ParameterSetName='User', Mandatory=$true, Position=1)]
        [string]$User,

        [parameter(ParameterSetName='User', Mandatory=$true, Position=2)]
        [string]$password,

        [parameter(ParameterSetName='SSPI', Mandatory=$true, Position=3)]
        [switch]$SSPI,

        [parameter(Mandatory=$true, Position=4)]
        [string]$Folder,

        [parameter(Mandatory=$true, Position=5)]
        [string]$Project,

        [parameter(Mandatory=$true, Position=6)]
        [ValidatePattern('^.*\.dtsx$')]
        [string]$Package,

        [parameter(Position=7)]
        [switch]$RunIn32Bit
    )

    $sqlConn = New-Object System.Data.SqlClient.SqlConnection
    $sqlConn.ConnectionString = "Server=$($SQLInstance);`
                                 Database=SSISDB;`
                                 User=$($User);`
                                 Password=$($Password);`
                                 Integrated Security=$( @{$true="SSPI"; $false="False"}[$SSPI -eq $true] )" 
    try {
        $sqlConn.Open()

        Write-Host "Creating SSIS execution.." -ForegroundColor "Yellow"

        $sqlCmd = New-Object System.Data.SqlClient.SqlCommand ("[catalog].[create_execution]", $sqlConn)
        $sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $sqlCmd.Parameters.AddWithValue("folder_name", $Folder) | Out-Null
        $sqlCmd.Parameters.AddWithValue("project_name", $Project) | Out-Null
        $sqlCmd.Parameters.AddWithValue("package_name", $Package) | Out-Null
        $sqlCmd.Parameters.Add("use32bitruntime", [System.Data.SqlDbType]::Bit).Value = $RunIn32Bit.IsPresent
        $sqlCmd.Parameters.Add("execution_id", [System.Data.SqlDbType]::BigInt).Direction = [System.Data.ParameterDirection]::Output
        $sqlCmd.ExecuteNonQuery() | Out-Null
        
        [int64]$execID = $sqlCmd.Parameters["execution_id"].Value 
        $sqlCmd.Dispose()

        Write-Host ""
        Write-Host "Starting SSIS execution.." -ForegroundColor "Yellow"

        $sqlCmd = New-Object System.Data.SqlClient.SqlCommand ("[catalog].[start_execution]", $sqlConn)
        $sqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure
        $sqlCmd.Parameters.AddWithValue("execution_id", $execID) | Out-Null
        $sqlCmd.ExecuteNonQuery() | Out-Null
        
        $sqlCmd.Dispose()
    }
    catch {
        throw
    }
    finally {
        $sqlConn.Dispose()
    }
}

Although the example above uses PowerShell, implementing it in other programming language such as C#.NET or VB.NET could be easily followed with minor changes. Please note that the script returns result if execution is started successfully, but not if the SSIS completes its execution successfully.

Hope you find the example script useful.

SQL Server Database File - Date Modified

2015-01-07T08:00:00.000-06:00

Recently, there was a discussion if the date modified of the database files shown in Windows explorer could be used to determine when the database was last used (or recently used). Often, this date is used to determine when a file (eg. word, excel) is last updated. Could that also be applied to SQL Serve database?

Let's do a few tests to see how the date correlate with the database change.

Create a database called Testing,

CREATE DATABASE Testing
ON PRIMARY
(
 NAME = 'Testing',
 FILENAME = 'D:\MSSQL12.MSSQL2014\MSSQL\DATA\Testing.mdf',
 SIZE = 4 MB,
 FILEGROWTH = 8 KB
) 
LOG ON
(
 NAME = 'Testing_Log',
 FILENAME = 'D:\MSSQL12.MSSQL2014\MSSQL\DATA\Testing_log.ldf',
 SIZE = 1 MB
);

Here is what it look slike with PowerShell Get-ChildItem cmdlet on the database file folder. LastWriteTime as Date Modified as seen in the explorer. Length as the size (in byte) of the file.

During the database creation, the size of the database files was specified (eg. 4 MB for the MDF data file). However, that doesn't mean the file has been entirely allocated (or being used). The script below shows the physical size of the database file as well as the size of what actually being used,

USE testing;

SELECT 
 CURRENT_TIMESTAMP AS [time],
 f.name [file_name], 
 f.size * 8192 [file_size_B], 
 FILEPROPERTY(f.name, 'SpaceUsed') * 8192 [Used_size_B]
FROM sys.database_files f;

Notice that the data file (MDF) used size is around 2.49 MB out of the 4 MB of the data file. Similarly to the log file.

Now, we going to perform a few task and see what affect the modified date of the database file

Create table (Data Definition Language, DDL)
The script below create a table in the database,

USE Testing;

CREATE TABLE dbo.tbl1
(
 ID int IDENTITY(1,1),
 Col1 nchar(4000) DEFAULT (REPLICATE('G', 4000)),
 Col2 char(49) DEFAULT (REPLICATE('A', 49))
);

Notice that the used size of the log file has increased slightly. Let's examine the date modified of the database files.

Still the time where the database was first created for both data and log file.

Insert Data (Data Manipulation Language, DML)
Since the database is created on Simple recovery model (Full recovery model without any backup also behave like Simple recovery model) for this demo, the log file is automatically truncated and reused without growing if the transaction size is smaller than the file size. Because of this, we will only focus on the data file from now on.

INSERT INTO dbo.tbl1
DEFAULT VALUES;
GO 2

The data file used size has increased to around 2.56 MB. However, date modified of the data file still shown as time when the database first created.

Insert large data that require file growth
Now, we are going to insert more data that large enough to require the data file growth.

INSERT INTO dbo.tbl1
DEFAULT VALUES;
GO 207

The data file has grown to around 4.25 MB (64 KB more from the original size), and the data file modified date is updated.

The database is configured to grow (mean auto grow) with 8 KB. Some of you with eagle eye may notice that during the database creation, 8 KB is specified as file growth rate. However, SQL Server grow by minimum an extent (8 data page (8 KB) = 64 KB).

Modify database file size
The script below change the data file size to 10 MB.

ALTER DATABASE Testing
MODIFY FILE (
 NAME = 'Testing',
 SIZE = 10MB );

The modified date of the data file has been updated.

Shrink database file
Below is the script to shrink the data file to 6 MB. Some data was added before the shrink task just to show different used size.

INSERT INTO dbo.tbl1
DEFAULT VALUES;
GO 10

DBCC SHRINKFILE ('Testing', 6);

The data file modified date reflect the time the shrink action was performed.

Database Backup
Below script perform a full backup. Some data is also added prior just for the sake of it.

INSERT INTO dbo.tbl1
DEFAULT VALUES;
GO 10

BACKUP DATABASE Testing
TO DISK = 'Testing.bak';

Nothing change in data file size and its used size. Data file date modified remain the same as previous time.

Auto Close
By default, this auto_close database setting is set to false. As best practice, this should be false. Turning on this setting make SQL Server close the database after the last disconnected connection to the database. This is not recommended in practice as it takes time to re-obtain resources when it reopen the database.

The script below set the Auto_Close setting to true

ALTER DATABASE Testing
SET AUTO_CLOSE ON;

In another windows,
Using sp_who2, we identify and kill all the connection to Testing database,

KILL 56
KILL 57

Both data and log file date modified are updated due to the auto close.

Taking database offline
Below script take the database offline.

ALTER DATABASE Testing
SET OFFLINE
WITH ROLLBACK IMMEDIATE;

The date modified for both data and log file are updated upon this offline action.

We do another test to turn the database back online and without any changes, take the database offline.

ALTER DATABASE Testing
SET ONLINE;

ALTER DATABASE Testing
SET OFFLINE
WITH ROLLBACK IMMEDIATE;

The date modified also updated upon the last database offline action.

Stop SQL Server service
The testing database is brought online. Stop the SQL Server service from SQL Server Configuration Manager, both database files date modified are updated.

Observation
The database files date modified is updated due to these events,

1) Physical file growth - Alter statement, file auto grow
2) Physical file shrink - Shrink command (eg. DBCC SHRINKFILE, DBCC SHRINKDABASE), auto shrink (not tested in this demo, but same concept applied)
3) Database offline - Alter statement, SQL Server service

For the first two observations, the key word is that only physical file size change will update the date modified. Data records are stored in the data pages within the data file. The database engine continue to allocate new data to unallocated data page in the existing data file until it run out of unallocated page, and request/perform a physical file growth which then trigger the update on date modified property.

In this demo, it is not obvious on the log file because the database is behaving as Simple recovery model which it will automatically reuse the virtual log file (smaller logical log file within the log file) without growing if the transaction is small enough within the size of the log file. If it is on Full recovery model, similar date modified concept discussed apply to log file as well.

Conclusion
The date modified of the database files are not a good indicator to determine the recency of the database. The database could be actively being used and the date modified is dated a while back. It is possible that database file with plenty of unallocated space within could continue to allocate new data without triggering database file date modified property to be updated for a long time.

Here are few suggestions without additional setup (eg. trace or audit) to determine if the database is recently used. Each of these method has its pro and con,

- sp_who2 or sp_whoisactive to see if any current or sleeping connection on the database
- Query the sys.dm_db_index_usage_stats dmv to examine the most recent seek, scan, lookup, and update of database table / index.
- Database backup size from backup history table (msdb.dbo.backupset) to see any size change historically.

Microsoft Specialist - Implementing Microsoft Azure Architecture Solutions

2015-01-04T23:30:00.000-06:00

I have been using Microsoft Azure with credit from MSDN subscription (Free credit! See the MSDN subscription section on this post) for sometimes now. Mostly for VMs and SQL Database (Microsoft Azure SQL Database). Recently I have also started exploring other Azure services like Azure Active Directory for cloud identity and access management, as well as deploying and implementing .NET web application to Azure Websites along with Visual Studio Online.

Back in October 2014, Born To Learn (Microsoft Training and Certification Community) blogged about some free voucher to get certified for Microsoft Azure (The offer has ended). I was lucky enough to get the free voucher. In addition, there was a week long Azure IaaS for IT Pros Online Event on the first week on December 2014. There were multiple experts sharing some technical insights which could be helpful on this particular certification. If you missed the live event, the videos are also available here.

Below are the technical tasks measured for the Implementing Microsoft Azure Infrastructure Solutions certification,

With all these great incentive, I thought this might be the great opportunity for me to widen / deepen my understanding on the Microsoft Azure platform like the details of how virtual network is being implemented and different type of storage and its relevant performance.

Although some times I have people downplayed the benefit and validity of certification, I encourage people to get certified. Out of many benefits as listed on this Microsoft learning page, personally, one of the main reason is while preparing for the exam, I am often exposed to part and piece of some feature of a technology I don't usually work on, and sometimes not aware of. This give me an opportunity to verify my skills as well as to learn new knowledge in depth while exploring it. I always emphasize that passing an exam is not the end, but rather a part of the journey of getting better understanding of a technology.

I passed the exam and is certified as Microsoft Specialist for Implementing Microsoft Azure Architecture Solutions. Microsoft Azure has been increasing its collection of services with new improvements and features at a very fast pace. So, learning continue, just like this.

SSIS Connection Manager Properties

2014-12-01T10:03:00.001-06:00

The SSIS packages stored in SSIS Catalog (introduced in SQL Server 2012, and also SQL Server 2014) allow configuration changes made on the project or the package connection managers, depending how the connection was setup. It is important to have a good understanding of how the values in these properties are being used.

I can't find any documentation on how these SSIS connection manager properties affect the actual connection value. In this post, Here is my observation on how these properties changes affect the actual value used during the execution.

Here is the SSIS connection manager in the designer (in this case Visual Studio with BI)

This is the connection manager property setting on the deployed package in SQL Server SSIS Catalogs.

It seems the ConnectionString value is generated based on the setting configured at the designer. Other properties (eg. ServerName and InitialCatalog) values appears to be similarly mapped, but not all the property like integrated security as SSPI.

Looking at this example, the connection string property, along with other properties appear to be serving the similar purpose. Let's look at the detail. Click the '...' on the right of the property show the different options,

1) Using the default value from package (not editable)
2) Edit value. new value could be entered here. This value is stored in the SQL Server.
3) Use environment value. If environment have been setup for the project, the value of the property could be mapped to the value setup in the environment value.

Let's perform a few testings to see how changing these properties affect the connection values being used during execution.

Preparation

I designed a simple SSIS package that obtain the server, database and login information, and store them in a table for examination.

For the Get Session Info task, it query the connection server name, database name and the login.

Store Session Info task pretty much store the information into a table.

Once the package is ready, it is deployed to the SSIS Catalogs for testing

First Test
Change the ServerName value to TestServer, the ServerName value font is in bold. Notice that the Data Source value in the connection string still Server1.

Execution shows the server used is TestServer. Similar test shows that execution pick up the edited InitialCatalog property (database) value as well.

Second Test
With ServerName property value edited as TestServer, now we change the Data Source value of the ConnectionString value to ProdServer.

Execution shows the server used is TestServer. In this case, it seems like the ServerName property edited value takes precedence.

Third Test
The original (unedited) connection string is this,
Data Source=Server1;Initial Catalog=master;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

Notice the connection string has the SSPI specified.

Change the UserName property value to TestUser and add a Password to the Password property.

Executions shows the SSPI is used. In this case, seems like the SSPI take precedence.

Fourth test
Remove the SSPI, add user and password on the connection string,
Data Source=Server1;Initial Catalog=master;Provider=SQLNCLI11.1;User Name=User1; Password = CrazyPassword; Auto Translate=False;

Execution shows TestUser from User property and its Password value (CrazyPassword) are being used. These two properties take precedence when there is no SSPI specified in the connection string property.

Observation
It appears that the edited Server, InitialCatalog, UserName and Passoword property values take precedence over the values in ConnectionString. However, SSPI takes precedence wherever it is specified in the connection regardless of the value of the UserName and Password edited value specified in UserName and Password property.

Based on this observation, if these properties value are different from the designer value (default value), we could change the ConnectionString property value to reflect the change, or we could also change these value directly and possibly clearer on the respective property. When I say directly, I mean changing a specific property (eg. only the server value), without changing the entire connection string. Apparently this is when SSPI is not specified.

This also applicable when using environment variable. If we previously have an environment setup with its variables defined.

We could assign the ServerName property to use the environment value. Notice the property value now is underlined.

This is often the case when developer design the SSIS with connection pointing to developer database servers (sometimes different database as well), and these properties need to be modified when the packages are deployed into production environment that are to be pointed to production servers and databases, with different user account credential. With this clarification, we know where and how to change the connection configuration correctly and efficiently.

Defunct File Cause SQL Server Upgrade Failure

2014-06-12T13:16:00.001-05:00

In the previous post, we discussed how a data file or its filegroup becomes defunct. As making data file defunct may be desirable in certain situation to resolve immediate issue and allow the database continue to be operational, you may encounter some surprise problem when you are ready to move up to the next version during the SQL Server upgrade.

As shown in the previous post that in a situation where a data file is missing or could not be brought online, there is no backup available and has been determined that the data resides in the data file is no longer needed and does not need to be restored, one way to resolve this is making the file/filegroup defunct allows certain database operations like backup to be completed successfully.

Warning. The only way to bring back offline or defunct file online is restore from a backup that was taken prior to making the data file offline/defunct.

As making a data file/filegroup defunct resolve immediate issue, however it may generate problem during SQL Server upgrade. Let's see some example. First we create a database with primary and secondary filegroup on a SQL Server 2008 instance,

Note: If you have performed the steps as in the previous blog post, you may need to delete the example database (testdb), its data files and backups for the steps below.

USE master;
GO

--Create database
CREATE DATABASE testdb
ON PRIMARY
( NAME = testdb_data, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb.mdf' ),
FILEGROUP FG1
( NAME = testdb_file1, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_file1.ndf' )
LOG ON 
( NAME = testdb_log, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_log.ldf' );
GO

Creates a table on Primary filegroup and index on the secondary filegroup

USE testdb;
GO

--Create table on primary filegroup
CREATE TABLE tbl1
(
 col1 int IDENTITY (1,1) CONSTRAINT PK_tbl1_col1 PRIMARY KEY,
 col2 varchar(50)
);
GO

--Create index on FG1 filegroup
CREATE INDEX IX_tbl1_col2
ON tbl1
(
 col2
) ON FG1;
GO

--Insert data into table and index
INSERT INTO tbl1
VALUES ('ABC');
GO

At the moment, the database has all the data file online. Let's backup the database for later use.

--Backup for later use
BACKUP DATABASE testdb
TO DISK = 'testdb_good_backup.bak';

Make the secondary data file defunct.

--Make the data file defunct
ALTER DATABASE testdb
MODIFY FILE 
( NAME = testdb_file1,
 OFFLINE );
GO

ALTER DATABASE testdb
REMOVE FILEGROUP FG1;
GO

This query below shows that the data file is now in defunct state

SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

Take a backup for the database with the defunct file

BACKUP DATABASE testdb
TO DISK = 'testdb_defunct_backup.bak';
GO

In the newer version of SQL Server instance, in this case SQL Server 2014, restore the database from the backup with the defunct file.

USE master; 
GO

RESTORE DATABASE testdb
FROM DISK = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\testdb_defunct_backup.bak'
WITH 
 MOVE 'testdb_data' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb.mdf',
 MOVE 'testdb_file1' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_file1.ndf',
 MOVE 'testdb_log' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_log.ldf';
GO

The metadata of the index in defunct file doesn't affect the restore process.The database restored and upgraded successfully. Of course it will return error if the index is queried on the new version instance, just like in the original SQL Server instance.

Let's take another example. This time with the full text index. First, restore the database back to the status before the secondary data file becomes defunct. We will restore from the good backup file.

USE master;
GO

ALTER DATABASE testdb
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

--Restore the database back to prior defunct
RESTORE DATABASE testdb
FROM DISK = 'testdb_good_backup.bak'
WITH REPLACE;
GO

Create fulltext catalog and fulltext index on filegroup FG1

USE testdb;
GO

--Create fulltext catalog
CREATE FULLTEXT CATALOG catalog1 AS DEFAULT;
GO

--Create fulltext index on filegroup FG1
CREATE FULLTEXT INDEX ON tbl1 (col2)
KEY INDEX PK_tbl1_col1
ON (FILEGROUP FG1);
GO

Now make the data file defunct like how it was done previously.

-Make the data file defunct
ALTER DATABASE testdb
MODIFY FILE 
( NAME = testdb_file1,
 OFFLINE );
GO

ALTER DATABASE testdb
REMOVE FILEGROUP FG1;
GO

Take another backup. The backup consist of defunct data file with fulltext index.

BACKUP DATABASE testdb
TO DISK = 'testdb_defunct_fulltext_backup.bak';
GO

In the new version of SQL Server instance, drop the testdb database and restore from the backup just taken.

USE master; 
GO

ALTER DATABASE testdb
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

DROP DATABASE testdb;
GO

RESTORE DATABASE testdb
FROM DISK = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Backup\testdb_defunct_fulltext_backup.bak'
WITH 
 MOVE 'testdb_data' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb.mdf',
 MOVE 'testdb_file1' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_file1.ndf',
 MOVE 'testdb_log' TO 'D:\MSSQL12.MSSQL2014\MSSQL\Data\testdb_log.ldf';
GO

The restore operation failed with error.


Processed 184 pages for database 'testdb', file 'testdb_data' on file 1.

Processed 0 pages for database 'testdb', file 'testdb_file1' on file 1.
Processed 7 pages for database 'testdb', file 'testdb_log' on file 1.
Converting database 'testdb' from version 661 to the current version 782.
Database 'testdb' running the upgrade step from version 661 to version 668.
Database 'testdb' running the upgrade step from version 668 to version 669.
Database 'testdb' running the upgrade step from version 669 to version 670.
Database 'testdb' running the upgrade step from version 670 to version 671.
Database 'testdb' running the upgrade step from version 671 to version 672.

Msg 667, Level 16, State 1, Line 24
The index "" for table "" (RowsetId 72057594039369728) resides on a filegroup ("FG1") 
that cannot be accessed because it is offline, is being restored, or is defunct.

Msg 3013, Level 16, State 1, Line 24

RESTORE DATABASE is terminating abnormally.

and also this error in Windows application log and SQL Server log,

During upgrade, database raised exception 3602, severity 25, state 53, address 000007FED2F36365. Use the exception number to determine the cause.

If we pay closer attention, we can see the the restore step completed successfully. The failure is during the conversion (upgrade) step.

With the query below on the original SQL Server instance, it shows that the object is one of the ifts (integrated full text search) internal table.

SELECT 
 p.partition_id,
 o.name [object_name], 
 o.type_desc,
 f.name [filegroup]
FROM sys.allocation_units a
JOIN sys.partitions p
 ON a.container_id = p.hobt_id
JOIN sys.filegroups f
 ON a.data_space_id = f.data_space_id
JOIN sys.objects o
 ON o.object_id = p.object_id
WHERE p.partition_id = '72057594039369728'

Observation
As the data file becomes defunct, metadata of the objects in the defunct data file retained. SQL Server upgrade process attempts to upgrade objects (eg. fulltext index) to newer version including the ones in the defunct data file (as the metadata is retained), it leads to the upgrade failure. However, even the fulltext index was determined as no longer needed, it couldn't be removed as the data file is in defunct state and on the other hand it causes error during the upgrade. In this predicament, the only solution appears to be extract everything (minus the ones in defunct data file) from the existing database to a new database.

This issue is observed on upgrade from SQL Server 2008 to SQL Server 2012 and SQL Server 2014. Restore from SQL Server 2008 to SQL Server 2008 R2 doesn't have any problem as there is no upgrade step involved during the restore operation between these two versions.

SQL Server - Defunct Filegroup and Defunct Data File

2014-06-06T10:17:00.000-05:00

A data file becomes DEFUNCT when its respective filegroup is removed or when the data file or its filegroup is not included during the piecemeal restore in simple recovery model. Usually a filegroup can not be removed if the data file is not empty. However, a filegroup could be removed if one of its data file is not online. This post illustrates how a data file becomes DEFUNCT.

Let's create a database and show how a data file becomes DEFUNCT.

USE master;
GO

--Create database
CREATE DATABASE testdb
ON PRIMARY
( NAME = testdb_data, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb.mdf' ),
FILEGROUP FG1
( NAME = testdb_file1, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_file1.ndf' ),
( NAME = testdb_file2, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_file2.ndf' )
LOG ON 
( NAME = testdb_log, 
 FILENAME = 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_log.ldf' );
GO

--Set database to simple recovery model
ALTER DATABASE testdb
SET RECOVERY SIMPLE;
GO

The query above creates a database with the PRIMARY filegroup and FG1 secondary filegroup. The PRIMARY filegroup has one data file (mdf) and FG1 filegroup has two data files (ndf). The database is also set to simple recovery model.

Next, we creates some data.

USE testdb;
GO

--Create table on primary filegroup
CREATE TABLE tbl1
(
 col1 int IDENTITY (1,1) CONSTRAINT PK_tbl1_col1 PRIMARY KEY,
 col2 char(800)
);
GO

--Create index on FG1 filegroup
CREATE INDEX IX_tbl1_col2
ON tbl1
(
 col2
) ON FG1;
GO

--Insert enough data so both secondary files in FG1 are filled with some data
INSERT INTO tbl1
VALUES ('A');
GO 120

The query above created the table is created on PRIMARY filegroup, while its index is created on FG1 filegroup.

SELECT 
 o.name tbl_name,
 i.name index_name,
 i.type_desc,
 f.name fg_name
FROM sys.objects o
JOIN sys.indexes i
 ON o.object_id = i.object_id
JOIN sys.filegroups f
 ON i.data_space_id = f.data_space_id
WHERE o.is_ms_shipped = 0;
GO

The query above verified which filegroup the data is stored at. Now we try to make the data file in FG1 defunct. Let's first take a good backup for later use.

BACKUP DATABASE testdb
TO DISK = 'testdb_good_backup.bak';
GO

Now, takes the first secondary file (testdb_file1) offline.

ALTER DATABASE testdb
MODIFY FILE 
( NAME = testdb_file1,
 OFFLINE );
GO

Warning. Please beware that the only way to bring the data file back online is restore from a backup.

SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

The query above shows that the testdb_file1 data file is now offline. That also makes the FG1 filegroup offline as well. Any attempt to query data located on the offline filegroup returns error. The query below force the query to use the index located in the offline filegroup.

SELECT col2
FROM dbo.tbl1 
WITH (INDEX(IX_tbl1_col2));
GO


Msg 315, Level 16, State 1, Line 56
Index "IX_tbl1_col2" on table "dbo.tbl1" (specified in the FROM clause) 
is disabled or resides in a filegroup which is not online.

What about backing up the database at this moment?

BACKUP DATABASE testdb
TO DISK = 'testdb_backup.bak';
GO

Msg 3007, Level 16, State 1, Line 115

The backup of the file or filegroup "testdb_file1" is not permitted because it is not online. 
Container state: "Offline" (7). Restore status: 0. BACKUP can be performed by using the FILEGROUP 
or FILE clauses to restrict the selection to include only online data.

Msg 3013, Level 16, State 1, Line 115

BACKUP DATABASE is terminating abnormally.

The only way to bring the data file back online is restore the database from the backup. You should always do that. However, if there is no backup and it has been determined that data in the offline filegroup is not needed, one way to allow backup is to make the filegroup defunct. That will remove the filegroup from the database but retain the metadata.

ALTER DATABASE testdb
REMOVE FILEGROUP FG1;
GO

Now if we check the status of the data files.

SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

Even the testdb_file2 secondary file is online and is not empty, the FG1 filegroup removal completed successfully. Notice that both secondary files in FG1 filegroup are now in DEFUNCT state. Query against data located in this filegroup returns same error.

BACKUP DATABASE testdb
TO DISK = 'testdb_defunct_backup.bak';
GO

Backup could be performed successful now with the understanding the data in the FG1 filegroup is no longer available.

As mentioned earlier, another way data files become DEFUNCT is during the piecemeal restore for database in simple recovery model. In this case, we restore the good testdb backup (testdb_good_backup.bak) taken earlier and restore only the primary filegroup to a new database (testdb_copy).

RESTORE DATABASE testdb_copy
FILEGROUP = 'PRIMARY'
FROM DISK = 'testdb_good_backup.bak'
WITH 
 MOVE 'testdb_data' TO 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_copy.mdf',
 MOVE 'testdb_log' TO 'D:\MSSQL10_50.MSSQL2008R2\MSSQL\Data\testdb_copy_log.ldf',
 PARTIAL;
GO


Processed 184 pages for database 'testdb_copy', file 'testdb_data' on file 1.
Processed 2 pages for database 'testdb_copy', file 'testdb_log' on file 1.

Msg 3127, Level 16, State 1, Line 75

The file 'testdb_file1' of restored database 'testdb_copy' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.

Msg 3127, Level 16, State 1, Line 75

The file 'testdb_file2' of restored database 'testdb_copy' is being left in the defunct state because the database is using the simple recovery model and the file is marked for read-write access. Therefore, only read-only files can be recovered by piecemeal restore.

RESTORE DATABASE ... FILE= successfully processed 186 pages in 0.049 seconds (29.595 MB/sec).

Running the below script shows the two secondary files in FG1 filegroup in DEFUNCT state.

USE testdb_copy;
GO

SELECT 
 f.name file_group, 
 d.name file_name, 
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

Note that the new database is piecemeal restore from a good complete backup. Full restore from the backup (testdb_defunct_backup.bak) taken after the defunct files creates database with the secondary files in DEFUNCT state, just like how it was.

Summary, data file becomes DEFUNCT when its filegroup is removed when one of its data file is not online. This action may be intentionally taken to allow tasks like backup to perform successfully when the offline file could not be found and there is no backup and more importantly, the data in the filegroup has been determined not needed or restored. Another situation is during piecemeal restore for database in simple recovery model that left the non-recovered file or filegroup in DEFUNCT state.

DEFUNCT data files affects SQL Server upgrade in some cases. More detail in next post.

SQL Server Agent Job and SQLCMD

2014-04-15T06:00:00.000-05:00

Have you ever encountered missing database backup or maintenance plan didn't complete as it was scheduled, especially error was encountered? This post discusses a behavior difference between SQL Server agent using T-SQL type and SQLCMD with CmdExec type.

SQL Server agent job is widely used to execute scheduled SQL Server tasks like backup, maintenance, SQL stored procedures or scripts as well as non-SQL tasks like executing program.

When creating a SQL Server job, a job step(s) define the functionality or task it performs. SQL Server job step provides different types (subsystems). Here listed some of the commonly used types (subsystems),

Transact-SQL script (T-SQL)
Operating System (CmdExec)
PowerShell
SQL Server Analysis Service (SSAS) command / query
SQL Server Integration Services (SSIS) package

T-SQL type is the most common job step type. It is used to execute T-SQL Script or stored procedures. CmdExec is used to run program, utility. batch files and etc. PowerShell type is used to execute PowerShell script within the job step command. SSAS type is used to execute SSAS XMLA script to backup SSAS or batch processing. SSIS type is used to execute SSIS package including maintenance plan created through SSMS.

Let also discuss briefly about the SQLCMD utility. SQLCMD utility allows us to run T-SQL statement in command prompt. This nice little utility allows administrator to perform SQL Server execution without bringing up the SSMS. Often this tool is used for quick admin check or some automated tasks.

Now back to SQL Agent job. When executing T-SQL command or stored procedure (SP) through SQL Server Agent, most of time the T-SQL type in the job step is selected. This type is best for most of the functionality. However, there is one behavior executing T-SQL or SP this way when an error is encountered during the execution. Let's examine. Here is a simple example,

Create a stored procedure to perform backup for multiple databases.

USE [master];
GO

CREATE PROCEDURE dbo.backupsp
AS
BACKUP DATABASE [msdb] TO DISK = 'msdb.bak';
BACKUP DATABASE [nono] TO DISK = 'nono.bak';
BACKUP DATABASE [master] TO DISK = 'master.bak';
GO

There is no 'nono' database. The second backup statement should throw an error.

Now we create a SQL Agent job with job step type T-SQL to execute this stored procedure.

USE [msdb]
GO

EXEC dbo.sp_add_job
  @job_name=N'Test Job (TSQL)',
  @enabled=1;

EXEC dbo.sp_add_jobstep
  @job_name=N'Test Job (TSQL)',
  @step_name=N'Test Step (TSQL)',
  @subsystem=N'TSQL',
  @command=N'EXEC [master].dbo.backupsp;',
  @flags=4;

EXEC dbo.sp_add_jobserver
  @job_name=N'Test Job (TSQL)', @server_name = N'(LOCAL)';
GO

Start the job,

EXEC dbo.sp_start_job @job_name = N'Test Job (TSQL)';
GO

SQL Server 2014 and SQL Server 2012 job step detail

Executed as user: NT SERVICE\SQLSERVERAGENT. Processed 1728 pages for database 'msdb', file 'MSDBData' on file 3. [SQLSTATE 01000] (Message 4035)  Processed 7 pages for database 'msdb', file 'MSDBLog' on file 3. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 1735 pages in 3.124 seconds (4.337 MB/sec). [SQLSTATE 01000] (Message 3014)  Database 'nono' does not exist. Make sure that the name is entered correctly. [SQLSTATE 08004] (Error 911)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)  Processed 472 pages for database 'master', file 'master' on file 2. [SQLSTATE 01000] (Message 4035)  Processed 2 pages for database 'master', file 'mastlog' on file 2. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 474 pages in 0.534 seconds (6.933 MB/sec). [SQLSTATE 01000] (Message 3014).  The step failed.

SQL Server 2008 R2 and earlier job step detail

Executed as user: NT AUTHORITY\NETWORK SERVICE. Processed 1856 pages for database 'msdb', file 'MSDBData' on file 2. [SQLSTATE 01000] (Message 4035)  Processed 2 pages for database 'msdb', file 'MSDBLog' on file 2. [SQLSTATE 01000] (Message 4035)  BACKUP DATABASE successfully processed 1858 pages in 2.716 seconds (5.344 MB/sec). [SQLSTATE 01000] (Message 3014)  Database 'nono' does not exist. Make sure that the name is entered correctly. [SQLSTATE 08004] (Error 911)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

You may have already notice that in SQL Server 2008 R2 and earlier version, there is no backup for master database. The SQL agent job is terminated when it encounters an error. This is likely not the intended goal in this scenario. All other database backup should be performed even if there an error was encountered.

In order to mitigate this issue, we could utilize the sqlcmd with job step CmdExec within the SQL Agent job.

USE [msdb]
GO

EXEC dbo.sp_add_job
  @job_name=N'Test Job (CmdExec)',
  @enabled=1;

EXEC dbo.sp_add_jobstep
  @job_name=N'Test Job (CmdExec)',
  @step_name=N'Test Step (CmdExec)',
  @subsystem=N'CmdExec',
  @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -Q "EXEC [master].dbo.backupsp" -b';

EXEC dbo.sp_add_jobserver
  @job_name=N'Test Job (CmdExec)', @server_name = N'(LOCAL)';
GO

The -b option at the end of command returns a DOS ERRORLEVEL value when an error is encountered within the sqlcmd. This give us a better indication of the job failure. The SQL Server Agent token (SRVR) returns the computer and SQL Server instance name.

Start the job,

EXEC dbo.sp_start_job @job_name = N'Test Job (CmdExec)';
GO

SQL Server 2014, SQL Server 2012, SQL Server 2008 R2 job step detail

Executed as user: NT Service\SQLSERVERAGENT. Processed 1728 pages for database 'msdb', file 'MSDBData' on file 4.  Processed 7 pages for database 'msdb', file 'MSDBLog' on file 4.  BACKUP DATABASE successfully processed 1735 pages in 2.613 seconds (5.186 MB/sec).  Msg 911, Level 16, State 11, Server SQL2014, Procedure backupsp, Line 5  Database 'nono' does not exist. Make sure that the name is entered correctly.  Msg 3013, Level 16, State 1, Server SQL2014, Procedure backupsp, Line 5  BACKUP DATABASE is terminating abnormally.  Processed 472 pages for database 'master', file 'master' on file 3.  Processed 2 pages for database 'master', file 'mastlog' on file 3.  BACKUP DATABASE successfully processed 474 pages in 0.656 seconds (5.644 MB/sec).  Process Exit Code 1.  The step failed.

Except the executing user difference (WORKGROUP\SQL2008R2$ for SQL Server 2008 R2), master database backup is performed for all SQL Server versions. Also, if you pay close attention, with sqlcmd, there is no [SQLSTATE 08004] (Error 911) funky stuff at the end of each execution like T-SQL type does.

Observation
For earlier SQL Server version, SQL Server agent job terminates immediately when it encounters error. This may produce unintended results as all the subsequent statements are not executed. This may become potential issue for backup, integrity check or index maintenance operations as it misses the rest of the executions.

One way to mitigate this behavior is to utilize the sqlcmd with CmdExec type within the SQL Server agent job. Sqlcmd complete the entire executions even if it encounters error.

From SQL Server 2012 and later version, SQL Server agent job appears to complete all execution regardless if it encounters error.

SQL Server 2014 RTM Release!

2014-04-01T22:47:00.004-05:00

Microsoft releases SQL Server 2014 RTM to public today! Yes, it's April Fool day, I know what you thinking but this is real. This post discuss different ways of getting the software.

SQL Server 2014 packs with a lot of new features like In-memory technologies (Code name Hekaton), buffer pool extension, new cardinality estimation logic, tighter integration with Windows Azure and many more!

Excited? Let's get it!. There are multiple ways you could download SQL Server 2014. You could download the evaluation edition with 180-day trial at TechNet Evaulation Center. The evaluation edition contains all features available for Enterprise edition so you can test all these new features.

If you are MSDN subscriber, all SQL Server 2014 editions are available for download. Once you login to MSDN download page, there is a new link for SQL Server 2014.

Here shows partial list of downloadable images available for SQL Server 2014

Another way to get your hand on SQL Server 2014 is through Windows Azure. It takes only matter of minutes to provision a Windows Azure virtual machine with SQL Server 2014. The Windows Azure virtual machine gallery provides several images contain different version and edition of SQL Server. The picture below shows the selected image for Windows Server 2012 R2 with SQL Server 2014 RTM. If you already a MSDN subscriber, definitely check this option out since there is free monthly Window Azure credits as well as discounted rate.

Traditionally there will be a developer edition for sales (more detail here). Since Microsoft just release SQL Server 2014 today, it may takes some time for any online or physical stores to start selling them.

Use PowerShell To Test Port

2014-03-25T06:00:00.001-05:00

There are times when we need to identify or troubleshoot if firewall exception is configured correctly and the desired remote server port is open. Often time, I have seen IT Professional use Telnet or PuTTY to test. However, there is another way to do it with PowerShell.

By default, Telnet is not installed on Windows or Windows Server. In order to use Telnet, the Telnet client has to be installed. In Windows, this can be done through Control Panel > Programs and Features > Turn Windows features on or off, and check the Telnet Client. For Windows Server, go to Server Manager > Features > Add Features > Telnet Client.

Or add the Telnet Client feature through PowerShell,

Import-Module ServerManager
Add-WindowsFeature -Name Telnet-Client

Instead of installing the Telnet client, alternatively we could use Windows Sockets through the System.Net.Sockets provided in .NET framework.

To test TCP port,

$tcp = New-Object System.Net.Sockets.TcpClient
$tcp.connect('<remote server>', <port>)

Or even one line of code if you wish,

(New-Object System.Net.Sockets.TcpClient).Connect('<remote server>', <port>)

That's it! No additional installation (provided that PowerShell is installed, of course).

Starting in Windows Server 2012 R2, Microsoft includes Test-NetConnection in NetTCPIP Module. This command could be used to simulate the traditional ping, tracert, and testing TCP connectivity.

# ping
Test-NetConnection -ComputerName <remote server>

# tcp test on a port
Test-NetConnection -ComputerName <remote server> -Port <port>

# tracert
Test-NetConnection -ComputerName <remote server> -TraceRoute

A note on UDP port testing. UDPClient.connect only specify the host/port to that UDP client without actually connecting the remote host. Since UDP uses connectionless transmission which is hard to reliably determining if a remote UDP port is open/close. it seems like one way to test is to send some packets to the remote UDP port and if "ICMP port unreachable" is received back, the UDP port is considered as close. Otherwise, it is unknown.

PowerShell Cim Cmdlet to PowerShell 2.0 or older

2014-03-24T06:00:00.000-05:00

Starting in PowerShell 3.0, Microsoft introduces a new set of cmdlets to manage servers or devices that complies to DMTF standards. These new set of cmdlets, CIM (Common Information Model (CIM) Cmdlets allow IT professional to better manage their data center especially when it consists of servers from different vendors.

Before PowerShell 3.0, IT professional commonly use WMI Cmdlets (WMI is Windows standard of the CIM Server) to obtain resources information for Windows, and WSMan Cmdlets for Non-Windows. In order to allow and simplify management on heterogeneous environment, a standard compliance and richer PowerShell CIM Cmdlets are introduced. You can find a list of CIM Cmdlets from this TechNet article. This msdn article provide a good introduction of CIM cmdlet as well comparison with the older WMI.

Windows Server 2012 and Windows 8 shipped with PowerShell 3.0 which support these new CIM Cmdlets. For servers or devices with older version of PowerShell, it doesn't support CIM Cmdlets directly.

For example, if we want to get some processor information of a remote server (e.g. Server1), traditionally we use WMI cmdlets like this,


Get-WmiObject -Class Win32_Processor -ComputerName Server1 | Select-Object -Property SocketDesignation, NumberOfCores, NumberOfLogicalProcessors, CurrentClockSpeed

Win32_Processor represents instances for each processor
Get-WmiObject get instance of WMI class in this case Win32_Processor

To use the new CIM Cmdlet, for the remote server (e.g. Server1) that has at least PowerShell 3.0, we could execute the command like this (try using the tab completion for ClassName like *processor for for richer experience),


Get-CimInstance -ClassName CIM_Processor -ComputerName Server1 | Select-Object SocketDesignation, NumberOfCores, NumberOfLogicalProcessors, CurrentClockSpeed

CIM_Processor represents instance for each processor. This CIM_Processor class is parent class for Win32_Processor
Get-CimInstance gets the CIM instances of a class, in this case CIM_Processor

If the same command issued to obtain processor information from another server (e.g server2) that currently installed with PowerShell 2.0,

The command failed.

The reason is because Get-CIMInstance Cmdlet is using WinRM (Windows implementation of WSMAN protocol) which is only supported from PowerShell 3.0. In order to execute the Get-CIMInstance command on Server2 with PowerShell 2.0 version installed, we have to force the CIM cmdlet to use DCOM protocol (used by WMI) which is supported in all PowerShell version (As of this post).

To do that, we use the CIM Session which provide an option of using DCOM or WSMAN. In this case, select the DCOM and execute the Get-CIMInstance against the CIM session.


$so = New-CimSessionOption -Protocol Dcom

$s = New-CimSession -ComputerName Server2 -SessionOption $so

Get-CimInstance -ClassName CIM_Processor -CimSession $s |

Select-Object -Property SocketDesignation, NumberOfCores, NumberOfLogicalProcessors, CurrentClockSpeed

It is important to know which protocol is being used as it uses different ports which will affect firewall exception setup.

If the firewall is not configured correctly, you may receive this error.

New-CimSession : The RPC server is unavailable.

DCOM assigns TCP port dynamically from 1024 through 65535. In order for WMI cmdlets to connect the remote server successfully, at remote server enable the Windows Firewall: Allow remote administrator exception (gpedit.msc > local computer policy > computer configuration > administrative templates > network, network connections > windows firewall > domain/standard profile), which will enable these RPC firewall rules as shown below.

For asynchronous callback from remote computer, the local computer need to enable the Windows Management Instrumentation (Async-In) firewall rule and sometimes (DCOM-In) firewall rule. One example is the remote SQL Server Configuration Manager. More information at this post.

The easier way for WMI firewall setup I found is go to control panel > System and Security > Windows Firewall > Allow a program or feature through Windows Firewall, Select Windows Management Instrumentation (WMI).

This will enable the firewall rules as shown below.

As long as there are no RPC or other similar firewall rules blocking the ports, these WMI firewall take care of the required WMI firewall exceptions.

WinRM protocol is a SOAP (standard simple object protocol) based protocol that uses TCP port 5985 for HTTP transport and 5986 for HTTPS.

To enable the firewall, you can run winrm quickconfig on powershell on the remote server, which will enable the firewall rules show below.

Similarly, we could configure the Windows Remote Management through Windows Firewall and Allow a program or feature through Windows Firewall.

These are the firewall rules enabled.

The compatibility mode is for the older WinRM (WinRM 1.1) which uses the common port 80. Since WinRM 2.0, the port has changed to 5985. Also, if required, HTTPS firewall rule could be configured.

If the firewall for WinRM is not configured correctly, you may receive this error when using CIM Cmdlets with the default WinRM (or specified in the session option to use WSMAN) protocol.


Get-CimInstance : WinRM cannot complete the operation. Verify that the specified computer name is valid, that the computer is accessible over the network, and that a firewall exception for the WinRM service is enabled and allows access from this computer. By default, the WinRM firewall exception for public profiles limits access to remote computers within the same local subnet.

Hope this post help you to use CIM cmdlets against older PowerShell version and the firewall setup.