Posts

SQL Server Audit 302 Deploying Audit Solution with Powershell

Auditing doesn’t have to be scary. SQL Server Audit 302 – Deploying Audit Solution with Powershell is part of a blog series designed to help you audit database changes with SQL Server Audit. Contact us if you have any questions about how to select and implement the right auditing solution for your organization.

SQL Server Audit 302 – Deploying Audit Solution with Powershell

SQL Server Audit 302 – Deploying Audit Solution with PowershellOriginally published on ColleenMorrow.com.

This is it, ladies and gentlemen. One last post and I promise I won’t mention the word audit for at least a week.

Before we begin, let’s take a moment to recap what we’ve covered so far:

For the final installment of this series, we’re going to take everything we’ve learned and put it all together.

Premise

In case you haven’t been paying attention, I really don’t like pointing and clicking my way through a GUI to do, well, just about anything. For any task that needs to be done more that once, I’d much rather spend the time upfront automating it, in order to save time later. Besides, automating tasks is fun! So it only makes sense that I’d want to use the same approach to auditing.

In SQLAudit 201, we created a SQLAudit database to act as our central audit repository. That’s where we’re storing the info on our current audits, and importing data from our audit files into a table for permanent storage and reporting.

The other thing we’re going to use that SQLAudit database for is to hold our “master” database audit specifications. These “master” specs will be used as templates to deploy auditing to other databases/instances. How, you ask? By using PowerShell, of course!

How it Works

I’ve split the work into 2 scripts: one to deploy an audit, one to remove an audit. Both can be executed interactively or in batch mode.

DeployAudit.ps1

Parameters

  • Target Instance – where to deploy audit to
  • Target Database – database to deploy audit to; null for a server audit
  • Target Login – Login with permissions to manage audits
  • Target Password
  • Audit Specification – name of the master audit specification to be deployed
  • UpdateAudit – y/n flag to overwrite an existing audit object if found
  • UpdateSpec – y/n flag to overwrite existing audit specification if found

If executed interactively, the script will prompt the user for target instance and database, login and password. It will connect to master instance and list available audit specs in the SQLAudit database and any server audit specs starting with “master”. The user is then asked to specify an audit to deploy. The script verifies that the user specified a valid master audit specification and determines whether the selected spec is databsse or server audit specification.

The script then connects to target instance, and determines if the target audit object exists. If it doesn’t, the audit object is created and a record is inserted into the SQLAudit.dbo.AuditLocator table. If the audit does exist, the user is prompted to overwrite definition. This is useful if you’ve modified the location or file options in the master audit object and you want to propagate those changes. If the path to the audit file has been changed, the AuditLocator table will be updated accordingly.

If a database audit specification was selected, the script will connect to the target database and check for a pre-existing spec. If one is found, the user is asked if the apec should be overwritten. If yes, the specification will be dropped and recreated. (There didn’t seem to be any sense to writing a separate function to update an audit specification.) Otherwise, the new specification is created. The same general logic is used for a server audit specification.

RemoveAudit.ps1

Parameters

  • Instance Name – name of the instance you want to stop auditing
  • Database Name – name of database being audited
  • Audit Specification Name – name of audit specification to remove.
  • Target Login
  • Target Password

If run interactively, the user is prompted for the target instance and database, login and password. The script connects to target instance and lists all existing server audit specs and any database audit specs in specified database. It then prompts the user for the audit spec to remove. The script verifies the selected spec name is valid, and, if so, deletes that specification. If that was the last audit specification associated with that audit object, the audit object is also dropped and the record in AuditLocator is updated to mark the audit as inactive.

Download the Scripts

I’ve packaged up my scripts into one handy-dandy zip file for your downloading pleasure.

SQLAuditScripts.zip contains:

  • SQLAuditDatabase.sql – Script to create the SQLAudit database and its objects.
  • Library-ManageAudits.ps1 – Library of PowerShell functions to drop, create, and update audit objects and audit specs.
  • DeployAudit.ps1 – Powershell script for deploying an audit.
  • RemoveAudit.ps1 – Powershell script for removing an existing audit.

Download - SQL Server Audit 302 Deploying the Audit Solution with Powershell
First Name*
Last Name*
Email*
Twitter

