Preparation for the 70-467 SQL BI exam

Microsoft does not provide any preparation materials for the 70-467 exam [Designing Business Intelligence Solutions with Microsoft SQL Server]. There are Microsoft Virtual Academy videos here or here, which are nice to watch but definitelly not enough to be able to answer any particular question. Microsoft does though provide a list of topics here 70-467, so, while preparing I was collecting related links.

Plan business intelligence (BI) infrastructure (15–20%)
Plan for performance
• Optimize batch procedures: extract, transform, load (ETL) in SSIS/SQL; Link
• Optimize processing phase in Analysis Services, Partitions, Lazy Aggregation; Link, Link1, Link2, Link3
• Configure Proactive Caching within SQL Server Analysis Services (SSAS) for different scenarios; Link, Link1
• Understand performance consequences of named queries in a data source view; Link
• SSAS Performance Counters; Link, Link1
• Analyze and optimize performance, including Multidimensional Expression (MDX) and Data Analysis Expression (DAX) queries; Link
• Understand the difference between partitioning for load performance versus query performance in SSAS; Link, Link1 (Lazy Aggregations)
• Appropriately index a fact table; Link
• Optimize Analysis Services cubes in SQL Server Data Tools; Link
• Create aggregations; Link, Link1
Continue reading

Advertisements

Export recently modified SQL Stored Procedures with Powershell

The need of detecting and exporting Stored Procedures that were recently modified is a rather often database synchronization task. Powershell can work with SMOs (SQL Server Managent Objects) via .NET and extract the database objects [procedures, tables, views, etc] schemas.

Lets have a look how the recently modified Stored Procedures can be exported. Comments are inline:


# I'm wrapping everything into a function, it's just easier to reuse
function GenerateSPsScript([string]$serverName, [string]$dbname, [string]$scriptpath)
{
  # Lets load the SMO assembly first (this is basically a collection of SQL Server related objects)
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
  
  # Lets initiate a SQL server object
  $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $serverName 
  
  # Lets prepare a Database object and connect to it
  $db = New-Object "Microsoft.SqlServer.Management.SMO.Database" 
  $db = $srv.Databases[$dbname] 
  
  # And finally lets create a Scripter object
  $scr = New-Object "Microsoft.SqlServer.Management.SMO.Scripter" 
  $scr.Server = $srv 
  
  # Now we can prepare the options for the Scripter object. Check this web site for the details:
  # https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx
  $options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions" 
  $options.AllowSystemObjects = $false 
  $options.IncludeDatabaseContext = $false 
  $options.IncludeIfNotExists = $false 
  $options.Default = $true 
  $options.IncludeHeaders = $false 
  $options.ToFileOnly = $true 
  $options.AppendToFile = $true
  
  # Lets set the option of the target file name and create a text file where the SPs code will be exported
  $options.FileName = $scriptpath + "\$($dbname)_modified_stored_procs_$(get-date -f yyyy-MM-dd).sql"
  New-Item $options.FileName -type file -force | Out-Null
  
  # now apply the above options to the SMO.Scripter object
  $scr.Options = $options 
  
  # OK, connection and settings are ready. Now we can start working with the database objects
  # We want to work with the Stored Procedures, so lets grab all the non-system SPs
  $StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
  
  # Say you need to export only the SPs that were modified during the last 7 days
  $cd = Get-Date
  $d = $cd.AddDays(-7)
  
  # Now for every SP we will execute the Script method
  Foreach ($StoredProcedure in $StoredProcedures)
  {
    if ($StoredProcedures -ne $null -and $StoredProcedure.DateLastModified -ge $d)
    {   
      # Script the Stored Procedure
      $scr.Script($StoredProcedure)
    }
  }
} 
# Function is completed

# Finally we call the above function by putting our environment details
GenerateSPsScript "SQLServer\InstanceName" "DatabaseName" "D:\ExportFolder"
# If you have a default instance then just put the server name

So just copy/paste the above code into a .ps1 file, put your SQL instance details, database name, target folder and run it. A .sql file with the Stored Procedures will be generated.

SQL – Creating a SQL Server AlwaysOn on Amazon EC2, Part 6

In this last post of the AlwaysOn series I’m going to build a simple C# console application to demonstrate how a  business app can benifit from using an AlwaysOn enabled database.

In Visual Studio I’m going to create a Console Application project:

1To be flexible in changing connection strings and SQL queries I’m going to use an external XML config file – App.config. For that I need to include few additional namespaces into my Program.cs code:

App1 Continue reading