Wednesday, September 21, 2016

xSQL Schema/Data Compare database list is empty

Applies to: xSQL Schema Compare v 7.0.0 to v 9.0.0; xSQL Data Compare v 7.0.0 to v 9.0.0

Summary: When adding databases to the workspace both in xSQL Schema Compare and xSQL Data Compare the first step after connecting to the specified SQL Server is to read the list of databases from the server and make that list available for the user to select the databases he/she wishes to work with. SQL Server stores the list of the databases in the master database so in order to obtain that list the account that is used to connect to the SQL Server instance should have permission to read from the master.

In the case of SQL Server this is almost never a problem as all users usually have permission to read from the master however, the situation is different in SQL Azure. Often, the developers only have access to certain databases on an Azure account and do not have permission to read from the master. Therefore, in these cases, the Schema and Data Compare tools are not able to read the list of available databases.

Symptoms: After providing the login credentials on the "Add Database" dialog window and clicking on the "Refresh" link-button to populate the "Databases" window the list fails to populate and the database window remains empty. You are thus not able to proceed with the comparison and synchronization operation.

Cause: The account used for connecting to the SQL Server instance or SQL Azure does not have permission to read from the master database.

Resolution:

  • Install the latest build from our website - builds v. 9.0.1.0 and higher include a fix that eliminates the need to read from the master db, but only for the default database for that user. If you need to add a database other than the default one then continue reading. 
  • Grant read permission on the master database to the account you are connecting with. 
  • Login with a different account that does have read permission on the master database.

Future versions of the compare tools: both comparison tools will eliminate the need for reading from the master database by allowing the user to specify the database(s) to be added.

Workaround: xSQL Schema and Data Compare tools come with Command Line Utilities that allow you to specify all the comparison and synchronization details, including the database name, in a XML file which is given as an argument to the comparison command, thus eliminating the need to read from the master. The command line utilities are intended primarily for unattended, automatic database comparison and synchronization operations and they provide great convenience and utility for that purpose but you don't get the benefits of the interactive GUI.



Tuesday, September 13, 2016

Migrating to Azure - a very helpful article

Our friend Albert Tollkuci just posted an article about migrating to Azure that a lot of you will find very helpful: http://www.tollkuci.com/blog/post/Migrating-to-Azure-Quirks-Tips

Of course we like the article even better since he has included our Schema and Data Compare tools into his migration process but even if our tools were not mentioned we found the article worth sharing.

Monday, August 22, 2016

xSQL Compare v9 w/ support for SQL 2016 and SQL Azure v12 now available

We are excited to announce the release of version 9 of our SQL Server Schema and Data Compare tools with full support for SQL Server 2016 and latest features of SQL Azure v12! The new version includes support for security policies, encrypted columns, stretched databases, system-versioned tables etc. A complete list of the new and improved features included in this version can be found at http://www.xsql.com/products/sql_server_comparison_bundle/release_notes/v9/ .

Here are a few important things to note:

  • if you have an active Silver or Gold subscription you can simply download and install the latest build from our site and you are set. The new build will install over the exiting one and the license will remain intact, support for SQL 2016 will be included.
  • if you have a perpetual license with an active maintenance agreement, you should have received a new license via email last week. Please note that the new license was sent to the email address that was used when purchasing the original license. Once you activate the new license the tools will then support SQL Server 2016.
  • if you have a perpetual license but no active maintenance you will need to purchase an upgrade license of the same "class" as the original license in order to be able to compare SQL 2016 databases. You may also switch from a perpetual license to a subscription if you wish. Note that you can download and use version 9 with your old license but SQL 2016 comparison and synchronization will not be supported.
  • if you are a lite edition user you may download the latest build to take advantage of the improvements and fixes that the new version brings, but unfortunately you will not be able to compare and synchronize SQL Azure databases.

Please send all questions and / or suggestions to support@xsql.com 

Thursday, April 14, 2016

Let us do your work for FREE

Are you struggling with a SQL Server problem / task? We can help for free, no strings attached, no obligation whatsoever. In this little “transaction” you are supplying us with real life scenarios and we are giving you a certain amount of expert hours in return. Here are the conditions /rules:
  • The problem or task should in general not require more than about 2-3 hours to solve / complete.
  • We will have the right to de-personalize and generalize the problem / solution and then publish it as we see fit for the whole SQL Server community to benefit from it.
  • We will choose the problems / tasks to work on based on the appeal we think it will have to the community at large and not based on how important or urgent it may be to you.
  • We will let you know within 48 hours if we will work on your problem and when to expect a solution. Our goal, but not guarantee, will be to let you know within the day and have a solution ready within 2 days.
  • No guarantees, written or implied. The responsibility for adopting / implementing the solution we provide is solely yours.
We may put a form up on our website but for now please email the task/problem you would like us to work on at support@xsql.com – here are some guidelines:
  • Write “Solve my problem” on the subject line
  • Provide the following information on the body of the email:
    • Your full name
    • Company name
    • Your position in the company
    • Your email address
    • Clear and concise description of your task / problem
      SQL Server versions/editions where the solution will be implemented
    • Acceptance criteria for the solution we provide
    • Your desired “deadline” (when you would ideally like to receive the solution from us)
Your personal information, your company information and any information related to your environment will never be published. The problem description and the solution will be completely anonymized before they are published (if and when we decide to publish them).

Go ahead and email us, see how good it feels to have an expert work for you and cost you nothing!

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)