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:
(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.
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.
- 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.
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.
del.icio.us Tags: Microsoft, BI, Setup, SQLServer, SharePoint, PerformancePoint, ProClarity, FUTURESULTS