Monthly Archives: May 2013

Add Adventure Works Database to Windows Azure VM

Posted on by

This has been an interesting journey. The Adventure Works database is frequently used in blogs and reference books and I wanted to install it in my Windows Azure Learning Lab and I also wanted to automate the process.

The easiest way is to download the Windows Azure MDF file from  http://msftdbprodsamples.codeplex.com/ jump through all the security warnings in Internet Explorer and save the file and then create the database as follows

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'PATH TO \AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

That is the way I will do it from now on! After reading this page I tried to download the file with Powershell but it would not as I could not provide a direct link to the file. Maybe someone can help me with that. So I thought I would use my SkyDrive to hold the MDF file and map a drive on the server.

\to do this you need to add the Desktop Experience feature to the server. This can be done as follows

Import-Module ServerManager
Add-WindowsFeature Desktop-Experience -restart

This will take a few minutes to install, reboot and then configure the updates before you can log back in. While it is doing this log into your SkyDrive and navigate to a folder and copy the URL to notepad

It will look something like this

https://skydrive.live.com/?lc=2137#cid=XXXXXXXXXXXXXXXX&id=CYYYYYYYYYYYYYYYY

Copy the GUID after the cid=

and write this command

net use T:  \\d.docs.live.net@SSL\XXXXXXXXXXXXXXXX /user:$user $password

I keep this in a script and pass the user and password in via Read-Host

However, if you try to copy the item from the folder you will get an error

The file size exceeds the limit allowed and cannot be saved

So you will need to alter a registry key as follows

Set-ItemProperty -Path HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WebClient\Parameters -Name FileSizeLimitInBytes -Value 4294967295

and then restart the WebClient service Then run the net use command to map the drive and copy the file with Copy-Item

But my script to auto install the Adventure Works database via Powershell once you have completed all the pre-requisites is

$user = Read-Host "user"
$password = Read-Host "Password"
net use T:  \\d.docs.live.net@SSL\XXXXXXXXXXXXXXX /user:$user $password
New-Item c:\AW -ItemType directory
Copy-Item T:\Documents\Azure\AdventureWorks2012_Data.mdf C:\AW
Invoke-Sqlcmd -ServerInstance YourServerName -Database master -Query "CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'C:\AW\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;"

To be honest I don’t think I will use this method as my copy failed twice before it succeeded so I will just download the file and create the database!!

Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine

Checking SQL Error Logs, Event Logs and Stopping Services with Powershell

Posted on by

It was patching time this week at MyWork so I thought I would share some Powershell scripts I use to speed up the process.

I keep these in their own folder and cd to it. Then I can just type the first few letters and tab and Powershell completes it. Nice and easy and time saving

The first thing I do is to stop the SQL services with the StopSQLServices.ps1

Get the server name with Read-Host then I like to see the before and after using

get-service -ComputerName $server|Where-Object { $_.Name -like '*SQL*' }

This uses the Get-service CMDlet to find the services with SQL in the name and display them. Then we pass the running services to an array and use the stop method with a while to check if the services are stopped before displaying the services again. Note this will stop all services with SQL in the name so if for example you are using Redgates SQL Monitor it will stop those services too. If that could be an issue then you may need to alter the where clause. As always test test test before implementing in any live environment.

Once the services are stopped I RDP using the RDP script which again uses Read-host to get a server and then opens up a RDP with a simple Invoke-Command. This means I can stay in Powershell.

Then I patch the server and reboot using the ping script to set up a continuous ping.

If you want to install Windows Updates via Powershell you can use the details here. I like to jump on the box to keep an eye on it.

To check the event log The EventLog.ps1 script is very simple

Get-EventLog  -computername $server -log $log -newest $latest | Out-GridView

Enter the server name and then application or system and it will display the results using out-gridview which will allow you to filter the results as required. I have another version of this script with a message search as well.

You can simply add where {$_.entryType -match “Error”} if you only want the errors or Warning for the warnings. I like to look at it all.

Check the SQL error log with this script which uses the SMO method

$Server = Read-Host "Please Enter the Server" 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server  
$Results = $srv.ReadErrorLog(0) | format-table -Wrap -AutoSize  
$Results

I love these four lines they make it so easy for me to look at the SQL error log whenever I need to. If you want you can pipe to Out-GridView or even to notepad. If I want to check one of the previous error logs I change ReadErrorLog(0) to ReadErrorLog(1) or 2 or 3 etc. I have a daily script which emails me any SQL error log errors and DBCC errors every day so I am aware of any issues before

