Skip navigation
All People > tpcolson > Finding Sasquatch with ArcGIS Pro > 2015 > January
2015

In Real-time Geodatabase Replication? Part 1 you were introduced to SQL Real-Time Replication. One requirement of SQL Merge Replication is that the table being replicated must have a rowguidcol. This is easy with a cursor:

 

DECLARE 
@sql VARCHAR(500), 
@tableName VARCHAR(128)
DECLARE gandgCursor CURSOR 
FOR 
SELECT 
table_name
FROM 
SDE_column_registry
WHERE 
table_name like 'CR%' 
ORDER BY 
table_name ASC 
OPEN gandgCursor 
FETCH NEXT FROM gandgCursor  
INTO @tableName 
WHILE ( @@FETCH_STATUS = 0 ) 
BEGIN 
SET @sql = ' 
ALTER TABLE [dbo].['+ @tableName + '] 
ALTER COLUMN GlobalID ADD ROWGUIDCOL ;
' 
 PRINT 'Executing Statement - '+ @sql 
 EXECUTE ( @sql ) 
 FETCH NEXT FROM gandgCursor  
 INTO  @tableName 
 END 
 CLOSE gandgCursor 
 DEALLOCATE gandgCursor

Messin' up the scenery, breakin' my mind. Do this, don't do that, can't you read the LINE? In Hey Neighbor? What's Your Value? , Rasters, Pixels and Code Oh My!, and Using SQL to Auto-Populate XY Values I had introduced some SQL trickery to automate some data entry. Probably should have mentioned those methods mostly just work on points....

 

For our GIS data standard, we also want to collect the same administrative values and automation for line types such as roads, trails, and other linear features. Problem is, there's a lot of vertices (nodes, coordinate pairs) on a line, which one to use for the SQL trickery?

 

There are some existing SQL Geography methods for manipulating well-known-text from a Geography line instance, but none of them give me a midpoint value, and they certainly don't lend themselves well to being part of a trigger.

 

In order to easily get a line midpoint value from Geography, you'll need the SQL Server Spatial Tools - Home  installed, which is as easy as registering a DLL through SQL on your database instance- works on 2008 and 2012.

 

A line midpoint can be queried as

 

SELECT dbo.LocateAlongGeog(SHAPE, SHAPE.STLength()/2).Lat FROM [dbo].[SOMELINE]
SELECT dbo.LocateAlongGeog(SHAPE, SHAPE.STLength()/2).Long FROM [dbo].[SOMELINE]

 

Wrapping that up in a trigger to populate the LAT, LON, and Elevation fields we get:

 

CREATE TRIGGER    [dbo].[SOMELINE_GEO]
ON    [dbo].[SOMELINE]
AFTER INSERT, UPDATE NOT FOR REPLICATION
AS   
BEGIN    
SET NOCOUNT ON;  
 UPDATE p SET  
LON = CASE WHEN p.SHAPE IS NULL THEN p.STStartPoint.LON ELSE p.SHAPE.Long END,  
LAT = CASE WHEN p.SHAPE IS NULL THEN p.STStartPoint.LAT ELSE p.SHAPE.Lat END,
ELEVATION = (SELECT
pdata.getValueByLoc(1,p.SHAPE.Long,p.SHAPE.Lat)  FROM [dbo].[DEM10MP])
  FROM  
    dbo.SOMELINE AS p 
  INNER JOIN  
    inserted AS i 
    ON i.GlobalID = p.GlobalID 
END
GO

 

Polygons don't require the "special function" and simply can be queried for midpoint values with:

 