Additional Tools You’ll Need

I’ve mentioned these before, in my Inventory scripts. If you haven’t already, you’ll need to download a couple of PowerShell functions.

Name: Write-DataTable.ps1

Author: Chad Miller

Loads data into from a datatable object into SQL Server tables

Name: Out-DataTable.ps1

Author: Chad Miller

Formats input into a datatable object which can then be imported into SQL Server using Write-DataTable.

Sample Deployment

Let’s say we want to audit DDL (a common theme for me). Start by creating a Master_DDLAudit audit object on whatever instance holds the SQLAudit database. Use a file output, specifying a UNC path to our central audit folder. This is where all our audits will write to, so make sure it’s accessible by all your potential target instances. We’re not actually auditing with this master audit, so leave it disabled.

Next create an audit specification, Master_Database_DDLAudit, in the SQLAudit database. Specify the SCHEMA_OBJECT_CHANGE_GROUP action, and assign the spec to the Master_DDLAudit audit. Again, leave the specification disabled.

Suppose we also want to create a security audit to monitor changes to logins and server-level permissions. These are instance-level events, so we need to create a server audit specification, rather than a database audit spec. I don’t want this output going to the same file as my DDL audit, so I’ll create a new audit object, Master_SecurityAudit. Then I’ll create a server audit spec called Master_Server_SecurityAudit.

Now if I want to deploy the DDL audit to my AdventureWorks2012 database, I simply run the DeployAudit script.

DeployAudit

If I check my AuditLocator table, I see the audit has been added as an active audit.

AuditLocator

To stop auditing DDL on AdventureWorks, I use the RemoveAudit script.

RemoveAudit

Since this was the only audit specification attached to this audit, a select on my AuditLocator shows that the audit is now marked as inactive. The audit object has also been removed from the instance.

AuditLocator2

Conclusion

That concludes this series on SQL Audit. I hope you’ve enjoyed it as much as I have and I really hope you’ve found it helpful for your own auditing needs. Please don’t hesitate to contact me with any questions or problems you run into with the scripts.

SQL Server Audit Series

This blog series was designed to help you audit database changes.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization with SQL Server Audit.
  1. SQL Server Auditing – Getting Started
  2. SQL Server Audit 101 – Creating Basic Audit
  3. SQL Server Audit 102 – Reading Audit Output 
  4. SQL Server Audit 201 – Creating Audit Solution
  5. SQL Server Audit 301 – Using PowerShell to Manage Audits
  6. SQL Server Audit 302 – Deploying Audit Solution with PowerShell
UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQL Server Audit 301 Using Powershell to Manage Audits

Auditing doesn’t have to be scary. SQL Server Audit 301 – Using Powershell to Manage Audits is part of a blog series designed to help you audit database changes with SQL Server Audit. Contact us if you have any questions about how to select and implement the right auditing solution for your organization.

SQL Server Audit 301 – Using Powershell to Manage Audits

SQL Server Audit 301 – Using Powershell to Manage AuditsOriginally published on ColleenMorrow.com.

Today we’re going to go over some very basic scripts to create, drop, and copy SQL Audit objects using Powershell and SMO. Managing SQL Audit objects via PowerShell is actually pretty simple, even for a newbie like me. And I’m proud to say that these might be the first PowerShell scripts I’ve written that were entirely my own and not based on someone else’s work. I might actually be learning something! Nah, probably not. :-)

Creating an Audit Object

The first step in implementing SQL Audit is to create the audit object, so that’s where we’ll start. Let’s look at the whole script and then break it down.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instance = 'MyServer'
$auditName = $instance+"_TestAudit"
$auditDir = '\\MyServer\D$\Audits\'

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance

$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName")
$newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
$newAudit.FilePath = $auditDir
$newAudit.MaximumRolloverFiles = 10
$newAudit.MaximumFileSize = 100
$newAudit.QueueDelay = 1000
$newAudit.Create()
$newAudit.Enable()

The first thing we’re doing is simply declaring some variables to hold our instance name, the name of the audit we want to create, and the folder where we want our audit file to be written. For re-usability, we could even make these into parameters, but I wanted to keep this simple. Next we create a new SMO connection to our instance with the command

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance

Once we’re connected to SQL Server, we can create a new audit class object and start assigning attribute values. Here, we’re setting the destination to a file, and the file path to our $auditDir variable. We set the maximum number of rollover files, the queue delay, etc. All of the available properties can be found here: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.audit.aspx, but everything I’m setting here should look very familiar if you’ve been following along in this series.

$newAudit = new-object Microsoft.SqlServer.Management.Smo.Audit($srv, "$auditName")
$newAudit.DestinationType = [Microsoft.SqlServer.Management.Smo.AuditDestinationType]::File
$newAudit.FilePath = $auditDir
$newAudit.MaximumRolloverFiles = 10
$newAudit.MaximumFileSize = 100
$newAudit.QueueDelay = 1000

And once we’ve got all of our properties set, we do the PowerShell equivalent of clicking OK: we create the audit and enable it.

$newAudit.Create()
$newAudit.Enable()

Want to make sure our audit was created? We can list all of our audit objects like this:

$instance = 'MyServer'
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
foreach ($a in $srv.Audits)
{
   Write-Host $a.Name
}

Creating an Audit Specification

Once we’ve got our audit object created, we need to create the audit specification. For this example, we’ll create a database audit specification, rather than a server audit specification, though there are only slight differences between the two. In this particular audit spec, we’ll audit DDL changes to our AdventureWorks database and we’ll audit the execution of a stored procedure call usp_ChangeEmpSalary. As before, let’s see the whole script and break it down.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instance = 'MyServer'
$database = 'AdventureWorks'
$auditName = $instance+"_TestAudit"
$specName = $instance+"_"+$database+"_AuditSpec"

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item($database)

## Set audit spec properties
$AuditSpec = new-object Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification($db, $specName)
$AuditSpec.AuditName = "$auditName"

## Set audit actions
$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("SchemaObjectChangeGroup")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)

$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("EXECUTE","OBJECT","dbo","usp_ChangeEmpSalary","public")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)

## Create and enable audit spec
$AuditSpec.Create()
$AuditSpec.Enable()

Just like in the previous script we start out by defining our variables for instance, database, the audit we’re assigning this spec to, and the name of the new spec. Then we create our SMO objects for our server and database. Now we can get down to business. We start by creating a DatabaseAuditSpecification class object and setting its AuditName property to the name of the audit object we’re assigning this spec to.

## Set audit spec properties
$AuditSpec = new-object Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification($db, $specName)
$AuditSpec.AuditName = "$auditName"

Note that, if we wanted to create a server audit specification, we would use the ServerAuditSpecification class:

$AuditSpec = new-object Microsoft.SqlServer.Management.Smo.ServerAuditSpecification($srv, $specName)

Ok, so we’ve got our audit spec named and assigned, now we need to tell it what to audit. This is a two-step process in SMO, first we create an AuditSpecificationDetail object, and then we add it to the audit spec. I used these two actions to give you an idea of how to add an action group vs an individual action with more configuration options.

## Set audit actions
$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("SchemaObjectChangeGroup")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)

$SpecDetail = new-object Microsoft.SqlServer.Management.Smo.AuditSpecificationDetail("EXECUTE","OBJECT","dbo","usp_ChangeEmpSalary","public")
$AuditSpec.AddAuditSpecificationDetail($SpecDetail)

And finally, we create the audit specification and enable it.

## Create and enable audit spec
$AuditSpec.Create()
$AuditSpec.Enable()

Dropping Audits and Audit Specifications

Dropping an audit or audit spec is even easier than creating one. You find the one that matches the name you’re looking for, you disable it and drop it. In fact, it’s so straightforward that I’m not going to step through this one. If you’ve been following along so far, you’ll see what I’m doing here.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instance = 'MyServer'
$database = 'AdventureWorks'
$auditName = $instance+"_TestAudit"
$specName = $instance+"_"+$database+"_AuditSpec"

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item($database)

## Delete the audit spec
$auditSpec = $db.DatabaseAuditSpecifications | where {$_.Name -match $specName}

if ( $auditSpec.Name -eq $null )
{
	Write-Host "Database Audit Spec $specName does not exist."
}
else
{
	$auditSpec.Disable()
	$auditSpec.Drop()
	Write-Host "Database Audit Spec $specName dropped."
}

## Delete the audit object
$delAudit = $srv.Audits | where {$_.Name -match $auditName}

if ( $delAudit.Name -eq $null )
{
	Write-Host "Audit $auditName does not exist."
}
else
{
	$delAudit.Disable()
	$delAudit.Drop()
	Write-Host "Audit $auditName dropped."
}

Copying an Audit Specification

The last activity I want to cover today is copying an audit specification from one database to another. This way we can define a “master” audit spec to use as a template for deployment to other databases/instances. Let’s imagine I have a database called SQLAudit where I’ve created such a template. And I want to copy that audit spec definition to AdventureWorks.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$instance = 'MyServer'
$masterDB = 'SQLAudit'
$masterSpec = "Master_DB_AuditSpec"

$targetDB = 'AdventureWorks'
$targetSpec = $instance+"_"+$database+"_AuditSpec"

$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instance
$mdb = New-Object Microsoft.SqlServer.Management.Smo.Database
$mdb = $srv.Databases.Item($masterDB)

$mAuditSpec = $mdb.DatabaseAuditSpecifications | where {$_.Name -match $masterSpec}

if ( $mauditSpec.Name -eq $null )
{
	Write-Host "Master Database Audit Spec $masterSpec does not exist."
}
else
{

	$tdb = New-Object Microsoft.SqlServer.Management.Smo.Database
	$tdb = $srv.Databases.Item($targetDB)

	$newAuditSpec = new-object Microsoft.SqlServer.Management.Smo.DatabaseAuditSpecification($tdb, $targetSpec)
	$newAuditSpec.AuditName = $mauditSpec.AuditName
	$newAuditSpec.AddAuditSpecificationDetail($mauditSpec.EnumAuditSpecificationDetails())
	$newAuditSpec.Create()
	$newAuditSpec.Enable()

	Write-Host "Database Audit Spec $targetSpec created."

}

The only somewhat tricky part of copying an audit spec definition is that you can’t directly copy the actions. You need to use the EnumAuditSpecificationDetails function to assign the details from the original specification to the new one. Everything else is easy.

What’s Next?

Now that we’ve got the basics of managing audits using PowerShell under our belts, we’re going to use these skills to deploy the audit solution I talked about previously. That’s what we’ll do next in SQL Server Audit 302 – Deploying Audit Solution with PowerShell. Good stuff people!!

SQL Server Audit Series

This blog series was designed to help you audit database changes.  Contact us if you have any questions about how to select and implement the right auditing solution for your organization with SQL Server Audit.
  1. SQL Server Auditing – Getting Started
  2. SQL Server Audit 101 – Creating Basic Audit
  3. SQL Server Audit 102 – Reading Audit Output 
  4. SQL Server Audit 201 – Creating Audit Solution
  5. SQL Server Audit 301 – Using PowerShell to Manage Audits
  6. SQL Server Audit 302 – Deploying Audit Solution with PowerShell
UpSearch

About the Author

SQL Server Consultant

Colleen Morrow

UpSearch Alum Colleen Morrow is a database strategist, community advocate, author, blogger and public speaker. She is passionate about helping technology leaders use Microsoft's SQL Server to protect, optimize and unlock data's value.

Colleen has been working with relational databases for almost 20 years. Since 2000, Colleen has specialized in SQL Server and Oracle database management solutions. She excels at performance tuning, troubleshooting mission critical SQL Server environments, and training technology professionals and business users of all levels.

Since 2011, Colleen has maintained a SQL Server focused blog at http://colleenmorrow.com. She is an active member of the Ohio North SQL Server User Group, as well as a volunteer for the Professional Association for SQL Server (PASS). Colleen earned a Bachelor of Science in Computer and Information Systems from Cleveland State University.

Learn more about Colleen Morrow at https://upsearch.com/colleen-morrow/.

About UpSearch

up-social-round

UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

SQLSaturday #315 – Pittsburgh 2014

SQL Server Team - blog - August 2014

UpSearch at SQLSaturday #315

