Tuesday, April 12, 2016

Build database schema snapshot functionality into your .NET application

If you wish to build your own .NET application that allows the user to take database schema snapshots on demand or schedule them to be taken at certain times, report on differences between different versions of the schemas, etc. you can do that easily by utilizing our xSQL Schema Compare SDK. To demonstrate how easy it is to use the xSQL SDK here is a simple example written in C# that takes a schema snapshot of the AdventureWorks database.

The xSQL Schema Compare SDK support SQL Server 2014, 2012, 2008/2008R2, 2005 and SQL Azure v11 & v12.

Friday, April 8, 2016

Why is my xSQL Silver Subscription showing as expiring soon when I just renewed it?

Question: I just activated my Silver Subscription renewal license but I am still seeing the original subscription end date on the about screen! Is this normal?
Answer: Yes, this is normal. The licensing mechanism looks for the first valid, non-expired license and displays the information for that license. Once that expiration date is reached it will automatically display the expiration date for the new license that you just activated.

This applies to all tools included in the xSQL Silver / Gold subscriptions: xSQL Schema Compare, xSQL Data Compare, xSQL Script Executor, xSQL Builder, RSS Reporter and xSQL Schema Compare SDK.

If you have any questions regarding our tools please let us know and we will try to post the answers on this blog for all interested users.

Thursday, April 7, 2016

Comparing two tables with millions of rows

Nowadays the number of the users of our SQL Server tools dealing with very large data sets is growing rapidly and that has been reflected in a significant increase of queries regarding comparing the data in two large databases or even just two very large tables. As a result we decided to explain once more how our SQL Data Compare handles the comparison and what the user can do to speed the process up.

First the basics - here is what happens when you say compare ServerS.DatabaseS.TableS with ServerT.DatabaseT.TableT (I am using S to denote the Source server /database / table and T to denote the Target server /database/ table):

  1. xSQL Data Compare  will connect to both servers and read the table definitions for both sides
  2. It will then try to identify a common unique key (primary key is the first choice) that will be used to pair the rows. At this point it gives the user the opportunity to change the comparison key if the user thinks a different existing key would be better, or even create a custom comparison key. 
  3. Once a comparison key has been selected it's time to compare the two tables:
    1. Read rows from TableS and from TableT and bring them to the machine that is performing the comparison
    2. Pair the rows based on the comparison key
    3. Compare the values for each column row by row
    4. Display the comparison results
  4. Next step is to generate the synchronization script that will make TableT same as TableS
  5. Last step is to execute the synchronization script on the target server.
Now, steps 1 and 2 above are very quick and not taxing at all for any of the systems involved, however, when you reach step 3 is where the "trouble" starts. Let's suppose that tableS and tableT contain around 100 million rows each and each of them is over 10GB in size. You can easily imagine how expensive this operation will be - the performance will depend on the parameters of the servers hosting those databases, parameters of the machine running the comparison and the speed of the connection between the machine running the compare to each of the servers. Here are the things that xSQL Data Compare does to make this kind of comparison possible (note that a lot of the tools in the market will not be able to do this at all):
  1. carefully controls memory usage on the machine running the comparison by processing the data in chunks and serializing the data to disk
  2. dynamically managing the comparison results grid to only load portions of the data at time, on demand. 
No matter how large the tables may be, the xSQL Data Compare will succeed in comparing and synchronizing those tables as long as there is sufficient free disk space on the machine doing the compare. However, understandably, the operation may take a very long time. So what can the user do to speed up the operation? There are two simple but very important steps that the user can take:
  1. exclude from the comparison the columns that may not need to be compared, especially the blob type columns. 
  2. use the "Where Clause" to exclude the rows that do not need to be compared. For example if your table contains a "date updated" time stamp and you know that you synchronized those tables a week ago then you can use the where clause to instruct the Data Compare tool to only compare rows where the "DateUpdated" is > than the last time you synced the tables. This will significantly reduce the amount of work and the process may only take a few minutes instead of a few hours. 
 In order to exclude certain columns and set a "where clause" you would need to drill down the the table pair that is being compared (see the screen shot below)

Wednesday, April 6, 2016

SQL Search tools all developers and DBAs can use

If you work with SQL Server sooner or later you will be looking for a good sql search tool that allows you to search through all the objects and the code in a sql server database to find a certain stored procedure or a certain function or a trigger or a view or a table etc. We have two great tools that I use on a regular bases and that you should find very helpful too:

  1. xSQL Object Search 
    • a completely free sql search tool
    • a sql search tool that does not need to be installed - just a simple, stand alone executable that has no dependencies of any sort
    • a sql search tool that allows you to search in all databases in a sql server at once
    • a sql search tool that allows you to search for a given phrase in the name of the object, in the definition of the object or in both; 
    • a sql search tool that allows you to use matching criteria such as "contains", "exact match", "sql expression", "regular expression" etc.  
  2. xSQL Documenter - this one was not intended as a sql search tool but I find it very convenient to use for that purpose. Especially helpful are the color coded dependency graphs that are shown for each object and allow you to easily navigate through the dependency tree and locate the object you are after. 

How to move your SQL Server database to SQL Azure – easy as 1, 2, 3

Are you preparing to move your SQL Server databases to SQL Azure? In the words of one of our customers that just completed his transition "using xSQL's compare tools the process was as easy as 1,2,3": 
  1. Download and install the xSQL Comparison Bundle (1 minute)
  2. Run xSQL Schema Compare – add your SQL Server / Database to the workspace; Add your SQL Azure / Database to the workspace; Compare, Generate Sync Script, Execute (4-8 minutes*)
  3. Run xSQL Data Compare – add both servers/databases to the workspace; Compare, Generate Sync Script, Execute (5-20 minutes*)

