MS Sql Replication setup

Hi,

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.)
IzzyTwinklyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Q1 - Yes
Q2 - You replicate articles and that means database objects (tables, sps, ...). This means that if a table or a column isn't marked to be replicated, any changes you made on those articles it won't be replicated.
Q3 - SQL Server Replication will add internally a GUID in each replicated table to guarantee the uniqueness of the row to be replicated.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Máté FarkasDatabase Developer and AdministratorCommented:
Q1: Yes, setup distribution on sqlSvr2 then setup publisher on sqlSvr1 and then setup subscriber on sqlSvr2
Q2: Yes, this is possible you can select tables one-by-one to replicate them but remember: you can choose replicable tables only on publisher level and not subscriber level. So once you added a table on sqlSvr1 to publication then you must replicate them to all subscribers.
Q3: You need primary keys only on tables what you want to replicate. You must add a new column with identity or guid if a replicable table does not have it. This has no effect on existing INSERT INTO commands (from application side).
IzzyTwinklyAuthor Commented:
Vitgor and Mate,
Thanks for your in-depth answers!
Vitor MontalvãoMSSQL Senior EngineerCommented:
Izzy, please let us know if you need further assistance. Otherwise, please close this question accordlying.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Answers provided.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.