Then the AutoServices.ps1 to show the state of the auto start services. Strangely you cannot get the Start Type from Get-Service so I use Get-WMIObject. If any have failed to start then I use Get-Service to get the service  and pipe to Start-Service

This is what works for me I hope it is of use to you

Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine

<# 
.NOTES 
    Name: StopSQLServices.ps1 
    Author: Rob Sewell http://sqldbawithabeard.com
    Requires: 
    Version History: 
                    Added New Header 23 August 2014
    
.SYNOPSIS 
    
.DESCRIPTION 
    
.PARAMETER 
    
.PARAMETER 

.PARAMETER 

.EXAMPLE 
#> 
#############################################################################################
#
# NAME: StopSQLServices.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com @fade2blackuk
# DATE:15/05/2013
#
# COMMENTS: This script will stop all SQL Services on a server
# ------------------------------------------------------------------------

$Server = Read-Host "Please Enter the Server - This WILL stop all SQL services"

Write-Host "###########  Services on $Server BEFORE  ##############" -ForegroundColor Green -BackgroundColor DarkYellow
get-service -ComputerName $server|Where-Object { $_.Name -like '*SQL*' }Write-Host "###########  Services on $Server BEFORE  ##############" -ForegroundColor Green -BackgroundColor DarkYellow
## $Services = Get-WmiObject Win32_Service -ComputerName $server|  Where-Object { $_.Name -like '*SQL*'-and $_.State-eq 'Running' }
$Services = Get-Service -ComputerName $server|Where-Object { $_.Name -like '*SQL*' -and $_.Status -eq 'Running' }