UpSearch will proudly sponsor PASS SQLSaturday #315 – Pittsburgh 2014. The event will be held October 4, 2014 at the Pittsburgh Technical Institute, 1111 McKee Road, Oakdale, PA 15071. UpSearch’s sponsor raffle giveaway will be a FREE SQL Server Health Check, a $2,000 value.

Sponsor Raffle Winner

Sponsor Raffle Will Be Saturday, October 4, 2014

Presentations

The UpSearch team will also support SQLSaturday #315- Pittsburgh 2014 by making two presentations:

Brian Davis will present on how Hekaton can help you.

gettingstartedwithhekaton

Allen White will present on how to use PowerShell to efficiently manage SQL Server.

Special Mentions

We appreciate the Pittsburgh SQL Server User Group’s team effort to host SQLSaturday #315 – Pittsburgh 2014. If you live in the Greater Pittsburgh, PA area, consider getting involved with PSSUG.

About PASS SQLSaturday

up-social-round

The PASS SQLSaturday program provides the tools and knowledge needed for groups and event leaders to organize and host a free day of training for SQL Server professionals. 

At the local event level, SQLSaturday events:

  • Encourage increased membership for the local user group
  • Provide local SQL Server professionals with excellent training and networking opportunities
  • Help develop, grow, and encourage new speakers

To learn more about PASS SQL Saturday visit http://www.sqlsaturday.com/about.aspx.

About UpSearch

up-social-round

UpSearch provides as needed Microsoft SQL Server DBA Services across the globe. We specialize in helping leaders protect, unlock and optimize data’s value.

To learn more about UpSearch visit https://upsearch.com.

 

SQLSaturday #320 – Raleigh 2014

SQL Server Team - blog - August 2014

UpSearch at SQLSaturday #320

UpSearch proudly sponsored PASS SQLSaturday #320 – Raleigh 2014. The event was held September 6, 2014 at the Wake Tech Northern Campus, 6600 Louisburg Road, Raleigh, NC 27616. UpSearch’s sponsor raffle giveaway was a FREE SQL Server Health Check, a $2,000 value.

Sponsor Raffle Winner

Congratulations Geetha Namballa and Fujitsu America, Inc.

Geetha Namballa won UpSearch’s sponsor raffle. Her organization receives a FREE SQL Server Health Check.

Presentations

The UpSearch team (pictured left to right: Allen White and Brian Davis) also supported SQLSaturday #320 – Raleigh 2014 by making four presentations:

SQL Server Team Allen White and Brian Davis

Allen White presented on how to use PowerShell to efficiently manage SQL Server.

Allen White presented on how to use Server Broker.

Brian Davis presented on how to use SSIS Templates.

Brian Davis presented on how to automate SQL Server installs.

Goodbye Next, Next, Next...Hello Automated Installs!

Special Mentions

We appreciate the Triangle SQL Server User Group’s (triPASS) team effort to host SQLSaturday #320– Raleigh 2014. If you live near Raleigh, N.C. consider getting involved in triPASS and find them on Twitter at .

And to all of our SQL Server BFFs, like Paul Turley and Randy Knight, thank you for making SQLSaturdays so much fun!

 

About PASS SQLSaturday

up-social-round

The PASS SQLSaturday program provides the tools and knowledge needed for groups and event leaders to organize and host a free day of training for SQL Server professionals. 

At the local event level, SQLSaturday events:

  • Encourage increased membership for the local user group
  • Provide local SQL Server professionals with excellent training and networking opportunities
  • Help develop, grow, and encourage new speakers

To learn more about PASS SQL Saturday visit http://www.sqlsaturday.com/about.aspx.

About UpSearch

up-social-round UpSearch provides as needed Microsoft SQL Server DBA Services across the globe. We specialize in helping leaders protect, unlock and optimize data’s value.

To learn more about UpSearch visit https://upsearch.com.

 

Use PowerShell Remoting to Manage SQL Servers Efficiently

Allen White’s April 2014 article in SQL Server Pro magazine introduces PowerShell Remoting as a lightweight way to manage all of your servers at the same time.

Continue Reading on SQLMag.com >>

 

About the Author

Microsoft SQL Server MVP and Practice Leader

Allen White

Allen White is a Microsoft SQL Server MVP and Practice Leader at UpSearch.

For over 30 years, Allen has specialized in developing applications that manage the movement of data and maximize data’s usefulness. Allen excels at communicating highly technical information using language that results in increased client engagement and understanding, regardless of technical competency.

Allen has been working with relational database systems for over 20 years. He has architected database solutions in application areas like retail point-of-sale (POS), POS audit, loss prevention, logistics, school district information management, purchasing and asset inventory and runtime analytics. Allen thrives on providing comprehensive solutions to information management problems across a great variety of application environments.

Learn more about Allen White at  https://upsearch.com/allen-white.

 

About UpSearch

up-social-round

UpSearch provides as needed Microsoft SQL Server DBA Services across the globe. We specialize in helping leaders protect, unlock and optimize data’s value.

 

Automate Your ETL Infrastructure with SSIS and PowerShell

 

About the Presentation

 Much of your ETL process flow consists of packages that are very similar in structure, capturing data from a single source and transferring that to a single destination. Creating the individual packages can be tedious and it’s easy to miss something in the process of generating the same basic package over and again. BI Markup Language makes it easy to build new packages, and PowerShell makes creating the BIML scripts easy. In this session we’ll show you how to use PowerShell to generate dozens of SSIS packages doing similar tasks from a defined set of ETL sources.

Session Level: Intermediate

Presentations (Upcoming & Past)

About the Author

Microsoft SQL Server MVP and Practice Leader

Allen White

Allen White is a Microsoft SQL Server MVP and Practice Leader at UpSearch.

For over 30 years, Allen has specialized in developing applications that manage the movement of data and maximize data’s usefulness. Allen excels at communicating highly technical information using language that results in increased client engagement and understanding, regardless of technical competency.

Allen has been working with relational database systems for almost 20 years. He has architected database solutions in application areas like retail point-of-sale (POS), POS audit, loss prevention, logistics, school district information management, purchasing and asset inventory and runtime analytics. Allen thrives on providing comprehensive solutions to information management problems across a great variety of application environments.

Learn more about Allen White at  https://upsearch.com/allen-white.  

About UpSearch

up-social-round       UpSearch provides as needed Microsoft SQL Server DBA Services across the globe. We specialize in helping leaders protect, unlock and optimize data’s value.

SQLSaturday #303 – Rochester 2014

SQL Server Team - blog - August 2014

UpSearch at SQLSaturday #303

UpSearch proudly sponsored PASS SQLSaturday #303 – Rochester 2014. The event was held June 21, 2014 at the Rochester Institute of Technology (CIMS Building #78), Rochester, NY 14623.  UpSearch’s Allen White and Kendal Van Dyke volunteered to make four presentations.

Sponsor Raffle Winner

Congratulations Muhammad Nezami and Paychex!

Muhammad Nezami won UpSearch’s sponsor raffle. His organization, Paychex, receives a FREE SQL Server Health Check.

Presentations

The UpSearch team (pictured left to right: Allen White and Kendal Van Dyke) also support SQLSaturday #303 – Rochester 2014 by volunteering to make four presentations:

 

SQLSaturday #303 Rochester - June 21 2014 2

Kendal Van Dyke – Automate Your SQL Server Install With Desired State Configuration

Abstract: Desired State Configuration, a new feature of PowerShell version 4, finally delivers on an experience that’s been missing from SQL Server for years: An end-to-end, fully automated installation. From a single standalone instance to a multi-replica AlwaysOn Available Group and everything in between, Desired State Configuration (DSC) is up to the task. In this session you’ll learn how Desired State Configuration works and see first-hand how to leverage it to perform repeatable, error-free SQL Server installations. You’ll return to work never wanting to click Next, Next, Next through an installer wizard again!

Session Level: Intermediate

Kendal Van Dyke – Inspector Insert And The Case Of The Mistaken IDENTITY

Abstract: Think you know everything about working with identity values? Think again – they’re not as straightforward as they seem! Follow along as Inspector Insert discovers the many ways to end up with a case of mistaken identity and learn how to make sure you’re working with the right identity values every time. Attendees will: – Understand how identity columns work – Understand all the ways to return identity values and what factors influence their accuracy. – Learn how to always be sure they’re working with the right identity values in TSQL.

