I have some questions about setting up transactional replication on 2 different versions of MS-SQL.
In Windows Server1, a SQL 2012 is installed.(sqlSvr1)
In Windows Server2, a SQL 2014 is installed.(sqlSvr2)
I currently can't access to Windows Server2, but sqlSvr2 can be accessed from sql mgmt. studio in Windows Server1.
In addition to these severs, we have a production server, ProdSqlSvr. All these 3 sql servers have a db called 'myDB'.
sqlSvr1 gets db backup from ProdSqlSvr.
This is what I want to accomplish.
As soon as 'myDB' is restored to sqlSvr1, I want this db(myDB) in sqlSver2 is updated almost right away.
So I decided to setup Transactional Replication.
I created a new publication and "distribution" in sqlSvr1.
Then when I tried to create a new subscription(using New Subscription Wizard) on sqlSvr2 in "Windows Server1", it opened up sql2012 mgmt. studio and I got the following error:
"The selected subscriber does not satisfy the minimum version compatibility level of the selected publication."
Now I realized that "distributor" should be created on the highest version of sql sever.
When I tried to create a distribution (using Configure Distribution Wizard) on sqlSvr2 in "Windows Server1", I got the following error:
" 'sqlSvr2' cannot be configured for publishing & distribution. Publishing & distribution are supported only SQL Server version 7.0 or later"
This doesn't make sense since sql versions I am using are 2012 and 2014.
Let's say that I have access to Windows Server2 as well. Here are my questions.
Q1 - Is it possible to create both "distribution" and "subscription" in sqlSvr2(sql2014) and create a "publisher" in sqlSvr1(sql2012) using ssms?
If so what's the order of creation should be? Distribution --> Publication --> Subscription?
Q2 - If myDB in sqlSvr2 have more tables than myDB in sqlSvr1, will these tables be remained unchanged when replication occurs or during the replication setup?
Q3 - I know that Transactional Replication is applicable to the tables that have primary keys only. If these are not acceptable, what are the possible other solutions beside adding primary keys to all the tables? (I can't have more Windows server than what I currently have.)