foreach ($Service in $Services) {

    $ServiceName = $Service.displayname
    (get-service -ComputerName $Server  -Name $ServiceName).Stop()
    while ((Get-Service -ComputerName $server -Name $ServiceName).status -ne 'Stopped')
    {<#do nothing#>}
}
Write-Host "###########  Services on $Server After  ##############" -ForegroundColor Green -BackgroundColor DarkYellow
Get-Service -ComputerName $server|Where-Object { $_.Name -like '*SQL*' }
Write-Host "###########  Services on $Server After  ##############" -ForegroundColor Green -BackgroundColor DarkYellow
 

#############################################################################################
#
# NAME: RDP.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com @fade2blackuk
# DATE:15/05/2013
#
# COMMENTS: This script to open a RDP
# ------------------------------------------------------------------------

$server = Read-Host "Server Name?"
Invoke-Expression "mstsc /v:$server"
#############################################################################################
#
# NAME: Ping.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com @fade2blackuk
# DATE:15/05/2013
#
# COMMENTS: This script to set up a continous ping 
# Use CTRL + C to stop it
# ------------------------------------------------------------------------

$server = Read-Host "Server Name?"
Invoke-Expression "ping -t $server"
#############################################################################################
#
# NAME: SQLErrorLog.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com @fade2blackuk
# DATE:15/05/2013
#
# COMMENTS: This script will display the SQL Error Log for a remote server
# ------------------------------------------------------------------------
$Server = Read-Host "Please Enter the Server" 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server  
$srv.ReadErrorLog(0) | Out-GridView 

#############################################################################################
#
# NAME: Autoservices.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com @fade2blackuk
# DATE:15/05/2013
#
# COMMENTS: # Script to show the services running that are set to Automatic startup - 
# good for checking after reboot
# ------------------------------------------------------------------------

$Server = Read-Host "Which Server?"

Get-WmiObject Win32_Service -ComputerName $Server  |  
Where-Object { $_.StartMode -like 'Auto' }| 
Select-Object __SERVER, Name, StartMode, State | Format-Table -auto
Write-Host "SQL Services"
Get-WmiObject Win32_Service -ComputerName $Server  |  
Where-Object { $_.DisplayName -like '*SQL*' }| 
Select-Object __SERVER, Name, StartMode, State | Format-Table -auto

 

Lessons Learnt from my first talk at SQL SouthWest

Posted on by

The timing was good enough that I could offer to do a talk based on my previous post on Windows Azure for my SQL User Group SQL SouthWest when Jonathan and Annette.( @FatherJack and @MrsFatherJack) put out a call for volunteers.

I did my best with the 7 P’s. I ran through it at lunchtime, I made sure I had power and a HDMI lead after checking with Jonathan, I got a glass of water. I knew the first line I was going to say

However, I neglected to check that I would have HDMI in at the location so everything that was on my laptop was useless! My laptop did very odd things to the USB stick when I tried to transfer to Jonathans laptop and he didn’t have Powershell V3 installed so whilst Neil Hambly @Neil_Hambly from Confio was speaking I was busy ignoring a very interesting talk on Waits to install and configure Powershell Azure on my Azure VM. Sorry Neil.

But in the end it more or less worked and we are lucky to have such a patient and supportive user group who helped me along the way as well. Thank you folks

Things I took away from the evening

  1. Double check you have all the connections
  2. Practice and Practice some more
  3. Think about the times when something is running and what you will say when there is nothing to see
  4. Presenting completely inside a RDP session adds unnecessary complication
  5. The Demo Gods WILL hit you and the curse of the red text will fall upon you during the presentation. Accept it and move on.
  6. Have an opening line
  7. Remember to breath (especially when the demo falls over)
  8. Enjoy it!

It didn’t go perfectly but people gave me some good feedback and I am pleased to say that I have pointed people towards something new that will help them and passed over my knowledge and that to me is what the SQL Community is all about. I have a load of other ideas for things I can talk about and blog about so it is going to be a very busy time for me as I work my way through them and do all the other exciting things coming my way in the SQL world.

Visit your own User Group – You can find them here http://www.sqlpass.org/

If you are in the South West UK then come and join our group. Free training and conversation with like minded people once a month and pizza too what could be better!!

Spinning up and shutting down Windows Azure Lab with Powershell

Posted on by

So at SQL Bits I went to Chris Testa-O’Neill’s session on certification. This has inspired me to start working on passing the MCSE exams. My PC at home doesn’t have much grunt and my tablet wont run SQL. I considered some new hardware but I knew I would have a hard time getting authorisation from the Home Financial Director (Mrs F2B) despite my all the amazing justification and benefits I could provide!!

So I looked at Windows Azure as a means of having some servers to play with. After watching this video and then this video I took the plunge and dived in.

After setting up my account I read a few blogs about Powershell and Windows Azure.

http://adminian.com/2013/04/16/how-to-setup-windows-azure-powershell/

Note – Here I only spin up extra small instances and don’t configure SQL as per Microsoft’s recommendations. I am only using these VMs for learning and talking at my user group your needs may be different

First you’ll need Microsoft Web Platform Installer. Then download and install Windows Azure PowerShell,

Import-Module c:\Program Files\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"

This gives you all the Windows Azure Powershell Cmdlets.

Get-AzurePublishSettingsFile which will give you a download for a file.  PowerShell will use this to control your Windows Azure so although you need it now, keep it safe and probably out of your usual directories so it doesn’t get compromised.

Import-AzurePublishSettingsFile and the file path to import it into Powershell.

Get-AzureSubscription to see the results and note the subscription name.

Now we create a storage account

New-AzureStorageAccount -StorageAccountName chooseaname -label 'a label' -Description 'The Storage Account for the Lab Spin Up and Down' -Location 'West Europe'

Get-AzureLocation will show you the available locations if you want a different one.I then set the storage account to be default for my subscription

Set-AzureSubscription -SubscriptionName 'Subscription Name from Earlier' -CurrentStorageAccount 'theoneyouchose'

I spent a couple of days sorting out the following scripts. They set up three SQL Servers, configure them to allow SSMS, Powershell and RDP connections and also remove them all. The reasoning behind this is that you will be charged for servers even when they are turned off

First we set some variables

$image = 'fb83b3509582419d99629ce476bcb5c8__Microsoft-SQL-Server-2012SP1-Standard-CY13SU04-SQL11-SP1-CU3-11.0.3350.0-B'
$SQL1 = 'SQL1'
$SQL2 = 'SQL2' 
$SQL3 = 'SQL3' 
$size = 'ExtraSmall' 
$AdminUser = 'ChoosePCAdminName' 
$password = 'SUPERCOMpl1c@teDPassword'
$Service = 'theservicenameyouchoose' 
$Location = 'West Europe'

To choose an image run Get-AzureVMImage|select name and pick the one for you. I chose a size of extra small as it is cheaper. As I won’t be pushing the servers very hard I don’t need any extra grunt. Set up a service the first time and use the location switch but then to use the same service again remove the location switch otherwise you will get an error stating DNS name already in use which is a little confusing until you know.

$vm = New-AzureVMConfig -Name $SQL1 -InstanceSize $size -ImageName $image | 
Add-AzureProvisioningConfig -AdminUsername $AdminUser -Password $password -Windows | 
Add-AzureEndpoint -Name "SQL" -Protocol "tcp" -PublicPort 57502 -LocalPort 1433| 
Add-AzureEndpoint -Name PS-HTTPS -Protocol TCP -LocalPort 5986 -PublicPort 5986

This creates a VM object and adds two endpoints for the server, one for Powershell and one for SSMS. When you provision more than one server you will need to make sure you use a different Public Port for each server otherwise you will get an error. You will need to note which server has which port when you need to connect with SSMS.

Once you have your VM object just pass it to New-AzureVM as shown

New-AzureVM -ServiceName $Service -VMs $vm

 

Providing you have no errors you can then just wait until you see this.

image_thumb

It will take a few minutes. Long enough to get a cuppa. Even then you won’t be able to connect straightaway as Azure will be provisioning the server still.

The next bit of the script downloads the RDP shortcut to a folder on the desktop and assigns a variable for the clean up script. I use this because the next time you spin up a server it may not use exactly the same port for RDP.

$SQL1RDP = "$ENV:userprofile\Desktop\Azure\RDP\$SQL1.rdp" 
Get-AzureRemoteDesktopFile -ServiceName $Service -name $SQL1 -LocalPath $SQL1RDP 
Invoke-Expression $SQL1RDP

The Invoke-Expression will open up a RDP connection but unless you have gone to get a cuppa I would check in your management portal before trying to connect as the server may still be provisioning. In fact,I would go to your Windows Azure Management Portal and check your virtual machine tab where you will see your VMs being provisioned

Now you have three servers but to be able to connect to them from your desktop and practice managing them you still need to do a bit of work. RDP to each server run the following script in Powershell.

# Configure PowerShell Execution Policy to Run all Scripts – It’s a one time Progress 
Set-ExecutionPolicy –ExecutionPolicy Unrestricted 
netsh advfirewall firewall add rule name=SQL-SSMS dir=in action=allow enable=yes profile=any 
netsh advfirewall firewall add rule name=SQL-SSMS dir=out action=allow program=any enable=yes profile=any 
netsh advfirewall firewall set rule group="Remote Administration" new enable=yes 
netsh advfirewall firewall set rule group="File and Printer Sharing" new enable=yes 
netsh advfirewall firewall set rule group="Remote Service Management" new enable=yes 
netsh advfirewall firewall set rule group="Performance Logs and Alerts" new enable=yes 
netsh advfirewall firewall set rule group="Remote Event Log Management" new enable=yes 
netsh advfirewall firewall set rule group="Remote Scheduled Tasks Management" new enable=yes 
netsh advfirewall firewall set rule group="Remote Volume Management" new enable=yes 
netsh advfirewall firewall set rule group="Remote Desktop" new enable=yes 
netsh advfirewall firewall set rule group="Windows Firewall Remote Management" new enable =yes 
netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable =yes

I use netsh advfirewall as I find it easy and I understand it. I know you can do it with Set-NetFirewallProfile but that’s the beauty of Powershell you can still use all your old cmd knowledge as well. This will allow you to remote manage the  servers. You can do it from your laptop with the creation of some more endpoints but I just use one server as a management server for my learning.

The last part of the script changes SQL to Mixed authentication mode and creates a SQL user with sysadmin and restarts the SQL service on each server and that’s it. Its ready to go.

Open up SSMS on your desktop and connect to “YourServiceName.Cloudapp.Net, PortNumber (57500-5702 in this example)

To remove all of the implementation run the code that is commented out in steps. First it assigns a variable to each VHD, then it removes the VM. You should then wait a while before removing the VHDs as it takes a few minutes to remove the lease and finally remove the RDP shortcuts as next time they will be different.

<# 
.NOTES 
    Name: CreateLab.ps1 
    Author: Rob Sewell http://sqldbawithabeard.com
    Requires: Get the Windows Azures CmdLets then run this
    Version History: 
                    Added New Header 23 August 2014
    
.SYNOPSIS 
	This script will create 3 Windows Azure SQL Servers and open up RDP connections
	ready for use. There is also the scripts to remove the Windows Azure Objects to save on
	usage costs
    
.DESCRIPTION 
    
.PARAMETER 
    
.PARAMETER 

.PARAMETER 

.EXAMPLE 
#> 


# Get the Subscription File and Import it
Get-AzurePublishSettingsFile 

Import-AzurePublishSettingsFile FilepathtoPublishSettingsFile

<# Run this once to set up a Storage Account
New-AzureStorageAccount -StorageAccountName storageaccountname -location 'West Europe' -Label 'Storage Account for My Lab'
#>

Get-AzureSubscription #Note the name

#Set the storage account to the subscription
Set-AzureSubscription -SubscriptionName SubscriptionName -CurrentStorageAccount storageaccountname


#Some variables

# Use Get-AzureVMimage to find the one you want ie Get-AzureVMImage | where { ($_.ImageName -like "*SQL*") }|select ImageName 

$image = 'fb83b3509582419d99629ce476bcb5c8__Microsoft-SQL-Server-2012SP1-Standard-CY13SU04-SQL11-SP1-CU3-11.0.3350.0-B'
$SQL1 = 'SQL1'
$SQL2 = 'SQL2'
$SQL3 = 'SQL3'
$size = 'ExtraSmall'
$AdminUser = 'ChoosePCAdminName'
$password = 'SUPERCOMpl1c@teDPassword'
$Service = 'theservicenameyouchoose'
$Location = 'West Europe'

<# Run this the first time to create a Service

New-AzureService -ServiceName $Service  -Location $Location -Label 'SQLDBA with a Beard Service' 

#>

#Configure the VMs

$vm = New-AzureVMConfig -Name $SQL1 -InstanceSize $size -ImageName $image |
  Add-AzureProvisioningConfig -AdminUsername $AdminUser -Password $password -Windows|
  Add-AzureEndpoint -Name "SQL" -Protocol "tcp" -PublicPort 57500 -LocalPort 1433 


$vm2 = New-AzureVMConfig -Name $SQL2 -InstanceSize $size -ImageName $image |
  Add-AzureProvisioningConfig -AdminUsername $AdminUser -Password $password -Windows |
    Add-AzureEndpoint -Name "SQL" -Protocol "tcp" -PublicPort 57501 -LocalPort 1433


$vm3 = New-AzureVMConfig -Name $SQL3 -InstanceSize $size -ImageName $image |
  Add-AzureProvisioningConfig -AdminUsername $AdminUser -Password $password -Windows |
    Add-AzureEndpoint -Name "SQL" -Protocol "tcp" -PublicPort 57502 -LocalPort 1433| 
    Add-AzureEndpoint -Name PS-HTTPS -Protocol TCP -LocalPort 5986 -PublicPort 5986

#Provision the VMs

New-AzureVM -ServiceName $Service -VMs $vm, $vm2,$vm3 

# Get the RDP Files

$SQL1RDP = "$ENV:userprofile\Desktop\Azure\RDP\$SQL1.rdp"
$SQL2RDP = "$ENV:userprofile\Desktop\Azure\RDP\$SQL2.rdp"
$SQL3RDP = "$ENV:userprofile\Desktop\Azure\RDP\$SQL3.rdp"

Get-AzureRemoteDesktopFile -ServiceName $Service -name $SQL1 -LocalPath $SQL1RDP
Get-AzureRemoteDesktopFile -ServiceName $Service -name $SQL2 -LocalPath $SQL2RDP 
Get-AzureRemoteDesktopFile -ServiceName $Service -name $SQL3 -LocalPath $SQL3RDP

# Open the RDP Fies - Check the machine is up in your Management Portal

Invoke-Expression $SQL1RDP
Invoke-Expression $SQL2RDP
Invoke-Expression $SQL3RDP

# Now run the SetupVM script for each server

<# 

This is the clean up script to remove the servers and services

Run this first

 $SQL1Disk = Get-AzureDisk|where {$_.attachedto.rolename -eq $SQL1}
 $SQL2Disk = Get-AzureDisk|where {$_.attachedto.rolename -eq $SQL2}
 $SQL3Disk = Get-AzureDisk|where {$_.attachedto.rolename -eq $SQL3}

#Then This

    Remove-AzureVM -Name $SQL1 -ServiceName $Service
    Remove-AzureVM -Name $SQL2 -ServiceName $Service
    Remove-AzureVM -Name $SQL3 -ServiceName $Service

Then wait a while and run this

 $SQL1Disk|Remove-AzureDisk -DeleteVHD
 $SQL2Disk|Remove-AzureDisk -DeleteVHD
 $SQL3Disk|Remove-AzureDisk -DeleteVHD

  #Remove-AzureService $Service

  Get-ChildItem "$ENV:userprofile\Desktop\Azure\RDP\*.rdp"|Remove-Item

  #>

  <# 

  This is the clean up script for variables

    
    Remove-Variable [a..z]* -Scope Global 
    Remove-Variable [1..9]* -Scope Global 

   #>

<# 
.NOTES 
    Name: SetUpVMSQL1.ps1 
    Author: Rob Sewell http://sqldbawithabeard.com
    Requires: 
    Version History: 
                    Added New Header 23 August 2014
    
.SYNOPSIS 
 	This script will set up the SQL1 VM ready for use and enable SQL Authentication
	Add a user called SQLAdmin with a password of P@ssw0rd
	Restart SQL Service.Run on SQL1
.DESCRIPTION 
    
.PARAMETER 
    
.PARAMETER 

.PARAMETER 

.EXAMPLE 
#> 

    # Configure PowerShell Execution Policy to Run all Scripts � It�s a one time Progress
    Set-ExecutionPolicy �ExecutionPolicy Unrestricted

netsh advfirewall firewall add rule name=SQL-SSMS dir=in action=allow enable=yes profile=any
netsh advfirewall firewall add rule name=SQL-SSMS dir=out action=allow program=any enable=yes profile=any
netsh advfirewall firewall set rule group="Remote Administration" new enable=yes
netsh advfirewall firewall set rule group="File and Printer Sharing" new enable=yes
netsh advfirewall firewall set rule group="Remote Service Management" new enable=yes
netsh advfirewall firewall set rule group="Performance Logs and Alerts" new enable=yes
Netsh advfirewall firewall set rule group="Remote Event Log Management" new enable=yes
Netsh advfirewall firewall set rule group="Remote Scheduled Tasks Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Volume Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Desktop" new enable=yes
netsh advfirewall firewall set rule group="Windows Firewall Remote Management" new enable =yes
netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable =yes

    # To Load SQL Server Management Objects into PowerShell
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMO�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMOExtended�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SqlWmiManagement�) | out-null

