Preparation for the 70-465 exam, Designing solutions for SQL Server

I’m starting my preparation for the final SQL Server exam in the series, the 70-465, Designing Database Solutions for Microsoft SQL Server. I will be collecting links in this post.

There are also some videos available: Link1 nad Link2.
Exam topics are taken from here:

Design a database structure (20-30%)

• Business to data translations, identify which SQL Server components to use to support business requirements,
• Design a normalization area, Link;
• De-normalize technically (versus by remodeling) by using SQL Server features (materialization via indexed views and more) Link, Link1
• Design a physical database, including file placement, FILESTREAM, FILETABLE, file groups, and RAID; Link;
• Configure system database settings
• Develop the optimal strategy for indexing, Link
• archive using partitions and tables, Link
• design columnstore indexes, Link
• design XML indexes; Link
• Upgrade with minimal downtime; Link, Link1
• design a cross-cluster migration; Link
• Plan a database deployment, including Windows PowerShell, Server Core, and contained databases;
• Migrate to SQL Database;
• Migrate query plans;
• Design a migration strategy using Distributed Replay Controller;
• Design a SQL Server virtualization strategy;
• Identify hardware for new instances;
• SQL Server Distributed Replay; Link
• Design CPU affinity;
• Design clustered instances using Microsoft Distributed Transaction Control (MSDTC);
• Define instance memory allocation;
• Design installation strategies, including sysprep, slipstream, and SMB file server;
• Define cross db ownership chaining
• Design a backup strategy based on business needs, including differential, file, log, striped, and Microsoft Azure Blob Storage Service;
• design a database snapshot strategy;
• design appropriate recovery models; design a system database backup strategy; recover Tail-Log backups
Continue reading


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

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:
  $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
# 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.

Docker on Windows – FATA[0021] An error occurred trying to connect

This post is to show how to fix “An error occurred trying to connect: …” error after installing a fresh Docker on Windows:

Docker1So, asuming, you have downloaded the latest Docker for Windows package from this link – You install VirtualBox and other components. You open a VirtualBox manager to see that there is nothing yet created :), then you double click on “Boot2Docker start” icon and you see how a new virtual machine is being created and started:Docker15

Continue reading

Installing Spark on Windows

Here I’m going to provide a step by step instructions on how to install Spark on Windows.

Computer: Windows 7 x64, 8 GB RAM, i5 CPU.

Spark is written with Scala and runs in the Java virtual environment. To build Spark we need to prepare the environment first by installing: JDK, Scala, SBT and GIT. Versions are important.

Let’s start with Java. The latest JDK 7 will be used:

1 JDK 1 Continue reading

Creating a Self-Signed SSL Certificate

Creating a Self-Signed SSL certificate is a nice option when you need to run a quick https test for a web site. Lets take a look on how we can make one.

I’ll be using OpenSSL toolkit. I’m on Windows therefore package can be ataken from here:
“openssl-0.9.8h-1-setup.exe” executable by default drops everything here: “C:\Program Files\GnuWin32\bin”. Or, if you are on 64 bit OS, then here: “C:\Program Files (x86)\GnuWin32\bin”.

OpenSSL package comes with a default config file – openssl.cnf. It is located in the “share” folder, in my case here: “C:\Program Files\GnuWin32\share”. Config file holds important settings that are used to generate the certificate. We need to  comment “attributes” parameter line, otherwise an error will be presented. I also set the default length to 2048, but this one can be controlled with the command line parameters:
OpenSSL0 Continue reading

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