An introduction to CDC can be found here.
When CDC (Change Data Capture) is enabled on a database, a SQL Agent job is automatically created called cdc.XXX_capture. The capture job reads asynchronously from the SQL Server transaction log to retrieve all modifications (DML statements) made to all CDC enabled tables. The changes are then written to a change table.
In this blog post I’ll describe the CDC capture job and how it can be configured.
An introduction to CDC can be found here.
All data modifications in a CDC enabled table are saved in a separate change table. But in order to limit the size of this change table, a SQL Agent job is automatically created, named cdc.XXX_cleanup, that deletes rows older than the configurable “retention value”. The job is created once per database at the moment when CDC is enabled.
Currently, SSDT (SQL Server Data Tools) in Visual Studio doesn’t accept self referencing three part names. That is, objects qualified as DatabaseName.Shema.Object, where the database is the same as where the object resides.
A build will generate an error similar to this one:
[red_box]Error: SQL71561: View: [dbo].[VIEW1] has an unresolved reference to object [SSDT1].[dbo].[TBL1].[/red_box]
As a result, the solution won’t build as long as there are unsolved errors of this kind. Three part names in stored procedures only results in a warning for some reason, and won’t stop a build:
[yellow_box]Warning: SQL71562: Procedure: [dbo].[PROC1] has an unresolved reference to object [SSDT1].[dbo].[TBL1].[/yellow_box]
Some types of data are shared across systems. One of the best examples of data that is shared across systems is customers. They can appear in a lot of different systems, perhaps a CRM system and a sales system. This means there is a risk of conflicting information. If the CRM system has one customer address, and the sales system another, which one is the correct?
In master data management discussions the phrase “single version of the truth” is often used. What it means is that there must be one reliable source for information regarding, for instance, customers.
Not all types of information are suitable for a MDM solution. Borrowing terminology from data warehouse concepts, dimensions can be managed in a MDM solution. Facts are not suitable in a MDM solution and are better managed by the traditional OLTP systems.
A MDM solution is also a good place to centralize data quality improving activities.
A MDM solution has to be architected so that some systems publish information to the MDM solution, and other systems subscribe to the information. Some systems can be both publisher and subscriber.
Note that normally in a MDS solution, each source system has its own copy of the data. There are solutions where the master data is removed from all source systems and they instead have to look up that information from a centralized master data hub. However, those solutions require rather large changes in the source systems that may not be practical.
In this blog post I’ll briefly describe Microsoft’s solution for MDM – SQL Server 2012 Master Data Services, or MDS. I’ll explain the central concepts in MDS, but I won’t show the steps to actually use MDS. That may be another blog post.
On a heavily utilized system with lots of transaction, shrinking the log file may be difficult.
The normal procedure when shrinking the log file, is to first do a backup of the log. Then directly after that try to shrink the log file. But if there are active transactions writing to the log file, shrinking may not be possible, or at least not shrinking as much as expected.
Change Data Capture (CDC) was first introduced in SQL Server 2008, and is a tool that is used to keep track of data changes (DML statements) in tables. CDC must be enabled on the database level, then on the table level, and lastly on the column level (optional).
When CDC is enabled on a table, a new similar table is created, but under the cdc schema. In the new table all changes to to base table are recorded.
Traditionally, triggers have been created to track changes in tables. But CDC is a more light weight technology since the transaction log is read asynchronously for changes. Triggers are part of the original transaction.
CDC is only supported in the Enterprise or Developer editions of SQL Server.
Read on to see a demonstration of CDC basics.
An aggregate in SQL Server will ignore NULL values. Check out this simple demonstration to learn how aggregates work with NULL values.
If you are using SQL Agent together with SSIS and if you have a batch job solution with hundreds of interdependent SQL Agent steps spread across dozens of servers, you may have run in to some maintenance problems.
For complex batch solutions, a Business Process Automation software can be of great help. In this blog post I’ll give a brief introduction to Automate BPA Server from Network Automation.
“Why can’t I login after the database restore?”
As a DBA, this is one of the questions I get most.
Sometimes the error log files in SQL Server get too large to view and manage in a practical way in Management Studio. SQL Server keeps a number of log files (between 6 and 99), and a new error log file is created each time the SQL Server Service is restarted.
If you want to cycle the logs more frequently without a service restart, you can do so with the system stored procedure sp_cycle_errorlog. This way, you can get smaller and more log files.