Share via

How to Build a SQL Server 2012 Hyper-V Virtual Machine (KIWI build)

  • Article

This how-to article will guide you through the process of building a Hyper-V virtual machine with Microsoft SQL Server 2012, the latest Visual Studio 2010 development tools, SQL Server Data Tools (SSDT), and an assortment of other software products to create a test environment ready for testing and product demos.

If you are interested in building an EMU + KIWI build see this wiki: http://social.technet.microsoft.com/wiki/contents/articles/10304.how-to-build-a-combined-set-of-kiwi-and-emu-vms-sql-server-2012-demos.aspx 

Prerequisites for build machine (for creating VM on)

  • Hyper-V role enabled in Windows Server 2008 R2 SP1 64-bit host machine
  • 8 - 10 GB RAM recommended minimum
  • 200 GB free space suggested for VM, installation media, snapshots, extra room to work in

Prerequisites for host machine (for running VM on)

Same as build machine, but allow for about 100 GB for free drive space, running preferably on a separate spindle from the main OS drive for performance reasons.

Important note about Office activation:
The output of this build is to export from Hyper-V. An import *should* keep Office activated even when moving to another machine. However, if you create a new virtual machine and then attach the VHD instead, Office will de-activate because of new virtual HAL that was created. Regardless, here is how you can re-activate Office using your own key if needed:

  • Add external network adapter to VM for Internet access
  • Open command prompt (Run as admin)
  • Change current directory to c:\program files\microsoft office\office14\
  • Execute the command "cscript ospp.vbs /inpkey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX"
  • Execute the command "cscript ospp.vbs /act"

Software prerequisites for guest VM (to install on VM)

  • Note: the idea of this section is to list all required software so that you can gather up as much of it as possible before beginning the build.
  • Windows Server 2008 R2 Enterprise with SP1 (64-bit)
  • Internet Explorer 9 (latest)
  • Office Professional Plus 2010 64-bit
  • Visual Studio 2010 Ultimate & SP1
  • SharePoint Server Enterprise 2010 & SP1
  • Microsoft Access Database Engine 2010 Redistributable (64-bit) (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en)
  • Install SQL Server Compact 4.0 (SSCERuntime_x64-ENU)
    •  http://www.microsoft.com/download/en/details.aspx?id=17876
  • Install Master Data Service Add-in for Excel (64-bit)
  • Install PowerPivot for Excel (64-bit)
  • Windows Azure SDK (use Web Platform Installer)
  • SQL Server 2012 Developer Edition ISO
  • SQL Server Data Tools

IMPORTANT CONSIDERATIONS (READ BEFORE BUILDING)

  1. Minimize dependencies on internet connectivity if feasible for your scenario (feedback/customer experience programs, automatic updates)
  2. Evaluate whether or not you want to run Windows Update BEFORE you build or use a base VM. For some scenarios, software installed with Windows Update can lead to unforeseen consequences for testing or the evaluation of pre-release software. If you are building this VM for someone else, it would be wise to explicitly ask them this question!
  3. Some aspects of setup and configuration can be error prone, so take SNAPSHOTS as you build

OPTIONAL

If you wish to have connectivity between host and guest, configure virtual internal network according to these instructions: http://technet.microsoft.com/en-us/library/ee256061(WS.10).aspx

Create VM

  • Create new Hyper-V VM
  • Assign 8000 MB RAM (less may work but slower)
  • Assign an Internal network connection
  • Create new virtual hard disk (75GB to leave enough head-room for pagefile and additional installs & demo material)
  • For VHD Installation Options, choose “Install and operating system from a boot CD/DVD-ROM” and choose Windows Server ISO

