ProClarity Dashboard Server Migration to a New Database Server


What would you do if you needed to change your ProClarity Dashboard Server database instance name?  Microsoft BI product installations have a separate data tier option.  In other words, the database can be on a different machine than the actual product (like ProClarity).  Occasionally, you may have a need to change your database server or move your data to a new database instance on the same machine.  Maybe you have outgrown your current hardware and need a larger SQL Server machine.  In any case, you are going to have to backup and restore the database, ensure that you have the correct logins and permissions, and change any application configurations to use the new database instance.  Let’s look into these items and see how to make the changes for ProClarity Dashboard Server.

Preparing the Database Machine

There is only one database for ProClarity Dashboard Server. I posted previously about Database Migrations – Configure Logins and Permissions.  This post gives you information about:Backup and Restoration of the SQL Database (Data contained in each database).

After you complete the work above, your new database machine is ready and the data and access to the configuration data is ready.

 

Configure the ProClarity Dashboard Server

Step 1. Make a copy of the data.config file.

Find the ProClarity Dashboard Server data.config file.  It is located in the ProClarity Dashboard folder.  By default, it is located here:

C:\program files\proclarity\proclarity dashboard server\config

Change directory by executing the following command:

cd C:\program files\proclarity\proclarity dashboard server\config

Path

Copy the data.config file and rename the copy to data.config.original.  The data.config.original file is a backup of this file should we need to restore it.The original file (and it’s copy) will look something like this:

<snip>

<connectionStrings configProtectionProvider=”DataProtectionConfigurationProvider”>
<EncryptedData>
<CipherData>
<CipherValue>ExampleReallyLongCipherValueStringThatIsShortenedAndReadable</CipherValue>
</CipherData>
</EncryptedData>
</connectionStrings>

</snip>

Step 2. Unencrypt the data.config file

Change directory by executing the following command:

cd C:\program files\proclarity\proclarity dashboard server

Run this command to unencrypt the data.config file:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis –pd “connectionStrings” -app “/Dashboard”

Your data.config file will now look something like this:

<snip>

<connectionStrings>
<add name=”Default” connectionString=”server=OldServerDB;database=ProClarity_DashboardServer;User ID=passqladmindashboard;Password=P4ssw0rd”
providerName=”System.Data.SqlClient” />
<add name=”AesEncryptionKey” connectionString=”CAa4WSnHT1uq6EMMnjl6xOuenv9mmS0AWrxMWXhHVQY=” />
</connectionStrings>

</snip>

Step 3. Change the database name

Remember that the data.config file is in the config directory.

Change the database name from OldServerDB to NewServerDB

Your data.config file will now look something like this:

<snip>

<connectionStrings>
<add name=”Default” connectionString=”server=NewServerDB;database=ProClarity_DashboardServer;User ID=passqladmindashboard;Password=P4ssw0rd”
providerName=”System.Data.SqlClient” />
<add name=”AesEncryptionKey” connectionString=”CAa4WSnHT1uq6EMInjl6xOuenv9mmS0AWrxMWXhHVQY=” />
</connectionStrings>

</snip>

Step 4. Encrypt the file

Make sure you are in the “C:\program files\proclarity\proclarity dashboard server” before you execute this command.  Run this command to encrypt this file

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -pe “connectionStrings” -app “/Dashboard” -prov “DataProtectionConfigurationProvider”

Your data.config file will be encrypted again with a new <CipherValue>

The connection information in the data.config file should now be encrypted and look something like the data.config.original file with different encrypted values.

Step 5. Reboot the server

Restart the ProClarity Dashboard Server and reboot the server to insure that the application can connect to the database on the new server.

If there was an issue, you can restore the original file by deleting the current data.config file and renaming the data.original.config file to data.config and rebooting the server once again.

Notes:

For more information on how to encrypt and decrypt the config file, see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000005.asp

If you try to copy and paste the commands from this post, sometimes the “minus” sign and “dash” symbol get superimposed.  It is best to type the command instead of using cut and paste.

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

del.icio.us Tags: , , , , ,
Advertisements
Posted in Microsoft BI, ProClarity | Tagged , , , , , | Leave a comment

ProClarity Analytics Server Migration to a New Database Server


