Challenge
Even now there are some isolated SQL Servers (without internet access or a limited one) that need to have databases updated with data changes on a daily basis. These kinds of SQL Servers need a way to get the latest data changes from the master SQL database.
May 8, 2017
It’s often quite a challenge to keep all SQL databases located on different SQL Servers in sync. As time goes by, a lot of schema and data changes are made on QA databases on daily basis that require to be in sync with Production databases.
To keep everything in sync, there should a system that would be either triggered or scheduled to run the comparison of all SQL databases and synchronize the ones were changes are detected. This system should be also aware of any dependencies during the synchronization in order to keep the SQL database integrity.
April 5, 2017
In the Understanding SQL Server database static data and how it fits into Database lifecycle management, we covered topics from the basics (what is the static data, why it is used), followed by the explanation of how to version control the static data, meaning how to initially commit, how to track changes and how to apply static data from SQL source control to a database.
February 13, 2017For the second use case, when only data changes are made to the source control repository, a combination of 3rd party applications – ApexSQL Build and ApexSQL Data Diff, can be used to perform this Sync step. ApexSQL Build can be used to build a database from the source control repository along with its static data, while ApexSQL Data Diff can be used to synchronize data from the built database to a QA database.
January 13, 2017
In order to version static data, just like database objects e.g. Tables, a mechanism needs to be created to identify only those tables designated as “static” (see below) and to come up with a reproducible process, that will Commit the data in these tables to source control. This article covers this process
December 14, 2016
It’s often needed to have a way of synchronizing data between SQL databases in real time, e.g. when a certain data is changed/added in one place (database) to be synchronized with another database. Changes need to be detected, automatically, on a schedule, and automatically synchronized with another database. This process should be fully automated and run totally unattended.
December 9, 2016
In this article, the last step of SQL Server database continuous integration (CI) workflow, or the first step of a continuous delivery (CD) workflow) the Sync step.
In the previous article, the Test step described how to create and run unit tests against a database. If all tests are passed, the tested database can be compared to a final QA environment or even Production databases, and a synchronization script will be created to publish the changes.
July 4, 2016When confronted with a disaster recovery scenario with a very large database, but a small group of effected records, an opportunity exists to both speed the process and reduce risk of further damaging data, by updating more rows than necessary, simply by narrowing the subset of compared records.
ApexSQL Data Diff offers the ability to narrow the rows that should be synchronized, so only affected rows are updated. But, if the filtering isn’t done properly, “false positives” are risked, rows that are flagged as different/changed and should be synchronized, even if they weren’t in the subset of affected rows. Synchronizing these rows will update “good” data and roll back any production changes that may have been made, further damaging the data.
November 6, 2015When comparing data sources using ApexSQL Data Diff, tables with the same name are mapped automatically. But, what happens with tables and columns with different names when SQL tables are compared to check for data differences?
By default, they are excluded from the comparison process and need to be mapped manually using the Object mapping feature. This feature also allows users to change tables paired by default, i.e. unmapping them and creating customized comparison pairs. This can be helpful in scenarios where the same tables are differently named in the development and the production database, and data needs to be pushed from the development database to the production one.
April 2, 2015In the past two weeks we had some interesting Solution center articles describing Microsoft SQL Server topics – each described as the problem (or the challenge) users may encounter with SQL Server data recovery
Any of recovery options using Microsoft’s SQL Server Management Studio requires the availability of a full database backup. And, the backup must be restored in order to be used as a recovery data source
July 5, 2013Recovering DELETED records is something we would all like to avoid, but no matter how careful we are, it can come to that. Having regular full database and transaction log backups is a necessary practice and a great advantage in this situation, but even then, bringing back the deleted records might not be easy
June 4, 2013ApexSQL Diff and ApexSQL Data Diff provide a user friendly GUI for SQL comparison and synchronization of database schemas and database objects. They both include the command line interface which provides the exact same options, and which you can use to schedule unattended comparisons and synchronizations
But, what can be done when the features that the GUI and CLI provide are simply not enough? In such cases a more flexible solution is needed and the good news is – there’s a programmable API
April 30, 2013When synchronizing large databases that contain millions of records, you will come across several challenges The first challenge is to just compare such databases. When the database tables contain millions of records, their comparison through the ApexSQL Data Diff graphical interface will be very slow and in such cases, it is strongly recommend using the ApexSQL Data Diff command line interface. As there’s no need to show millions of records visually in the tool’s grid, such large databases are processed faster.
April 29, 2013The applications used by travelling sales representatives, or other field workers – delivery drivers, visiting nurses, etc., are designed to collect data from remote locations and then send it to a data center. Also, the data from the data center, occasionally need to be sent back to these remote locations, to keep them up to date.
For example, whenever nurses pay a visit to a patient, they enter the information about the visit into the database on mobile devices. At the end of the day, all these entries created during the day are sent to the central database in a hospital. After that, the nurses can synchronize mobile devices with the database in the hospital data center, so they get the new information about their patients, and also, the information about any new visits they need to make the next day.
In the scenario such as this, there’s constantly a need to synchronize the information from a mobile device to a central database.
April 19, 2013
There are numerous scenarios when data synchronization between the two databases is needed, such as distributing data to different locations, integration of data from different locations into a central repository, or just a simple synchronization between a test and production database.
The recommended solution is to use database replication.
However, even with database replication there’s a need to check whether all the data is synchronized and to synchronize it if needed.
April 4, 2013
DBAs are well aware that downgrading a SQL Server database cannot be done out of the box. Even when the compatibility level of the database that needs to be migrated to an older SQL Server version matches that version, the backup can’t be simply restored. Upgrading can also be a problem.
April 4, 2013
If there was need to restore a SQL Server database backup to an older version of SQL Server there was “greeting” with error messages along the lines of:
Msg 3169, Level 16, State 1, Line 1
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
or
Msg 3241, Level 16, State 7, Line 1
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
and the attempted backup restore would fail.
April 4, 2013
If there is a need to quickly revert an inadvertent change such as an Update without a Where clause, to recover missing or corrupt data, to move data across development, test, integration and production environments or even just to avoid generating a report using a busy production SQL Server database as a data source, it brings the question if there is a way just to restore specific rows instead of restoring the whole database backup.
April 4, 2013“I’ve mistakenly deleted/updated important records in a SQL Server table. Is there any way to get these records back?”
This is a frequently asked question, and very often a headache for DBAs. If they are careful and make regular database backups, they can recover the lost database data even where the SIMPLE recovery database model is used.
If that is the case, then database backups are the only solution to recover the lost database data. The deleted data has to be from the period prior to the database backup creation.
April 4, 2013
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy
