Script Database Mail Setup in SQL Server using PowerShell

You can use a PowerShell script to enable SQL Server’s Database Mail feature, as shown in this article on the SQL Server Magazine site.


Build Your Own Google?

PageRank is the original algorithm of Google, and this page shows you how to do ranking in SQL Server using some basic concepts.

You could rank the entire internet in only 100 passes.

A little more information on this page.

The Bizzaro Guide to SQL Server Performance

This article is now not to do performance tuning, also known as “The World’s Worst Performance Tuning Advice“, provided as an April’s Fool Day joke from 2010.

Password Compromise

Researchers stumbled across a cache of stolen passwords for internet accounts, and once again they (and I) are stunned as to the most common passwords used by people to secure these sites. The news story at ZDNet says that over two million account credentials and passwords for Facebook, Yahoo, Google, Twitter, Linkedin, Odnoklassniki (the second largest Russian social network site) and more were uncovered and the most common password is “123456”

The most common passwords found in the cache and the count of occurrence:

Please make sure you have a difficult password and that you change it often. If you are going to manage databases, you should start by setting a good example. There are several sites with advice on selecting passwords, like this one at Lifehacker.

Comparison of SQL Server 2012 Editions

Do you know which edition you should buy? This site shows the different editions of SQL Server 2012 side-by-side.

Most applications will work correctly using the Standard Edition, but if you can afford it you should get the Enterprise Edition.

Current SQL Server Version List

This chart that lists the latest versions of the various database products available. This unofficial build chart lists all of the known KB articles, hotfixes and other builds of MS SQL Server 2014, 2012, 2008 R2, 2008, 2005, 2000 and 7.0 that have been released.

It is important that you have the latest service packs and updates installed for the version of Windows the server is running, but also for the SQL Server instance as well.

You can get the current version of your SQL Server instance with this query, which should return your version information:

Select @@versionMicrosoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

SQL Server Training

Microsoft has a great training resource at their training web site.

This Microsoft site allows you to track down available training courses and determine which classes are required for certification, and the latest books published.


You can use your Microsoft Live login to create a lesson plan and start taking some of the free courses today.

SQL Server Instance Information with PowerShell

You can use PowerShell, provided by Microsoft, to gather and display information about your instance of SQL Server. The scripting process is fairly easy, and it can help you gather information about an instance very quickly.

In this blog post by Michiel Worie, he covers the building and running of a PowerShell script from SQL Server 2008:

## Initialize-SqlpsEnvironment.ps1## Loads the SQL Server provider extensions## Usage: Powershell -NoExit -Command "& '.\Initialize-SqlPsEnvironment.ps1'"## Change log:# June 14, 2008: Michiel Wories#   Initial Version# June 17, 2008: Michiel Wories#   Fixed issue with path that did not allow for snapin\provider:: prefix of path#   Fixed issue with provider variables. Provider does not handle case yet#   that these variables do not exist (bug has been filed)$ErrorActionPreference = "Stop"$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue"){    throw "SQL Server Powershell is not installed."}else{    $item = Get-ItemProperty $sqlpsreg    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)}## Preload the assemblies. Note that most assemblies will be loaded when the provider# is used. if you work only within the provider this may not be needed. It will reduce# the shell's footprint if you leave these out.#$assemblylist = "Microsoft.SqlServer.Smo","Microsoft.SqlServer.Dmf ","Microsoft.SqlServer.SqlWmiManagement ","Microsoft.SqlServer.ConnectionInfo ","Microsoft.SqlServer.SmoExtended ","Microsoft.SqlServer.Management.RegisteredServers ","Microsoft.SqlServer.Management.Sdk.Sfc ","Microsoft.SqlServer.SqlEnum ","Microsoft.SqlServer.RegSvrEnum ","Microsoft.SqlServer.WmiEnum ","Microsoft.SqlServer.ServiceBrokerEnum ","Microsoft.SqlServer.ConnectionInfoExtended ","Microsoft.SqlServer.Management.Collector ","Microsoft.SqlServer.Management.CollectorEnum"foreach ($asm in $assemblylist){    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)}## Set variables that the provider expects (mandatory for the SQL provider)#Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $falseSet-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000## Load the snapins, type data, format data#Push-Locationcd $sqlpsPathAdd-PSSnapin SqlServerCmdletSnapin100Add-PSSnapin SqlServerProviderSnapin100Update-TypeData -PrependPath SQLProvider.Types.ps1xml update-FormatData -prependpath SQLProvider.Format.ps1xml Pop-LocationWrite-Host -ForegroundColor Yellow 'SQL Server Powershell extensions are loaded.'Write-HostWrite-Host -ForegroundColor Yellow 'Type "cd SQLSERVER:\" to step into the provider.'Write-HostWrite-Host -ForegroundColor Yellow 'For more information, type "help SQLServer".'

FineBuild Version 3.1.0

This utility is an open source utility that helps you install and configure a SQL Server instance in a uniform and secure way.

From the web site:FineBuild provides 1-click install and best-practice configuration of SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.”

Once you have downloaded the documentation and installed scripts, you can customize the install scripts until you get them working the way you like. Then you can run FineBuild against a Windows server to create a new SQL Server instance that is the same each time you run the install. You can document the process and that becomes your build guide, which really makes it easy for the auditors to see your procedures and audit your SQL Server build process.




%d bloggers like this: