Here’s an interesting option when copying a database just to transfer it. Say to bring down a copy of production to troubleshoot or test. This allows you to backup a database without affecting the regular backup history or restore ability.
Copy-Only Backups (http://msdn.microsoft.com/en-us/library/ms191495(v=sql.105).aspx)
OK, we try to setup our dev/test servers with a well known, common password but sometimes you’ve got an older virtual machine or something setup by someone outside you group who didn’t use it. And then maybe your having an issue so you decide to remove said machine from the domain and then re-add it. Only to discover you have no idea what the administrator password was. Anyway, I’m sure this is posted in a billion other places on the interweb, but one of my colleagues used this method to get us out of just such a pickle and I’d like to remember where to find it next time.
This article (Why doesn’t ISNUMERIC work correctly?) explains why the the SQL ISNUMERIC function returns true for strings with more than just digits and decimal points. The short answer is that it considers formatted numbers, such as those with currency symbols or commas among others as numeric.
So how do you test for just digits and decimal points? Use the following comparison:
NOT LIKE '%[^0-9.]%'
This is using a kinda sorta regular expression with the LIKE operator. '%[^0-9.]%' expression matches the pattern if there is any character that does not match(the ^ character) a digit between 0 and 9 or the decimal point anywhere in the string. Since this is searching for a pattern that doesn’t match a number you then need to search for values that are NOT LIKE it.
If you want to exclude decimal numbers then just remove the '.' from the character list.
Here’s an interesting article (http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/) on the affects of using the NOLOCK hint in your SQL queries and how even data not being directly affected by an update can be messed up in your query.
I’ve always tried to avoid using it but have to admit to being a bit more liberal about it recently to solve some sticky deadlocking issues. Those places where it’s being used could happily show bad data and not have any real affects, but it’s easy to see where they could, especially where aggregates are being calculated. I’ve also not yet made the jump to snapshot isolation as that presents it’s own set of issues but will be looking into it more in the future.
I was forwarded in interesting article, Developing Low-Maintenance Databases by Alex Kuznetsov, that talked about good database design and implementation practices. Many I already adhere to, such as limiting access to the database through stored procedures, views and functions. However, there was one section in the article that I found particularly intriguing, How to Refactor a Table without Downtime.
In many of the systems I work on we have many components on many servers accessing the same database, coordinating the simultaneous upgrade of those components can be tricky at best. Although in some cases access can be controlled through a web service and thus limiting upgrade points, performance factors have led us to have many of the components access the database directly.
I definitely intend on looking at incorporating Alex’s pattern into our future upgrade procedures.
A colleague of mine forwarded the following article along to me:
Deciding between COALESCE and ISNULL in SQL Server (http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/)
I switched over from ISNULL to COALESCE a couple of years ago and haven’t looked back, primarily to standardize and avoid nested ISNULLs. This article seems to affirm my thought that there are few disadvantages to COALESCE except in certain unusual circumstances. It’s worth a quick read.
I ran into an issue with one of my WCF services and was having the quite a time trying to track it down, especially since it was failing before it was getting to the actual service. I came across a nice way of tracing the call all the way through the process. Add the following section to your web.config file:
<system.diagnostics>
<sources>
<source name="System.ServiceModel"
switchValue="Information, ActivityTracing"
propagateActivity="true">
<listeners>
<add name="traceListener"
type="System.Diagnostics.XmlWriterTraceListener"
initializeData= "C:\Logs\Traces.svclog" />
</listeners>
</source>
</sources>
</system.diagnostics>
This will create a trace file that you can open up in the Microsoft Service Trace Viewer. You can read about the details here.
Quick note – Don’t forget to remove it when you’re done!
Not infrequently, while deploying my WCF web services to various dev and QA environments I run into security issues, where for some reason I can’t get authenticated. I recently found an article that (WCF: "An error occurred when verifying security for the message." and Service Security Audit) that talks about how to get more info on these errors logged in the application log using the following serviceSecurityAudit behavior:
<serviceSecurityAudit
auditLogLocation="Application"
serviceAuthorizationAuditLevel="Failure"
messageAuthenticationAuditLevel="Failure"
suppressAuditFailure="true"/>
The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)
Here’s a neat little feature I wasn’t aware of until recently. You can make a .NET Dictionary object case-insensitive. i.e. myDictionary[“ABC”] returns the same as myDictionary[“aBc”]. You can read more at:
<orderformdata> <orders> <order> <orderdate>1/1/2011</orderdate> <ordertypeid>1</ordertypeid> <orderamount>1000.00</orderamount> </order> <order> <orderdate>1/1/2011</orderdate> <ordertypeid>1</ordertypeid> <orderamount>1000.00</orderamount> </order> </orders> <references> <ordertypes> <ordertype id="1" name="Professional Services"/> <ordertype id="2" name="Product"/> <ordertype id="3" name="Support Contract"/> </ordertypes> </references> </orderformdata>
So this week we pushed out an early iteration of the latest version of our integration tool to a customer as a proof-of-concept for evaluation. One of the features they were most interested in evaluating is the web service interface. Our current customers interact with the existing version of the integration tool through FTP and a UI.
Although the backend functionality is fairly stable, the web service layer itself is not that mature. Not too big a deal as it’s a fairly thin wrapper over our existing service layer. We created, tested and delivered a test .NET client that seemed to work fine and so were reasonably confident that there wouldn’t be too many hiccups.
Not so much. We spent the better part of the day simply trying to get their tool to retrieve and understand our WSDL. The good news is that we finally got it to work.
One of the really powerful features of PowerShell is the ability to run commands remotely. I used this when I wanted to loop through my test servers from the build machine and run my MSI installs.
The following script loops through an array of machines and returns a list of .log files under the C:\Windows folder. One thing to note. The remote session doesn't have access to any of the local script variables, so we pass them as parameters using the -Args argument of the Invoke-Command cmdlet, receiving them using the param statement inside the remote script block.
$AgentServers = @("MYSERVER1", "MYSERVER2");
ForEach ($AgentServer in $AgentServers) {
$Session = New-PSSession -ComputerName $AgentServer;
$FilePattern = "*.log";
$Arguments = $FilePattern
Invoke-Command -Session $Session -Args $Arguments -Script {
param($FilePattern)
Set-Location "C:\Windows"
Get-ChildItem -Include $FilePattern -Recurse
}
Remove-PSSession -Session $Session
}
Now the first time you run the remote script above, it may well fail. Why? Because you forgot to enable remoting on the target machine. On each machine you want to run remote sessions on you’ll have to run:
Enable-PSRemoting
Note that you’ll have to start PowerShell as administrator to perform this.
Also take note that we’re killing each session using Remove-PSSession when we’re done with it as there is a 5 session limit on each remote server and it’s pretty easy to hit that if you forget to close out prior ones.
On that note, there’s a quick trick on clearing out all those orphaned sessions:
Get-PSSession | Remove-PSSession
Get-PSSession will return a list of all open session objects, piping them to Remove-PSSession which subsequently closes them out.
As I mentioned in an earlier post, just like the DOS command line has batch files, PowerShell can run PowerShell script files. These have the .PS1 extension.
So go ahead, throw a bunch of PowerShell commands together into a .PS1 file in NotePad and save it. Next, navigate to the folder you saved it to and double-click on it. Awesome, you just ran your first PowerShell script! What do you mean it didn’t run? It came up in NotePad?
Oh yeah. By default, for security reasons, double-clicking on a PowerShell script doesn’t run it. To do that you have a couple of options. First is to open up PowerShell and run the script from PowerShell's command line. To do that you simply type:
C:\MyScripts\MyCoolScript.ps1
If your current folder is already C:\MyScripts you’ll type:
.\MyCoolScript.ps1
Note that we prefaced the script file with “.\”. Without that, PowerShell thinks it’s a built-in command and yells that it doesn’t recognize it.
OK, you’ve typed it in, hit enter and away it goes, no? Except that all you see is:
File C:\MyScripts\MyCoolScript.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.
At line:1 char:11
+ .\test.ps1 <<<<
+ CategoryInfo : NotSpecified: (:) [], PSSecurityException
+ FullyQualifiedErrorId : RuntimeException
$%!#$%!^&*#…OK, ok, breath…again…deep breath…Yet another security “feature”. With this one type at the command line:
Set-ExecutionPolicy RemoteSigned
Or
Set-ExecutionPolicy Unrestricted
You only need to run this once and it will allow you to run scripts. When setting RemoteSigned, any local scripts will run as-is, but remote scripts will need to be digitally signed. Like Enable-PSRemoting, you’ll need to open PowerShell as administrator.
But I’m not an administrator! The execution policy is not a true security setting, it’s simply there to help prevent the inadvertent running of malicious scripts. You can actually set the execution policy when you open PowerShell from the command line by using the –ExecutionPolicy argument, using the same RemoteSigned or Unrestricted value. This will only set the execution policy for that session.
Note that when using the Set-ExectuionPolicy you can set the scope of the setting using the –Scope argument to be either the current process (-Scope Process, same as setting it on the command line), current User (-Scope CurrentUser) or the local machine (-Scope LocalMachine). The default value is LocalMachine.
OK, so one of the promises of PowerShell is that not only does it do all this cool new stuff, but all your favorite DOS commands are aliases of the new PowerShell commands, but that you can also run your old executables just like you used to….
Well not so fast….
Running a straight executable such as MyExe.exe, works just fine as long as you’re in the exe’s folder or it resides in a folder in the PATH environment variable.
Try passing to start passing it command line arguments and things start to get squirly.
This is due to how PowerShell handles command line arguments. PowerShell has three types of arguments:
PowerShell tries to treat executable paramters the same way. This works great if your executable uses the same format, but if you’re trying to run MsiExec you’ve got a problem.
MsiExec /i MyInstall.msiWill work fine, but try:
$MyCustomInstallFolder = “D:\Custom Program Files”
MsiExec /I MyInstall.msi TARGETDIR=$MyCustomInstallFolder
Not so much. the problem is is that what is actually getting passed is:
MsiExec /i MyInstall.msi “TARGETDIR=D:\Custom Program Files”MsiExec ends up ignoring the TARGETDIR parameter because PowerShell didn’t recognize TARGETDIR as a named parameter and treated it as a positional parameter, surrounding it with quotes because the expanded string contained spaces.
After fighting with this for quite some time (and doing a fair amount of Googling), I ended up writing the following function that utilizes the .NET Process object to execute an executable, passing a string variable for the entire command line.
Function Start-Process
{
Param([string]$Executable,
[string]$Arguments,
[string]$WorkingFolder,
[int]$Timeout = 240000,
[switch]$ShowStandardOutput)
Write-Host ("Starting Process, {0}" -F $Executable);
Write-Host ("Command Line Args: {0}" -F $Arguments);
Write-Host ("Working Folder: " -F $WorkingFolder);
Write-Host ("Timeout: {0}" -F $TimeOut);
Write-Host ("Show Std Out: {0}" -F $ShowStandardOutput);
$ProcessInfo = New-Object -TypeName System.Diagnostics.ProcessStartInfo;
$ProcessInfo.FileName = $Executable;
$ProcessInfo.WorkingDirectory = $WorkingFolder
$ProcessInfo.UseShellExecute = $false;
if ($ShowStandardOutput) {
$ProcessInfo.RedirectStandardOutput = $true;
}
else {
$ProcessInfo.RedirectStandardOutput = $false;
}
$ProcessInfo.RedirectStandardError = $false;
$ProcessInfo.Arguments = $Arguments;
$ProcessInfo.CreateNoWindow = $false;
$ProcessInfo.ErrorDialog = $false;
$Process = New-Object -TypeName System.Diagnostics.Process;
$Process.StartInfo = $ProcessInfo;
$Process.EnableRaisingEvents = $false;
$Process.Start();
if ($ShowSTandardOutput) {
$Output = $Process.StandardOutput.ReadToEnd();
$Output;
}
if (-not $Process.WaitForExit($Timeout)) {
$Process.Kill;
$ProcessInfo;
throw "Start-Process - Process timed out";
}
if ($Process.ExitCode -ne 0) {
$ProcessInfo;
throw "Start-Process - Process failed with exit code - " + $Process.ExitCode
};
}
Since then I’ve read that you can actually run Cmd.exe, passing the entire command line (executable and arguments) as a string, thus doing something similar to what I’m doing with the Process object.
PowerShell provides a comprehensive set of flow-control constructs, starting with if/elseif/else as follows:
$Value = 2
If ($Value -eq 1) {
Do-Something
}
ElseIf ($Value -eq 2) {
Do-SomethingElse
}
Else {
Do-SomethingCompletelyDifferent
}
Note that we have a completely different set of comparison and logical operators to remember as follows:
| Equals | -eq |
| Not Equal To | -ne |
| Greater Than | -gt |
| Greater Than or Equal To | -ge |
| Less Than | -lt |
| Less Than or Equal To | -le |
| Not | -not or ! |
| And | -and |
| Or | -or |
Additionally, if you're doing string comparison, you can force case-sensitive or case-insensitive (default comparison is case-insensitive) by prefacing the operator with a c or i e.g. -ceq is a case-sensitive equal comparison.
In addition to the if flow control, PowerShell also has:
Switch ($MyValue)
{
Result1 {Code Block}
Result2 {Code Block}
Default {Code Block}
}
A couple of good articles on flow control are:
I am pretty light on the details of variables, particularly around scope, expansion and built-in variables, but PowerShell does have them. Variables are always prefaced with “$”. Declaration and assignment of variables is as simple as:
$Number = 2
Or
$Files = Get-ChildItem
In the first case, we’re assigning the number 2 to the variable $Number. In the second we’re assigning $Files an array of files in the current folder. Remember that depending on the provider this may not be files. In the case of:
SQL:\MYSQLSERVER\DEFAULT\Databases\MyDatabase\Tableswe’d be assigning an array of SMO table objects in the MyDatabase database.
I’m a little shaky on variable expansion. For example:
$Subfolder = “MySubFolder”
Set-Location C:\MyFolder\$Subfolder
will set your current location to C:\MyFolder\MySubFolder. If you have space in your path you could type:
Set-Location “C:\My Folder\$Subfolder”
and the value of $Subfolder will replace $Subfolder in the path. However, I seem to have had cases where the string replacement doesn’t happen and I simply end up with a string “C:\My Folder\$Subfolder”
When in doubt, you can look to string concatenation as follows:
$Subfolder = “MySubFolder”
$Folder = “C:\MyFolder\” + $Subfolder
Or PowerShell’s equivalent of the .NET string.Format(string, arg1, arg2, …) as follows:
Set-Location (“C:\My Folder\{0}” –F $Subfolder)
Although you don't see it often, you can type variables by declaring them with a specific .NET type as follows:
[int]$MyInteger = 1
This will explicitly type the variable as an integer. There are two special types of variables and literal assignments, arrays and hastables. You can define and assign a literal array as follows:
$MyArray = @(1, 2, 3)
$MyValue = $MyArray[1]
This creates a 3 integer, zero-indexed array and then assign the second value, 2 to the variable $MyValue. A hash table variable, or dictionary, creates a list of name/value pairs as follows:
$MyHashTable = @{"Value1" = 123, "Value2" = 456}
$MySecondValue = $MyHashTable["Value2"]
You can add a new value by simply assigning a value to an unused name as follows:
$MyHasTable["Value3"] = 789
Or remove a value using the .Remove method as follows:
$MyHashTable.Remove("Value2")
Don't forget you can do all of this interactively from the command line and viewing the value of the variable is as simple as typing the variable name at the command line and hitting enter.
As noted at the beginning, I’m still a little light on the details of variables. I believe there are a number of scope rules around access to variables inside modules etc. that I am not familiar with so Google will be your friend with this.
A good article I found on variables is PowerShell Tutorial 7: Accumulate, Recall, and Modify Data.
PowerShell allows you to pipe the results of one command into the next. It doesn’t simply pass along the text output but sends the entire resulting .NET object. This may be an array or IEnumerable collection of objects.
For example, Get-ChildItem *.txt will return a list of files in the current folder. However what it’s actually returning is a list of System.IO.FileInfo objects. Typing Get-ChildItem *.txt | Remove-Item will cause it to pass along that list of objects to the Remove-Item cmdlet which will subsequently delete them.
That’s not so interesting because you could have simply typed Remove-Item *.txt (or Del *.txt). But what if you wanted to delete only text files larger than 10KB? Try this:
Get-ChildItem *.txt | Where-Object {$_.Length -gt 10000} | Remove-itemSo what’s going on here? First we’re taking the output of the Get-ChildItem *.txt cmdlet which returns an array of System.IO.FileInfo objects representing all of the text files and passing it to the Where-Object. One of the possible arguments for the Where-Object (aliased as Where) is a script block.
The script block here is {$_.Length > 10000}. What’s going on here is that the Where-Object cmdlet is passing each input object (i.e. each FileInfo object) into the script block. The passed in object is represented in the script block as “$_”. We’re then testing to see whether it’s Length property is greater than 10000 using the –gt greater than operator and returning only those that evaluate to true. (Yes, PowerShell doesn’t use the standard operator symbols we’re used to but that’s another blog entry).
Next along we’re passing along the resulting text files bigger than 10KB to Remove-Item and deleting them. Note that since we’re dealing with standard .NET FileInfo objects we could have used any of it’s properties such as LastAccessTime or Attributes.
PowerShell is Microsoft’s scripting alternative to ye olde DOS command line. Although it shares many of it’s concepts, it it immensely more flexible and extensible.
It’s primary purpose is to allow system admins to accomplish more through a command line interface and scripts using a more standard command syntax than the collection of DOS commands, VBScripts and random executables that are available with the standard cmd.exe interface.
At it’s base it’s an interactive shell that allows you to type commands and view the results. Like the DOS command line, you can create script files (.PS1 instead of .BAT). Unlike the DOS command line you can load extensions in the form of Snap-ins and Modules (Snap-Ins are a version 1 concept that can still be loaded but have largely been replaced by Modules in version 2).
There are two important concepts in PowerShell, CmdLets and Providers.
CmdLets are commands you type at the command line such as Set-Location and Get-ChildItem. Note that these can be aliased. You can type Del *.txt and it will delete all text files in the current folder because Del is an alias of Remove-Item.
You’ll notice that the non-aliased naming convention for CmdLets is Verb-Object. Not only that, but if you define a CmdLet or function that doesn’t use one of the pre-defined verbs, PowerShell will yell at you, although it will still function correctly (You can get a list of approved verbs by typing Get-Verb).
Providers on the other hand allow you to navigate systems through a folder system. The most obvious is the file system. The File System provider allows you to type CD C:\Users (translates to Set-Location C:\Users) which will set your current location to the C:\Users folder.
However, unlike the DOS command line, we’re no longer limited to the file system. Load up the SQL Server provider, type CD SQL:\MYSQLSERVER\DEFAULT\Databases\MyDatabase\Tables, type Dir (translates to Get-ChildItem) and you’ll get a list of the tables in the MyDatabase database on the default SQL instance on MYSSQLSERVER. there are also providers for the registry and IIS, allowing for easy navigation.