SELECT SHAPE.EnvelopeCenter().Lat
FROM [SOMEPOLY
SELECT SHAPE.EnvelopeCenter().Long
FROM [SOMEPOLY]
tpcolson

It's 4:20. Got a minute?

Posted by tpcolson Champion Jan 19, 2015

It's very easy as a GIS administrator to add lots and lots of fields to a feature class and even easier to naively think that folks are going to populate or edit them! One common theme for me is date fields. We have edit date, create date, FGDC edit and create date, year...blah blah blah.

 

I wish there was just one, or even no, onus on editors to have to think about dates and the database just...handled it.

 

This can be so. With a database trigger. Let's start with FGDC dates. If you're implementing some form of feature-level metadata, or some data-mining that tags another metadata element with FGDC source, create or edit date yet have editor tracking enabled, there's no reason why you should have to also populate the FGDC[whatever]DATE column as well.

 

CREATE TRIGGER [dbo].[SOMETABLE_DATE]
ON [dbo].[SOMETABLE]
AFTER INSERT, UPDATE NOT FOR REPLICATION
AS BEGIN
SET NOCOUNT ON; 
UPDATE [dbo].[SOMETABLE]
SET 
SRCDATEFGDC = (convert(varchar(8), SOURCEDATE, 112)), 
CREATEDATEFGDC = (convert(varchar(8), CREATEDATE, 112)), 
EDITDATEFGDC = (convert(varchar(8), EDITDATE, 112))
END
GO

 

Here we're taking the EDITDATE and CREATE date values, which are sql

datetime(2)

and converting them to a string format as yyyymmdd. The 112 is what controls the output format. See CAST and CONVERT (Transact-SQL) for a full list of date conversion formats.

 

In addition, the user is also selecting a source date in this case which may be different than create date and that is converted as well.

 

If you have a year column you can also

 

YEAR = CASE WHEN SOURCEDATE IS NULL THEN NULL ELSE YEAR(SOURCEDATE) END

 

with null value handling thrown in.

 

If you have editor tracking disabled for some reason (often causes issues with Collector for ArcGIS) you could through a default constraint on your date column(s) as

getdate()

.

 

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.

Do you have someone in your organization that YELLS WITH THEIR KEYBOARD? Everything is upper case? What about the rogue all-lower-case folks? Or ever worse, the First-word-is-proper-case-all-other-words-are-lower-case ninjas.

 

I have a personal pet-peeve (OCD). Feature names in GIS should be Proper Case. Happy Valley Road. Not Happy valley road, not Happy valley Road, and definitely not HAPPY VALLEY ROAD.

 

I'd like to enforce Proper Case naming of those feature regardless of how the text is cased by the editor. This can be accomplished with a simple Function and Trigger in SQL.

 

First create the following Function:

 

create function [dbo].[ProperCase](@Text as varchar(8000))
returns varchar(8000)
as
begin
   declare @Reset bit;
   declare @Ret varchar(8000);
   declare @i int;
   declare @c char(1);


   select @Reset = 1, @i=1, @Ret = '';
   
   while (@i <= len(@Text))
    select @c= substring(@Text,@i,1),
               @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,
               @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,
               @i = @i +1
   return @Ret
end
GO

 

Then this Triggger:

 

CREATE TRIGGER [dbo].[NAME_UPDATE]
ON [dbo].[SOME_TABLE]
AFTER INSERT NOT FOR REPLICATION
AS BEGIN
SET NOCOUNT ON; 
UPDATE SOME_TABLE
SET
NAME = dbo.Propercase(NAME)
END
GO

 

Note here that this only fires after an insert, not an update. There could be legitimate reason why the YELLERS want something other than proper case, e.g. "ND Happy Valley Road" (ND for "North District). This allows them (or me after they YELL at me) to update that one feature without the trigger proper-casing my edit.

 

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.

I have tried, and failed, many times to get geodatabase replication using ESRI tools to work, work reliably, or work at all. Perhaps it's all the un-authorized SQL tinkering I do. Even when I do get it working, I'm not a big fan of having to fire up Arc Toolbox and push buttons to make replication happen. Not a big fan of writing Python code, either.

 

With SQL server, there IS a way to replicate an entire database, or parts of of a database. The part of a database is handled with Merge Replication, in which you define what tables to replicate, and what criteria under which data is replicated. I like that option! Consider this environment:

 

I have a water quality geodatabase (in SQL), which is big, complex, has lots of legacy stuff in it that I just don't feel like dealing with right now. Replicating or mirroring the entire database is not an option....because.....in a brilliant management decision, the "datacenter" was placed in a building very far from any modern telco hubs. On a good day I can actually download email attachments. The problem is, the users of this database are every where else but in this building.

 

More drama: We're drinking the Portal for ArcGIS Cool Aid and high on Collector for ArcGIS. Can't really connect my database to either of those technologies using carrier pigeons.

 

But I do have some very limited in capacity database and application servers in the "cloud". Certainly not the kind that can house a 7GB water quality database, but here's an idea. What if I could replicate the feature classes that are most needed operationally to these cloud platforms? Field users could get to their data, and I could consume it in my remote datacenter in other applications. All they need access to is the "dots on the map" for their mobile devices, desktop applications, and management access to web-based maps (what stream did that oil spill impact?).

 

Here's what we'll need:

 

  1. An obnoxiously massive and complex SDE/SQL database called "FISH";
  2. A feature class that's been around forever and has a ton of data called "TEST";
  3. A remote SDE/SQL database called "FISHREPL" which is the same SQL/SDE version as "FISH";
  4. A horrible internet connection.


This is a multi-part blog post, here are the steps:

Real-time Geodatabase Replication? Part 1

Real-time Geodatabase Replication? Part 2

Real-time Geodatabase Replication? Part 3

Real-time Geodatabase Replication? Part 4

Real-time Geodatabase Replication? Part 5

Real-time Geodatabase Replication? Part 6

 

 

And of course a few caveats: This only works with unversioned data which places quite a limit on things. In some cases the cons of having unversioned feature classes are not as great as the pros of having a real-time replication service.

 

Please bear in mind that this method of replication is extremely unsupported. I just did it because I like tinkering under the hood of SDE and wanted to see if it would work. It "sort of" does! However in contrast to the out-of-the-box ESRI replication tools, this process sure resolves a lot of headaches for me. I can attach user-editors to a local SDE instance where "bulk" editing occurs and SQL Merge Replication will fire those changes to the "cloud" SDE instance where the rest of my organization can access (and edit) the data through Portal for ArcGIS, and my mobile clients can perform edits and updates using Collector for ArcGIS. And it works continuously. Full-time. Even with file attachments-enabled and 2 mb photos being attached to a point those changes sync within 60 seconds. SQL replication in 2012 is very efficient in compressing data and ONLY sending deltas. When I generate a "snapshot" of a feature class with file attachments, SQL sees 50 mb of data that needs to be replicated. Performing a versioned-checkout of the same data using the Distributed Geodatabase tools results in a 220 mb file geodatabase.

 

But emphasis on "experimental". There is a lot of overhead to managing SQL Merge Replication and you can really screw up some operational data. If you get it working after following this blog series I suggest you spend some time deliberately breaking things and see if you can recover your data integrity! Otherwise stick to the ESRI replication tools. They work. When you push the button.....

 

 

 

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.

Setting up a Publication Server

 

First a little side-step to prepare our GIS table for replication. More on the caveats later!

 

In order to participate in replication a SQL table will need  a column flagged as a ROWGUID column. In Dear GlobalID: I Hate You! Rants from an OCD data manager  we talked about the Global ID Unique Identifier. We're going to use it to tell the Replication engine that this column is also used to manage changes between the Publisher database and any subscribers. There are two requirements for this column:

 

  1. It has to default to newsequentialid();
  2. It has to be flagged as a ROWGUID column.

 

4332.JPG

If you don't set the GlobalID to be the ROWGUID, when you create the publication a new ROWGUID column will be created for you, outside of the "ESRI stack", which will cause problems when you attempt to edit this feature class (as this new column has been created without the participation and consent of SDE).

 

 

In Microsoft SQL Merge Replication you first need a publication server, from which other SQL servers can "subscribe" to replication articles published by the publication server.

 

In SSMS launch the New Publication Wizard and select the Fish Database as the publication database. Select Merge Publication as the publication type.

 

qwr4e3.JPG

 

12434.JPG

 

Since we're using the Geography Storage type we don't need to publish any other table other than the "Test" base table. Select "Test" as the Object to Publish.

 

rewrew.JPG

Instinctively you think we need more tables than that considering how Arc SDE manages data. The secret will be revealed in a few more posts. Don't filter any tables.

 

324.JPG

 

Configure the Snapshot Agent to run every hour (you can pick any schedule you like!).

 

 

reqwrew.JPG

 

Set the Snapshot Agent to use a domain account (ideal).

 

fdsfdsf.JPG

Then check Create the publication and Generate a script file. Not pictured, but one of the dialogues will ask you where the Snapshot files will go. Be sure to pick a network share that both servers will be able to access. It's very important that you check the box that asks if you want to create the script that will generate the Publication Agent!

 

543252.JPG

34.JPG

qw.JPG

 

Make sure the Publication and Snapshot Agent is working by Launching Replication Monitor and starting the Agent. The directory you picked to host the replication articles should start filling up with some files.

 

4.JPG

 

You should see a status of [100%] in the last action column.

 

 

e4532.JPG

 

Double-click on the agent and select Action -> Start Agent. Wait a few minutes and select Action -> Refresh. You should see another successfully completed Agent job.

 

42.JPG

Now review the Publisher Properties. It's good that you see how all of the pieces are put together! In Replication Monitor in the left window pane select the Replication (FISH) and right-click -> Properties. You should see:

 

t543543.JPG

 

434.JPGNote here in the Location of Snapshot files that they are going to a UNC network share that the domain account used to run the agent has full access to.

 

r32542.JPG

 

We need to make a few changes though.

 

Convert filestream to MAX data types is set to False by default. If you attempt to Subscribe to this publication any column data in a Geography or Geometry column will be converted to a string during the replication process. Not good for GIS data! The only way to correct this is to delete the publication and recreate it programmatically. However since you saved the script the created the publication all you have to do is edit two lines of that script and re-run it!

 

 

use [FISH]
exec sp_replicationdboption @dbname = N'FISH', @optname = N'merge publish', @value = N'true'
GO
-- Adding the merge publication
use [FISH]
exec sp_addmergepublication @publication = N'FISH_REPL', 
@description = N'Merge publication of database ''FISH'' from Publisher ''INPGRSMS04TC''.', 
@sync_mode = N'native', @retention = 14, @allow_push = N'true', @allow_pull = N'true', 
@allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', 
@compress_snapshot = N'false', @ftp_port = 21, @ftp_subdirectory = N'ftp', @ftp_login = N'anonymous', 
@allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'false', 
@conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', 
@max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = null, 
@publication_compatibility_level = N'100RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'false', 
@allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days',
 @conflict_logging = N'both', @automatic_reinitialization_policy = 0
GO

exec sp_addpublication_snapshot @publication = N'FISH_REPL', @frequency_type = 4, 
@frequency_interval = 14, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, 
@frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 500, 
@active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, 
@job_login = N'domain\account', @job_password = null, @publisher_security_mode = 1

use [FISH]
exec sp_addmergearticle @publication = N'FISH_REPL', @article = N'TEST', 
@source_owner = N'dbo', @source_object = N'TEST', @type = N'table', 
@description = null, @creation_script = null, @pre_creation_cmd = N'drop', 
-- Change 0x000000010C034FD1 to 0x000000000C034FD1
@schema_option = 0x000000010C034FD1, 
@identityrangemanagementoption = N'manual', 
@destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', 
@subset_filterclause = null, @vertical_partition = N'false', @verify_resolver_signature = 1, 
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, 
@subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', 
-- Change true to false
@stream_blob_columns = N'false', 
@partition_options = 0
GO

 

In the code above which was saved when the Publication Agent was first created @schema_option has been changed to 0x000000010C034FD1 and @stream_blob_columns has been set to false.

 

Run the script however note that you will have to re-generate the agent job that runs the article generation and synchronization process. Right click on the Publication in Replication Monitor and select Agent Security and configure the agent to run using a domain service account.

 

re3453.JPG

 

Instead of re-creating the Snapshot Agent you could just:

 

sp_changemergearticle 'PFMD_REPL', 'TRAILSIGN', 'schema_option','0x000000000C034FD1',1,1
GO
sp_changemergearticle 'PFMD_REPL', 'TRAILSIGN', 'stream_blob_columns','false',1,1
GO

 

And then re-initialize the snapshot.

 

But it's a good idea to learn how to create the Publication using SQL.

 

 

Real-time Geodatabase Replication? Part 1

Real-time Geodatabase Replication? Part 2

Real-time Geodatabase Replication? Part 3

Real-time Geodatabase Replication? Part 4

Real-time Geodatabase Replication? Part 5

Real-time Geodatabase Replication? Part 6

 

 

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.

Setting up a Subscription Server

 

On your remote database server, create a NEW SDE database called "FEMPFISH" (or something other than fish).

 

Once the database has been created, manually replicate by right-clicking and selecting Import, and Import the TEST feature class definition from the FISH (publication) database. Make sure you select the Geography storage type. Everything about the two feature classes must be the same! If your feature class has attribute domains the best way to do this is with a XML export/import. Otherwise your feature class on the remote (subscription) server won't have attribute domain pick-lists for editors to use!

 

Note that I'm not using Feature Datasets here! More complexity....

 

On the remote SQL server activate the New Subscription Wizard.

 

Select the Local SQL Server that hosts the FISH database as the Publisher. You should then see this:

 

rftewrtwe.JPG

 

I prefer to run all agents at the Distributor as this a) keeps the overhead on my server that has the capacity to do so and b) makes managing many subscriptions a lot easier (one interface).

 

