Tag Archives: msSQL

[How-To] Import large .sql file into MSSQL (Microsoft SQL) Server

Objective: You have a .sql file you wish to import in Microsoft SQL Server (MSSQL)

Solution: Microsoft has a command line utility called Sqlcmd that can be used to import very large datasets into SQL server without having to open the file.  More information on this utility can be found here: http://msdn.microsoft.com/en-us/library/ms162773.aspx

Here is a snippit of the utility and its available switches before beginning:

C:\Users\Administrator>sqlcmd /?

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

Here is an example of how to use the utility:

  1. Open up a command prompt
    1. Note: Administrative privileges are optional depending on how you have your security settings configured.
      Server 2012 - Administrative Command Prompt
  2. Execute the following command
    1. sqlcmd -S SERVERNAME\INSTANCE_NAME -i C:\path\mysqlfile.sql
      1. Here are some notes/tricks you might want to use:
        • If you are using the default instance when you installed MSSQL server, you don’t have to specify \INSTANCE_NAME
        • You can log results to a text file by appending the following switch: -o C:\path\results.txt
        • You can specify a database to import to by appending the following switch: -d MYDATABASE
        • You can specify username and password by appending the following switch: -u USERNAME -p PASSWORD
          –Note: You can wait to be prompted for credentials by optionally leaving out the -p switch, however if you are piping the results out to notepad, the command will not fully execute.

 

[Tutorial] Deploying VMware vCloud Director 5.5

Here are some notes on deploying VMware vCloud Director 5.5.  I fealt the process as a whole was very confusing as there really isn’t a definitive guide out there for folks going from vCenter to a suite product such as vCloud Director.

Prerequisites / Tutorial To-Do List

  • Microsoft SQL Server 2008 R2
    • The SQL Server must use Mixed Mode authentication (cannot use Windows Authentication, must be a SQL account)  We’ll go over configuring this in the guide.
  • Linux VM
    • Must run one of the following OSes (I’m using CentOS6 for this guide)
      • CentOS 6 (64-bit) Update 4
      • Red Hat Enterprise Linux 5 (64-bit) Update 4
      • Red Hat Enterprise Linux 5 (64-bit) Update 5
      • Red Hat Enterprise Linux 5 (64-bit) Update 6
      • Red Hat Enterprise Linux 5 (64-bit) Update 7
      • Red Hat Enterprise Linux 5 (64-bit) Update 8
      • Red Hat Enterprise Linux 5 (64-bit) Update 9
      • Red Hat Enterprise Linux 6 (64-bit) Update 1
      • Red Hat Enterprise Linux 6 (64-bit) Update 2
      • Red Hat Enterprise Linux 6 (64-bit) Update 3
      • Red Hat Enterprise Linux 6 (64-bit) Update 4
    • Two network adapters
    • At least 4 GB of memory
    • Hard drive space for the OS and an additional 2 GB of disk space for vmware installation and log files
  • Working ESXi environment with vCenter Server
  • VMware vCloud Networking and Security 5.5.x
  • VMware vCloud Director License Key

Best practicies article can be found here: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2059451

Deploy VMware vCloud Networking and Security 5.5.x 

Prior to installing vCloud Director, you must have the VMware vCloud Networking and Security 5.5.x appliance configured and running.  This product was formally called vShield, as is a required component of vCloud Director.  Instructions on how to deploy this can be found here: http://jackstromberg.com/2014/04/tutorial-deploying-vmware-vcloud-networking-and-security-5-5/

Linux VM Installation (CentOS 6)

  1. Grab a copy of the live CD iso to install the OS
    1. http://isoredirect.centos.org/centos/6/isos/x86_64/
  2. Next your way to victory through the install, substituting in your location and server info.
  3. Install VM tools once you have your OS up and running
    1. Tutorial on how to install VMtools on CentOS 6 can be found here: http://jackstromberg.com/2014/04/tutorial-how-to-install-vmtools-on-centos-6/
  4. Configure static IPs on each of your network cards
  5. Enable the firewall to allow inbound connections on port 443 (HTTPS)
    1. Tutorial on how to add firewall rules to CentOS 6 can be found here: http://jackstromberg.com/2014/04/tutorial-adding-firewall-rules-via-system-config-firewall-tui-on-centos-6/

