Getting Started

1. Download and unblock RSDD module. See Unblocking RSDD below
2. Create an empty SQL Server database. For example "RSDD"
3. Run the install.sql installation script to create tables
4. Copy RSDD PowerShell module folder to directory listed in your $env:psmodulepath
5. Modify RSDD.psm1.config connection string settings to point to the server and database created in step 1
6. Modify RSDD.psm1.config encryption key password. Note: This is the password used to encrypt connection strings stored in SQL Server table
7. Add entries for each database server to dbms_lku table. See Specifying Connection Strings
8. Optionally add exclusions to exclusion_lku. See Using Exclusions
9. Optionally use add-dbms function to add new entries to dbms_lku. See get-help add-dbms
10. Execute RSDD using invoke-RSDD.ps1 script or the invoke-RSDDThreaded.ps1 script. Modify the MaxRunspaces param in RSDD.psm1.config as needed. NOTE: Invoke-RSDDThreaded.ps1 cannot be run from PowerShell_ISE!!
11. Optionally create a SQL Agent with the following job steps. Note: job truncates and loads collections

Step 1 Execute SQL task:
TRUNCATE TABLE dbo.sql_columns
TRUNCATE TABLE dbo.sqlkeycolumn_usage
TRUNCATE TABLE dbo.sqlreferentialconstraints
TRUNCATE TABLE dbo.sqltableconstraints
TRUNCATE TABLE dbo.error_log
Step 2 CmdExec:
C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.EXE -command "C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\RSDD\Invoke-RSDDThreaded.ps1 2>&1"

Specifying Connection Strings

See http://www.connectionstrings.com/
Note: USE OLE DB .NET Connection string syntax

Oracle

Provider=OraOLEDB.Oracle;Data Source=XE;User Id=RSDDUSER;Password=RSDDUSER;

• NOTE: You'll need to install the Oracle Client. See http://sev17.com/2010/02/querying-oracle-from-powershell-part-1/ *

SQL Server enum all databases (set has_databases = 1):

Server=Z003\SQL2K8;Trusted_connection=yes;database={0};Provider=SQLNCLI10;

SQL Server enum single database pubs:

Server=Z003\SQL2K8;Trusted_connection=yes;database=pubs;Provider=SQLNCLI10;

Using Exclusions

Adds rows to exclusion table for each exclusion. For example to exclude master
database on all servers:

INSERT exclusion_lku VALUES('sqlserver','ALL','master')

To exclude a database from a single server specify the server name instead of ALL.

Privileges needed:

SQL Server 2005 or higher

--Server level permission
GRANT VIEW ANY DEFINITION TO RSDD_USER;
--For each database
CREATE USER RSDDUSER FOR LOGIN RSDDUSER;

SQL 2000

--For each database
EXEC spgrantdbaccess RSDDUSER, RSDD_USER;

Oracle

GRANT CREATE SESSION TO RSDD_USER;
GRANT SELECT ANY DICTIONARY TO RSDD_USER;

Reports

PowerPivot makes a great front-end for the collected data
See http://www.powerpivot.com/

Unblocking RSDD

By default scripts downloaded from browsers are blocked for execution. You'll need to unblock RSDD before unzipping file by right-clicking zip file and selecting unblock: