SQLServer Implementation Scripts

The following implementation scripts must be run on your Responder SQLServer database to add the correct tables, views, and indices. When performing a new Responder implementation execute ONLY the Implementation Scripts. Do NOT execute any Upgrade scripts.

Responder requires two databases: Responder and Archive. Some scripts are run on the Responder database and some on the Archive database. In the list below, the database on which the script should be executed is in parentheses.

01_RxTables.sql (Responder)
02_ArcJoinViews.sql (Responder)
03_ArchiveTables.sql (Archive)
04_GenerateGrants.sql (Responder and Archive)
05_SnapshotIsolation.sql (Responder and Archive)

Create a new database on your SQL Server instance to house the Responder database. An existing database can be used, but it might be easier to manage (backups, disk storage, etc.) if you create a new database dedicated to the Responder tables.
Create the RESPONDER user in the SQL Server instance (this is your database owner). Grant this user access to the database discussed in Step 1.
Open a new query, connecting to the database discussed in Step 1 as the new RESPONDER user.
Execute 01_RxTables.sql. This script creates the database tables required by Responder.
The 02_ArcJoinViews.sql script creates the views that are used to display Responder data in ArcMap. Before executing this script, you may need to modify the feature class names within it to match those in your geodatabase. Open the script and ensure that the feature class names in the script match the corresponding feature class names in your geodatabase. The ownership is set to ArcFM and needs to be modified if you are not using ArcFM (such as SQL users.) Execute 02_ArcJoinViews.sql.

TIP: If your SDE tables reside in a database other than SDE, you will need to edit the script to set the correct database name. For example, if your database is named DB, then the following would change:FROM SDE.SDE.GDB_ITEMSIt would become:FROM DB.SDE.GDB_ITEMSBe sure to search the script for all instances of SDE.SDE. and modify to include the correct database name.
Create a new database on your SQL Server instance to house the Archive database. An existing database can be used, but creating a new database dedicated to the Archive tables is recommended, since it will be easier to manage (backups, disk storage, etc.).
Execute 03_ArchiveTables.sql on the Archive database. This script creates the tables used by Archive Services and Archive Explorer.
Open 04_GenerateGrants.sql in a text editor (e.g., Notepad). This script generates a grant script that must also be run on both the Responder and Archive databases. Before executing, you must determine the roles and the permissions to assign them. The roles must exist in both the Responder and Archive databases. You can assign permissions to several different roles. Look for the following lines of code in the script:
```
-- rx_editor role can edit data in responder or archive schema
select 'grant insert, update, delete, select on ' 
+ table_name + ' to rx_editor ;' 
from information_schema.tables where table_type = 'base table' and table_schema = 'responder'
go
select 'grant select on ' 
+ table_name + ' to rx_editor ;' 
from information_schema.tables where table_type = 'view' and table_schema = 'responder'
go
-- rx_viewer role can view data in responder schema
select 'grant select on ' 
+ table_name + ' to rx_viewer ;' 
from information_schema.tables where table_type = 'base table' and table_schema = 'responder'
go
select 'grant select on ' 
+ table_name + ' to rx_viewer ;' 
from information_schema.tables where table_type = 'view' and table_schema = 'responder'
go
```
NOTE: The table_schema = 'responder' portion above refers to the database owner (Responder).

The first four lines assign permissions to a role for tables. The second set of four lines assign permissions to a role for views (views can only be assigned the Select permission). The last eight lines perform the same tasks as the first half, but for views rather than tables. You will need a set of lines for each role to assign permissions to tables and views. The next few steps describe how to perform the modifications you can make for each role.

NOTE: You can execute the same script for the Responder database and Archive database. The script MUST be executed on both databases.
The "grant insert, update, delete, select" portion determines which permissions are to be assigned. Modify these permissions as necessary.
Replace "rx_editor" with the name of the role to assign the permissions. There are two possible roles: rx_editor and rx_viewer.
When you've finished adding statements for roles, close and save the file.
Open 04_GenerateGrants.sql in SQL Server Query Analyzer.
Right-click in the top grid (with the query) and select Results to Text.

Execute 04_GenerateGrants.sql. This script only generates a script that sets permissions, it does not execute anything.. Below is an example of the resulting script:

grant insert, update, delete, select on RX_TAG_GROUND_STEPS_HISTORY to rx_editor ;
grant insert, update, delete, select on RX_TAGS_AND_GROUNDS to rx_editor ;
grant insert, update, delete, select on RX_TAGS_AND_GROUNDS_HISTORY to rx_editor ;
grant insert, update, delete, select on RX_TRUCKS to rx_editor ;
grant insert, update, delete, select on RX_TRUCKS_HISTORY to rx_editor ;
grant insert, update, delete, select on RX_USERS to rx_editor ;
(41 row(s) affected)
                                                                                                                                                                
--------------------------------------------------------------------------------------- 
grant select on Current_Outages to rx_editor ;
grant select on RX_CREW_ASSIGNMENT_COUNT_VIEW to rx_editor ;
grant select on RX_CREW_INCDEV_DPD_VIEW to rx_editor ;
grant select on RX_CREW_INCDEV_FUS_VIEW to rx_editor ;

Copy your script results from Step 14 and paste them into a new query. Remove any empty lines from the script results, the line containing only dashes, and the line indicating the number of affected rows. Your new query should only include lines that start with "grant insert, ..." .
Execute the copied query to assign permissions to roles. The permissions assigned apply to all Responder tables and views.

IMPORTANT: The final script (05_SnapshotIsolation.sql) requires that the user executing it have sysadmin privileges. To execute the script, only the connection running the script should connect to the database. If any other connections are present, the query will try to execute indefinitely.
Lastly, execute 05_SnapshotIsolation.sql. You will need to execute this script on the both the Responder and Archive tables. This script configures your SQL database to use Read Committed Snapshot Isolation. You will receive errors if this is not configured. Responder supports Read Committed Snapshot Isolation to provide performance enhancements.