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!

Read More

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.

Read More

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.

Read More

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) CodeProject

Read More

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. 
CodeProject

Read More

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. 

CodeProject

Read More

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. 

CodeProject

Read More