It is clear by now that MS SQL server and powershell are going to be good friends for a long time to come. So a prudent DBA should be building a war chest of script modules that can be reused in different cases. I will go through a series of short posts that will make every DBAs life easier and more productive.
let’s start with basics – connection provider. If you’re going to interface with a SQL server, you need to connect to it. Here is a very simple script that will do the job:
#get_conection.ps1
param(
[string] $datasource="myserveraddress",
[string] $database = "MyDb",
[string] $authType = "Integrated",
[string] $username ="",
[string] $password=""
)
## Prepare the authentication information.
if ($authType -eq "Integrated")
{
$authentication = "Integrated Security=SSPI;"
}
else
{ $authentication = ("uid={0};pwd={1};" -f $username,$password) }
# Create the actual connection string
$connectionString = "Persist Security Info=True;Initial Catalog=$database;Data Source=$dataSource;$authentication"
## Connect to the data source and open it
$connection = new-object System.Data.SqlClient.SqlConnection $connectionString
$connection
As you can see, there is practically nothing to it. you can easily switch between SQL [regular] and Windows [integrated] modes of authentication. We’ll be using this script a lot in the future.
