Thursday, January 21, 2021

SQL version control - schema snapshots

Database version control is one of the most critical tasks in ensuring the safety and reliability of the software development process and the operation of data-driven applications. Every code deployment coincides with a particular state of the database supporting it - the code and the database must be in sync. If you roll your application back to a point in time you may also have to roll back the database schema changes or your application may become unstable or non-functional. 

Many database administrators and developers commit the database change script or the whole database create script in the repository together with the application code, and that serves the purpose, but it's far from being efficient or convenient. Our SQL Schema Compare tool offers a better way, schema snapshots. 

A schema snapshot in this context is a small encrypted file that contains 100% of the database schema information in it. You can manually take schema snapshots of a given database any time you decide to through the SQL Schema Compare GUI or, you can use the included schema compare Command Line utility to automate the process and take snapshots on a schedule. Furthermore you can automate the decision on whether you need to store the snapshot in the repository or not by comparing the current snapshot with the previous one: if there are no changes you can discard the new snapshot otherwise you commit it to repository. 

Utilizing schema snapshots you can then do any of the following:

  • Create a copy of the database (schema only). To do that, you create a blank database, then compare the desired source snapshot with the blank database and generate the "sync" script which will make the blank database exactly the same as the database from which the snapshot was taken. 
  • Compare any two snapshots to each other to generate the changes that have occurred in the time period between the two snapshots. 
  • Compare a snapshot directly to a live database and generate synchronization script to revert the database back to the state it was when the snapshot was taken. 


Wednesday, January 20, 2021

Free SQL Server tools

Our product offering includes 6 awesome tools that are free, you don't have to pay a dime for:
  • SQL Schema Compare - allows you to compare and synchronize the schemas of any 2 SQL Server Express databases, no expiration, no limitation and no external dependencies other than .NET framework. Supports from SQL Server 2005 to SQL Server 2019. One of the best schema compare tools you will find in the market. Cost: FREE
  • SQL Data Compare - allows you to compare and synchronize the data between any 2 SQL Server Express databases, no expiration, no limitation and no external dependencies. Cost: FREE
  • Documenter - document any database for free. The only catch is that the documentation will include a "this is an unlicensed version" text throughout the documentation - a bit of inconvenience but does not limit the functionality.
  • Script Executor  - build script execution packages with constraints and conditional branching and run them interactively, via the command line, or deploy them as executable packages. FREE for personal use only. 
  • SQL Search - you can run it as a stand-alone application or you can add it as an add-in to SQL Server Management Studio. No restrictions, no expiration. Cost: FREE 
  • xSQL Profiler - Tracing, monitoring and event collection of multiple SQL Server instances from one location FREE for one SQL Server Express instance.

SQL Server versions

Here's the list of SQL Server versions from SQL Server 2000 to SQL Server 2019:
  • SQL Server 2019 -> v 15.0.x (October 2020 CU v 15.0.4073.23)
  • SQL Server 2017 -> v 14.0.x (September 2020 CU v 14.0.3356.20)
  • SQL Server 2016 -> v 13.0.x
  • SQL Server 2014 -> v 12.0.x
  • SQL Server 2012 -> v 11.0.x
  • SQL Server 2008 R2 -> v 10.50.x
  • SQL Server 2008 -> v 10.0.x
  • SQL Server 2005 -> v 9.0.x
  • SQL Server 2000 -> v 8.0.x
If you want to see what version you are using just run SELECT @@VERSION;

DATETIME vs DATETIME2 - 5 reasons why DATETIME2 is better

Recommendation first: if you are not sure whether to choose Datetime or Datetime2 the answer is choose Datetime2. Why?
  1. Higher precision – up to 7 fractional digits for datetime2 vs 3 for datetime 
  2. Higher level of accuracy – datetime rounds the last digit to an increment of .000, .003 or .007 whereas datetime2 supports accuracy of 100 nanoseconds 
  3. Wider range of values – datetime2 supports dated from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.9999999 whereas datetime supports dates from 1753-01-01 00:00:00 to 9999-12-31 23:59:59.997 
  4. Space requirements – depending on the fractional precision you require datetime2 needs from 6 Bytes to 8 bytes of space whereas datetime requires 8 bytes 
  5. Compliance with standards: datetime2 is compliant with both ANSI and ISO 8601 whereas datetime is not.
For a more in depth review of those reasons you can check our other post here.

SQL Server Editions

SQL Server comes in a variety of editions each of which targets different audiences based on performance, runtime and price requirements. Here is a quick summary of the editions currently available for SQL Server 2019:

Express: SQL Server Express edition is free and it's one of the best choices for small software vendors, developers, and hobbyists learning and building data-driven client applications. There's also a lightweight version of Express called LocalDB which runs in user mode and is installed quickly without having to configure anything. SQL Server Express does have some limitations but it can be seamlessly upgraded (except for the money you need to pay) to higher end versions. Some of the limitations you should be aware of: 

  • max compute for a single instance is 1 socket or 4 cores
  • max memory buffer pool is 1,410 MB
  • max DB size is 10 GB
  • does not support most of the high availability features like log shipping, fast recovery etc.
  • only supports subscriber replication
  • SQL Profiler and SQL Server Agent are not available
