I can't get a proper Data Compare
My intent is to use Data Compare to keep non-production databases in sync with production databases. However, during my evaluation, I am running into numerous errors and have not been able to successfully synchronize a database yet (or even generate a script).
I am running SQL Data Compare from a Win2k8 64 bit server with 32GB of memory. I have (at this point after reviewing the forums) set up the RGTEMP system variable and set it to a directory on the E: drive which has plenty of free space. Running a sync direct from one database to another (located on different database servers), I seem to always receive a "System.OutOfMemoryException" during the "Comparing Databases" step. This occurs in random places during the compare - and I am running against a very small database for my testing.
I have also tried running a sync against a SQL Server Native compressed backup (.bak) file without any luck. Another product that I am evaluating does not have support for compressed bak files - which would be a deciding factor in my purchasing decision, except that I can't get a sync to work properly at all with SQL Data Compare. The error I receive in this is "Object reference not set to an instance of an object". This seems to fail in a more consistent place - here is the schema for the table:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[mytable]( [col1] [char](8) NULL, [col2] [char](6) NULL, [col3] [char](5) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
This table has no records in it.
SQL Compare runs through the backup vs. database comparison just fine and finds no differences on this table, same goes for DB vs. DB compare with SQL Compare.
Are there any log files generated that might point me in the right direction?