reqwr32.JPG

 

Select the Subscription Database as the one you created on the remote server to host the replicated feature class.

 

r3ew45r3.JPG

In the Agent Security, you SHOULD use the SAME domain account you used to create the Publisher Agent. Good luck with another security model....and set the agent schedule to run continuously.

 

r32w423.JPG

 

 

 

qew453.JPG

r3432.JPG

tr3254.JPG

 

qwer34.JPG

 

Real-time Geodatabase Replication? Part 1

Real-time Geodatabase Replication? Part 2

Real-time Geodatabase Replication? Part 3

Real-time Geodatabase Replication? Part 4

Real-time Geodatabase Replication? Part 5

Real-time Geodatabase Replication? Part 6

 

 

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.

Making Sure the Agent is Working 

 

Before we dive into testing replicated CRUD operations on either SDE instance we need to make sure the Agent(s) are working.

 

In Replication Monitor select the Agent tab and right-click on Snapshot Agent and select Start Agent. It should report "Running".

 

When it reports "Completed" double-click on it. You should see something like this:

 

r3452.JPG

This indicates that the Agent is successfully reading the local (source) table TEST and any changes and publishing change articles to the server share.

 

Select the All Subscriptions tab and double-click on the subscription. You should see something like this:

 

r3e243.JPG