Developer: SQL Server Developer edition is the best choice for hard-core developers as it includes all the functionality that the highest edition (Enterprise) includes so you can build and test any kind of application. However, this edition is restricted for development and testing only - it can not be used in production, therefore, as a developer you must consider the costs involved when deciding to utilize certain functionality that may only be available in the expensive Enterprise edition. 

Web: SQL Server Web edition targets Web hosts and Web VAPs aiming to lower the total cost of ownership. Limitations include:
  • max compute 4 sockets or 16 cores
  • max memory for buffer pool 64GB
  • missing some high availability features like backup compression, fast recovery etc.
  • missing some scalability features like resource governor, partitioned table parallelism etc.
  • No encryption for backups
  • Subscriber only merge replication
Standard: SQL Server Standard edition targets departments within large organizations and small organizations delivering basic data management and business intelligence.  Limitations include:

  • max compute 4 sockets or 24 cores
  • max memory for buffer pool 128 GB
  • missing some high availability features like mirrored backups, fast recovery etc.
  • missing some scalability features like resource governor, partitioned table parallelism etc.
Enterprise: SQL Server Enterprise edition is the ultimate edition that includes everything Microsoft has got to offer, it provides high availability, scalability, end-to-end business intelligence, fast performance, unlimited virtualization etc. The only limits on scalability are those dictated by the OS limits and the max relational database size being 524 PB

Tuesday, January 19, 2021

SQL Search tool for SQL Server is free for all

 Our SQL Search tool for SQL Server is free with no restrictions and:

  • you can run it as a stand-alone or as an add-in to SQL Server Management Studio;
  • it supports both SQL Server and SQL Azure;
  • it allows you to conduct a simple search using "like" or "exact" search criteria, an advanced search using SQL Server expressions, that combines multiple terms with the "and", "or" and other SQL operators, or a very complex search using regular expressions;
  • it allows you to search in multiple databases at once;
  • you can search not only the regular database objects but also SQL Server jobs including job steps and statements executed on each step.
Download now and check it out. Let us know how we can make it even better, we would greatly appreciate it. 

Oracle Data Compare v5 available

Our Oracle Data Compare tool is getting better with every new build we release. Version 5 now supports all Oracle versions from Oracle 9i  all the way up to Oracle 19c. Three week fully functional trial, custom mappings, custom comparison keys, command line utility for automating the data compare and synchronization process, one-click compare and a lot more. 

Download now and see what you have been missing. 

Monday, January 18, 2021

Oracle Schema Compare V5 now available

Version 5 of xSQL's Schema Compare tool for Oracle that provides for comparing and synchronizing objects in Oracle schemas is available for download from our website. The new version supports all Oracle versions, from Oracle 10g up to Oracle 19c and comes with a 3 week fully functional trial. 

Some of the new features include:

  • Exposes over 100 options for fine-tuning the comparison operations
  • A storage and management of the comparison sessions allows for fast, one-click compare
  • Schema filters can exclude Oracle objects based on complex rules
  • Proprietary snapshots, that store Oracle schemas in files, allows for schema archiving or offline comparison
  • Extensive logging provides operational, execution or error details when needed
  • A target-specific synchronization script, combined with a synchronization log, captures all schema changes quickly and accurately
  • A command line utility allows for unattended or scheduled comparisons
  • A step-by-step wizard simplifies the management of the command line config files
Schema Compare for Oracle runs on Windows operating systems, client and server, that have the .NET framework 4.6 or higher installed.

Wednesday, July 5, 2017

Oracle Data Compare 3.0 released

The best Oracle Data Compare tool in the market is now better. We just released version 3.0 with full support for Oracle 9i, Oracle 10g, Oracle 11g, and Oracle 12c.

The new version removes the dependency on the Oracle unmanaged ODP.NET provider and replaces it with the compact, easily deploy-able Oracle Managed Data Access Provider for .NET. Furthermore the new version is faster, more robust, has a better GUI and a much improved command line utility.

Download your free trial now and see for yourself.

Monday, July 3, 2017

A license for your opinion

We want to hear your thoughts regarding our xSQL Profiler tool. In exchange we will grant you a "5 SQL Server Instances" license with 3 years of unlimited upgrades, a total value of over $1.5K.

What are the conditions:

  • you must have used xSQL Profiler in the last 3 months and preferably are currently using it. 
  • you agree to email us your candid answers to the following questions:
    • how long have you used xSQL Profiler?
    • what exactly do you use it for?
    • how often do you use the tool?
    • what do you like about the tool?
    • what new features would be helpful to you (be specific)?
    • how would you change / improve the existing features to make them more useful?
  • you agree to reply to at least 2 follow-up emails for the purpose of clarifying your suggestions. 
If you are interested please email us at support@xsql.com and reference xSQL Profiler on the subject line. 

Only the first 10 people to email us will get the license. If you are not one of the first 10 we will decide on a case by case bases whether we will grant you a license based on the quality of your feedback.