In the article “Understanding SQL Server database static data and how it fits into Database lifecycle management” we covered what is static data in general and numerous reasons why it needs to be version controlled.
January 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, 2017If only schema changes are made in the source control repository, a 3rd party tool – ApexSQL Diff can be used to perform the schema synchronization. ApexSQL Diff is a tool that can be used to compare and synchronize schema differences between live SQL database, source control repositories, database backups, script folders and ApexSQL snapshots. It can handle any dependencies, triggers, etc. and create an error-free synchronization script, while ensuring a database integrity.
January 13, 2017In order to begin this process, first the tSQLt, (SQL Server database unit testing framework) has to be installed on the database which will be used for unit testing. This can be done manually in SSMS by right-clicking the selected database in the Object explorer, find the Unit tests menu and select Install tSQLt.
January 13, 2017ApexSQL Generate is a tool that can populate SQL databases with test data using multiple generators for each column. It supports a full range of SQL data types and has an ability to recognize them in a loaded SQL database and provide real-world test data based on contained data types. It can generate: randomized test data using the Random generator and various options, and using the Regular expression generator, sequential data using the Incremental generator, and much more.
January 13, 2017
ApexSQL Build is a tool that can build a new SQL database, update an existing SQL database, and consolidate scripts. Additionally, it can deploy databases directly from the source control repository, create C# solution or executable installer, and build SQL databases from scripts, ApexSQL snapshots, or script folders.
January 13, 2017
Before proceeding with committing database changes, a database first needs to be linked to the source control repository:
January 13, 2017
Once you have your continuous integration (CI) and or continuous delivery (CD) aka Database lifecycle management process built, configured, successfully testing and ready to roll, the next decision point is when to run it and how.
January 13, 2017
In general, the term “failover” refers to switching from a previously active machine, other hardware component, or network to a passive (or unused) one, to sustain high availability and reliability. In most cases, a failover event is an automatic process, while a the similar event, switchover, requires manual intervention in switching between the active/passive elements.
January 13, 2017
When working with SQL Server database static data in the context of version control, there are several key requirements.
January 12, 2017
As a DBA considering version controlling a database, there are a lot of challenges to setting up the team with software to check in changes, to manage the development environment (shared or dedicated models) and set the rules of the game – locking vs not locking etc. But before committing to this fully, a potentially preliminary stage would be to set up a centralized system, where the database was automatically committed to source control each night. This could be done by a single person and wouldn’t require developer participation or even knowledge.
January 10, 2017
In the article, “What is a data dictionary and why would I want to build one?” a data dictionary was described and compared to other alternatives to documenting, auditing and versioning a database. The article also described the difference between a Dumb and a Smart data dictionary.
Now that we’ve whetted your appetite, this article will explain how to create a smart data dictionary using XML schema change exports from ApexSQL Diff.
January 10, 2017
When working on a SQL database development, there is sometimes a requirement to keep two databases in sync. For example, in a development environment there is a need to automatically synchronize changes with a QA database frequently, so that tests can always be run on the most recent version. The implemented mechanism should handle this by detecting a specific schema change in the DEV database and automatically synchronizing with the QA database, fully unattended and on a schedule. The whole process should be run unattended and to be fully automated and the databases will be updated in near real time as we’ll schedule the process to run every 15 minutes.
January 9, 2017
One of the challenges SQL Server development teams face is whether to version control a database and if so how to keep the development database in sync with the source control repository.
January 9, 2017
The core component of SQL Server is the Database Engine service. It is identified with the Windows service named MSSQLSERVER (sqlservr.exe) for the default instance, and for the named instance, the customized instance name is appended to the name of the service, e.g. MSSQLTESTDEPT.
January 5, 2017
One of the main benefits of a SQL database version control is that any version of an object committed to the repository, is available through the revision history. With that being said, browsing the history allows seeing all versions of the specific database object, committed over time, and reverting any version from the history in order to apply it against a database. By utilizing such functionality, a database can be brought back to a working state in case some change caused a problem
December 30, 2016
Non-transactional data in tables that is never (or rarely) changed, for example currency codes, postal codes or personal ID numbers, is often referred to as “static data” (also known as lookup or reference data). Although static data isn’t part of the database schema, it can be thought of in much the same way. Simply creating test data for static tables, for testing purposes, may allow the tests to be successfully executed, but often won’t render meaningful looking results and in some cases, could result in failure, if the static data was tightly coupled to application/database function. Static data tables also tend to be much smaller than transactional tables.
December 30, 2016
Overall stability and availability of a machine in a specific time range is usually called system uptime. This measurement represents a period (sometimes percentage) when the system is stable and performing without unattended reboots, except for maintenance and administrative purposes, and works without issues. The opposite, system downtime is a period when machine is turned off (on purpose), or encounters experiences problems that result is the system being unavailable to users and processes. The combination of these two measurements is called system availability which is both identified and tracked with Windows Event Viewer (System Log).
December 23, 2016
SQL Server’s built in abilities to track query execution via wait statistics is critical to resolving performance problems. Wait statistics or response time analysis are metrics that provide the ability to measure the time needed for the database to respond to executed queries. It doesn’t simply represent the time needed for a query to complete execution, but the wait statistic is also the measurement of the time the query has taken for each step in its execution. That information becomes the basis for identifying bottlenecks that are affecting the execution time.
December 20, 2016
For the purpose of versioning a SQL database, there should be a mechanism that allows for quickly linking a database to an existing or to a blank repository and to initially Commit all database objects, or at least a set of objects that needs to be versioned.
December 16, 2016
