Tablediff - Quickly compare column data and schema between two Database Tables on Different Machines

Today Learning's 

• Tablediff gets installed with replication features
• Prepare Tablediff for multiple tables
• Run it from a batch file and log results in text file
• One Click Results Displayed :). Tested on SQL 2012

Example Batch File 1

TableDiff.bat
cd "C:\Program Files\Microsoft SQL Server\110\COM"
tablediff.exe -sourceserver SourceDBServerIP -sourcedatabase TestDB -sourcetable Table1 -destinationserver DestDBServerIP  -destinationdatabase TestDB -destinationtable Table1 -c -dt DiffsTable1
tablediff.exe -sourceserver SourceDBServerIP -sourcedatabase TestDB -sourcetable Table2 -destinationserver DestDBServerIP  -destinationdatabase TestDB -destinationtable Table2 -c -dt DiffsTable2

Explanation

• Option –c - Perform column level comparisons
• Option –dt – Output different records to particular table (drop and create if exists)
• By checking the log and the diff tables created we can find error code and description of differences
• By adding -strict option it expects the same schema (column order). I have not used it in above example
• Add -sourceuser,  -sourcepassword , -destinationuser, -destinationpassword parameters in case of sql credentials. If not specified windows credentials will be used

Batch File 2 - Capture Log Results
RunTD.bat

C:\\TableDiff.bat > C:\\TableDiffResults.txt 2>&1

One Click RunTD.bat and check logs for results. SO link

Happy Learning!!!

Two interesting bugs - Web service changes, DB column position changes

Two interesting bugs and learning's from them

Bug#1 - Due to install / upgrade paths it resulted in different ordinal position of same tables. Due to usage of select * this resulted in wrong update on columns

Automation Solution - With SQL command (link) we can figure out ordinal position differences between two same tables. Querying two environments for each tables, dumping the results in a XML. XML comparison  between them would highlight tables with different cardinal positions for two same tables.

Bug #2 - When web services are changed, the optional parameters introduced, signatures changed often fails to support backward compatibility 

Automation Solution - SOA model is open source tool for wsdl comparison. This can be used to validate wsdl differences between versions. Example code from SO

More Reads

Determine space used for each table in a SQL Server database

Happy Bugging & Learning!!!

Good QA Reads

Good QA Reads

• Test Manager in Agile - Great summary in terms of responsibilities (Collaboration, Hands on technical manager, High level perspective of overall execution, Planning, Managing escalations)
• Bug tracking for agile teams - Good Suggestions in Managing bugs with fixed timelines, Tracking without tools, Find patterns etc..
• 7 Commandments of Growth-Oriented Leadership - Good Thoughts - Take risks, compete, be transparent, patient listener

More Reads

Reporting session based testing

How to implement UI testing without shooting yourself in the foot

Aaron Hodder on using mind-mapping software as a visual test management tool

Happy Reading!!!

TSQL Learning's @ Work - Date Conversion yyyymmdd to mm/dd/yyyy, xp_cmdshell Tips

Tip#1 - Converting YYYYMMDD into MM/DD/YYYY Format

SELECT CONVERT(VARCHAR(10),CONVERT(DATETIME, '20120101', 121),101)

Tip #2 - Coverting HHMMSS into HH:MM:SS

SELECT STUFF(STUFF('211532', 3, 0, ':'), 6, 0, ':')

Tip #3 - xp_cmdshell does not work with temp tables / table variables. Use Global Temp Tables ## (Stackoverflow :) link)

Tip #4 - Enable xp_cmdshell in SQL Server - SO Link

Tip #5 - Testing Dynamic SQL Code with EXEC command - SO Link

Happy TSQL Revising!!!

Interesting Learning Notes

Note #1 - What is difference between performance and scalability problem ?

• Performance problem - Fixing performance issue for a website example - Pageload time is very high for homepage of website
• Scalability problem - Scaling website to support 10X user base than current user base

This post was very useful for above answer Performance v Scalability – For Employers

