axlrnish

How to get the SQL server snapshot replication working with SQL server 2012 SP2 in replicating an ArcGIS 10.3.1 Geodatabase

Blog Post created by axlrnish on Aug 6, 2015

Problem:

 

When the SQL server snapshot replication is setup with default settings and parameters to replicate a 10.3.1 Geodatabase, after the replication, when you try to connect to the GeoDatabase through ArcCatalog, you get the error message saying Failed to connect to database. Underlying DBMS error [sde.DEFAULT][STATE_ID = 1924] with no specific reason.

 

Cause:

 

ArcGIS 10.3.1 Geodatabase has extended set of properties maintained to keep the integrity of the GeoDatabase specially compared to 10.0 (we migrated from 10.0 to 10.3.1 keeping SDE binary type Geometry) which are not copied over to the subscriber. Particularly:

 

 

- Non-clustered indexes

- Check constraints

- default values

- User Triggers

- extended properties

- XML indexes

- permissions

- permissions related to Stored procedures and Functions.

 

Solution:

 

(Assuming that the person is fully conversant of the SQL server snapshot replication procedure.)

 

Set up the following parameters on top of the default parameters when configuring the publisher under all Tables. The relevant schema_option if you use the command is 0x000000004C037DDD.

snapshot_parameters.jpg

 

you will be unable to enable copying permissions for the stored procedures due to existing bug on the SQL server (https://connect.microsoft.com/SQLServer/feedback/details/431393/replicate-permissions-option-on-stored-procedure-in-non-dbo-schema-causes-snapshot-agent-to-crash )

 

Apply the following SQL after the replication on the relevant GeoDatabase via SQL server management studio: This will grant EXECUTE permissions to SDE user for all stored procedures and functions which is required if there is a connection to the GeoDatabase using db_ddladmin role user.

 

DECLARE @DB_Name varchar(100)

DECLARE @Command nvarchar(200)

 

DECLARE database_cursor CURSOR FOR

SELECT '[' + SPECIFIC_SCHEMA + ']' + '.' + '[' + SPECIFIC_NAME + ']' AS NAME  FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE IN ('FUNCTION','PROCEDURE') AND SPECIFIC_SCHEMA = 'SDE'

 

OPEN database_cursor

 

FETCH NEXT FROM database_cursor INTO @DB_Name

 

WHILE @@FETCH_STATUS = 0

BEGIN

     SELECT @Command = 'GRANT EXECUTE ON OBJECT::' + @DB_Name + ' TO PUBLIC'

     EXEC sp_executesql @Command

     --print @Command

 

     FETCH NEXT FROM database_cursor INTO @DB_Name

END

 

CLOSE database_cursor

DEALLOCATE database_cursor

Outcomes