Author: Steve M

SQL Server Replication Options

Common configuration notes/options.

==============================

Snapshot publication: The Publisher sends a snapshot of the published data to Subscribers at scheduled intervals.

Transactional publication: The Publisher streams transactions to the Subscribers after they receive an initial snapshot of the published data.

Transactional publication with updatable subscriptions: The Publisher streams transactions to SQL Server Subscribers after they receive an initial snapshot of the published data. Transactions originating at the Subscriber are applied at the Publisher.

Merge publication: The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications

=============================

All merge articles must contain a uniqueidentifier column with a unique index and the ROWGUIDCOL property. SQL Server adds a uniqueidentifier column to published tables that do not have one when the first snapshot is generated.

Adding a new column will:      » Cause INSERT statements without column lists to fail      » Increase the size of the table      » Increase the time required to generate the first snapshot

SQL Server will add a uniqueidentifier column with a unique index and the ROWGUIDCOL property to each of the following tables.

============================================

Below shows one of the configuration wizard screens during setup of SQL Server Replication from SSMS SQL Server 2008 R2.

You have the option of configuring replication to support SQL 2008 and/or lower SQL server versions.

sql_replication

 

 

 

 

List database file locations

Below query will list the database file locations on a sql server instance.

USE master

GO

SELECT  name AS [Logical Name],  physical_name AS [DB File Path],  type_desc AS [File Type],  state_desc AS [State]

FROM  sys.master_files

GO

 

Uploading ESRI Shapefile into Microsoft SQL Server 2008 Spatial Database

Microsoft SQL Server from release 2008, 2008 R2, 2012 and above, support spatial data types.

Tool used for this – shape2sql – by Morten Nielsen/SharpGIS can be found by clicking here.

Sample test data used was downloaded from here – OpenStreetMap and  here – Processed Coastline Data

Screenshot

Capture_spatial

After import into SQL, this translated in rows/size as:

Capture_spatial2

T-SQL code for table generated:

USE [Spatial] GO
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [dbo].[Processed_p]
(  [ID] [int] IDENTITY(1,1) NOT NULL,
[error] [smallint] NULL,
[tile_x] [int] NULL,
[tile_y] [int] NULL,
[geom] [geometry] NULL,
PRIMARY KEY CLUSTERED (  [ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Processed_p]
WITH CHECK ADD  CONSTRAINT [enforce_srid_geometry_Processed_p] CHECK  (([geom].[STSrid]=(0))) GO

ALTER TABLE [dbo].[Processed_p]
CHECK CONSTRAINT [enforce_srid_geometry_Processed_p] GO