Session Level: Beginner

Allen White – Automate Your ETL Infrastructure with SSIS and PowerShell

Abstract: Much of your ETL process flow consists of packages that are very similar in structure, capturing data from a single source and transferring that to a single destination. Creating the individual packages can be tedious and it’s easy to miss something in the process of generating the same basic package over and again. BI Markup Language makes it easy to build new packages, and PowerShell makes creating the BIML scripts easy. In this session we’ll show you how to use PowerShell to generate dozens of SSIS packages doing similar tasks from a defined set of ETL sources.

Session Level: Intermediate

Allen White – Manage SQLServer Efficiently w/PowerShell Remoting

Abstract: You have more and more servers to manage and less time to accomplish everything. You’re writing scripts to automate those tasks but they still take time to run. PowerShell remoting allows you to manage servers without the overhead of Remote Desktop, and allows you to run processes on all your servers simultaneously. In this session we’ll walk through how PowerShell remoting works, how to set it up, and how you can save time getting things done more quickly.

Session Level: Intermediate

Special Mentions

We appreciate the Rochester SQL Server User Group’s team effort to host SQLSaturday #303 – Rochester 2014. If you live in the Greater Rochester, NY area, consider getting involved with Rochester PASS.

Thanks to Andy Levy () for sharing this picture of the Friday night speaker’s dinner.

SQLSaturday #303 – Rochester 2014 – speaker dinner

About PASS SQLSaturday

up-social-round

The PASS SQLSaturday program provides the tools and knowledge needed for groups and event leaders to organize and host a free day of training for SQL Server professionals. 

To learn more about PASS SQL Saturday visit http://www.sqlsaturday.com/about.aspx.

About UpSearch

up-social-round

UpSearch provides as needed Microsoft SQL Server DBA Services across the globe. We specialize in helping leaders protect, unlock and optimize data’s value.

To learn more about UpSearch visit https://upsearch.com.

 

SQLSaturday #299 – Columbus 2014

SQL Server Team - blog - August 2014

UpSearch at SQLSaturday #299

UpSearch proudly sponsored PASS SQLSaturday #299 – Columbus 2014.  The event was held Jun 14, 2014 at the Bishop James A. Griffin Student Center (Bldg 17) on the beautiful Ohio Dominican University campus located at 1215 Sunbury Road, Columbus, OH 43219. UpSearch’s Allen White and Brian Davis volunteered to make four presentations.

Sponsor Raffle Winner

Congratulations Scott Thomas and Fiserv!

Scott Thomas won UpSearch’s sponsor raffle. His organization, Fiserv, receives a FREE SQL Server Health Check.

Presentations

The UpSearch team (pictured left to right: Brian Davis and Allen White) also supported SQLSaturday #299 – Columbus 2014 by volunteering to make four presentations:

SQLSaturday #299 Columbus - June 14 2014

Brian Davis presented Getting Started with Hekaton and how Hekaton can help you.

gettingstartedwithhekaton

Brian Davis made a presentation on how to use SSIS Templates.

by Brian Davis

Allen White presented how to keep your data warehouse updated using SQL Server Service Broker messages from your OLTP database.

Allen White made a presentation on how to use PowerShell to efficiently manage SQL Server.

Special Mentions

We appreciate the Columbus SQL Server User Group’s team effort to host SQLSaturday #299 – Columbus 2014. If you live in the Greater Columbus, Ohio area, consider getting involved with Columbus PASS (CBusPASS).

And finally, a special thank you to Chris Bell of WaterOx Consulting for making a SQLSaturday #299 – Columbus 2014 quick review.

About PASS SQLSaturday

up-social-round

The PASS SQLSaturday program provides the tools and knowledge needed for groups and event leaders to organize and host a free day of training for SQL Server professionals. 

To learn more about PASS SQL Saturday visit http://www.sqlsaturday.com/about.aspx.

About UpSearch

up-social-round

UpSearch provides as needed Microsoft SQL Server DBA Services across the globe. We specialize in helping leaders protect, unlock and optimize data’s value.

To learn more about UpSearch visit https://upsearch.com.