Generate SSL certificates

We will need to generate some SSL certificates before running the vCloud Director installation.  Please follow the steps below to create a java keystore that vCloud director will use for SSL.  Below are two different methods of generating certificates.  Use the first if you don’t want to sign your certificates, use the second if you want to sign your certificate with an internal or external certificate authority.  Note: Usually I create seperate keystores with for each service, in this case, VMware wants both certificates in the same keystore.  Additionally, make sure you change the default password in the commands below to something stronger (other VMware products want you to use a generic password, this one you can change as it will be prompted during the install process).

Creating Self-Signed Certificates

keytool -genkey -keyalg RSA -storetype JCEKS -alias http -keystore certificates.ks -storepass passwd -validity 360 -keysize 2048
Self-Signed vCloud Director Certificate - http

keytool -genkey -keyalg RSA -storetype JCEKS -alias consoleproxy -keystore certificates.ks -storepass passwd -validity 360 -keysize 2048
Self-Signed vCloud Director Certificate - consoleproxy

–Verify both certificates are in the same keystore–

keytool -list -keystore certificates.ks -storetype JCEKS -storepass “passwd
Self-Signed vCloud Director Certificate - keystore

Creating Signed Certificate Requests (use this if you have an internal PKI, skip this step if you used self-signed certs above)

–Create the certificate requests–

  • HTTP Web Cert Request
    • keytool –certreq -keystore certificates.ks -storetype JCEKS -storepass passwd -alias http -file http.csr -validity 360 -keysize 2048
  • Console Proxy Cert Request
    • keytool -keystore certificates.ks -storetype JCEKS -storepass passwd -certreq –alias consoleproxy -file consoleproxy.csr -validity 360 -keysize 2048

–Import CA Chain–

  • Root Certificate Authority
    • keytool -storetype JCEKS -storepass passwd -keystore certificates.ks -import –alias root -file root.cer
  • Intermediate Certificate Authority (only needed if you have one)
    • keytool -storetype JCEKS -storepass passwd -keystore certificates.ks -import –alias intermediate -file intermediate.cer

–Import Signed Cert–

  • HTTP Web Signed Cert
    • keytool -storetype JCEKS -storepass passwd -keystore certificates.ks -import –alias http -file http.cer
  • Console Proxy Signed Cert
    • keytool -storetype JCEKS -storepass passwd -keystore certificates.ks -import –alias consoleproxy -file consoleproxy.cer

Note: Official VMware KB article on generating SSL certificates for vCloud Director: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1026309

Configure the Microsoft SQL (MSSQL) Server Database

  1. Login to your SQL Server/Cluster
  2. Right click on your SQL Server and select Properties
    msSQL - Server Properties
  3. Select the Security page and ensure SQL Server and Windows Authentication mode is checked
    1. Per VMware’s documentation, Windows Authentication is not supported when using Microsoft SQL with vCloud Director.
      Server Properties - SQL Server and Windows Authentication Mode
  4. Click OK
  5. Click on the New Query button and Execute the following query (make sure you change the path to the database and log files). This command will create the database instance and log files, specifying the proper collation sequence:
    USE [master]
    GO
    CREATE DATABASE [vcloud] ON PRIMARY
    (NAME = N'vcloud', FILENAME = N'C:\vcloud.mdf', SIZE = 100MB, FILEGROWTH = 10% )
    LOG ON
    (NAME = N'vcdb_log', FILENAME = N'C:\vcloud.ldf', SIZE = 1MB, FILEGROWTH = 10%)
    COLLATE Latin1_General_CS_AS
    GO
    msSQL - vCloud Director - Database Creation
  6. Use VMware’s recommended transaction isolation level.  Click the New Query button again and then Execute the following query:
    USE [vcloud]
    GO
    ALTER DATABASE [vcloud] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [vcloud] SET ALLOW_SNAPSHOT_ISOLATION ON;
    ALTER DATABASE [vcloud] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT;
    ALTER DATABASE [vcloud] SET MULTI_USER;
    GO
    msSQL - vCloud Director - Transaction Isolation Level
  7. Next, create the SQL User to connect to the database (we will use vcloud as the username, you can change this if you would like).  Click the New Query button again and then Execute the following query (amke sure to change the default password):
    USE [vcloud]
    GO
    CREATE LOGIN [vcloud] WITH PASSWORD = 'vcloudpass', DEFAULT_DATABASE =[vcloud],
    DEFAULT_LANGUAGE =[us_english], CHECK_POLICY=OFF
    GO
    CREATE USER [vcloud] for LOGIN [vcloud]
    GO
    msSQL - vCloud Director - Create User Account
  8. Last, assign the proper permissions to the SQL user.  We will need the user to have db_owner permissions for the install.  Click the New Query button again and then Execute the following query:
    USE [vcloud]
    GO
    sp_addrolemember [db_owner], [vcloud]
    GO
    msSQL - vCloud Director - db_owner privileges

The official VMware KB article on configuring MSSQL Server (and oracle) can be found here: http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2034540

Install vCloud Director

  1. Copy the certificates.ks keystore over to the vCloud Director VM if you didn’t generate them on it.
    1. I placed my ssl certificates in the following directory (VMware doesn’t recommend a location, so I just picked this one): /opt/vmware/
  2. Download a copy of VMware vCloud Director 5.5.X from myvmware.com
  3. Open up Terminal
    CentOS6 - Terminal
  4. Execute the following command to install one of the prerequisites for the vcloud director installer (CentOS should come preinstalled with the others)
    1. yum install redhat-lsb
      yum install redhat-lsb
  5. Enter y and then press enter to continue the install
    yum install redhat-lsb - Download Packages
  6. Enter y and then press enter to continue the install
    yum install redhat-lsb - Install Packages
  7. Navigate to the folder where you downloaded the vmware-vcloud-director bin file and execute the following command to allow the bin file to be executed
    1. chmod u+x vmware-vcloud-director-5.5 (tab to the end of the file)
      chmod vmware-vcloud-director
  8. Execute the following command to begin the installation
    1. ./vmware-vcloud-director (tab to the end of the file)
      Install vmware-vcloud-director-5.5
  9. Enter y to run the script after the installer verifies prerequisites
    Install vmware-vcloud-director-5.5 - Run the script
  10. Select which adapter you would like to assign the HTTP service to and press enter (this will be for the web management interface)
    Install vmware-vcloud-director-5.5 - HTTP service adapter
  11. Select which adapter you would like to assign the console proxy IP address to and press enter
    Install vmware-vcloud-director-5.5 - Remote console proxy adapter
  12. Enter in the path to your certificates.ks file and press enter (in this tutorial, I used /opt/vmware/certificates.ks for example)
    Install vmware-vcloud-director-5.5 - Java keystore - SSL Certificates
  13. Enter in the password to the keystore when prompted and press enter
    Install vmware-vcloud-director-5.5 - Java keystore - SSL Certificates - Password
  14. If you have a syslog server enter in the IP to it, otherwise press enter to skip it
    Install vmware-vcloud-director-5.5 - Syslog host name
  15. Type 2 to use Microsoft SQL Server and press enter
    Install vmware-vcloud-director-5.5 - Microsoft SQL Server
  16. Enter in the hostname or IP address to your MSSQL server and press enter
    Install vmware-vcloud-director-5.5 - Microsoft SQL Server - host
  17. Press enter to use the default SQL server port
    Install vmware-vcloud-director-5.5 - Microsoft SQL Server - port
  18. Enter in the name of your sql database (using the default database name vcloud for this guide) and press enter
    Install vmware-vcloud-director-5.5 - Microsoft SQL Server - database name
  19. Press enter to use the default database instance
    Install vmware-vcloud-director-5.5 - Microsoft SQL Server - database instance
  20. Enter your database user (vcloud is what we have been using for this tutorial) and press enter
    Install vmware-vcloud-director-5.5 - Microsoft SQL Server - database user
  21. Enter the password to your sql user and press enter
    Install vmware-vcloud-director-5.5 - Microsoft SQL Server - database password
  22. Type y and press enter to start the service
    Install vmware-vcloud-director-5.5 - Start Service

