Recently I’ve been making a lot of use of the OUTPUT option for insert and update actions. From this work I’ve already posted about the behaviour of IDENTITY and suchlike.
Now I have looked at the behaviour of OUTPUT within the MERGE command.
Continue reading
Archive | Training RSS feed for this section
MERGE, OUTPUT and $Action
19 May
Primary Key With Non Clustered Index
28 Apr
As a default, SQL Server creates a Primary Key column with a Clustered Index. However, this doesn’t have to be the case.
Continue reading
The Joys of @@IDENTITY, Scope_Identity and IDENT_CURRENT
31 Mar
For several years I have happily relied on @@IDENTITY to return the latest identity of a row when inserting data. However, I have started using the OUTPUT clause and have discovered the entertainment to be had in getting the latest identity when writing to two or more tables with what can appear to be one command. Continue reading
LDAP – Using SQL to get Active Directory details – Basic Examples
14 Jul
Recently I have had to extract user’s details from Active Directory (AD) for certain security groups. Having looked through a slew of internet resources it is obvious that the work required to do this has changed little over many years – and it looks like is was deliberately designed to make it difficult. I’d like to think it wasn’t but, that is how it looks. Continue reading
Partitioned Views
17 Dec
Partitioned views have been around since before SQL Server 2005 (whereupon partitioned tables were introduced) but this does not mean that they aren’t without their uses now.
Continue reading
Forwarding Pointers and Fragmentation on a Heap Table
3 Dec
A heap is a table that has no clustered index – it may have non-clustered indexes but without a clustered index it is a heap.
When a row is updated within the heap, if the new data size means a row cannot be stored in the original location, SQL Server moves the row to another location and leaves a pointer (called a forwarding record) to show where it has been moved to.
Replication – the sprouts of my DBA world.
14 Aug
Actually, that isn’t entirely fair. I don’t have to eat sprouts, which is just as well. However, I do have to deal with Replication – certainly in my current job. Whereas my refusal to eat the Little Green Balls of Death won’t result in a divorce (because my wife, as wonderful as she is, does for some strange reason really like sprouts) I can’t imagine a job that specified it uses Replication will suffer my presence for long if I flatly refuse to touch it.
SQL Server has a broad range of tools and features, so understanding all of them beyond a basic level is probably beyond most of us. However, within your role you will be expected to have a detailed understanding of some aspects of SQL Server and here, Replication is amongst those required skills.
Like many people, I have created Replication tasks in the past by using the Wizards, so I can at least have a basic understanding of the subject. However, the Wizard does everything for you, makes assumptions and on a simple test system it does work quite well.
On an industrial scale though, and processed via SQL Scripts it is another story. Some of the error messages it can give are less than useful and let’s face it – Replication Monitor isn’t the most helpful tool.
The following series of articles on Replication are to act as an aide-memoire for myself, which is the real reason I have this blog series. Like most people I find that if I don’t use an aspect of SQL Server for an extended period of time I forget the finer details. Having got to grip with a portion of Replication I need to document it. If it helps anybody else then that’s a bonus.
Assumptions
First – the interesting bits. No DDL changes replicated (generally), because in some cases we don’t require replication of all of the columns and the subscriber table may have additional columns (its own partition scheme – so another partition id and a date time showing when it was written to the subscriber).
No snapshots, because of the size of some of the publisher’s tables. A lock is taken while a snapshot is generated, which isn’t desirable on a database that is required 24/7.
And of course – no Wizards. If you have to rebuild a server then it is so much easier to have the scripts to hand, rather than answer a plethora of questions from a Wizard and hope you gave the answers that the DBA who first set up Replication gave. Easier to see what is going to happen and also removes some of the more interesting defaults that the Wizards can specify.
I have three SQL Server 2012 instances on my Development machine and I’ll be using these as Publisher, Distributor and Subscriber.
The articles that follow will include the initial creation of the Distributor, creating a Pull Subscription and changing an Article. As my experience with sprouts Replication continues I’ll add more articles, because there’s no way this mess can stay fresh in my mind for long.
Recent Comments