SQLPS

$Name = 'SQL1'

Invoke-Sqlcmd -ServerInstance $Name -Database master -Query "USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO
"


Invoke-Sqlcmd -ServerInstance $Name  -Database master -Query "USE [master]
GO
CREATE LOGIN [SQLAdmin] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [SQLAdmin]
GO

"
get-Service -ComputerName $Name  -Name MSSQLSERVER|Restart-Service -force





<# 
.NOTES 
    Name: SetUpVMSQL2.ps1 
    Author: Rob Sewell http://sqldbawithabeard.com
    Requires: 
    Version History: 
                    Added New Header 23 August 2014
    
.SYNOPSIS 
    
.DESCRIPTION 
    
.PARAMETER 
    
.PARAMETER 

.PARAMETER 

.EXAMPLE 
#> 
      #############################################################################################
#
# NAME: SetupVMSQL2.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:10/05/2013
#
#
# COMMENTS: This script will set up the SQL1 VM ready for use and enable SQL Authentication
# Add a user called SQLAdmin with a password of P@ssw0rd
# Restart SQL Service
# ------------------------------------------------------------------------

   
   # Run on SQL2

    # Configure PowerShell Execution Policy to Run all Scripts � It�s a one time Progress
    Set-ExecutionPolicy �ExecutionPolicy Unrestricted