Now create lots of new points in the TEST feature class on the local (publication) SDE instance. Wait a few minutes and refresh the Subscription status in Monitor.

 

You should see something like this:

 

453432.JPG

This indicates that inserts on the FISH database are being replicated to the FEMPFISH database. Success! Try some updates and deletes.

 

However it is very likely you'll see some errors like this, which is related to our previous issue of the Geography column being converted to text. While we did update the properties of the Publication Agent to correctly merge the spatial column we need to reinitialize the subscription agent to recognize this change.

 

re432423.JPG

 

To resolve this:

 

Restart the Publication Agent:

 

qwer4325432.JPG

 

After is has completed its job with 100% success stop, and restart, the sync agent:

 

r3254232.JPG

aws4r3432.JPG

 

Real-time Geodatabase Replication? Part 1

Real-time Geodatabase Replication? Part 2

Real-time Geodatabase Replication? Part 3

Real-time Geodatabase Replication? Part 4

Real-time Geodatabase Replication? Part 5

Real-time Geodatabase Replication? Part 6

 

 

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.

Testing CRUD Operations on the Publisher

 

Lets make some edits on the Publisher (local) SDE and see if they show up on our remote SDE instance.

 

In order to edit non-versioned data you will need to set your editor options to allow un-versioned editing:

 

