Servicenow MSSQL Mirror v2 Files

Servicenow MSSQL Mirror
~~~~~~~~~~~~~~~~~~~~~~~~
FULL ServiceNow mirror using ONLY MS SQL and the ODBC provided !

~~~~~~~~~~~~~~~~~~~~~~~~
Changes v 2
~~~~~~~~~~~~~~~~~~~~~~~~
- Cleaned EVERYTHING
- Renamed a number of things for logic purposes
- A lot of other things that make everything work BETTER !
- Added zzz_tbl_changes - tracks changes to SNOW schema and automatically recreates where there is a difference

~~~~~~~~~~~~~~~~~~~~~~~~
Notes
~~~~~~~~~~~~~~~~~~~~~~~~

* Code is not well documented but is tested and works !
* Tested on MS SQL 2012
* I know everyone disses the ODBC ... but I found it to be quite efficient.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Installation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~
1 Install SNOW ODBC
~~~~~~~~~~~~~~~~~~~~~~~~

For more info http://wiki.servicenow.com/index.php?title=ODBC_Driver#gsc.tab=0

~~~~~~~~~~~~~~~~~~~~~~~~
2 Create linked server on MS SQL Server (call it SERVICENOW)
~~~~~~~~~~~~~~~~~~~~~~~~

For more info http://wiki.servicenow.com/index.php?title=ODBC_Driver#gsc.tab=0

~~~~~~~~~~~~~~~~~~~~~~~~
3 Create DB
~~~~~~~~~~~~~~~~~~~~~~~~

Call it whatever you prefer ... I called mine snow_mirror

~~~~~~~~~~~~~~~~~~~~~~~~
4 Execute script:  SMv2_1_db_objects
~~~~~~~~~~~~~~~~~~~~~~~~
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Config step 1 of 2
* Find the section in SMv2_1_db_objects commented as 'FILTER WHAT YOU WANT'
* The code just below filters what tables I wanted for my mirror (remove the filters if you want everything !!!)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 

Does the following:

* Adds table 'sys_dictionary' and populates with SNOW data
* Adds table 'sys_glide_object' and populates with SNOW data
* Adds table 'sys_import_set' and populates with SNOW data

* Adds view 'view_tbl_dictionary' - used as our control-dictionary throughout

* Adds table 'zzz_tbl_changes - tracks changes to SNOW schema
* Adds table 'zzz_tbl_control' -- used during step 7
* Adds table 'zzz_tbl_deletes' -- used during daily update process
* Adds table 'zzz_tbl_fk' -- used during step 8

~~~~~~~~~~~~~~~~~~~~~~~~
5 Add ALL Stored Procs:
~~~~~~~~~~~~~~~~~~~~~~~~

Table POP-ulate procedures:

sp_pop_full - used during step 7 to BULK populate tables
sp_pop_monthly - used during step 7 to BATCH populate tables
sp_pop_ginormous - used during step 7 to TRICKLE populate tables

Table procedures:

sp_tbl_changes - used during step 7
sp_tbl_counts - used during step 9
sp_tbl_create - used during step 7 to create mirrored DB objects
sp_tbl_updates - used during step 9


~~~~~~~~~~~~~~~~~~~~~~~~
6 First of two bits of configuration for you 2 do
~~~~~~~~~~~~~~~~~~~~~~~~
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Config step 2 of 2
* UPDATE variable @date in sp_pop_monthly (set it to a date close to when SNOW was 1st used in your company)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 

~~~~~~~~~~~~~~~~~~~~~~~~
7 Execute script:  SMv2_2_mirror
~~~~~~~~~~~~~~~~~~~~~~~~

The MIRROR !
* Loop for each table it mirrors: creates + populate table
* Logs output to table zzz_setup_control
* If your instance has a lot of data execute this script over a weekend and don’t expect it to be 100% completed by Monday morning !

~~~~~~~~~~~~~~~~~~~~~~~~
8 Execute script:  SMv2_3a_PK + SMv2_3b_FK + SMv2_3c_BK
~~~~~~~~~~~~~~~~~~~~~~~~
Keys and more keys:
* FK's are added and output is logged to zzz_setup_fk
* PK's are already created when the tables are created
* Base keys build a link between base tables and their extended tables

~~~~~~~~~~~~~~~~~~~~~~~~
9 Schedule SQL Agent jobs
~~~~~~~~~~~~~~~~~~~~~~~~

Setup a SQL Agent job:
* sp_tbl_updates run DAILY to keep your mirror up to date
* sp_tbl_counts run WEEKLY or MONTHLY to check and log HEALTH of your mirror
* sp_tbl_changes run WEEKLY or MONTHLY to drop and recreate tables from scratch when there schema's have been changed

~~~~~~~~~~~~~~~~~~~~~~~~
10 Sit back RELAX
~~~~~~~~~~~~~~~~~~~~~~~~

 ... and buy me a beer !