netsh advfirewall firewall add rule name=SQL-SSMS dir=in action=allow enable=yes profile=any
netsh advfirewall firewall add rule name=SQL-SSMS dir=out action=allow program=any enable=yes profile=any
netsh advfirewall firewall set rule group="Remote Administration" new enable=yes
netsh advfirewall firewall set rule group="File and Printer Sharing" new enable=yes
netsh advfirewall firewall set rule group="Remote Service Management" new enable=yes
netsh advfirewall firewall set rule group="Performance Logs and Alerts" new enable=yes
Netsh advfirewall firewall set rule group="Remote Event Log Management" new enable=yes
Netsh advfirewall firewall set rule group="Remote Scheduled Tasks Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Volume Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Desktop" new enable=yes
netsh advfirewall firewall set rule group="Windows Firewall Remote Management" new enable =yes
netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable =yes

    # To Load SQL Server Management Objects into PowerShell
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMO�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMOExtended�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SqlWmiManagement�) | out-null

SQLPS

$Name = 'SQL2'

Invoke-Sqlcmd -ServerInstance $Name -Database master -Query "USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO
"


Invoke-Sqlcmd -ServerInstance $Name  -Database master -Query "USE [master]
GO
CREATE LOGIN [SQLAdmin] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [SQLAdmin]
GO