What would you do if you needed to change your ProClarity Analytics Server database instance name?  Let’s take a step back and start with some background.  Microsoft BI product installations have a separate data tier option.  In other words, the database is on a different machine than the actual product (like ProClarity).  Occasionally, you may have a need to change your database server or move your data to a new database instance on the same machine.  Perhaps you have a leased server and the lease expired.  Maybe you have outgrown your current hardware and need a larger SQL Server machine.  In all of these cases, you are going to have to backup and restore the database, ensure that you have the correct logins and permissions, and change any application configurations to use the new database instance.  Let’s look into these items and see how to make the changes for ProClarity Analytics Server.

Preparing the Database Machine

There is only one database for ProClarity Analytics Server.

I posted previously about Database Migrations – Configure Logins and Permissions.  This post gives you information about:

Backup and Restoration of the SQL Database (Data contained in each database).

After you complete the work above, your new database machine is ready and the data and access to the configuration data is ready.

Configuring ProClarity Analytics Server

Open the ProClarity Analytics Server Administration Tool

clip_image001[1]

Right-click the name of the PAS server & select Properties.

clip_image002[1]

In the “SQL server” box enter the name of the new SQL Server.  For example, change the SQL server name from “BI-VPC” to “NewDB“.  Better yet, create a SQL Alias so you will have an easier way to make this change in the future.  ProClarity Analytics Server will restart after selecting OK.

While not always required, I have found it beneficial to Reboot the ProClarity Analytics Server.  I suggest doing this as part of your process.

Navigate to the ProClarity Analytics Server web site to insure that the applications still renders all of your Briefing Books. FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

del.icio.us Tags: , , , , ,
Posted in Microsoft BI, ProClarity, Setup, SQL Server | Tagged , , , , , , | 1 Comment

Kerberos SPN Generation Setup Tool – Generate SPNs


To date, I’ve written a series of blog posts that describe how to use the “Input” section of the “Kerberos SPN Generation / Setup Tool”.  This is a continuation of the series of blog posts “Kerberos SPN Generation / Setup Tool” that describe how to use the Kerberos SPN Generation Setup Tool Beta for Kerberos Constrained Delegation with Integrated Windows Authentication for Microsoft BI tools.  The next short series of posts will discuss the “Generate SPNs” process.  You can download the Kerberos SPN Generation Setup Tool Beta at FUTURESULTS, LLC.

Main Menu / Navigation – SPN Generation Section

GenSPNs

Generate SPNs

You made it through all of the details on how to enter data into each of the input tabs.  This section’s example will consist of only the “PAS Tab” example given previously.  In other words, pretend that we have only entered information into the “Common Tab” and the “PAS Tab”.  While this example is for a ProClarity setup, it works the same with all input tabs that are complete.

For review purposes, the “PAS Tab” entries look like this:

PASEntry

Now that we have completed the input section, go back to the “Main Menu / Navigation” tab and select the “Generate SPNs” link.  You must be connected to the domain prior to selecting the “Generate SPNs” link.  If there are any errors when we select “Generate SPNs”, you will be notified either by pop up messages, messages in the “Messages” section, or non-green “traffic light” symbols by the appropriate input sections.  You must clear all errors prior to generating SPNs.

When the “Generate SPNs” traffic light is green, you have successfully generated SPNs.  The tool interrogates your domain (that is why you must be connected to your domain) and creates the proper SPNs.  It is that easy!

The next step is to review the SPN Output via the “SPNOutput” tab.  Notice in this case that there are SPNs Suggested to Add (column E).  Your implementation may have more or less SPNs to add based on information that is already in your domain.  In this case, the SPNs associated with “sql_analysis” already resided in the domain so there was no need to add SPNs for this domain account.

“SPNOutput” Tab Example.

SPNs2AddB4DelegationSPNTab

If we look at the “Delegation” tab, notice that the delegation needed does not exist (cell D8).  More on Delegation in the future.

Delegation Tab Example.

SPS2AddB4DelegationDelTab

Export SPNs to Add

Since there were SPNs to add listed in the “SPNOutput” tab, we need to Export SPNs.  Go back to the “Main Menu / Navigation” tab and select the “Export SPNs to Add” link.  This link creates a file with the commands needed to add the appropriate SPNs.  You must be a Domain Administrator to run the batch file on the Domain Controller.

