Contents
Do you need primary keys for transaction replication?
Let’s create a sample database to demonstrate the Primary Keys requirement for the transactional replication. The database will have one table with a primary key and tree tables without (one without indexes, one with clustered index, and one with unique nonclustered index):
How to replicate schema changes on publication databases?
To replicate schema changes, the compatibility level of the publication must be at least 90RTM. If Subscribers are running previous versions of SQL Server or the compatibility level is less than 90RTM you can use sp_repladdcolumn (Transact-SQL) and sp_repldropcolumn (Transact-SQL) to add and drop columns. However, these procedures are deprecated.
Can a source database have a primary key?
The source database is used by a third-party application. Some tables have clustered and/or unique indexes, but the database doesn’t have primary keys. We can’t make changes to the existing objects, but we can create new ones. How can we (or can we) configure transactional replication without primary keys?
Can a table be published without a primary key?
The rest of the tables have a warning that a table cannot be published because it doesn’t have a primary key column. We can reproduce some of the steps from the wizard using T-SQL. Note, that some parameters for the replication stored procedures are omitted here only to demonstrate the primary key requirement:
How to replicate a table in SQL Server?
Create a subscription. To replicate the data using indexed views we will need to use the sp_addarticle stored procedure with @type parameter set to the “indexed view logbased”. SSMS only creates articles for the views with “indexed view schema only” type.
Are there any limitations to transactional replication in SQL?
The best solution in our case is transactional replication, but there are some limitations though. The source database is used by a third-party application. Some tables have clustered and/or unique indexes, but the database doesn’t have primary keys. We can’t make changes to the existing objects, but we can create new ones.
Are there any replication options for reporting databases?
Some popular options for the reporting databases copies include Read-only AlwaysOn Availability Group Replica or Log Shipping. But we can’t use these options as the new reporting database is going to have different indexes for reports and ad-hoc queries.