All done, now your database is on SQL Azure. 

* time estimates for steps 2 and 3 depend on the database size and are ranges from this one customer's experience with about 12 databases that they transferred. 

Tuesday, April 5, 2016

Schema Snapshots – how to preserve the history of database schema changes

What did my database schema look like a month ago, a year ago? What has changed between then and now? If you are a DBA, or even if your title does not say DBA but you are playing the DBA’s role, you have most likely run into those questions more than ones. In most cases such questions are not simply a matter of curiosity but rather critical compliance related questions. As a DBA you need a way to quickly and efficiently identify the changes that have happened to your database schema during a given time interval.

Of course, one option would be to restore the database at that point in time and compare that restored copy with the live copy. However, depending on the size of your database that may prove to be a much more challenging task that it sounds.

Our “Schema Snapshot” command line utility that comes with our Schema Compare tool makes your job very easy. Here is what you can do:
  1. Create a batch file that contains the following three commands:
    • CD R:\DBSchemaArchive (replace this path with the path where you will be storing the schema snapshots)
    • set d=%date:~7,2%-%date:~4,2%-%date:~10,4%  (get the current date from the machine and put it on a variable which I am calling “d”. 
    • "C:\Program Files (x86)\xSQL Software\SQL Server Comparison Bundle v7\xSQLSnapshot" /s:xSQLDemo2014 /d:AdventureWorks /sp:AdventureWorks_%d%.snpx  (in this statement you will need to replace the server name and the database name – you can also provide credentials if you need to)
  2. Schedule the above batch file to run daily and you are done. The Schema snapshots have a very small footprint and normally take very little time to complete. 
The Schema Compare tool allows you to then compare any two of those snapshots, or any of the snapshots with the live database and see what has changed. 

Thursday, February 25, 2016

Download and Win!

Download any of our tools from now until March 4, 2016 and you can win a SQL Server book of your choice from Amazon (up to $50) as well as 1 year Silver Subscription, a $249 value.

To be entered in the drawing you must type in the code DWIN16 into the “Comments” field of the download form.

Chances of winning are 1:100. Winner selection: random drawing. No purchase necessary.

Monday, January 25, 2016

Up to 80% off based on the country

When is it the best time to buy xSQL Software's tools in 2016? Depends on the country you are visiting from. For example, if you are visiting our website from Australia this week you will see the regular prices crossed out and new lower prices in effect. Depending on the country the discounts can be as high as 80% of the regular prices.

Check out the calendar for the first quarter of 2016 here: http://www.xsql.com/promotions/ and see when your country's week is. If you don't see your country on the list don't worry, we'll be updating the calendar throughout the year and all countries will be included.

Monday, October 26, 2015

xSQL Compare Tools with Azure support released

We just released version 7 of our SQL Server Schema and Data Compare tools with full support for SQL Azure v11 and SQL Azure v12. You can now compare and synchronize SQL Azure databases with on premise or on the cloud SQL Server databases. Platform differences are handled automatically and are transparent to the end user. Details of the new release are listed here http://www.xsql.com/products/sql_server_comparison_bundle/release_notes/v7/

Download the new version now: http://www.xsql.com/download/sql_server_comparison_bundle/

Monday, June 1, 2015

To push or not to push, that is the question? Comments and suggestions will be rewarded.

For the last 11 years we have chosen to not push our users to pay for our tools. In fact, the “edict” was to focus on providing value and avoid any sales gimmicks. The thinking was that happy users will convert to paying customers on their own volition. Well, looking at our stats we have come to realize that there is something not quite right with that approach. The results of an informal study we conducted recently show that the users will only consider paying when they “hit a wall” and even then, when the cost of “switching” is negligible as it is in the case of the type of tools we sell, the value that the user may have received from using your tools for years, does not factor positively in the purchasing decision. In fact, a hypothesis was made that it may actually negatively impact your chances of being selected since the user has a hard time reconciling the additional functionality with the additional cost. Case in point: a user has been utilizing the lite edition of our SQL Server Schema and Data Compare tools for years at a cost of $0. Now he/she runs into a case that the lite edition does not cover and therefore the purchase of a license at a cost of $499 is required – all of a sudden a price that can be easily justified considering the hours saved (in fact the user in question has likely saved 10 times that amount by utilizing the lite edition of the tools) appears to be ridiculously high! “I have used those tools a thousand times for free and now you want me to pay $499 for just this one project!” We were not able to prove or disprove the hypothesis but there is empirical evidence that supports it.

We are considering a significant shift in our approach but are of course worried of the reaction of our users and hence we are seeking feedback before making any big changes. The basic idea is:

  • Raise more “walls” so that the user will likely hit one much earlier. We still want the users to be able to try the tools until they are 100% satisfied with them before paying, but not to the point where it may start to feel like the tools should always be free. 
  • “sprinkle” the free version with a bit of “annoying” seasoning – right now the user experience with the free lite edition of the tools is exactly the same as that of the paid edition. A bit of annoyance may provide an added incentive for the user purchase a license. 

We are currently experimenting with our Script Executor – what we did there is fairly subtle, it does not interrupt the flow and we don’t think it is annoying but we will see what the users think.

Tell us what you think – any comments / suggestions would be greatly appreciated and will be rewarded with a free 6 months Silver Subscription (only if you send your comments/suggestions before June 8, 2015). Either post your comments here or email us at sales@xsql.com.