Configuring vCloud Director

  1. Open up your web browser and navigate over to your vCloud instance
  2. Optionally, install the VMware Remote Console Plug-in if prompted and then click Next
    1. The VMware Remote Console plug-in is used to manage your VMs through the web.  It is not needed during the installation process.
      vCloud Director - Setup Wizard - Welcome
  3. “Read” and check Yes, I accept the terms in the license agreement and click Next
    vCloud Director - Setup Wizard - License Agreement
  4. Enter your vCloud Director license key (can be obtained my myvmware.com) and click Next
    vCloud Director - Setup Wizard - Licensing
  5. Enter in an administrator account used to manage vCloud Director and then click Next
    vCloud Director - Setup Wizard - Create an Administrator Account
  6. Enter a system name (I used vCloudDirector) that gets added to vSphere.  This is where all the vCloud Director VMs will go when they are provisioned.  Click Next
    vCloud Director - Setup Wizard - System Settings
  7. Click Finish
    vCloud Director - Setup Wizard - Ready to Complete
  8. Login using your Administrator account once redirected
  9. Click on Attach a vCenter undernetth provision your Cloud resources…
    vCloud Director - Attach a vCenter
  10. Enter in your vCenter info and click Next
    vCloud Director - Attach a vCenter - Name this vCenter
  11. Enter in the username and password to your vShield Manager instance and click Next
    vCloud Director - Connect to vShield Manager
  12. Verify your settings and click Finish
    vCloud Director - Connect to vShield Manager - Ready to Complete
  13. Click on Step 2, Create a Provider VDC
    vCloud Director - Create Provider VDC
  14. If you have different resource pools with different configurations, I would create a similar name for your vDC.  In this example, I only have one resource pool containing all the resources in my environment, so I am going to name my vDC (Virtual Data Center) My Company.  Click Next when ready to proceed.
    1. Additionally, you can select what supported hardware version you wish to use.  As I have no ESX 4.x hosts, I am going to select Hardware version 9 as I have a newer environment.
    2. Note: Per VMware’s website, here is the definition of a Provider vDC: A Provider vDC is a collection of compute, memory, and storage resources from one vCenter. A Provider vDC provides resources to organization vDCs.
      vCloud Director - Create Provider VDC - Name this Provider VDC
  15. Select a resource pool you wish to deploy the VMs to and click Next
    vCloud Director - Create Provider VDC - Select a Resource Pool
  16. If you have a specific datastore or storage policy you wish to use for this vDC, select the Storage Policy/Datastore, click the Add button, and then click Next.
    vCloud Director - Create Provider VDC - Select a Resource Pool - Add
  17. Enter in the credentials to each of the hosts to deploy the vCloud Director agent.  Once completed, click Next.
    vCloud Director - Create Provider VDC - Prepare Hosts
  18. Click Finish if the provided information looks correct
    vCloud Director - Create Provider VDC - Ready to Complete
  19. Now, depending on your configuration, if you had other VMs deployed to this resource pool, you may receive an error stating that the machines cannot enter maintennace mode.  The reason behind this is that DRS is preventing the hosts from entering maintnenace mode because DRS cannot move the VMs around to achieve High Availablity.
    vCloud Director - vCenter - Operation timed out - Prepare Host

    1. If you see this behavior, you will have to manually deploy the vCloud Director agents to the hosts.  To do this, click on the Manage & Monitor tab inside of vCloud Director.
      vCloud Director - Manage & Monitor
    2. Select Hosts
    3. Right click on one of the hosts and select Prepare Host…
      vCloud Director - Manage & Monitor - Prepare Host
    4. Enter the username and password to the host and click OK
      vCloud Director - Manage & Monitor - Prepare Host - Credentials
    5. Repeat this process for the other hosts in your cluster (you can select multiple hosts at a time. Now that we have at least one host available, we can failover a few VMs via)
      vCloud Director - Manage & Monitor - Prepare Host - Success
      vCloud Director - Manage & Monitor - Prepare Host - Success All Hosts
  20. Click on the Home tab and then click on the Create a new organization link
    vCloud Director - Create a new organization
  21. Enter in your organization information and click Next
    1. The Organization name is simply a code used to generate a short url to identify the organization.  Only letters and numbers are accepted in this field.
      vCloud Director - Create a new organization - Name this Organization
  22. Optionally select whether you want to use LDAP to provide access to vCloud Director and then click Next.
    vCloud Director - Create a new organization - LDAP Options
  23. On the Add Local Users page, click Add to add virtual vCloud Users (non-ldap).  Click Next once you have added the users of your choice or click Next if you want to add users later.
    vCloud Director - Create a new organization - Add Local Users
  24. On the Catalog page, select whether or not you want to allow sharing/publishing between organizations.  In this case, just click Next.
    (oops, no picture for this one 🙁 )
  25. On the Email Preferences page, click Next
    vCloud Director - Create a new organization - Email Preferences
  26. Configure the policies to your liking.  In this case I am going to leave things with their default settings and click Next
    (Oops, no picture for this one 🙁 )
  27. Click Finish
    vCloud Director - Create a new organization - Ready to Complete
  28. Click on Step 6 Allocate resources to an organization
    vCloud Director - Allocate resources to an organization
  29. Select the Organization you created and click Next
    vCloud Director - Allocate resources to an organization - Select Organization
  30. Select your Provider VDC and click Next
    vCloud Director - Allocate resources to an organization - Select Provider VDC
  31. Select the allocation model you choose to best fit your organization.  In this case, I am going to use Pay-As-You-Go to only allocate used resources.  Click Next.
    vCloud Director - Allocate resources to an organization - Select Allocation Model
  32. Choose you you want to configure your allocation model from the previous step and click Next.
    vCloud Director - Allocate resources to an organization - Configure Pay-As-You-Go Model
  33. Select the storage you want to use, click Add, and then click Next
    vCloud Director - Allocate resources to an organization - Allocate Storage
  34. Click Next on the Select Network Pool & Services page
    vCloud Director - Allocate resources to an organization - Select Network and Services
  35. Click Next if you don’t need an edge gateway to an external network
    1. An edge gateway is needed to provide access to other internal and external (internet) networks.
      vCloud Director - Allocate resources to an organization - Create a new edge gateway
  36. Enter a name for the new Organization VDC and click Next
    vCloud Director - Allocate resources to an organization - Name this Organization VDC
  37. Click Finish
    vCloud Director - Allocate resources to an organization - Ready to Complete