asreew.JPG

In Arc Map, add one point to the local FISH feature class. In the Replication Monitor you should see that insert fire to the remote database within 60 seconds.

 

reqwr432.JPG

 

If you add the remote FISH feature class to Arc Map you should see the exact same point.

 

Perform an attribute edit of the new point. In the Replication Monitor you should see that update fire to the remote database within 60 seconds.

 

fewr3ew.JPG

 

Delete the point. In the Replication Monitor you should see that delete fire to the remote database within 60 seconds.

 

rf3e2432.JPG

 

Real-time Geodatabase Replication? Part 1

Real-time Geodatabase Replication? Part 2

Real-time Geodatabase Replication? Part 3

Real-time Geodatabase Replication? Part 4

Real-time Geodatabase Replication? Part 5

Real-time Geodatabase Replication? Part 6

 

 

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.

Schema Changes

 

In Arc Catalog add a new field to the local instance of FISH.

 

fewtrew.JPGIn the Replication Monitor you should see that schema change fire to the remote database within 60 seconds.

 

dsatrfew.JPGIn the remote instance of TEST you should see "ANEWFIELD" in its properties in Arc Catalog. Make some attribute edits to this field on the local instance and watch them fire.

 

Here's why we created an empty version of TEST on the remote SDE instance and ONLY replicate the TEST table. In the SDE "stack" there are lots and lots and lots of tables, functions, and stored procedures that handle things. Had we attempted to replicate TEST from the Publisher to the Subscriber without first having created the TEST feature class on the subscriber, none of the SDE dependencies would have been created and we wouldn't be able to use the remote TEST feature class in any GIS applications.

 

In this manner we can have a fully synchronized copy of TEST on both SDE instance yet the SDE "stack" on either instance independently manages the feature class without any interference caused by replication.

 

Real-time Geodatabase Replication? Part 1

Real-time Geodatabase Replication? Part 2

Real-time Geodatabase Replication? Part 3

Real-time Geodatabase Replication? Part 4

Real-time Geodatabase Replication? Part 5

Real-time Geodatabase Replication? Part 6

 

 

This is a personal blog and does not recommend, endorse, or support the methods described above. Alteration of data using SQL outside of the ESRI software stack, of course, is not supported and should not be applied to a production database without a thorough understanding and disaster recovery plan.