Base OS Setup and Configuration

  • Boot VM and proceed with default installation of Windows Server 2008 R2 Enterprise with SP1 (64-bit)
  • Set local Administrator password to Password;1
  • Configure network adapter IPv4 address to IP: 33.0.0.2, Subnet: 255.255.255.0, Default Gateway: 33.0.0.2, DNS: 33.0.0.2
  • Ensure following features are added/enabled: Desktop Experience, Quality Windows Audio Video Experience, Windows PowerShell Integrated Scripting Environment, XPS Viewer
  • Services set to Automatic for: Themes, Windows Audio
  • Disable IE ESC (Enhanced Security Configuration)
  • Computer name: KIWI
  • Remote Desktop enabled for Administrator
  • REBOOT
  • Install Active Directory Domain Services role
    • Run Installation Wizard (dcpromo.exe) :
      • Create a new domain in a new forest
      • FQDN: contoso.com
      • Forest Function Level: Windows Server 2008 R2
      • Additional Domain Controller Options: DNS
        • Note: select 'Yes' to bypass Static IP assignment warning
      • Directory Services Restore Mode Admin Password: Password;1
      • Reboot on completion: enabled
    • Add Service account intended for all service accounts
      • User: CONTOSO\SQLSERVICE
      • Password: Password;1
      • Enable Password Never Expires option
  • Install Application Server role
    • Web Server IIS Support: enabled (will turn on web server role by default)

Other OS Config

  • Disable expiration for Administrator passwords (local and domain)
  • Disable UAC
  • Turn off windows login/logoff sounds
  • Update folder options to show all files and extensions
  • Change to Windows 7 Theme
  • Add scenario appropriate desktop wallpaper
  • Add desktop icons for Computer, User Files, Recycle Bin
  • Select auto-arrange desktop icons
  • 'Do Not Show Server Manager' enabled
  • SHUTDOWN

Internet Access for VM

  • Make sure VM is shut down
  • Configure VM to add 2nd network, bind to appropriate external adapter for internet access

Baseline Software Install

  • Install IE 9: don't use recommended settings: enabled
  • Install Silverlight 5: do not enable updates, also include 64-bit version 
  • Install Office Professional Plus 2010 with SP1 64-bit
    • Custom install: defaults except do NOT include Publisher
    • Launch Excel to disable updates, then exit Excel
  • Install Visual Studio 2010 Ultimate
    • Custom install: defaults except SQL Server 2008 Express, Dotfuscator
    • Launch VS
    • Choose C# dev settings
    • Customer Improvement Program: No
    • Install local help
      • In VS, go to Help | Manage Help Settings
      • Select OK for default install location on HDD
      • Select "Install content from disk" option, load HelpContentSetup.msha from install disk ProductDocumentation folder
      • Select all content packages
    • Install VS SP1
  • Install SharePoint Server Enterprise 2010 with SP1
    • Run software pre-requisite installer (PrerequisiteInstaller.exe)
    • REBOOT
    • Start SharePoint install
    • Activate with enterprise key
    • Install SharePoint Server in Server Farm config (Standalone is NOT supported by PowerPivot)
    • Server type: complete
    • DO NOT run configuration wizard
    • Note: make sure to install SP1 if installing SharePoint from installer that does not include it