At this point you should be able to publish a catalog or create a new VM from scratch in a completely isolated environment.  You can add an external network or utilize a VXLAN with additional configurations to begin to connect your network to an outside world.

Hope this helps!

msSQL Server 2008 R2 – Restore Failed

Symptom: When restoring a database in Microsoft SQL Server Management Studio, you receive the following dialog box similar to something below:

Restore failed for Server ‘myserver’. (Microsoft SqlServer.SmoExtended)

Additional Information
System.Data.SqlClient.SqlError: File ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mydatabase.mdf’ is claimed by ‘mydatabase_FG1′(3) and ‘mydatabase'(1).  The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

Solution: On the Restore Database – mydatabase window inside of Microsoft SQL Server Management Studio:

  1. Click on the Options page.
  2. Underneath Restore the database files as: label, make sure all of the “Restore As” values are unique.  In this case, I renamed each *.mdf file to it’s “Original File Name” column value.
    1. Before
      1. Restore Database msSQL
    2. After
      1. Restore Database msSQL
  3. Click OK

Viola!  Your database should now successfully restore 🙂

Lync 2010 – Cannot impersonate user for data source ‘CDRDB’. (rsErrorImpersonatingUser) error

Symptom: You receive the following error when browsing to the following page in the Reports Viewer: https://mymachine.mydomain/ReportServer/Pages/ReportViewer.aspx?%2fLyncServerReports%2fReports+Home+Page&rs:Command=Render  Alternatively, you receive this error when you go to https://mymachine.mydomain.com/Reports/ and click on LyncServerReports and then Reports Home Page.