"
get-Service -ComputerName $Name  -Name MSSQLSERVER|Restart-Service -force





<# 
.NOTES 
    Name: SetUpVMSQL3.ps1 
    Author: Rob Sewell http://sqldbawithabeard.com
    Requires: 
    Version History: 
                    Added New Header 23 August 2014
    
.SYNOPSIS 
    
.DESCRIPTION 
    
.PARAMETER 
    
.PARAMETER 

.PARAMETER 

.EXAMPLE 
#> 
      #############################################################################################
#
# NAME: SetupVMSQL3.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:10/05/2013
#
#
# COMMENTS: This script will set up the SQL3 VM ready for use and enable SQL Authentication
# Add a user called SQLAdmin with a password of P@ssw0rd
# and enable PS Remoting
# Restart SQL Service
# ------------------------------------------------------------------------

   
   # Run on SQL3

    # Configure PowerShell Execution Policy to Run all Scripts � It�s a one time Progress
    Set-ExecutionPolicy �ExecutionPolicy Unrestricted



netsh advfirewall firewall add rule name=SQL-SSMS dir=in action=allow enable=yes profile=any
netsh advfirewall firewall add rule name=SQL-SSMS dir=out action=allow program=any enable=yes profile=any
netsh advfirewall firewall set rule group="Remote Administration" new enable=yes
netsh advfirewall firewall set rule group="File and Printer Sharing" new enable=yes
netsh advfirewall firewall set rule group="Remote Service Management" new enable=yes
netsh advfirewall firewall set rule group="Performance Logs and Alerts" new enable=yes
Netsh advfirewall firewall set rule group="Remote Event Log Management" new enable=yes
Netsh advfirewall firewall set rule group="Remote Scheduled Tasks Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Volume Management" new enable=yes
netsh advfirewall firewall set rule group="Remote Desktop" new enable=yes
netsh advfirewall firewall set rule group="Windows Firewall Remote Management" new enable =yes
netsh advfirewall firewall set rule group="windows management instrumentation (wmi)" new enable =yes