SQL Installation and Configuration

  • Install SQL 2012 Developer Edition

    • Execute SETUP.EXE to load Installation Center
    • Launch Installation wizard to add stand-alone installation
      • Accept license, do not send usage date to Microsoft
      • Can ignore 'Computer domain controller' and 'Windows Firewall' setup support rule warnings
      • Choose 'SQL Server Feature Installation'
      • For Feature Selection, select all and then de-select 'Reporting Services - SharePoint' and 'Reporting Services Add-in for SharePoint Products'
      • For Instance Configuration, set as Default instance
      • For Server Configuration
        • Use CONTOSO\sqlservice for all service accounts (some will remain greyed out), password is 'Password;1'.
        • Set all services to startup type of Automatic except for 'Distributed Replay *' services
      • For Database Engine Configuration
        • use Mixed Mode authentication, use default 'Password;1' password
        • Add CONTOSO\administrator and CONTOSO\sqlservice as administrators
        • In FILESTREAM tab, enable all options
      • For Analysis Services Config, add CONTOSO\administrator and CONTOSO\sqlservice as administrators, select Tabular Mode
      • For Reporting Services Config, choose "install only" option for Native Mode
      • For Distributed Replay Controller config, add CONTOSO\administrator and CONTOSO\sqlservice as administrators
      • For Distributed Replay Client config, use KIWI as the Controller Name
      • Review selected options on pre-install summary screen, then kick off install

  • Install another Analysis Services instance (MOLAP)

    • launch SQL setup, launch wizard to add stand-alone installation
    • Installation Type: choose to 'Perform a new installation...'
    • Setup Role: SQL Server Feature Installation
    • Feature Selection: select just Analysis Services
    • Instance Configuration: setup as MOLAP instance
    • Use CONTOSO\sqlservice for all service accounts (some will remain greyed out), password is 'Password;1'
    • For Analysis Services Config, select 'Multidimensional and Data Mining Mode' and add CONTOSO\administrator and CONTOSO\sqlservice as administrators
    • Finish installation

  • Install StreamInsight 2.0 Server from SQL 2012 Feature Pack, 64-bit version

    • Instance Configuration
      • Install Instance Now: Enabled
      • Instance Name: STREAMINSIGHT
    • Instance Configuration
      • Create the StreamInsight Host Service: enabled
      • Add Current user...: enabled

  • Install StreamInsight 2.0 Client

Reporting Services Configuration

  • Launch Reporting Services Configuration Manager
    • Connect to default instance of SSRS: KIWI
    • Click Database node:
      • Click 'Change Database' button
      • In Chage Database Wizard
        • Create a new report server database
        • Database Server:
          • Server Name: KIWI
          • Authentication Type: Current User: Integrated Security
          • Next
        • Database:
          • Database Name: ReportServer
          • Language: English
          • Mode: Native
        • Credentials:
          • Authentication: Service Credentials
    • Click Web Service URL node:
      • Change TCP Port to: 8080
      • Click Apply
    • Click Report Manager URL node:
      • Click Apply
    • Click Execution Account node:
      • Specify an execution account: enabled
      • Account: CONTOSO\sqlservice (Password;1)
      • Click Apply
    • Exit Reporting Services Configuration Manager