Note: the solution below applies to the QMSDB as well.

  • An error has occurred during report processing. (rsProcessingAborted)
    • Cannot impersonate user for data source ‘CDRDB’. (rsErrorImpersonatingUser)
      • Log on failed. Ensure the user name and password are correct. (rsLogonFailed)
        • For more information about this error navigate to the report server on the local server machine, or enable remote errors

Solution: For whatever reason, my service account I created actually had the incorrect password to login to the ‘CDRDB’ data source.  To fix/troubleshoot this, follow the steps below:

  • Head over to https://myserver.mydomain/Reports (myserver being the server with the reporting services on it)
  • Click on LyncServerReports
  • Click on Reports_Content
  • Click on CDRDB
  • Under Credentials stored securely in the report server, type in the username/password are want to use to connect to the SQL server, and then click the Test Connection button.  If you should see a Connection created successfully. message, you are good to go.  If you see a Log on failed. Ensure the user name and password are correct. message, ensure that the account you setup has the correct username/password in active directory, is Unlocked in Active Directory (due to bad password attempts), and that it has permissions to the databases mentioned in the Connection string on the same page.

Lync 2010 – The feature: “Customizing security roles” is not supported in this edition of Reporting Services.

Symptom: You receive the following log/error when on the Deploying Monitoring Server Reports step for Lync 2010.

> Deploying Monitoring Server Reports… This might take a few minutes.

The Monitoring Server is using SQL instance “mysqlserver.mydomain”.

The data source is using SQL instance “(local)”.

The following URL will be used for deployment: https://myserver.mydomain:443/ReportServer

SQL Server logon credentials for “MYDOMAIN\MYUSER” already exist. Use the existing logon credentials.

“[QoEMetrics]” role “[ReportsReadOnlyRole]” has already assigned to “MYDOMAIN\MYUSER”.

“[LcsCDR]” role “[ReportsReadOnlyRole]” has already assigned to “MYDOMAIN\MYUSER”.

Start to deploy reports…

Report: Monitoring Dashboard published successfully with no warnings

Report: Call Detail Report published successfully with no warnings

Report: Call List Report published successfully with no warnings

Report: Reports Home Page published successfully with no warnings

Report: Media Quality Summary Report published successfully with no warnings

Report: Media Quality Metrics Distribution Report published successfully with no warnings

Report: Media Quality Comparison Report published successfully with no warnings

Report: Device Report published successfully with no warnings

Report: Server Performance Report published successfully with no warnings

Report: Call Leg Media Quality Report published successfully with no warnings

Report: Call Leg Media Quality Trend Report published successfully with no warnings

Report: Server Media Quality Trend Report published successfully with no warnings

Report: QoE Call Detail SubReport published successfully with no warnings

Report: Location Trend Report published successfully with no warnings

Report: Location Report published successfully with no warnings

Report: Peer-to-Peer Voice and Video Report published successfully with no warnings

Report: Peer-to-Peer IM Report published successfully with no warnings

Report: Conference Activity Report published successfully with no warnings

Report: PSTN Conference Summary Report published successfully with no warnings

Report: Conference Summary Report published successfully with no warnings

Report: Peer-to-Peer Activity Summary Report published successfully with no warnings

Report: User Activity Report published successfully with no warnings

Report: Peer-to-Peer Session Detail Report published successfully with no warnings

Report: Conference Detail Report published successfully with no warnings

Report: Diagnostic Report published successfully with no warnings

Report: User Registration Report published successfully with no warnings

Report: Failure Distribution Report published successfully with no warnings

Report: Top Failures Report published successfully with no warnings

Report: Failure List Report published successfully with no warnings

Report: Peer-to-Peer Activity Diagnostic Report published successfully with no warnings

Report: Conference Diagnostic Report published successfully with no warnings

Report: Call Admission Control Report published successfully with no warnings

Report: P2P Summary SubReport published successfully with no warnings

Report: Conference Summary SubReport published successfully with no warnings

Report: Call Diagnostic Summary Report published successfully with no warnings

Report: IP Phone Inventory Report published successfully with no warnings

Report: Response Group Usage Report published successfully with no warnings

Report: Response Group Call List Report published successfully with no warnings

