Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-03 : 14:37:38
|
Hi,I have to solve the following problem. We used DoubleTake for the event of a disaster. The database server is mirrored 1:1.After a disaster event, the original database is offline. The system will switch to the mirrored backup system. The backup system is identical to the original system.How can I prove that the backup database is identical in content and data to the prod DB? |
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-03 : 14:42:36
|
There is a third party utility I've used before from Red Gate called SQL Data Compare (as well as SQL Compare, which is the schema version). The data compare tool will not work very well on very big databases as you might imagine, but if you have a smaller DB it might suit your needs.I have no connections to Red Gate, just used their products before. |
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-03 : 14:52:37
|
the prod DB is in time of a disaster point no longer available! for that reason I can't compare any schema or table. Please keep in mind that the prod db is physically offline in disaster time. I have only the mirrored DB on hand !!quote: Originally posted by lazerath There is a third party utility I've used before from Red Gate called SQL Data Compare (as well as SQL Compare, which is the schema version). The data compare tool will not work very well on very big databases as you might imagine, but if you have a smaller DB it might suit your needs.I have no connections to Red Gate, just used their products before.
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-03 : 16:12:17
|
This this a puzzle or a real world situation? Has it already happened or are you designing a solution to help in the event of a disaster? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-03 : 16:39:04
|
If the original primary is completely offline, there is no way to guarantee that.-Chad |
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-04 : 02:49:42
|
I been thereby to provide a solution, which will bee use in test case, to proves that, that the two DBs are equal. This can happen in following way. I create a deduction from prod system, then shut down the system and backup system is online. Now I make a deduction from the backup and compare the two.THX quote: Originally posted by lazerath This this a puzzle or a real world situation? Has it already happened or are you designing a solution to help in the event of a disaster?
|
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-04 : 02:50:59
|
see postet ne replyTHXquote: Originally posted by chadmat If the original primary is completely offline, there is no way to guarantee that.-Chad
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-04 : 10:50:20
|
quote: Originally posted by zero1de I been thereby to provide a solution, which will bee use in test case, to proves that, that the two DBs are equal. This can happen in following way. I create a deduction from prod system, then shut down the system and backup system is online. Now I make a deduction from the backup and compare the two.THX quote: Originally posted by lazerath This this a puzzle or a real world situation? Has it already happened or are you designing a solution to help in the event of a disaster?
It is not clear to me what you mean. What is a deduction? How can you compare the two systems post-deduction if "the prod DB is in time of a disaster point no longer available". |
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-04 : 11:35:50
|
Hi,I might be pushing at the beginning wrong. The whole thing should just be a test. The disaster date is determined by me. So I can first collect some data from any key tables and then get the prod DB offline and get backup DB online and compare these values ??there. It should only a test scenario.THXquote: Originally posted by lazerath
quote: Originally posted by zero1de I been thereby to provide a solution, which will bee use in test case, to proves that, that the two DBs are equal. This can happen in following way. I create a deduction from prod system, then shut down the system and backup system is online. Now I make a deduction from the backup and compare the two.THX quote: Originally posted by lazerath This this a puzzle or a real world situation? Has it already happened or are you designing a solution to help in the event of a disaster?
It is not clear to me what you mean. What is a deduction? How can you compare the two systems post-deduction if "the prod DB is in time of a disaster point no longer available".
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-04 : 13:19:52
|
I don't understand the point? In real life, the disaster will not be determined by you, so if you just want to compare DBs, why simulate a disaster at all? If it is for some sort of DR process, then this test is invalid because you won't know the date/time a disaster will occur.-Chad |
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-04 : 14:24:01
|
I do not understand what's so hard about understanding that. It is only a simulated disaster and want to proves that the data is mirrored correctly. It goes to prove us that the Double Taker Software syncroniziert correctly.quote: Originally posted by chadmat I don't understand the point? In real life, the disaster will not be determined by you, so if you just want to compare DBs, why simulate a disaster at all? If it is for some sort of DR process, then this test is invalid because you won't know the date/time a disaster will occur.-Chad
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-04 : 16:27:33
|
Ok, so you'll have to do some work and this isn't perfect, but maybe you can do something like this:DECLARE @SQL NVARCHAR(MAX);SELECT @SQL=COALESCE(@SQL+CONVERT(NVARCHAR(MAX),' UNION ALL '),CONVERT(NVARCHAR(MAX),'')) + 'select CHECKSUM_AGG(CHECKSUM(*)) FROM [' + CONVERT(NVARCHAR(MAX),Table_schema) + '].[' + CONVERT(NVARCHAR(MAX),Table_name) + ']'FROM INFORMATION_SCHEMA.Tables ORDER BY TABLE_SCHEMA, TABLE_NAMEselect @SQL;EXEC (@SQL);Keep in mind CHECKSUM won't work on data types: text, ntext, image, XML, cursor, and sql_variant with any one of the preceding types as its base type. If you don't have any of these datatypes, you could do a CHECKSUM_AGG on the result of the UNION ALL. Otherwise, you'll need to find someway to exclude those datatypes. |
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-05 : 02:27:52
|
Thanks for your help. But I do not understand what this query makes and what it checkes. Can you tell me please whats happining in this query ?THXquote: Originally posted by lazerath Ok, so you'll have to do some work and this isn't perfect, but maybe you can do something like this:DECLARE @SQL NVARCHAR(MAX);SELECT @SQL=COALESCE(@SQL+CONVERT(NVARCHAR(MAX),' UNION ALL '),CONVERT(NVARCHAR(MAX),'')) + 'select CHECKSUM_AGG(CHECKSUM(*)) FROM [' + CONVERT(NVARCHAR(MAX),Table_schema) + '].[' + CONVERT(NVARCHAR(MAX),Table_name) + ']'FROM INFORMATION_SCHEMA.Tables ORDER BY TABLE_SCHEMA, TABLE_NAMEselect @SQL;EXEC (@SQL);Keep in mind CHECKSUM won't work on data types: text, ntext, image, XML, cursor, and sql_variant with any one of the preceding types as its base type. If you don't have any of these datatypes, you could do a CHECKSUM_AGG on the result of the UNION ALL. Otherwise, you'll need to find someway to exclude those datatypes.
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-05 : 14:04:26
|
It's producing a CHECKSUM of all the data in each row in a table, then a CHECKSUM aggregate of those to produce a CHECKSUM of the table. It's then doing that for every table in the database and UNIONing them together. This list can then have a further CHECKSUM_AGG applied to it in order to get a single integer value for the DB.A check sum in principal is just a way to take a lot of data and get a semi-unique value that represents it. If the data changes, in theory a new checksum will be generated. I say theory, because this function uses INT and there are many chances for a "collision", which means different data can have the same CHECKSUM. For your purposes, it should be good enough. Again, there are many deficiencies to this approach, but the general principal should be relevant. |
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-05 : 15:59:35
|
o.k but the db which is to check has fields like ntext, images, text etc.. and there for a get this message when execute the query.Lookup Error - SQL Server Database Error: Argument data type image is invalid for argument 7 of checksum function.Lookup Error - SQL Server Database Error: Argument data type ntext is invalid for argument 12 of checksum function.THXquote: Originally posted by lazerath It's producing a CHECKSUM of all the data in each row in a table, then a CHECKSUM aggregate of those to produce a CHECKSUM of the table. It's then doing that for every table in the database and UNIONing them together. This list can then have a further CHECKSUM_AGG applied to it in order to get a single integer value for the DB.A check sum in principal is just a way to take a lot of data and get a semi-unique value that represents it. If the data changes, in theory a new checksum will be generated. I say theory, because this function uses INT and there are many chances for a "collision", which means different data can have the same CHECKSUM. For your purposes, it should be good enough. Again, there are many deficiencies to this approach, but the general principal should be relevant.
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-05 : 16:27:14
|
Like I said, you will need to do some work and it's not perfect, but this shows you a path. |
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-06 : 06:06:42
|
ok you mean that a should find a way to extract this fields Like ntext etc. But how can i Do that ? can you give me a Exempel Thxquote: Originally posted by lazerath Like I said, you will need to do some work and it's not perfect, but this shows you a path.
|
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-08 : 12:56:06
|
Here is a Script for the Table that contain ntext Field. Now how can i extract this Field from the query ?DECLARE @SQL NVARCHAR(MAX);SELECT @SQL=COALESCE(@SQL+CONVERT(NVARCHAR(MAX),'UNION ALL'),CONVERT(NVARCHAR(MAX),'')) + 'select CHECKSUM_AGG(CHECKSUM(*)) FROM [' + CONVERT(NVARCHAR(MAX),Table_schema) + '].[' + CONVERT(NVARCHAR(MAX),Table_name) + ']'FROM INFORMATION_SCHEMA.TablesORDER BY TABLE_SCHEMA, TABLE_NAMEselect @SQL;EXEC (@SQL);CREATE TABLE [dbo].[HL_NEW] ([DWDOCID] int NOT NULL,[DWPAGECOUNT] int NULL,[DWDISKNO] int NULL,[DWFLAGS] int NULL,[DWOFFSET] int NULL,[DWSTOREDATETIME] datetime NULL,[DWSTOREUSER] nvarchar(20) NULL,[DWMODDATETIME] datetime NULL,[DWMODUSER] nvarchar(20) NULL,[DWLASTACCESSDATETIME] datetime NULL,[DWLASTACCESSUSER] nvarchar(20) NULL,[DWSTORAGEACCESS] ntext NULL,[FORDNR] nvarchar(20) NULL,[ENGAGEMENTNR] nvarchar(40) NULL,[NACHNAME] nvarchar(40) NULL,[VORNAME] nvarchar(40) NULL,[GEBURTSDATUM] datetime NULL,[SPARTE] nvarchar(40) NULL,[TITEL] int NULL,[DOKDATUM] datetime NULL,[BEHLTER] nvarchar(20) NULL,[VBAKTZ] nvarchar(20) NULL,[VBDATUM] datetime NULL,[STATUS] nvarchar(20) NULL,[MATCHCODE] nvarchar(20) NULL,[PartnerID] int NULL,[ProdUnit] int NULL,[BARCODENR] nvarchar(38) NULL,[mig_id] int NULL,[mig_status] nvarchar(38) NULL,[mig_status_detail] nvarchar(255) NULL,[KATEGORIE] nvarchar(80) NULL,[BELEGART] nvarchar(40) NULL,[PHIN_STATUS] int NULL,[STAPELNUMMER] nvarchar(40) NULL,[FORDERGNR] int NULL,[GLAEUBIGERNR] int NULL,[HCSUPDATE] int NULL,[ABGLEICH] nvarchar(255) NULL,[BRIEFID] nvarchar(20) NULL,[BRIEFNR] nvarchar(20) NULL,[BETREFF] nvarchar(255) NULL,[EMAIL] nvarchar(255) NULL,[KONTONR] int NULL,[BEMERKUNG] nvarchar(255) NULL,[HAUPTSCHULDNER] int NULL,[DOKBEZEICHNUNG] nvarchar(255) NULL,[ZUSTSACHB] nvarchar(80) NULL,[DWDOCIDOLD] nvarchar(20) NULL,[EXPORTID] nvarchar(20) NULL,[ARCHIVIERT] int NULL,[DWHEADERSIZE] bigint NULL,[DWDOCSIZE] bigint NULL,[DWEXTENSION] nvarchar(20) NULL,[DWVERID] int NULL,[DWSYSVERSION] int NULL,[DWFLAGSEX] bigint NULL,[DELETE] varchar(5) NULL)ON [PRIMARY]TEXTIMAGE_ON [PRIMARY]WITH (DATA_COMPRESSION = NONE);quote: Originally posted by zero1de ok you mean that a should find a way to extract this fields Luke ntext etc. Bit how can i Do that ? can you give me a Exempel Thxquote: Originally posted by lazerath Like I said, you will need to do some work and it's not perfect, but this shows you a path.
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-09 : 16:08:13
|
I was hoping you would do more work and then show us where you were stuck, but I figured I'd just put something together for you. Try this:DECLARE @SQL NVARCHAR(MAX);DECLARE @datatype TABLE (DATA_TYPE NVARCHAR(128),ALTERNATE_EXPRESSION NVARCHAR(128));INSERT @datatype VALUES('text','CONVERT(VARCHAR(MAX),%n)')INSERT @datatype VALUES('ntext','CONVERT(NVARCHAR(MAX),%n)')INSERT @datatype VALUES('image','CONVERT(VARBINARY(MAX),%n)')INSERT @datatype VALUES('XML','CONVERT(VARCHAR(MAX),%n)')DECLARE @TABLETABLE (Table_schema sysname,Table_name sysname,column_list NVARCHAR(MAX)PRIMARY KEY(Table_schema, Table_name));DECLARE @COLUMNTABLE (ID INT,Table_schema sysname,Table_name sysname,column_name sysname,ORDINAL_POSITION INT,data_type NVARCHAR(128),max_ordinal INT,column_list NVARCHAR(MAX)PRIMARY KEY(ID));-- Capture an overridable column_listl, also enforce an order -- without using ORDER BY in the following SELECTINSERT @TABLESELECT t.Table_schema, t.Table_name, CASE WHEN c.COLUMN_NAME IS NULL THEN '*' ENDFROM INFORMATION_SCHEMA.Tables AS tOUTER APPLY (SELECT TOP 1 c.COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNS AS cWHERE c.Table_schema = t.Table_schema AND c.Table_name = t.Table_nameAND c.DATA_TYPE IN (SELECT d.DATA_TYPE FROM @datatype d)) AS c;-- need to assemble an ordered list for following update statement to build column_listINSERT @COLUMNSELECT ROW_NUMBER() OVER (ORDER BY c.Table_schema, c.Table_name, c.ORDINAL_POSITION) AS ID, c.Table_schema, c.Table_name, c.column_name, c.ORDINAL_POSITION, c.data_type, MAX(c.ORDINAL_POSITION) OVER(PARTITION BY c.Table_schema, c.Table_name) AS max_ordinal, NULL AS column_listFROM @TABLE AS tJOIN INFORMATION_SCHEMA.COLUMNS AS cON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_nameWHERE t.COLUMN_LIST IS NULLORDER BY c.Table_schema, c.Table_name, c.ORDINAL_POSITION;DECLARE @last_table_schema sysname, @last_table_name sysname, @column_list NVARCHAR(MAX);-- String concatenation to get COLUMN_LIST built the hard way -- I wish i had a CONCAT_STRING_AGG() function built inUPDATE cSET @column_list = COALESCE(CASE WHEN COALESCE(@last_table_schema,'') = c.table_schema AND COALESCE(@last_table_name,'') = c.table_name THEN @column_list + ',' END,'') + REPLACE(COALESCE(d.ALTERNATE_EXPRESSION,'%n'),'%n','[' + c.column_name + ']'), @last_table_schema = c.table_schema, @last_table_name = c.table_name, c.column_list = @column_listFROM @COLUMN AS cLEFT JOIN @datatype d ON c.DATA_TYPE = d.DATA_TYPE;-- go back and update our @TABLE variable with the column listUPDATE tSET t.COLUMN_LIST = c.COLUMN_LISTFROM @TABLE AS tJOIN @COLUMN AS cON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_nameWHERE c.max_ordinal = c.ORDINAL_POSITION -- get only the last column as only it will have the full column list-- Assemble the CHECKSUM for each row and a CHECKSUM_AGG for each TABLE with a UNION ALL to pull them togetherSELECT @SQL=COALESCE(@SQL+CONVERT(NVARCHAR(MAX),' UNION ALL '),CONVERT(NVARCHAR(MAX),'')) + 'SELECT CHECKSUM_AGG(CHECKSUM('+ t.column_list + ')) AS CS FROM [' + CONVERT(NVARCHAR(MAX),t.Table_schema) + '].[' + CONVERT(NVARCHAR(MAX),t.Table_name) + ']'FROM @TABLE t;-- Include a CHECKSUM_AGG on the resultset so we can get a DB level CHECKSUMSELECT @SQL = 'SELECT CHECKSUM_AGG(z.CS) AS DB_CHECKSUM FROM (' + @SQL + ') AS z';-- Run the Dynamic SQL statement to get a DB checksumEXEC(@SQL); |
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-10-11 : 02:56:58
|
Lazerath many thx for your work. It works fine :)Thank you.quote: Originally posted by lazerath I was hoping you would do more work and then show us where you were stuck, but I figured I'd just put something together for you. Try this:DECLARE @SQL NVARCHAR(MAX);DECLARE @datatype TABLE (DATA_TYPE NVARCHAR(128),ALTERNATE_EXPRESSION NVARCHAR(128));INSERT @datatype VALUES('text','CONVERT(VARCHAR(MAX),%n)')INSERT @datatype VALUES('ntext','CONVERT(NVARCHAR(MAX),%n)')INSERT @datatype VALUES('image','CONVERT(VARBINARY(MAX),%n)')INSERT @datatype VALUES('XML','CONVERT(VARCHAR(MAX),%n)')DECLARE @TABLETABLE (Table_schema sysname,Table_name sysname,column_list NVARCHAR(MAX)PRIMARY KEY(Table_schema, Table_name));DECLARE @COLUMNTABLE (ID INT,Table_schema sysname,Table_name sysname,column_name sysname,ORDINAL_POSITION INT,data_type NVARCHAR(128),max_ordinal INT,column_list NVARCHAR(MAX)PRIMARY KEY(ID));-- Capture an overridable column_listl, also enforce an order -- without using ORDER BY in the following SELECTINSERT @TABLESELECT t.Table_schema, t.Table_name, CASE WHEN c.COLUMN_NAME IS NULL THEN '*' ENDFROM INFORMATION_SCHEMA.Tables AS tOUTER APPLY (SELECT TOP 1 c.COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNS AS cWHERE c.Table_schema = t.Table_schema AND c.Table_name = t.Table_nameAND c.DATA_TYPE IN (SELECT d.DATA_TYPE FROM @datatype d)) AS c;-- need to assemble an ordered list for following update statement to build column_listINSERT @COLUMNSELECT ROW_NUMBER() OVER (ORDER BY c.Table_schema, c.Table_name, c.ORDINAL_POSITION) AS ID, c.Table_schema, c.Table_name, c.column_name, c.ORDINAL_POSITION, c.data_type, MAX(c.ORDINAL_POSITION) OVER(PARTITION BY c.Table_schema, c.Table_name) AS max_ordinal, NULL AS column_listFROM @TABLE AS tJOIN INFORMATION_SCHEMA.COLUMNS AS cON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_nameWHERE t.COLUMN_LIST IS NULLORDER BY c.Table_schema, c.Table_name, c.ORDINAL_POSITION;DECLARE @last_table_schema sysname, @last_table_name sysname, @column_list NVARCHAR(MAX);-- String concatenation to get COLUMN_LIST built the hard way -- I wish i had a CONCAT_STRING_AGG() function built inUPDATE cSET @column_list = COALESCE(CASE WHEN COALESCE(@last_table_schema,'') = c.table_schema AND COALESCE(@last_table_name,'') = c.table_name THEN @column_list + ',' END,'') + REPLACE(COALESCE(d.ALTERNATE_EXPRESSION,'%n'),'%n','[' + c.column_name + ']'), @last_table_schema = c.table_schema, @last_table_name = c.table_name, c.column_list = @column_listFROM @COLUMN AS cLEFT JOIN @datatype d ON c.DATA_TYPE = d.DATA_TYPE;-- go back and update our @TABLE variable with the column listUPDATE tSET t.COLUMN_LIST = c.COLUMN_LISTFROM @TABLE AS tJOIN @COLUMN AS cON c.Table_schema = t.Table_schema AND c.Table_name = t.Table_nameWHERE c.max_ordinal = c.ORDINAL_POSITION -- get only the last column as only it will have the full column list-- Assemble the CHECKSUM for each row and a CHECKSUM_AGG for each TABLE with a UNION ALL to pull them togetherSELECT @SQL=COALESCE(@SQL+CONVERT(NVARCHAR(MAX),' UNION ALL '),CONVERT(NVARCHAR(MAX),'')) + 'SELECT CHECKSUM_AGG(CHECKSUM('+ t.column_list + ')) AS CS FROM [' + CONVERT(NVARCHAR(MAX),t.Table_schema) + '].[' + CONVERT(NVARCHAR(MAX),t.Table_name) + ']'FROM @TABLE t;-- Include a CHECKSUM_AGG on the resultset so we can get a DB level CHECKSUMSELECT @SQL = 'SELECT CHECKSUM_AGG(z.CS) AS DB_CHECKSUM FROM (' + @SQL + ') AS z';-- Run the Dynamic SQL statement to get a DB checksumEXEC(@SQL);
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2012-10-11 : 11:10:31
|
Thanks for the feedback. Glad to help. |
|
|
Next Page
|
|
|
|
|