The file was purposely created with a .txt extension.  Many times this file must be emailed to another person with Domain Administrator rights on the domain controller.  Email systems normally block files with .bat extensions (that’s why the file is saved as a .txt).  Once you copy the file to the domain controller, change it to a .bat extension.  In this example, we would rename the file to “SPNs2ADDInput.bat”.

Export SPNs to Add Example (-L means List, -A means Add).

SPNs2AddInput

When the file is on the domain controller you can run it and redirect the output to a file if you like.  For example you would run this file and redirect it’s output as follows:

SPNs2AddInput.bat > SPNs2AddOutput.txt

The output file contains listings of what the service accounts looked like before adding the new SPNs as well as after the SPNs are added.  In addition, ensure that each SPN was successfully added by searching the output file for “Updated object” after each add SPN command.  If there was any kind of error or you mistakenly typed in the wrong service account, etc. use the “Export SPNS to Remove (Undo)” commands in the next section.

Example SPNs to Add Batch File Results.

SPNs2AddOutput

Export SPNs to Remove (Undo)

Hopefully, this section should be self-explanatory.  Basically it works exactly like the “Export SPNs to Add” section above except it removes SPNs instead of adding SPNs.  The process is similar and should always be done in conjunction with the “Export SPNs to Add” process.  In other words, you should always select this link immediately after saving the “Export SPNs to Add” link.  In this way, you can assure that the Remove file contains the same information as the Add file.  If anything goes wrong with the “Export SPNs to Add” process, you can remove whatever was done in the “Add” batch file.

Use this process to clean up mistakes (if an error exists).  The general steps are:

  1. Go to the Main Menu / Navigation Tab
  2. Select the “Export SPNs to Remove (Undo)” link (immediately after selecting the “Export SPNs to Add” link)
  3. Copy the file to the Domain Controller
  4. Rename the file to “SPNs2RemoveInput.bat
  5. Run command (ONLY IF NEEDED) SPNs2RemoveInput.bat > SPNs2RemoveOutput.txt
DO NOT RUN THIS FILE AFTER SUBSEQUENT CHANGES TO YOUR DOMAIN CONTROLLER HAVE BEEN MADE.  In other words, this command will remove the SPNs that were added only if subsequent changes have not been made.  If you made additional SPN changes, it could remove a SPN that is now used for another purpose.  The “Remove” process is no longer relevant once other SPN changes are made to the domain.  If you have any question about other changes, do not use this batch file and seek help from a knowledgeable source to remove SPNs manually.
Export SPNs to Add Example (-L means List, -D means Delete).

SPNs2RemoveInput

Example SPNs to Remove (Undo) Batch File Results.

SPNS2RemoveOutput

Review SPN Information

Now that we have completed adding SPNs to your domain, go back to the “Main Menu / Navigation” tab and again select the “Generate SPNs” link.  The tool interrogates your domain and creates additional SPN suggestions if needed.  In this case, it should find that you have added the appropriate SPNs and nothing additionally needs to be created.  You can validate this by reviewing the “SPNs to Add” section (column E) via the “SPNOutput” tab.

You can review the SPNs in your domain for each account that is entered into the spreadsheet (columns A & B).  Just as an FYI, some of the SPNs were generated automatically (in this case HOST and TERMSRV).  Other SPNs were entered manually via the SPN tool.

SPNOutput Tab – Review output information.  Notice there are no SPNs to Add.

SPNs2AddAfterDel-SPNTab

Other SPN Generation Tips and Tricks

Domain Controller Replication

Many domains use replication between domain controllers.  This replication may take several minutes to occur.  If you add a new SPN, you may need to wait several minutes to rerun the spreadsheet process in order to do the SPN review process.

The point of this tool is to help you generate SPNs correctly based on parameters that can be gathered by administrators.  The process outlined in these blog posts allows you to have good documentation, reduce issues (like duplicate SPNs), and have a tool to check and troubleshoot your configuration later in case additional changes were made to your domain (other product setups).

Export SPNs to Remove (Undo)

Do not try to generate the “Undo” file at a later date if any domain changes were made.  In other words, do not use the tool to add SPNs and then later select the “Generate SPNs” link in the tool and then try to create the “Undo” file.  The only way that the “Undo” file works is if it is generated at the same time as the “Add” file and no subsequent changes are made to SPNs in the domain.

What additional features would you like to see in a Kerberos SPN setup tool?  Leave your suggestions below.

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Website are both created by Robert Lambrecht.

Posted in Kerberos, Microsoft BI, Microsoft BI - Security - Kerberos, Security | Tagged , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , | 3 Comments

PerformancePoint Monitoring Server – Move Configuration Database


What would you do if you needed to change your PerformancePoint 2007 Monitoring Server Configuration database instance name?  Let’s take a step back and start with some background.  Microsoft BI product installations have a separate data tier option.  In other words, the database is on a different machine than the actual product (like PerformancePoint).  Occasionally, you may have a need to change your database server or move your data to a new database instance on the same machine.  Perhaps you have a leased server and the lease expired.  Maybe you have outgrown your current hardware and need a larger SQL Server machine.  In all of these cases, you are going to have to backup and restore the database, ensure that you have the correct logins and permissions, and change any application configurations to use the new database instance.  Let’s look into these items and see how to make the changes for PerformancePoint.

Preparing the Database Machine

I posted previously about Database Migrations – Configure Logins and Permissions.  This post gives you information about:

Backup and Restoration of the SQL Database (Data contained in each database).

Since this post is about PerformancePoint Monitoring Server, Microsoft has posted an article specifically about how to Backup and restore Monitoring Server databases.

After you complete the work above, your new database machine is ready and the data and access to the configuration data is ready.

Configuring the PerformancePoint Server

Web Configuration File Changes

There are three web.config files on the PerformancePoint Monitoring Server that must be updated to point to the new server name.  The default locations for the web.config files are listed below:

Application / Service Default File Location
SharePoint: C:\Inetpub\wwwroot\wss\VirtualDirectories\80
Monitoring WebService: C:\Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\WebService
Preview Site: C:\Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\Preview

Find each web.config file above and do a search for “PPSMonitoring”.

You should find a line that looks similar to this:

<add key=”Bpm.MonitoringConnectionString” value=”Data Source=OLD_SERVER_NAME;Initial Catalog=PPSMonitoring;Integrated Security=True” />

Replace OLD_SERVER_NAME with NEW_SERVER_NAME.

Example Configuration File Change

If your old database server name was “SQLServerOld” and your new database server name is “SQLServerNew”, you would do the following:

Current web.config file:

<add key=”Bpm.MonitoringConnectionString” value=”Data Source=SQLServerOld;Initial Catalog=PPSMonitoring;Integrated Security=True” />

Change the “Data Source” to the new database server name as shown below:

<add key=”Bpm.MonitoringConnectionString” value=”Data Source=SQLServerNew;Initial Catalog=PPSMonitoring;Integrated Security=True” />

These server name values may or may not be Fully Qualified Domain Names.

Complete the Process

After making the configuration file changes, go to the command prompt and do an “IISReset”.

You may or may not have to redeploy existing Dashboards to SharePoint.  If they still work, then you won’t have to refresh them.

Notes

Some of the information on the web file configuration changes came from this Microsoft TechNet forum post.

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

Posted in Microsoft BI, PerformancePoint, Setup | Tagged , , , , , , , , , , , , , | Leave a comment

SharePoint 2007 and 2010 – Move all Databases


On June 17, 2010 Microsoft updated the article – Move all databases (Office SharePoint Server 2007) that is referenced in my blog post “Setting up a Local SQL Alias on a SharePoint Farm”.  They also added instructions for SharePoint Server 2010.  The new process basically follows the recommendation that I have made in the prior blog post “Setting up a Local SQL Alias on a SharePoint Farm”.  Glad to see Microsoft took direction from FUTURESULTS!  So basically, the long process that was originally posted by Microsoft has been updated for SharePoint 2007 and 2010 and follows my recommendations (posted on June 7, 2010)!

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

del.icio.us Tags: , , , , , , , , ,
Posted in Microsoft BI, SharePoint | Tagged , , , , , , | Leave a comment

Database Migrations – Configure Logins and Permissions


Most Microsoft BI product installation have a separate data tier.  In other words, the database is on a different machine than the actual product (like SharePoint, PerformancePoint, ProClarity, …).  Occasionally, you may have a need to change your database server.  Perhaps you have a leased server and the lease expired.  Maybe you have outgrown your current hardware and need a larger SQL Server machine.  Each BI product has a lengthy methodology in order to change out the database server.   I’m blogging about processes to help you get this done in an easier fashion.  Setting up a Local SQL Alias on a SharePoint Farm can be used for many of these products.  I just happened to use SharePoint in my previous example.  Once you understand how easy it is to use a SQL Alias, then the next steps are to change your database machine.

