Tag Archives: User group

Enterprise Strategies – A #TSQL2sDay post

Posted on by

This months TSQL2sDay blog post party is hosted by Jen McCown and is about Enterprise Strategy.

Adam Mechanic started TSQL Tuesdays over 5 years ago and you will find many brilliant posts under that heading if you search for them

Managing SQL servers at enterprise scale is not a straightforward task. Your aim as a DBA should be to simplify it as much as possible and to automate everything that you possibly can. This post by John Sansom could have been written for this months party and I recommend that you read it.

So here are a few points that I think you should consider if you look after SQL in an Enterprise environment.

  • Enterprise Strategy will undoubtedly garner a whole host of excellent posts and Jen will provide a round up post which will I am certain will be an excellent resource. Take a look here
  • Know where your instances are and have a single place that you can reference them from. Some people recommend a Central Management Server but I find this too restrictive for my needs. I use an InstanceList table in my DBA Database with the following columns [ServerName], [InstanceName] , [Port] , [AG] , [Inactive] , [Environment] and [Location]. This enables me to target instances not just by name but by environment (Dev, Test, Pre-Prod, Live etc), by location or by joining the InstanceList table with another table I can target by the application or any number of other factors. I also capture information about the servers at windows and SQL level to this database so I can target the SQL 2012 servers specifically if need be or any other metric. This is very powerful and enables far greater flexibility than the CMS in my opinion.
  • Use PowerShell (no surprise I would mention this!) PowerShell is a brilliant tool for automation and I use it all of the time
  • Get used to using this piece of Powershell code
	 $Query = @"
	 SELECT [ServerName],[InstanceName],[Port]
	  FROM [DBADatabase].[dbo].[InstanceList]
	  Where Inactive = 0 AND NotContactable = 0
	"@
	try{
	$AlltheServers= Invoke-Sqlcmd -ServerInstance $CentralDBAServer -Database $CentralDatabaseName -Query $query
	$ServerNames = $AlltheServers| Select ServerName,InstanceName,Port
	}
	foreach ($ServerName in $ServerNames)
	{
	## $ServerName
	 $InstanceName =  $ServerName|Select InstanceName -ExpandProperty InstanceName
	 $Port = $ServerName| Select Port -ExpandProperty Port
	$ServerName = $ServerName|Select ServerName -ExpandProperty ServerName 
	 $Connection = $ServerName + '\' + $InstanceName + ',' + $Port
	
	 try
	 {
	 $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Connection

Notice the query variable above, this is where the power lies as it enables you to gather all the instances that you need for your task as described in the bullet post above. Once you get used to doing this you can do things like this identify all the instances with Remote DAC disabled using a query against the DBA Database and then enable it on all servers by adding this code to the loop above

$srv.RemoteDacEnabled = $true
$srv.alter()

Very quick very simple and very very powerful. You can also use this to run TSQL scripts against the instances you target but there are some added complications with Invoke-SQLCmd that you need to be aware of

  • BE CAREFUL. Test and understand and test before you run any script on a live system especially using a script like this which enables you to target ALL of your servers. You must definitely check that your $ServerNames array contains only the instances you need before you make any changes. You need to be ultra-cautious when it is possible to do great damage
  • Write scripts that are robust and handle errors gracefully. I use Jason Wasser @wasserja Write-Log function to write to a text file and wrap my commands in a try catch block.
  • Include comments in your scripts to assist either the future you or the folks in your position in 5 years time. I would also add one of my bug bears – Use the description block in Agent Jobs. The first place any DBA is going to go to when that job fails is to open the properties of the job. Please fill in that block so that anyone troubleshooting knows some information about what the job does or at the very least a link to some documentation about it
  • Finally in my list, don’t overdo the alerts. Alerting is vital for any DBA it is a brilliant way to ensure that you quickly know about any issues affecting your estate but all alerts should be actionable and in some cases you can automate the action that you can take but the message here is don’t send messages to the DBA team email for every single tiny thing or they will get swamped and ignore the vital one. This holds for whichever alerting or monitoring system that you use

This is but a small sub-section of things that you need to consider when responsible for a large SQL estate but if you need help and advice or just moral support and you don’t already interact with the SQL community then make today the day you start. Maybe this post by Thomas La Rock is a good place to start or your nearest User Group/Chapter or the #sqlfamily hashtag or give me a shout and I will gladly help.

Speaking at PowerShell Virtual Chapter and SQL Cardiff User Group this month

Posted on by

Just a quick post to say that I will be speaking at the PowerShell Virtual Chapter meeting this Thursday at 4pm GMT 12pm EDT and also at the Cardiff SQL User Group on Tuesday 31st March

I will be giving my Making Powershell Useful for your Team presentation

You have heard about PowerShell and may be spent a little bit of time exploring some of the ways in which it will benefit you at work. You want to be able to perform tasks more specific to your organisation and need to share them with your team. I will show you how you can achieve this by demonstrating

  • An easy way to learn the syntax
  • How to explore SQL Server with Powershell
  • How to turn your one off scripts into shareable functions
  • How to ensure that your team can easily and quickly make use of and contribute to PowerShell solutions
  • Where else to go for help

You can find out more about the Virtual Chapter here

http://powershell.sqlpass.org/ 

and the Cardiff meeting here

http://www.meetup.com/Cardiff-SQL-Server-User-Group/events/219492623/ 

The Cardiff meeting has been named The Battle Of The Beards as it features Tobiasz Koprowski: talking about Windows Azure SQL Database – Tips and Tricks for beginners and Terry McCann with SSRS Inception. I will be giving the same presentation as at the Virtual Chapter

I hope to see you at one or both sessions

Powershell can read email & insert excel file attachment into a SQL Database

Posted on by

So at our SQL SouthWest User Group session last week we had sessions from Jonathan @fatherjack and Annette @MrsFatherjack on SSRS and SSIS respectively. During Annettes SSIS session a question was asked about reading email attachments and then loading them into a database. No-one had an answer using SSIS but I said it could be done with Powershell . So I have written the following script.

What it does is open an Outlook com object, search for an email with a certain subject and save it in the temp folder and then import it into a SQL database. This needs to be done on a machine with Outlook and Excel installed. It is possible to process the email using EWS in an Exchange environment and other people have written scripts to do so.

It uses two functions Out-Datatable from http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

and Write-Datatable from

http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae

The first takes the output from parsing the Excel File and converts it into a datatable object which can then be piped to the second which uses the BulkCopy method. Alternatively if you require it you could add each row of the excel file to an array and then use Invoke-SQLCmd to insert the data row by row.

while($row1 -le
$lastusedrange) {
$Col1 = $ws.Cells.Item($row1,1).Value2
$Col2 = $ws.Cells.Item($row1,2).Value2 
$Col3 = $ws.Cells.Item($row1,3).Value2
$query = "INSERT INTO Database.Schema.Table
          (Column1
          ,Column2
          ,Column3 )
          VALUES
          ('$Col1'
          ,'$Col2'
          ,'$Col3')
GO
"
$dt = invoke-sqlcmd -query $query -ServerInstance $Server -database $database
## For Testing Write-Host $query

Here is the script

#############################################################################################
#
# NAME: ExcelEmailAttachmentToDatabase.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:15/06/2013
#
# COMMENTS: This script will read your email using outlook com object and save Excel Attachment 
# and import it into a database
# REQUIRES: It uses two functions Out-Datatable from 
# http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd 
# and Write-Datatable from
# http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae 
#
# ------------------------------------------------------------------------
 
# Create Outlook Object
Add-type-assembly "Microsoft.Office.Interop.Outlook"|out-null
 
$olFolders = "Microsoft.Office.Interop.Outlook.olDefaultFolders" -as [type]
$outlook = new-object -comobject outlook.application
$namespace = $outlook.GetNameSpace("MAPI")
 
# Set Folder to Inbox
$folder = $namespace.getDefaultFolder($olFolders::olFolderInBox)
# CHeck Email For Subject and set to variable 
$Email = $folder.items |  Where-Object Subject -Contains $Subject
 
$Attachments = $Email.Attachments
$filepath = $env:TEMP
$filename = "TestFilename.xlsx"
$Subject = "This is a Test"
$server = 'test server'
$Database = 'Test Database'
$Table = 'tbl_DataloadTest'
 
foreach ($Attachment in $Attachments) {
    $attachName = $Attachment.filename
    If
    ($attachName.Contains("xlsx")) {
 
        $Attachment.saveasfile((Join-Path $filepath $filename)) 
    }  
}
 
# Create an Excel Object
 
$xl = New-Object -comobject Excel.Application
<# 
##For testing 
$xl.visible = $true
#>
# Open the File
$wb = $xl.WorkBooks.Open("$filepath\$filename")
$ws = $wb.Worksheets.Item(1)
 
# If your data does not start at A1 you may need
$column1 = 1
$row1 = 2
$lastusedrange = $ws.UsedRange.Rows.Count
 
$dt = @()
while ($row1 -le $lastusedrange) {
    $Col1 = $ws.Cells.Item($row1, 1).Value2
    $Col2 = $ws.Cells.Item($row1, 2).Value2
    $Col3 = $ws.Cells.Item($row1, 3).Value2
 
    $newrow = ($Col1, $col2, $col3)
    $dt += $newrow
 
    # Move to next row
    $row1 = $row1 + 1
}
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
 
$Input = $dt|Out-DataTable
Write-DataTable -ServerInstance $server -Database $Database -TableName $Table -Data $Input

 

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!!

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