"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

June 26, 2014

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!!!

June 19, 2014

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

Happy Bugging & Learning!!!

Good QA Reads

Good QA Reads
More Reads
Happy Reading!!!

June 18, 2014

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!!!

June 15, 2014

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.

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

June 10, 2014

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!!!

June 03, 2014

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
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!!!