The feature: “Customizing security roles” is not supported in this edition of Reporting Services. —> Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedException: The feature: “Customizing security roles” is not supported in this edition of Reporting Services.

An error occurred when deploying Monitoring Server Reports. For details, see the log. 

Solution: Make sure the SQL server is running at least Standard or Enterprise.

To find out which version of msSQL you are running (version and 32-bit or 64-bit), follow this guide here: http://jackstromberg.com/2013/01/how-do-i-find-out-if-my-sql-server-is-32-bit-or-64-bit/

To find out how to upgrade your msSQL instance/server, follow this guide here:

SQL Server 2008 R2 – Updating a msSQL instance/server

Scenario: You have accidentally installed SQL Server 2008 R2 Workgroup and you need SQL Server 2008 R2 Standard.  Obviously, you don’t want to rebuild your server from the ground up and have extended downtime, so an upgrade option would be sweet.  Luckily, Microsoft has come to the rescue with an easy way to update your msSQL server/instances.

Solution: Go to the Microsoft Licensing center or grab your SQL Server disk/installation media of the correct version.  I.e. if I was running standard, I would grab the enterprise disk to upgrade.  Once you have the disk, follow these steps:

  1. Find your installation media and double click on Setup.exe
  2. When the SQL Server Installation Center window comes up, click on Maintenance
  3. Click on Edition Upgrade
  4. On the SQL Server 2008 R2 Setup – Setup Support Rules window, click OK
  5. Click Next > on the Upgrade the Edition for SQL Server 2008 R2 window
  6. Click Next > on the Enter a product key: page.
  7. Click I accept the license terms. and then click Next >
  8. Select the instance of the SQL Server you wish to upgrade and then click Next >
  9. Click Next >
  10. Click Upgrade

To verify your SQL Server upgraded, follow this guide here to pull your msSQL version: http://jackstromberg.com/2013/01/how-do-i-find-out-if-my-sql-server-is-32-bit-or-64-bit/

How do I find out if my SQL server is 32-bit or 64-bit?

Want to know what version of SQL server your have running? All we need to do is execute one SQL command and away we go 🙂

  1. Go to your SQL server
  2. Open up the Microsoft SQL Server Management Studio
    1. Start->All programs->Microsoft SQL Server 2008 R2->SQL Server Management Studio
  3. Login with your user
  4. Click the New Query button
  5. Execute the following query
    1. SELECT SERVERPROPERTY(‘edition’)

How do I setup msSQL to use SSL?

Want to encrypt your msSQL traffic?  Here is how to do it.

  1. Request/Install a certificate in the Windows Certificate store
    1. If you are on a domain with a certificate authority, you can do this by clicking Start->run->mmc
    2. Click File->Add/Remove Snap-in
    3. Select Certificates, click the Add button, select Computer account, click OK, click Finish the wizard.
    4. Expand Certificates (Local Computer) and navigate to Personal->Certificates
    5. Right click All Tasks -> Import… or Request New Certificate (depending on what you want to do)
  2. Once you have finished installing the certificate, click Start->All Programs->Microsoft SQL Server 2008 R2->Configuration Tools->SQL Server Configuration Manager (Launch SQL Server Configuration Manager)
  3. Expand SQL Server Network Configuration
  4. Right click on “Protocols for MSSQLSERVER” (or whatever your instance name is on the left side) and click Properties
  5. On the Flags tab, you can optionally set “Force Encyrption” to Yes, which will make your msSQL server only allow connections that are secure.  You may skip this step if you don’t want to do this.
  6. Click on the Certificate tab.
  7. Select your certificate that you installed in Step 1 in the Certificate dropdown box.
  8. Click OK
  9. Click on SQL Server Services
  10. Right click on the SQL Server (MSSQLSERVER) service and click Restart (MSSQLSERVER==your instance name)

That’s all that’s to it.  Note, if you receive an error that the service cannot run (I forgot what the original error was), try disabling the VIA Client Protocol underneath SQL Native Client 10.0 Configuration inside of SQL Server Configuration Manager.

As a reference, you can find the official Microsoft KB article on how to do this here.