Note #2 - Read / Write Advantages / Disadvantages for Normalized / Denomarmalized Databases ?

• Normalized - Insert in multiple tables, Highly consistent
• Denormalized - Easy Insert, Consistency issue with updates (Multiple  versions of Records may exist)

I loved reading this post again and again - Data storages and read vs write controversy. Post is Very Simple, intuitive and clear.

Session - Advanced techniques for Web functional testing

• Tip #1 - Changing window size during execution set_window_size
• Tip #2 - Screenshots comparison using needle (Python based)

More Reads

Successful Scalability Principles

Bayesian statistics made simple

Software Carpentry: Lessons Learned

Performance Testing and Profiling: A Virtuous Cycle

Happy Learning!!!

Interesting TSQL Question

Check the below TSQL Example involving NULL. What is the result for below three queries

Tested on SQL 2012 Setup

Create TABLE Test1

(Id           int,

 Score int not null)

Create TABLE Test2

(Id           int,

 Score2       int not null)

 insert into Test1(Id, Score) VALUES (NULL,10),(10,100),(20,200)

 insert into Test2(Id, Score2) VALUES (NULL,11),(10,110),(20,220)

Case #1 

 Select * from  Test1 JOIN Test2

 ON Test1.Id = Test2.Id

Case #2 

 Select * from  Test1 LEFT OUTER JOIN Test2

 ON Test1.Id = Test2.Id

Case #3

 Select * from  Test1 LEFT OUTER JOIN Test2

 ON Test1.Id = Test2.Id

 ORDER BY Test1.Id DESC

Results are

Happy Learning!!!

SQL Server Database Restore Notes

While installing SQL 2012 on Windows Server 2008 R2

• Enable windows 3.5 from Windows Add / Remove Features
• While trying to restore SQL 2012 backup on SQL 2008, Error 'media family on device is incorrectly formed' for version mismatch is misleading

SQL Version to check query

SELECT

SERVERPROPERTY('ProductVersion') AS ProductVersion,

SERVERPROPERTY('ProductLevel') AS ProductLevel,

SERVERPROPERTY('Edition') AS Edition,

SERVERPROPERTY('EngineEdition') AS EngineEdition;

GO

Useful Notes

• How to extract the contents from an .ISO file without burning the .ISO to disc.
• Copying a SQL Server Database to Another Environment - (RESTORE FILELISTONLY command was very useful)
• How I completely remove a SQL Server database – Guaranteed to work – At least on my current setup - (Stop Services -> Delete Db Files -> Start Services -> Delete DBs) - 2 mins deletion

Quick Script encompassing the steps from above notes

Step 1 - Stop SQL Server
NET stop MSSQLSERVER

Step 2 - Remove DB Folders (MDF, LDF Files)
rmdir /s /q "C:\Databases\VOL\Data\"
rmdir /s /q "C:\Databases\VOL\Log\"

Step 3 - Recreate Same file path for Restore
mkdir  "C:\Databases\VOL\Data\"
mkdir  "C:\Databases\VOL\Log\"

Step 4 - Start SQL Server
NET start MSSQLSERVER

Step 5 - Run DB Restore script
Db Restore Command

Restore Script

• Identify mdf, log files from bak files
• Set DB to single user to restrict access
• Restore DB
• Set DB to multi user mode

RESTORE FILELISTONLY

FROM DISK = N'E:\TestDB_backup_20150210_000143.bak'

use master

ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE TestDB

FROM DISK = N'E:\TestDB_backup_20150210_000143.bak'

WITH REPLACE,

FILE = 1

    , MOVE N'TestDB_DAT'

        TO N'C:\databases\vol\DATA\TestDB.MDF'

    , MOVE N'TestDB_LOG'

        TO N'C:\databases\vol\LOG\TestDB_log.LDF'

    , NOUNLOAD, STATS = 10

   

ALTER DATABASE TestDB

SET MULTI_USER

WITH ROLLBACK IMMEDIATE

GO

Tweak it as you need :)

Happy Learning!!!