#Extra one for PS Remoting
netsh advfirewall firewall add rule name="Port 5986" dir=in action=allow protocol=TCP localport=5986

    # To Load SQL Server Management Objects into PowerShell
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMO�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SMOExtended�)  | out-null
    [System.Reflection.Assembly]::LoadWithPartialName(�Microsoft.SqlServer.SqlWmiManagement�) | out-null

SQLPS

$Name = 'SQL3'

Invoke-Sqlcmd -ServerInstance $Name -Database master -Query "USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO
"


Invoke-Sqlcmd -ServerInstance $Name  -Database master -Query "USE [master]
GO
CREATE LOGIN [SQLAdmin] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [SQLAdmin]
GO

"
get-Service -ComputerName $Name  -Name MSSQLSERVER|Restart-Service -force
Enable-PSRemoting -force

 

Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine

Documenting SQL Server the easy way with Power Doc

Posted on by

You know how it is. Question questions questions. As a DBA you are the fount of all knowledge. You are the protector of the data after all so obviously you know every little thing that is needed to be known.

Frequently, I am asked

How many processors does that server have?
How much RAM is on that server? What type?
What OS? Which Patches were installed

or more SQL based questions about configuration

Which SQL Product? Which version? Which Service Pack?
What are the linked servers on that server?
Or you want to know which login have which roles on the server or the autogrowth settings or any number of other ‘little things’

As the DBA as they are asking about my servers I should know and whilst I have a lot of info in my head, there’s not enough room for it all!! So I have to break from what I am doing and dive into Powershell or SSMS and get them the info that they need. When this happens I often thought I wish I could have this information to hand but I have never had time to organise it myself.

Worse still, imagine your boss walks through the door and says we have to provide information for an audit. Can you give me full details of all the SQL Servers and their configurations both windows and SQL and I need it by the end of play tomorrow.

It happens.

Its Personal Development Review time. I should have asked my boss to give me an objective of thoroughly documenting the SQL Server estate this year. I could have done it in a few hours. You only think of these things when its too late.

How can I do this? I hear you cry. Head over to https://sqlpowerdoc.codeplex.com and you will see.

SQL PowerDoc was written by Kendal VanDyke who is a practiced IT professional with over a decade of experience in SQL Server development and administration. Kendal is currently a principal consultant with UpSearch SQL, where he helps companies keep their SQL Servers running in high gear. Kendal is also a Microsoft MVP for SQL Server and president of the PASS chapter MagicPASS in Orlando, FL. You can find his blog at http://www.kendalvandyke.com/ and on Twitter at @SQLDBA

I found out about Power Doc a few weeks ago. It looked so cool, I tested it at home and then on my dev server and then on the whole estate. It is CPU heavy on the box it is running on if you have a load of servers. I don’t know how long it took to run as it ran overnight but the information you get back is staggering, enough to satisfy even the most inquisitive of auditors or questioners. You can pass it to your server team too and they will love it as it can do a Windows based inventory.

What does it document? What DOESNT it document? If you head over to https://sqlpowerdoc.codeplex.com/wikipage?title=What%27s%20Documented the list you see doesn’t reflect the sheer amount of data you can get back. Run it against your own machine and you will see what I mean.

As well as documenting everything it also runs around 100 checks to diagnose potential issues and problems. You can see where autogrowth is set to percentage, databases that haven’t been backed up, auto shrink, Max Memory set too high, the list goes on. Even the links to the MSDN articles are in there for the things it finds.