There are many elements to consider when migrating a database from one server to another.

  • New Server Setup (installation of SQL Server, relevant security settings, DNS settings, …).
  • Backup and Restore the SQL Database (Data contained in each database).
  • Configure logins and permissions to match the old server (data contained in the old server – like server level user accounts).
  • Change configuration settings on individual products

In order to do a database migration, these items must be considered.  New server setup is beyond the scope of this topic.  I will assume that you are able to install SQL Server on a new machine.  Also, there are many resources that discuss how to backup and restore a SQL Server database.

Additionally, configuration files changes for products like ProClarity Analytics Server (PAS), ProClarity Dashboard, SharePoint, and PerformancePoint Server M&A will be examined in  future posts.

This article will focus specifically on configuring logins and permissions to match the old server.

Configure Logins and Permissions

Try this tool: SQL permissions v2.0

SQL permissions is a freeware tool that generates a customizable T-SQL script to configure logins and permissions on one server to match another.  SQL permissions lets you move one login, or a group of logins and apply permissions across all databases on the target server, or a single database.

  • Generates T-SQL scripts to move logins and permissions from one server to another
  • Moves either a single log-in or group of logins
  • Generates permissions on a single database or across all databases
  • Provides a simple view of logins and permissions

Custom SQL Login Script

Each SQL login on the current database server must be created on the new SQL server.  Download this Word document and copy the script it contains into SQL Server Management Studio.  Execute this script on the current server:

Custom SQL Login Permission Script

(Note: script origin is unknown)

This script will create two stored procedures in the master database: sp_hexadecimal and sp_help_revlogin.

Once the two stored procedures are completed you need to execute the sp_help_revlogin stored procedure; a parameter is optional.  If no parameter is included it will create a result set that consists of a set of T-SQL statements that can be used to create every login.  If a parameter is included it needs to be for an existing Login on the current server and only that Login will be created.

Example

For this effort we need to migrate the following accounts (see below).  These accounts may be SQL or Domain accounts depending on your setup.  In this example, I will show both types of accounts used in setting up PAS, PAS Dashboard, SharePoint, SSAS access, and SSRS access.  Your accounts may vary based on your setup.  The point is to migrate your SQL Server service accounts from the current database server to the new database server.

Example Accounts:

  • PAS_DB (Account that was used for Database access when setting up PAS)
  • PASsqlDB_Dashboard (Account that was used for Database access when setting up PAS Dashboard)
  • domain\MOSS_DB (Account that was used for Database access when setting up SharePoint)
  • domain\SSAS_Access_Account (Group that is used for access to SSAS)
  • domain\ssrswebapp (Web Application Account for the SSRS server)

To run the script for the PAS_DB account, do the following:

Exec sp_help_revlogin ‘PAS_DB’

Then take the T-SQL result of this script and execute it on the new SQL Server to create the login.

Do the same process for the other logins.

Documentation

Document each account and its password for the applications listed above (PAS, ProClarity Dashboard, PPS M&A, SharePoint, …) used to get a connection to a SQL database.

There is one and only one database for each of these applications: PAS, ProClarity Dashboard & SharePoint M&A; however there are multiple databases for SharePoint.  The databases for each application should be discovered and documented to insure that none are missed.

Your new database server should now be configured correctly and have the proper SQL databases and account access.  The next step is to make any necessary configuration changes for each BI product.  This will be covered in a future post.

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

del.icio.us Tags: , , , , , , ,
Posted in Microsoft BI, PerformancePoint, ProClarity, Setup, SharePoint, SQL Server, SSRS | Tagged , , , , , , , , , , , , , , , , , , , , , , , | 3 Comments

PowerPivot in Microsoft Excel 2010


PowerPivot for Excel is an Excel 2010 add-in that allows users to pull data from multiple sources, mash them up, and then build reports using regular pivot tables. You can even share these reports with others in Microsoft SharePoint (via PowerPivot for SharePoint). In this demo, Julie Strauss, Program Manager for Microsoft SQL Server Analysis Services, shows just how easy it is to get a better view into your data.

Watch the video at: PowerPivot in Microsoft Excel 2010

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

del.icio.us Tags:
Posted in Microsoft BI, PowerPivot | Tagged | Leave a comment