SharePoint Configuration

  • Launch SharePoint 2010 Products Configuration Wizard

    • Create a new server farm
      • Database server: KIWI
      • Database name: SharePoint_Config (is default)
      • Database Access Account: CONTOSO\sqlservice (Password;1)
      • Farm Security Settings Passphrase: Password;1
      • Central Administration Web Application port: use default which chooses random port
      • Choose NTLM authentication provider for Central Administration Web Application
    • Initial Farm Configuration website will load after configuration wizard completes
      • Choose not to participate in Customer Experience Improvement Program
      • Cancel when prompted to continue configuration...

  • Install PowerPivot for SharePoint 2010 instance

    • Execute SETUP.EXE to load Installation Center
    • Launch wizard to add stand-alone installation
    • For Installation Type, choose to 'Perform a new installation of SQL Server...'
    • When you get to Setup Role step, choose 'SQL Server PowerPivot for SharePoint' but do not select the option to add 'Database Relational Engine Services to this installation'
    • Continue past Feature Selection step, all options are selected but greyed out
    • For Instance Configuration, use default ID of POWERPIVOT
    • For Server Configuration, use CONTOSO\sqlservice for service accounts, Automatic startup
    • For Analysis Services Configuration, add CONTOSO\administrator and CONTOSO\sqlservice as admins

  • PowerPivot Configuration Tool

    • Configure or Repair PowerPivot for SharePoint node: LEAVE DEFAULTS (don't need to fill every thing in)
      • Default Account Username: CONTOSO\Administrator
      • Default Account Password: <blank>
      • Database Server: KIWI
      • Passphrase: <blank>
      • Confirm Passphrase: <blank>
    • Create Default Web Application node:
      • Web Application Name: SharePoint - 81
      • URL: http://KIWI:81
      • Application Pool: Default Application Pool
      • Application Pool Account: CONTOSO\sqlservice
      • Application Pool Account Password: Password;1
      • Database Server: KIWI
      • Database Name: <use default>
    • Update Secure Store Service Master Key node:
      • Passphrase: Password;1
    • Create Unattended Account for DataRefresh
      • Unattended Account User Name: CONTOSO\Administrator
      • Unattended Account Password: Password;1
    • Note: at this point no tasks should have yellow exclamations
    • Click Validate
    • After validation succeeds, click Run  

  • Install 'Reporting Services - SharePoint' and 'Reporting Services Add-in for SharePoint Products'

    • Execute SETUP.EXE to load Installation Center
    • Launch wizard to add features to an existing installation
    • For Installation Type, choose the 'Add features to an existing instance...' option and the default instance
    • Select the 'Reporting Services - SharePoint' and 'Reporting Services Add-in for SharePoint Products' features

  • Important: make sure that there is a default root site collection (at http://kiwi:81/). Excel viewer will cease to function after SSDT install otherwise. See http://social.technet.microsoft.com/Forums/en-US/sharepoint2010setup/thread/f8fdf03e-cf15-4e2d-985c-e88598f4580e

  • Create new BI Center site collection

    • Central Administration | Application Management | Site Collections | Create Site Collections
    • Web Application: http://kiwi:81
    • Title: BI Center
    • URL: http://kiwi:81/sites/bi
    • Template: Enterprise | Business Intelligence Center
    • Site Collection Administrator; CONTOSO\administrator

  • Create new PowerPivot Site Collection

    • Central Administration | Application Management | Site Collections | Create Site Collections
    • Web Application: http://kiwi:81
    • Title: PowerPivot Gallery
    • URL: http://kiwi:81/sites/powerpivot
    • Template: Collaboration | PowerPivot Site
    • Site Collection Administrator; CONTOSO\administrator

  • Create a SQL Server Reporting Services Service Application

    • In SharePoint Central Administration, click on Manage Service Applications link
    • Select the New dropdown and select SQL Server Reporting Services Service Application
    • Name: Reporting Services
    • New Application Pool Name: ReportingServicesAppPool, CONTOSO\sqlservice
    • Database Server: KIWI
    • Web Application Association: select default sharepoint site

  •  

  • configure email with pickup directory

    • Create c:\Email folder
    • Load Windows PowerShell ISE
    • Use the following PS shown below
    • This will take a few seconds to complete, if successful you will see the final config XML output

*********************

Add-PSSnapin 'Microsoft.Sharepoint.Powershell'

$apps = Get-SPRSServiceApplication
foreach ($app in $apps)
{
$emailCfg = Get-SPRSExtension -identity $app -ExtensionType 'Delivery' -name 'Report Server Email' | select -ExpandProperty ConfigurationXml
$emailXml = [xml]$emailCfg
$emailXml.SelectSingleNode('//SMTPServer').InnerText = ''
$emailXml.SelectSingleNode('//SMTPServerPickupDirectory').InnerText = 'C:\Email'
$emailXml.SelectSingleNode('//SendUsing').InnerText = '1'
$emailXml.SelectSingleNode('//SMTPAuthenticate').InnerText = ''
$emailxml.SelectSingleNode('//From').InnerText='Administrator@contoso.com'
Set-SPRSExtension -identity $app -ExtensionType 'Delivery' -name 'Report Server Email' -ExtensionConfiguration $emailXml.OuterXml
Get-SPRSExtension -identity $app -ExtensionType 'Delivery' -name 'Report Server Email' | select -ExpandProperty ConfigurationXml
}

**********************

  • Add reporting content types to BI Center site collection
    • Load BI Center site
    • Select Libraries link, load 'Documents' library, click on Library tab, click on Library Settings button
    • Under General Settings section, click Advanced Settings hyperlink
    • In Content Types section, select Yes to allow management of content types, click OK button to accept and return to Document Library Settings page
    • Under Content Types section, click 'Add from existing site content types' hyperlink
    • In the Select Content Types section, select the 'SQL Server Reporting Services Content Types' option from the 'Select site content types from' drop down box
    • Add all content types (Report Builder Model, Report Builder Report, and Report Data Source) to the 'Content types to add' box
    • Select OK button
  • Activate "Report Server File Sync" feature for BI and PowerPivot sites
    • On each site to enable feature:
      • Click 'Site Actions' menu and click 'Site Settings'
      • Click Manage Site Features
      • Activate Report Server File Sync
  • Create a PerformancePoint Services Service Application
    • In SharePoint Central Administration, click on Manage Service Applications link
    • Select the New dropdown and select PerformancePoint Service Service Application
      • Name: PerformancePointApp1, Add proxy to default proxy list enabled
      • Application Pool: PerformancePoint
      • Security Account: CONTOSO\sqlservice
      • Click Create
    • After PerformancePoint Services Service Application is created, load configuration page
      • Unattended Service Account: CONTOSO\administrator (Password;1)
    • go to Central Administration | System Settings | Manage services on server and enable PerformancePoint Service
  • Configure File Size Limits (some of these may be already be set as default)
    • Central Administration | Application Management | Mange web applications
    • Click the SharePoint-81 hyperlink, then select the General Settings | General Settings options
    • Change 'Maximum Upload Size' to 2047 MB
    • Central Administration | Application Management | Manger service applications
    • Click the Excel Services application hyperlink
    • Click 'Trusted File Locations' hyperlink
    • Click trusted file location entry (http://)
    • Scroll down to 'Workbook Properties' section and change 'Maximum Workbook Size' to 2000, 'Maximum Chart or Image Size' to 100, OK
  • VERIFY PowerPivot install (see http://msdn.microsoft.com/en-us/library/hh231684(v=SQL.110).aspx)
    • In Central Administration, navigate to PowerPivot Management Dashboard via 'manage service applications' link
    • This may bring up request to install Silverlight 5 64-bit, go ahead and do this, configure NOT to check for updates

Additional Software Installation

  •  Install Microsoft Access Database Engine 2010 Redistributable (64-bit)
  • Install SQL Server Compact 4.0 (SSCERuntime_x64-ENU)
    •  http://www.microsoft.com/download/en/details.aspx?id=17876
  • [INVESTIGATE for next VER] Install 2007 Office System Driver: Data Connectivity Components from
  •  
  • Install Master Data Service Add-in for Excel (64-bit)
  • Install PowerPivot for Excel (64-bit)
  •  Install Books Online documentation (load Help Library Manager)
    • install from online, choose SQL books to download and install
    • OR add link to MSDN SQL books online to links textfile on desktop
  • Install Semantic Language Statistics database (SemanticLanguageDatabase.msi from SQL installation media 64bit)
    • Copy *.mdf and *.ldf from c:\program files\microsoft semantic language database\ to Data directory of default instance
    • attach database to default instance (semanticsDB.mdf)
    • Create new query in SSMS, and run the following:
      • EXEC sp_fulltext_semantic_register_language_statistics_db 'semanticsdb'
  • Setup MDS website
    • Launch Master Data Services Configuration Manager
      • Database Configuration node, Create Database...
        • KIWI, Integrated security, CONTOSO\administrator, Next...
        • Database name: MDS, Next...
        • Administrator Account: CONTOSO\administrator
      • Web Configuration node
        • Select Create New Website...
        • MDS site name
        • Port 82
        • App pool user: CONTOSO\sqlservice
        • Click OK button, ignore warning about using HTTP
        • Select database previously created...
        • Click Apply button... 
      • Close configuration, additional steps follow...
    • http://kiwi:82/ in IE, site should now load
    • Load http://kiwi:82/service/service.svc in IE, should see info and not error
    • in MDS web app, load sample package data from Samples folder in MDS install folder
      • Use MDSModelDeploy.exe utility from Program Files\Microsoft SQL Server\110\Master Data Services\Configuration folder
      • example: MDSModelDeploy deploynew -package PackageName -model ModelName
      • master data services\samples has packages...
    • Load Excel and connect to MDS site as test (Master Data tab), load test data (use http://kiwi:82/, add-in will know to use service/service.svc)
      • Create connection named MDS to http://kiwi:82/ to make it easier for users to discover
  • Install Data Quality Services
    • Launch DQSInstaller.exe from install path of default SQL Server instance (\MSSQL\Binn folder). See here.
    • You will need to enter a strong password (see listed requirments). Suggested master key below will pass the tests, but you may use any password that you want:
      • suggested Database Master Key: QNiZcSxb14$
  • Install latest NuGet for VS (nuget.codeplex.com)
  • [OPTIONAL] Install Entity Framework 4.3 (using NuGet in VS) for non-Internet scenario
    • Load VS, create a throwaway project and add Entity Framework 4.3 to it in order to download the .nupkg file to local cache
    • Grab .nupkg file from cache location (can Browse from Tools | Options | Package Manager | General to find it)
    • Create a local package source (Tools | Options | Package Manager  | Package Sources) called “Local” and map it to c:\localNuGet on KIWI
    • Copy .nupkg file to c:\localNuGet
    • At this point, you can remove Internet connection, select the new “Local” source, and add the Entity Framework 4.3 into a project
    • Example to test with:
      • Load VS, Tools | Library Package Manager | Package Manager
        • Install-Package EntityFramework -Version 4.3.0
  • Install Windows Azure SDK
  • Install Office SP1 (client) -- requires reboot
  • Install SQL Server Data Tools (SSDT)

Additional Setup & Config

  • Configure default workspace server to work with Analysis Services Tabular Projects (a.k.a. Business Intelligence Semantic Model Projects
    • Launch VS 2010
    • Tools | Options | Analysis Services | Data Modeling
    • Ensure the "Default workspace server" points to "localhost", the tabular instance of SQL
    • Click OK
  • Launch BI Center and launch Dashboard Designer to make sure it is installed
  • Launch BI Center, go to Libraries | Documents, add a new Report Builder Report to launch Report Builder app for first time
  • [OLD, this is not being installed for RTM] Install AdventureWorks databases
  • Copy a loose email message to VM (such as .eml) and open with Outlook to initialize
    • Select No when asked to configure an account
    • Check box to "continue with no e-mail support"
  • Configure IE
    • In IE, show Favorites Bar (right-click on window select Favorites Bar option)
    • add favorite links to "BI Center - SharePoint" and "PowerPivot - SharePoint" sites
    • set about:blank as home page
    • (optional) In Internet Options | Connections | LAN settings, de-select 'Automatically detect settings'
      • Depends on the network environment that you will be running in...
    • In PerformancePoint Content area of BI site, create new doc and make sure designer loads
  • Open Excel, open File | Options | Trust Center | Trust Center Settings. In Trust Center window, navigate to Protected View tab, de-select 'Enable protected view for files located in potentially unsafe locations". This should enable you to open a PowerPivot doc from SharePoint in Excel.
  • Run PowerPoint slideshow in full screen and check the box so that the message about the experience not being "optimal" doesn't show up again.
  • (optional) Customize Notification Area icons
    • 'Hide icon and notifications' for Volume, Action Center
  • (optional) Cleanup event logs with PowerShell
    • wevtutil el | Foreach-Object {Write-Host "Clearing $_"; wevtutil cl "$_"}
  • Create links.txt file on Desktop with information about sites and SQL instances

 Verify Installations

  • Note: this section needs cleaned up & perhaps expanded. Some references may not be up to date and some references to sample may not be publicly accessible.

  • SNAPSHOT prior to testing

  • Load SSMS connect to all instances of db, AS, etc.

  • SharePoint

    • Verify the following sharepoint services are in Started state
      • Claims to Windows Token Service
      • Excel Calculation Services
      • Secure Store Service
      • SQL Server Analysis Services
      • SQL Server PowerPivot System Service
      • SQL Server Reporting Services Service
      • PerformancePoint Service
    • Verify the following sharepoint service applications are in the Started state
      • Note: actual Names may vary, look at the Type column
      • PowerPivot Service Application
      • Excel Services Application Web Service Application
      • PerformancePoint Service Application
      • SQL Server Reporting Services Service Application
      • Secure Store Service Application
      • Security Token Service Application

  • PowerPivot

    • http://msdn.microsoft.com/en-us/library/hh231684(v=SQL.110).aspx
    • Make sure you can load and work with PowerPivot workbook with Excel viewer in SharePoint
    • Make sure you can use PowerPivot add-in in Excel to load sample data from SQL
    • Make sure you can load and work with PowerPivot workbook in Excel client
    • Make sure PowerView works in interactive mode within PowerPoint (64bit office is installed) - create a PowerView report from a PowerPivot, export to PowerPoint file, open and view slideshow, click on interactive button and make sure it works

  • Ensure Data Alerts feature for reports in SharePoint is working

    • Use Report Builder to create a report in BI Center to test Data Alerts
      • Can use AdventureWorks DB for test
      • When setting up data source, click on Credentials tab to add stored credentials (needed to enable data alerts feature). Provide user name password and check box to use Windows credentials
      • Create a query, etc.
      • Save to BI Center Documents (http://kiwi:81/sites/bi/Documents)
      • Return to BI document library, click on hyperlink of report
      • Once data loads, click Actions | New Data Alert
      • Add a rule that will eval to true for the data set, expand Advanced and uncheck "Send message only if alert results change", add email address, Save button
      • View current alerts by going to document library where report is, clicking down arrow to the right of the hyperlink for the report, and selecting Manage Data Alerts
      • Right-click on alert and Run, see if mail is output (note: it may take a minute to run)

  • Test DQS

    • Load Data Quality Client and connect to KIWI
    • New knowledge base, any name, description
    • Create KB from data file EIMDemo.dqs (import) [TODO: provide link when/if made public]
    • Click Next, import happens, Finish, Publish
    • In SSMS, restore Stage.bak
    • See http://msdn.microsoft.com/en-us/library/gg492280(v=SQL.110).aspx for permissions setup                          
    • Create new data quality project, any name description
    • Use knowledge base previously imported, make sure Cleansing Activity selected
    • Click Next
    • Choose data source: SQL Server
    • Choose database: Stage
    • Choose Table/View: CRMAccounts
    • Map AccountNumber to AccountNumber (just one for smoke test)
    • Click Next, Start...

  • Load MDS website at http://kiwi/mds:82 and exercise it

  • In Excel, use Master Data add-in to connect to MDS database and load some test data

  • SSDT: in VS SQL Server Object Explorer, add local server to SQL Server node. View table from one of the adventure works dbs, perform a select query to test, right click on a database and create new project from that, build it.

  • In PerformancePoint Content area of BI site, create new doc and make sure designer loads

  • Streaminsight: download samples from streaminsight.codeplex.com, configure and run HitchHiker 'HelloTollTutorial'

    • In HelloTollTutorial.cs, use instance name in line 27, Server.Create("STREAMINSIGHT")
    • Start debugging "HelloTollTutorial" project

  • Analysis Services Tabular Data

    • VS: File | New Project | Business Intelligence | Analysis Services
    • Create new Analysis Services Tabular Project
    • View the model project properties
    • Verify the workspace server is set to localhost (default instance is tablular AS)
    • Test deploy

Post Build Checklist/Pre-Deployment

  • account passwords do not expire

  • Disable windows update

  • Ensure all software is activated (if desired)

  • Run disk cleanup on VM drive, delete any temporary installation files, etc.

  • Clear browser history, etc.

  • Cleanup event logs with powershell:

    • wevtutil el | Foreach-Object {Write-Host "Clearing $_"; wevtutil cl "$_"}

  • Notification Area Icons: hide Volume and Action Center notifications

  • Ensure size of VHD is approprate (account for pagefile and additional demo material)

  • Shutdown VM

  • Configure final VM settings (for consistency and to avoid warnings on import)

    • Memory: 8192
    • Remove any attached ISO images from drives
    • Remove network adapters not needed, for example an external adapter used for Internet access during setup
    • Set network adapters to "not connected"

  • Export VM

    • Note: Inform users of VM that Office will de-activate unless they IMPORT the VM (don't create a new VM and attach the VHD)

  • Mount VHD to host OS (use disk management tool), delete the following:

    • c:\pagefile.sys
    • c:\temp\.*
    • c:\Recycle.Bin
    • c:\windows\temp\.*
    • defrag VM drive TWICE
    • Detach VHD from host OS
    • Compact VHD with Hyper-V (use Edit Disk functionality...)

  • If RARing for distribution/handoff, consider using self-extracting archive option (use -sfx option) and split it into manageable sized volumes. In addition, consider using the -rr option to include recovery records in the archive so that data may be recovered in the event that some gets corrupted.

  • If RARing, also perform a test extraction locally to make sure everything worked as expected. If especially paranoid, also perform a test import of that test extraction -- sometimes strange things happen

Hosting in Azure (Optional)

Did you know you can run SQL Server in a Windows Azure Virtual Machine?  Check out the SQL Server in Windows Azure Virtual Machine Early Adoption Cook Book for more information. Assuming that you built this virtual machine using Hyper-V, you can upload and host it in Windows Azure and connect using remote desktop by following these steps:

  • Install the Windows Azure SDK 1.7 (current as of this writing)
  • In the Windows Azure admin portal (windows.azure.com), add a management certificate to the subscription you plan on using (if this is not already done) in Hosted Services, Storage Accounts & CDN | Management Certificates.
    • Note: this task will eventually make its way to the new portal, but for now you still have to perform this here
  • Activate the Virtual Machine preview feature for your subscription
    • Go to manage.windowsazure.com and sign in
    • Navigate to Account | Preview Features page
    • Click the 'try it now' button next to the Virtual Machines & Virtual Networks feature
      • Note: it should only take a few minutes for your account to be provisioned for the feature, and you will receive mail when it is done
    • Navigate to the preview management Portal and note that the "Virtual Machines" option is now available
  • Either create or obtain the VHD for your KIWI build.
  • Shrink your VHD to an appropriate size (maximum of 65GB for Azure VMs right now)
    • Note: this step is optional if the size of your VHD is already under 65GB
    • On the machine where the VHD resides, use disk management to attach, shrink volume as desired (leave room for pagefile, demo materials, etc.)
    • Detach the disk and then use VHD Resizer (free download) or similar tool to create a new VHD with smaller logical size
  • Use CSUPLOAD command-line tool from the Windows Azure SDK (in bin directory of install)
    • See docs at http://msdn.microsoft.com/en-us/library/windowsazure/gg466228.aspx
    • Example (replace the <> placeholders):
      • csupload Add-Disk -Connection "SubscriptionID=<GUID>; CertificateThumbprint=<THUMBPRINT>; ServiceManagementEndpoint=https://management.core.windows.net" -Destination "https://<YOURBLOBSTORAGENAME>.blob.core.windows.net/mydisks/kiwi.vhd" -Label "KIWI" -LiteralPath "<fullPathToVHD>" -OS "Windows"
      • Note: make sure that you use the OS flag to indicate that a Windows OS is installed on the VM
    • Wait for upload to complete, this will likely take at least a few hours
  • After VHD disk is uploaded, return to the preview portal and create a new virtual machine
    • Select Virtual Machines
    • Under VM Instances 'tab', click "Create a Virtual Machine" link
    • Select the "From Gallery" option to start the wizard
    • Select "My Disks", the VHD that you just uploaded, then click the Next arrow
    • Enter a VM name, like "KIWI". For VM Size, you will likely benefit most from Large or higher due to the size and complexity of the KIWI machine, then click the Next arrow
    • Select "Standalone Virtual Machine" option (default), then create a unique DNS name for it. Make sure other options look good then click the Next arrow
    • We don't need to create or participate in an availability set, so go ahead and click the Done checkbox button to provision the new VM
  • To RDP to your Azure hosted KIWI instance, navigate to the virtual machine dashboard page, then click on the Connect button near the bottom.

Return to [[SQL Server 2012 Developer Training Kit Virtual Machine]] wiki