Grumpy Old DBA

Transactional Replication PT2 - Getting table info

First task identifying which tables have identity columns and timestamps, the following query also checks for clustered indexes and primary keys.  I'll need to know the name of the timestamp columns so I can create the filter commands. Tables without a primary key won't replicate. I'm creating a table as I'll use the data in the table to create the replication commands.

--
-- query to populate table reference table
--
use DbaDatabase
go
create table DbaDatabase.dbo.MyDBTables
(TableName sysname not null,
ObjID int not null primary key,
TimestampName sysname not null,
HasTimestamp bit not null,
HasIdentity bit not null,
NoClustered bit not null,
NoPK bit not null
)
go
--
-- run in publication database
--
Use PublicationDatabase
go
insert into DbaDatabase.dbo.MydbTables(Tablename,ObjID,TimestampName,HasTimestamp,HasIdentity,NoClustered,NoPK)
--
-- tables with timestamp columns
--
select obj.name as "Table name"
,obj.id
,isnull(col.name,'') as "Timestamp Name"
, objectproperty(object_id(obj.name),'TableHasTimeStamp') as "TimeStamp"
,objectproperty(object_id(obj.name),'TableHasIdentity') as "Identity"
,case objectproperty(object_id(obj.name),'TableHasClustIndex')
when 0 then 1
else 0
end as "No Clustered Index"
,case objectproperty(object_id(obj.name),'TableHasPrimaryKey')
when 0 then 1
else 0
end as "No Primary Key"
from dbo.syscolumns col join  dbo.sysobjects obj on col.id=obj.id
where obj.xtype='U' and obj.name not like 'sys%'
and col.xtype=189
--
union
--
-- tables without timestamp columns
--
select obj.name as "Table obj.name"
,obj.id
,''
, objectproperty(object_id(obj.name),'TableHasTimeStamp') as "TimeStamp"
,objectproperty(object_id(obj.name),'TableHasIdentity') as "Identity"
,case objectproperty(object_id(obj.name),'TableHasClustIndex')
when 0 then 1
else 0
end as "No Clustered Index"
,case objectproperty(object_id(obj.name),'TableHasPrimaryKey')
when 0 then 1
else 0
end as "No Primary Key"
from dbo.sysobjects obj
where obj.xtype='U' and obj.name not like 'sys%'
and ( obj.id not in (select distinct col.id from syscolumns col where col.xtype=189))
go

Transactional Replication the hard way?

I've previosuly managed articles through T SQL in replication, anyone who has had to make changes to a  replicated table will know the pain I'm sure. When there are lots of  articles and data is in the hundreds of gigabytes the whole process of making changes can be time consuming, especially if the development environment that created the release scripts didn't have replication - well why make the DBA's job easy?

My task is to totally script transactional replication, so that it is a repeatable and recordable process, in controlled environments using the GUI or a wizard is not an option.

My subscriber doesn't have to update the publisher but it must be a fully functional database that a copy of the application(s) can connect to and work as if in production, a parallel support environment if you like. I have a the usual issues to resolve, timestamp columns, identity columns and so on.

Doing the whole thing has proved interesting and will produce copious documentation, I'll post the various scripts and solutions as I work through the documentation. I've just discovered that articles must be added in order of dependency ( well I know it seems obvious now! ) I'd added a view as a test without the table, got an error so no problem just add the table and off we go then. But no it doesn't work like that, have to remove the view, add the table and then re-add the view.

so simple script to remove a table, make a modification, and add it back to replication.

--
-- remove article from replication
--
exec dbo.sp_dropsubscription  @publication='mydb-to-myotherdb',@article='tabletochnage',@subscriber='ServerXXXX'
exec dbo.sp_droparticle @publication='mydb-to-myotherdb',@article='tabletochnage',@force_invalidate_snapshot=1
exec dbo.sp_refreshsubscriptions  'mydb-to-myotherdb'
go
--
alter table tabletochnage drop constraint PK_with_silly_name
go
alter table tabletochnage add constraint PK_tabletochnage primary key clustered (id) with fillfactor = 100
go
--
exec dbo.sp_addarticle 'mydb-to-myotherdb',@article='tabletochnage',@source_table='tabletochnage',@destination_table='tabletochnage',
@force_invalidate_snapshot=1
exec dbo.sp_refreshsubscriptions  'mydb-to-myotherdb'
go
--
exec msdb.dbo.sp_start_job @job_name='the snapshot job'
go
--

dbcc checkdb - things you find

I was sorting out some corruption in a database , error 8966 for the curious, which was preventing a backup occuring which is always bad news.

I just happened to run across the option  ESTIMATEONLY which gives an idea of how much tempdb space is required for a checkdb, use as  dbcc checkdb with estimateonly returns the estimated space for  CHECKALLOC and CHECTABLE(s) learn something new every day!!!