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, you have a development environment and want 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
SQL Server availability overview
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
System availability overview
Overall stability and availability of a particular 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
Wait statistics = Data
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
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