Microsoft SQL Server
SSIS Sort
Sorted data flows in SSIS
Sometimes a data flow in SSIS needs to be sorted. It may be because the business rules require sorted data, or it may be that you want to use either a Merge or Merge join component. Those component won’t work if the data isn’t sorted previously.
So, how does SSIS know if the data is sorted?
Lookup
Add missing lookup in SSIS
Before a fact table is loaded, a common pattern is to do a lookup in SSIS before the loading. The lookup can be some key from a dimension for instance, or maybe a repository of primary keys in a MDM solution. But what if the lookup doesn’t return a matching value? Sometimes when this happens, we want to insert new values to the lookup table (to get the key) before the loading of the fact table starts. Here I’ll show one way to handle this scenario.
Microsoft SQL Server
T-SQL function SERVERPROPERTY
The T-SQL function SERVERPROPERTY returns a lot of properties about the current SQL Server instance. The list of parameters it accepts can be found on Technet. On the top of the page, choose the version of SQL Server you are running.
Microsoft SQL Server
Table Valued Parameters in Stored Procedures
In most cases stored procedures are developed so that it accepts a number of input parameters with a single value. But a common requirement is to be able to submit multi value parameters. That is, parameters that accept several values as opposed to a single value.
In SQL Server 2008, table values parameters in stored procedures was introduced.
Microsoft SQL Server
MDS Error httpwebrequest_webexception_remoteserver arguments: NotFound in MDS
Microsoft SQL Server
Reading the SQL Server error log with T-SQL – xp_readerrorlog
SQL Server keeps a configurable number of error log files as text files on disk. So you can’t simply use T-SQL directly to read those text files. Instead you can use the system stored procedure xp_readerrorlog.
Microsoft SQL Server
SQL Server error log location
The SQL Server service keeps its own error logs outside of SQL Server in a number of text files. The location of the SQL Server error log is specified in a startup parameter for the SQL Server service.
Microsoft SQL Server
Manual failover in a SQL Server cluster
In this blog post, I’ll demonstrate how to find the currently active node in a SQL Server cluster, and how to do a manual failover to another node.
Note that a failover will result in a short down time for your SQL Server environment. This is because a recovery (rollback and roll forward of transactions) will be performed on the new active node in the SQL Server cluster. In an environment with long running transactions, the time for the recovery may be substantial (how to check the latest recovery time). So don’t do failover in a production environment unless absolutely necessary. Preferably, use a cluster lab environment to test things out first.
Microsoft SQL Server
CDC Capture job
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.
- Go to the previous page
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- …
- 10
- Go to the next page