Setting up SQL Server on Azure for testing

I recently had the need for testing out a setup program which installs database components, integration services packages and reporting services reports. Setting up a machine like this would be really quick if you have Hyper-V installed and a VHD already pre-created with a SQL Server image. What if you do not have that handy and need to carry out your testing. This is what Microsoft Azure Virtual Machines comes to the rescue.

I used my Azure subscription to create a virtual machine for my testing. In this blog post, I will walk you through the steps for setting up a SQL Server virtual machine for testing purposes!

Continue reading

A little bit of POSH for document conversion

This is a completely non-SQL Server post. I had to recently convert a large number of word documents into Web Archive (.MHT) format. I would not mind doing that for one or two documents but when I have over 50 documents to perform this exercise on, it could get cumbersome and monotonous! This is where PowerShell came to the rescue.

The most common example available on the web is to convert Word documents to PDF. What I needed for the work that I was doing was a way to convert the Word documents to the Web Archive format. After a few Bing searches, I was able to determine that the Web Archive format enumeration number was 9. The following link has information about all the enumeration values: http://msdn.microsoft.com/en-us/library/office/bb238158(v=office.12).aspx

The PowerShell script below allows you to traverse a folder, pick all the word documents in the folders recursively and then convert each of those word documents in a .MHT file with the same name in the same location.

The script can be downloaded from OneDrive also.


<#

#################################################################################
    
Script Name: ConvertToWord                        
    Author: Amit Banerjee                            
    Date: April 28, 2014                            
    
Description:                                 
    This script takes a folder as an input and then converts the docx files present in the folder to web archive documents        
#################################################################################

This Sample Code is provided for the purpose of illustration only and is not 
intended to be used in a production environment. THIS SAMPLE CODE AND ANY 
RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER 
EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF 
MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a 
nonexclusive, royalty-free right to use and modify the Sample Code and to 
reproduce and distribute the object code form of the Sample Code, provided that 
You agree: (i) to not use Our name, logo, or trademarks to market Your software 
product in which the Sample Code is embedded; (ii) to include a valid copyright 
notice on Your software product in which the Sample Code is embedded; and (iii) 
to indemnify, hold harmless, and defend Us and Our suppliers from and against 
any claims or lawsuits, including attorneys fees, that arise or result from the 
use or distribution of the Sample Code.

    The enumeration for the various document types that you can save as from Microsoft Word when you use the SaveAs option
    Const wdFormatDocument                    =  0
    Const wdFormatDocument97                  =  0
    Const wdFormatDocumentDefault             = 16
    Const wdFormatDOSText                     =  4
    Const wdFormatDOSTextLineBreaks           =  5
    Const wdFormatEncodedText                 =  7
    Const wdFormatFilteredHTML                = 10
    Const wdFormatFlatXML                     = 19
    Const wdFormatFlatXMLMacroEnabled         = 20
    Const wdFormatFlatXMLTemplate             = 21
    Const wdFormatFlatXMLTemplateMacroEnabled = 22
    Const wdFormatHTML                        =  8
    Const wdFormatPDF                         = 17
    Const wdFormatRTF                         =  6
    Const wdFormatTemplate                    =  1
    Const wdFormatTemplate97                  =  1
    Const wdFormatText                        =  2
    Const wdFormatTextLineBreaks              =  3
    Const wdFormatUnicodeText                 =  7
    Const wdFormatWebArchive                  =  9
    Const wdFormatXML                         = 11
    Const wdFormatXMLDocument                 = 12
    Const wdFormatXMLDocumentMacroEnabled     = 13
    Const wdFormatXMLTemplate                 = 14
    Const wdFormatXMLTemplateMacroEnabled     = 15
    Const wdFormatXPS                         = 18

#>

# Replace with the correct folder path
# Remove the -recurse option if you only want to convert the documents in the first level folders
# Retrieve the list of documents
$Files = Get-ChildItem "C:\Windows\*.docx" -recurse 

foreach ($File in $Files)
{
    # Create the name of the new document
    $Name = $File.FullName.replace(“docx”,”mht”)
    
    if (Test-Path $Name)
    {
        # Check if the file already exists
        # If it does then do not do anything
        Write-Host "Skipping conversion for " $Name  
    }
    else 
    {
        # Save the file as a web archive if it does not exist
        Write-Host "Creating file " $Name
        ConvertToMHT $File.FullName
    }
}

# Function to convert the file
function ConvertToMHT ($FileName)
{        
    # Create a word document object
    $Word=NEW-OBJECT –COMOBJECT WORD.APPLICATION
    # Open the word document
    $Doc=$Word.Documents.Open($FileName)
            
    # Replace with appropriate document format type using the enumeration provided above in the comments
    # Save the document in the required format in the same location
    [ref]$SaveFormat = "System.Object" -as [type]
    $Doc.saveas([ref] (($FileName).replace(“docx”,”mht”)),  [ref]9)
    # Quit word after closing the document
    $Doc.close()
    $Word.Application.Quit()
}

Reference:
http://blogs.technet.com/b/heyscriptingguy/archive/2013/03/24/weekend-scripter-convert-word-documents-to-pdf-files-with-powershell.aspx

Powershell for the perfmon files

Recently, I was working on a requirement to convert a number of BLG files into CSV and then changing the CSV files into Excel files.

So, the script below does the following:

1. Picks the BLG files from a folder and then creates a CSV file from the BLG file using RELOG.EXE

2. Then the same CSV file is converted to a XLS file

As always, this is a Powershell script which can be adapted in any way possible. Happy perfmon analysis!

The script can be downloaded from OneDrive as well.


