Tag Archive for sql server

15 Sep 2011
development

Sync Framework and Synchronizing two identical databases

I’ve spend the last 8 months working with a customer in a really huge solution involving the Microsoft Sync Framework. Today one of the developers approached to me and asked a very simple question:

I created a local Sql Server Compact Edition file, copy all the rows from all the tables from a Sql Server database, so I assume both databases should have the same data. When I try to synchronize from my remote Sql Server to my local Sql Ce file I would expect no changes sent from the Sql Server to the Sql Ce file, but I realized the Sync Framework sent all the rows from all the tables and overwrite all the rows in the local Sql Ce file. What should I do to avoid this?

The Sync Framework is doing exactly what the Sql Ce provider expect to do, specially if the behaviour of the provider is to overwrite the changes, sending all the data. A way to avoid this problem is controlling the provisioning process and simply tell to the provision class not to populate the change tracking tables with the current data. There is an method in the Sql Server Provisioner class SqlSyncScopeProvisioning to control this, SetPopulateTrackingTableDefault, and accept a DbSyncCreationOption enum.

Well, the explanation of the enum options are very simple:

DbSyncCreationOption  
CreateOrUseExisting Populate the change tracking tables with current data, it will transfer current data from source to destination. (default option)
Skip Do not populate the change tracking tables. It will not transfer the current data from source to destination

If you use the Skip option the provisioning process won’t add the current data in the change tracking tables, but any new row or changed rows after the provisioning process will be of course tracked. That means, you won’t transfer current data from source to destination, that’s very useful if your Sql Ce file is an exact copy of your Sql Server database.

This answer in the MSDN Forums from June Tabadero explain the option a little more http://social.microsoft.com/Forums/sl-SI/syncdevdiscussions/thread/5ae739c0-d52d-458e-98f6-8d799aa97c55

I created a simple project to show you the behaviour of both options, just download it from bitbucket. https://bitbucket.org/cprieto/syncsample01

Note: Looks like now the Sync Framework Toolkit is Open Source!

cristian | No comments | Tags: , , ,
04 Mar 2010
reviews

Review, RedGate SQL Source Control

cristian | No comments | Tags: , , , ,
22 Dec 2009
asp.net

Calculando la fecha UTC en SQL Server

Hace unos días me topé con algo interesante, cambiar la fecha ya establecida en los campos de una tabla a una estandarizada para varios paises. La solución simple fue utilizar la fecha UTC (GMT 0), pero ya en SQL Server las fechas estaban registradas con la hora local.

En SQL Server podemos obtener el tiempo actual UTC con la función GETUTCDATE(), utilizando esto a nuestro favor decidí crear un simple UDF para cambiar las fechas.

CREATE FUNCTION [dbo].[ConvertToUtc](@start datetime)
RETURNS DATETIME
AS
BEGIN
    DECLARE @offset INTEGER
    SET @offset = DATEDIFF(HOUR, GETUTCDATE(), GETDATE())
    RETURN DATEADD(HOUR, @offset, @start)
END

Luego el proceso es simple, como ejemplo, transformando la fecha actual a UTC:

SELECT ConvertToUtc(CURRENT_TIMESTAMP)
cristian | No comments | Tags: , ,