The documentation is thorough and even if you haven’t use Powershell before everything you need is on the website to run PowerDoc.

So much thought and effort has been put into this it’s difficult to see how it could be improved.

What I have done then is added the Excel file to our dba SharePoint team site and enabled the right people access to it. Equally they tell others and I get bothered slightly less.

Powershell won’t save when running as a scheduled job

Posted on by

Or, How SQLBits put me in touch with Laerte and solved a problem

I have a scheduled Powershell job which I use to create an Excel file colour coded for backup checks. (I will blog about it another time) It works brilliantly on my desktop and saves the file to a UNC path and emails the team the location. It works brilliantly when run in Powershell on the server. When I schedule it to run though it doesn’t do so well. The job completes without errors but no file is saved.

If you examine the processes running at the time you can see the excel process is running  so I knew it was doing something but couldn’t work out why it was failing.

It was one of those jobs that gets put to the bottom of the list because the service worked ok I just needed to have it running on the server rather than a desktop for resilience, recovery and security purposes. Every now and then I would try and work out what was going on but new work and new problems would always arrive and it has been like that for 6 or maybe even 9 months.

As you know I attended SQLBits this weekend and I went into a session with Laerte Junior. Laerte is a SQL Server MVP and can be found at simple-talk as well as his own blog http://shellyourexperience.com/ or on twitter @LaerteSQLDBA Oh and He loves Star Wars 🙂

Laerte_Junior[1]After a fascinating session I asked him if I could show him my problem. He very graciously said yes and after looking at the code and listening to me explain the problem he suggested this very simple solution which he said had taken him a great deal of searching to find. It’s a bug with COM objects and requires the creation of folders as shown below. I cam into work today, tried it and it worked. HOORAY another thing off my list and big thanks to Laerte

#Region Bug_Jobs_ComObjects
#(32Bit, always)
# Create Folder
#New-Item –name C:\Windows\System32\config\systemprofile\Desktop  –itemtype directory
#
#(64Bit)
# Create folder
#New-Item –name C:\Windows\SysWOW64\config\systemprofile\Desktop  –itemtype directory
#EndRegion Bug_Jobs_ComObjects

 

This worked for me however I had already implemented another fix for a possible gotcha so I will tell you of that one too

Sometimes Powershell cannot save to UNC paths because of  IE enhanced security.

Either log in as user and add server to intranet site zones or disable the warning in registry as follows

[HKEY_CURRENT_USER\Software\Policies\Microsoft\Windows\CurrentVersion\Internet Settings\ZoneMap] "UNCAsIntranet"=dword:00000000

 

Please don’t ever trust anything you read on the internet and certainly don’t implement it on production servers without first both understanding what it will do and testing it thoroughly. This solution worked for me in my environment I hope it is of use to you in yours but I know nothing about your environment and you know little about mine


12 Things I learnt at SQLBits XI

Posted on by

  • The Helpers are awesome

clip_image001

  • Often Sessions fill up very quickly – Get there early

clip_image002

  • You can learn as much outside of the sessions as you can in them

  • There are amazing prizes

A LEGO R2D2 !!!

  • Bring your sense of humour

  • The SQL community contains the most gracious and generous, willing to help people

  • You can connect with your user group and get a mini SQL Bits every month

Find your User Group Here http://sqlsouthwest.co.uk/national_ug.htm

  • If there is no user group in your area people will help you to start one

RT @fatherjack Interesting chat about a potential new user group in the uk. Anyone around Newcastle area looking for some free training?

  • Every session is videoed and will be available online. For free.

  • You will learn and have fun

Too awesome for words! “@justjonlevett: Lego Server! @fusionio #sqlbitspic.twitter.com/bhxPaTIq4K”

More blogs about SQL Bits XI and Photos

SQL Bits Facebook https://www.facebook.com/SQLBits

Roger Van Unen Gallery https://plus.google.com/photos/109984741094039234638/albums/5874913179986208577

JR’s Gallery https://skydrive.live.com/?cid=7b73b60f4c7d77c9&id=7B73B60F4C7D77C9%212222

Steve Jones Blog http://voiceofthedba.wordpress.com/2013/05/06/fun-at-sql-bits/

Chris Webbs Blog http://cwebbbi.wordpress.com/2013/05/05/sqlbits-xi-summary/

Find more from the Facebook Page or #sqlbits

Finally a BIG Thank you to all these people http://sqlbits.com/about/WhosWho.aspx

and the fantastic helpers without whom SQL Bits would never happen

Till Next year