#################################################################################
#                                        #
#                                        #
#                                        #
#                                        #
#    Script Name: Relog                            #
#    Author: Amit Banerjee                            #
#    Date: May 10, 2014                            #
#    Description:                                 #
#    The script uses relog to create CSV files from BLG files         #
#    It then converts the CSV files to XLS files                 #
#                                        #
#                                        #
#                                        #
#                                        #
#                                        #
#################################################################################

# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys fees, that arise or result from the use or distribution of the Sample Code.

# Enumerates BLG files on disk and starts running relog on them
$path = "C:\PerfmonFiles\" # Replace with correct path
$files = Get-ChildItem $path -Filter *.blg
foreach ($file in $files)
{
    # Create the CSV filename
    $filename = $file.FullName.Split(".")[0] + ".csv"
    # Run relog with the correct arguments
    $AllArgs =  @($file.FullName,  '-f', 'csv', '-o',  $filename)
    & 'relog.exe' $AllArgs
}

# Enumerates CSV files on disk and starts converting them to Excel
$files = Get-ChildItem $path -Filter *.csv 
foreach ($file in $files)
{
    # Launch Excel
    $xls = new-object -comobject excel.application
    $xls.visible = $true
    # Open the CSV file
    $Workbook = $xls.workbooks.open($file.FullName)
    $Worksheets = $Workbooks.worksheets
    $filename = $file.FullName.Split(".")[0] + ".xls"
    # Save the file as Excel
    # Depending on your compatibility settings you might have to accept a prompt to save the file
    $Workbook.SaveAs($filename,1)
    $Workbook.Saved = $True
    # Close Excel
    $xls.Quit()
    # Delete the CSV file
    Remove-Item $file.FullName
}

A full house–We could not have asked for more

The turnout at today’s event had an overwhelming response today. A big thank you to everyone who attended today’s event. An overwhelming response… So thank you everyone who attended and made this UG meeting a grand success! This was our very own version of introducing SQL Server 2014 to the SQLBangaloreUG community!

We had a brilliant line-up of topics… Just to re-cap the day.. Here is a short summary..

The day started off with a keynote session from Anupam Tiwari who is Business Program Manager and closely works with the team that supports developer technologies at Microsoft. He talked about where IT as we know it today is headed tomorrow.

Sourabh Agarwal [Blog | Twitter] covered the performance enhancements in SQL Server 2014 and explained why this version is bigger, faster and stronger! Smile

Then I had the opportunity of explaining the new enhancements in backup and restore in SQL Server 2014. I talked about backing up to a URL i.e. a storage account in Azure, managed backups to Azure and encrypted backups. The slide deck from today’s presentation is available below.

This was followed by Pinal Dave’s [Blog | Twitter] session on “Performing an effective Presentation” One key takeaway from this session is that the art of story-telling can weave out a presentation which can keep the audience hooked! He demonstrated this during his session and kept the audience awake after lunch! Smile

Then we had Balmukund Lakhani [Blog | Twitter] talking about InMemory Enhancements with SQL Server 2014. You can follow his SQL Server 2014 learning series to gain in-depth insights into the various enhancements that he talked about.

His session was followed by Vinod Kumar [Blog | Twitter] who talked about the Some more lesser known enhancements with SQL Server 2014 where he talked about managed lock priority, delayed durability and more.

And last but not the least was Kane Conway – (Support Escalation Engineer at Microsoft) who talked about Power Packed – Power BI with SQL Server. If you have not played around with PowerBI, then you should start now! Presenting data with compelling visualizations just got a new definition.

image image

It was a house full event and I had to put in two pictures to showcase the turnout today! It is not possible to have a successful event without the audience participation. We personally want to apologize to folks who came in late and had to return because the hall was overcrowded and the seats were full. Please do come in early for next UGs for sure.

A special thanks to Microsoft GTSC for giving us the space and the support till date. Also a special thanks to SQL Server Product Marketing team for sponsoring this Launch Event. Finally, a special thanks to team Pluralsight for supporting the UG till date and giving us numerous gifts as giveaway at the event.

The presentation that I used at the event today is available below:

Bangalore Community gets a peek at SQL Server 2014

SQL Server 2014 which was recently released will now receive special attention at the SQL Bangalore UG Meet scheduled to happen on Saturday, May 3rd, 2014. We are  conducting a full-day event for a SQL Server 2014 Community Launch at the SQL Bangalore UG. So don’t miss the opportunity to come and learn some of the new cool features introduced with SQL Server 2014.

I will be presenting on Backup Enhancements in SQL Server 2014 and will show you some exciting new features. This will get you thinking about your backup strategy when you upgrade to SQL Server 2014.

Just to tickle your learning taste buds, the agenda for the day is mentioned below:

Backup Enhancements with SQL Server 2014
Performance Enhancements with SQL Server 2014
Sourabh Agarwal [Blog | Twitter] – An active member on SQLBangalore community!

LUNCH BREAK

Performing an effective Presentation by Pinal Dave [Blog | Twitter] – He does not require an introduction!
InMemory Enhancements with SQL Server 2014 by Balmukund Lakhani [Blog | Twitter] – One of the most helpful people in the SQL community!
Some more lesser known enhancements with SQL Server 2014 by Vinod Kumar [Blog | Twitter] – Another individual who needs no introduction!
Power Packed – Power BI with SQL Server by Kane Conway – (Support Escalation Engineer at Microsoft) – A technical expert who will explain to about what PowerBI can do for you and your organization!

Register for the event (registration link) and feel free to pass along the message. The day will have an early start… 9.30AM!! Get your learning hats on and come visit us on Saturday!

Venue:
Microsoft Corporation, Signature Building,
Embassy Golf Links Business Park,
Intermediate Ring Road,
Domlur, Bangalore – 560071