Setup and Configure Transactional Replication with a Remote Distributor SQL Server - Step by Step
ฝัง
- เผยแพร่เมื่อ 27 ก.ค. 2024
- Best to watch this at 1080p or 720p minimum
How to properly setup/configure transactional replication with a remote distributor for SQL Server. This video shows how to do it on a real Windows Domain with active directory. Three servers are used - one for the Publisher, one for the Distributor, and one for the Subscriber.
It also uses proper user security to lockdown the privileges of the replication agents.
It use Windows Server 2019 and SQL Server 2017
![Configure Transactional Replication in SQL Server 2012 Step by Step [HD]](/img/n.gif)
Thank you for actually showing how to setup proper permissions. So many of these walk throughs are all on a single local box using an admin account for everything... hard to learn and understand whats actually going on that way. This helped me more than anything else in getting my multi-domain environment replicating the way I wanted it to.
Glad it helped. Thanks for the positive comments.
Thank you so much. It's very good explanation and right to the points. I keep watching over and over to refresh my memory from time to time.
Thank you. Glad it is helpful.
Excellent video. Very well explained and easy to follow
Thank you. It's clear and straight to the points.
Perfect video . One thing I felt missing is , you could have shown respective replication jobs in publisher server ,distributor server and subscription server . And those job owners as well ...
Best video on Replication
Really useful and a very good description. Thank you for taking the time to make this video.
Glad it was helpful!
Masterful job!!! And thank you.
If you have time please put up video for configuration translation replication for always on servers , really it is very easy to understand for us the way you are explaining is awasome..
very nice explanation helped me a lot thanks
Excellent video!
Excellent video. Thanks for sharing.
Glad you liked it. Nice GIF
Thank you for your great effort
Excellent tutorial!
Thank you. Hope it was helpful.
very nice explanation
keep the videos coming
Great video man thank you!
thanks
Nice video, keep up the good work..!
Thanks!
Very nice Explanation ..
Glad it was of help.
thank you for great tutorial
Glad it was helpful!
Great video. Thanks for sharing. Please share video like this for Migration steps for SQL server 2016 to SQL 2017/SQL 2019.
Glad you liked it. Your idea for a replication migration is a great idea. i will create one for that and post it. Thanks for the idea.
Great tutorial. It is real life scenario. A lot of tutorials are running on AgentAccount and lector doesn't show and explain AD users topic
Glad you found it useful.
Well explained
Very Good
What a great video!!! It works perfectly. Quick question if I have separate servers that I want to set up as new publications using the same subscriber server. Do I need to create separé distributors?
If I understand your question you can use 1 distributor to handle multiple publishers going to multiple subscribers.
@@odintree5704 Thanks for the answer. I'm preparing the stage to start using SSIS.
Incredibly well explained. Two questions if u dont mind: 1) Can this be done with SQL Express? and 2) Is it absolutely necessary to have Active Directory installed? Thanks.
Hello Motion, SQL Express does not come with the SQL Server Agent so therefore as far as I know it cannot be done. You do not need Active Directory installed to use replication. It can be done on a workgroup.
thx c'est super cool
Nice video. Thanks!. I have been using SQL Server replications for some years now. But I have published the table and field names as it is to the subscriber database. You have shown here how to change the destination table (article) names. Can we change (transform) the destination column names as well?
Hello Supun,
Some terms have been mixed up but I assume what you are asking is as follows:
I showed a different database name but I did not change the table names or column names at the subscriber.
If you wish to change the table names at the subscriber in a transactional replication, that can be accomplished with the sp_changearticle stored procedure and the dest_table property. After the change you must recreate a new snapshot and reinitialize the subscription.
As far as I know there is no straight forward way to change the column names at the subscriber.
If a viewer as any ideas that would be appreciated.
@@odintree5704 Thank you very much for the reply. Yes you do not change the table name there. Sorry for the mistake. @21.14 you can change the table name. Existing name was Address and you can give a name you want. Right? What I meant in my original question was whether we can change the field names like that. (You have replied to that already by now)
Thank you so much for the video. Hope you doing great.
I have a production database that is maintained by vendor and we have restrictions to use tables but only views. Vendor has provided one-to-one indexed views to access those tables.
Now I would like to have replica of the database in another accesble network, where also I have no restrictions on the database.
1. Is it possible to do transactional replication on indexed views? ([Source]Indexed views -> [Destination]Indexed views)?
2. In the above case, is it also possible to convert [Destination]Indexed views to a table instead of views?
If possible can you also please create a video on this as well?
Thank you. Have great holidays. 😊
Great question. I am happy to say that what I think you want to do is possible. I know most people say that you need to create the table structure first on the subscriber but this is not required. You can replicate just the data from the view without creating the underlying data structure first.
Currently I do not have the time to make the video but I googled and found this link. It should get you what you want, Keep in mind you will not be doing everything mentioned in the article. For instance you are not adding new columns to the underlying data.
Take a look at the link and give it a try and if you have any issues let me know and I will see if I can help.
virtual-dba.com/blog/replicating-indexed-views-as-tables/
Hey Bud, I'm moving from Transactional Rep to Read Scale Availability groups, Have you set those up? I'm doing some research now. I enjoyed your videos, you do a damn good job at explaining stuff. I see you haven't put out any content in a couple years though, hope you are doing well.
Hello CS,
If I get time this week I will create a video for an Availability setup. I know my Weekend is full (actually Saturday only) and this Friday afternoon is full but maybe I can get something running tomorrow (Thursday). If I get it done on Thursday I can hopefully edit it on Sunday.
But you can ask a question before then or in case something comes up for tomorrow.
@@odintree5704 sweet! I''ll be watching that one!
@@Bullbluegill Good news. I have it done and in the editing phase. Should be available by Mon or Tues of next week if I do not get side tracked. Once I start these I keep adding to them (lol). I show how to configure, how to connect from client, how to fail over, and how the redirection will work under normal conditions and after fail over.
@@Bullbluegill The video has been posted. If you have any problems let me know.
Thank YOu
So what happens in the case you were setting up this replication process, testing and such, then when ready to go live, you restore a new copy of the Publisher DB with same name? From what i found, you have to pretty much setup the whole shooting match again. Is this correct or are there tips you can offer?
Hell CS, You are correct - unfortunately once a publication has been created you can change the articles to be replicated but will not be able to change the database. You will have to recreate your publication etc.
@@odintree5704 Thanks for the reply. your video was super helpful. thank you. i've gone live in Dev and Prod and everything worked great. I made a lot of mistakes along the way and did a ton of testing. I can setup replication with out documentation. Pretty sad!!!! There is actually an order you have to take to delete everything to make a clean slate for a new DB. As easy as it sounds, it took some "googling"
I am using SQL Serve Enterprise Edition (64-bit) and in this i am using transactional-replication with on Publisher , one Replication Distribution Agent with four Subscriber. everything was going smoothly but now when i am seeing my replication in SQL server through replication monitor and clicking on 'subscription watch list' all subscribers status are showing as 'Expiring soon/Expired' and i am not sure why and when i am double clicking on subscriber error message are 'no replicated transactions are available'.
Hello Ashutosh,
When a subscriber expires it is because changes were made at the publisher and have not been replicated at the subscriber.
Directly from MS
If a subscription is not synchronized within the maximum distribution retention period (default of 72 hours) and there are changes in the distribution database that have not been delivered to the Subscriber, the subscription will be marked deactivated by the Distribution clean up job that runs on the Distributor. The subscription must be reinitialized.
If a subscription is not synchronized within the publication retention period (default of 336 hours), the subscription will expire and be dropped by the Expired subscription clean up job that runs on the Publisher. The subscription must be recreated and synchronized
So depending on your situation you must either Reinitialize the subscription or the subscription must be recreated and synchronized.
Here is the link to reinitialize a subscription
docs.microsoft.com/en-us/sql/relational-databases/replication/reinitialize-a-subscription?view=sql-server-ver15
Hi, I have the same scenario and it's working fine. Could you help me how can I move the distributor to another new server? I have 8 groups with 5 thousand tables replicated and I don't like to lose the data has replicated. Thanks
Hello Amaury,
That procedure is not straight forward and impossible for me to explain here. I have found this link and it is pretty thorough for moving the distributor. As you may or not be aware tthere is no mechanism for just moving the distributor. You must drop it and recreate it. But as I said the following link has very good step by steps instructions. Good luck.
www.sqlshack.com/sql-server-transactional-replication-moving-distribution-database-step-by-step-guide/
after right-clicking on the replication menu there is no 'configure distribution' option, there is a 'publisher propertise ' ?
Did you install the replication option when you installed SQL Server. You must select the Replication option during installation.
My sql INSTANCES are not in the same server, how can i connect with them with an ip at 28:47?
It all depends on how your servers are setup. Is it a public IP - then you can put the IP address and port number in for the server name.
Quick question... I have set up a server to be a distributor which I'm going to call "Server D". I have another server "Server P" that I want to be my Publisher. That SQL Server didn't have the "Replication" module installed, so I went to execute the installer, modify the instance, and then installed the Replication component. So, as far as I know, I don't need to restart the whole server.
Then I went to the Replication folder, right rick on "Local Publications">New Publication, I selected the database, configure the security, and assigned a name to my Transactional Replication.
The problem is that I'm getting an error when executing the first action (creating de Publication) and it says "This database is not enabled for publication. Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication. Changed database context to 'MYDB_NAME'. (.Net SqlClient Data Provider)" and the whole process fails.
Is there anything that I may be missing on the Publisher server? Thanks a lot with any feedback!
You are correct that you do not need to restart the Database service after installing the replication component on an existing SQL Instance but you do need to restart the SQL Server Agent Service.
Did you restart the SQL Server Agent? If not could you do that and let me know if the problem still persists.
Thank you for the insight, I'm setting up replication over the internet and I've followed the replication process. It is successful up to the subscription (push) stage where I get the following error.
******
The article '(null)' does not exist.
Could not update the distribution database subscription table. The subscription status could not be changed.
The subscription could not be created.
The subscription could not be found.
******
What could I be missing or doing wrong?
Do you actually have any objects (ie: tables) in your articles(s) that you are trying to publish?
Yes, the articles are there. I discovered that there was a difference between the number of MSarticles objects in the distribution database and the number of sysartcles objects in the main database. Two objects had the same name. Thanks for the reply.
I don't understand about domain concept? Is it mandatory my all three server in same series IP address series?
A windows domain is just a central location for all the users and securables on a Windows network. So servers can be use the same domain or separate domain. As for the different servers communicating with each other - if they are on different networks/subnets then that is the job of the router to allow communication between the 3 servers.
If you do not understand the concept of Domains or routers then you should set a meeting with the IT department and talk to your network manager and just explain to him/her that you need your 3 servers to be able to communicate and you need certain domain users setup (as in the video). Those functions are what the IT department is for.
How to solve this issue after publisher set up "proxy domain/user is not a valid windows user". Thank you
Is your SQL Server Agent set to run under a local system account or domain account? It needs to be under a domain account or else it can only access a local computer. I would need more information on your setup and the exact error message to help anymore.
Replicatin over the internet is possible ? without VPN/FTP.
COuld you please guide me how to configure that.,.,
i have two Servers..one in US (Directly on public ip ) and second inmexico (public ip)...i want to enable replicate from US to MEXICO Server without VPN\FTP...plz guide me how to do that..
Hard to give all the details but basically you can identify a remote server using the public IP address and port number that the server is running on (default is 1433). You would also need to configure your firewall to allow tcp traffic on the local port 1433 and allow either all remote ips or only specific ips. You may also need to configure port forwarding on your firewall.
Have you ever succeeded ? please kindly share your experience as I am in the middle of the same kind of deployment
@@putinilu770 I did, it is possible to configure it using internet, the key is public IP and ports..
Good Day sir your video is interesting ., i would like to ask if which of the 3 servers mention can handle multiple end user that are connected in the database.? if either the distributor server and subscription server have a direct changes in data , it can affect the Publisher server data also ?
Hello JV,
Could you rephrase your question please. I am not sure what you are asking.
@@odintree5704 aw sorry ., the situation is like this., i have a 2 servers that needs to be replicated like if the other server have a changes the other server will also be updated ., what process of replication should i do ?
@@jvofficial5838 Sounds like you want Merge Replication
@@odintree5704 both server's receive transactions still merge replication ?
@@odintree5704 sir question.., if the publisher(server1) runs Off., the distrution and subscriber can proceed ? and if yes. then when the publisher wakes.,. would it be the subscribers data be copied or prioritize?
thanks for sharing, but how if SQL server instance using custom port? please advice
Just use the public IP and the port number
@@odintree5704 all node server SQL using port custom, create distributor, publisher and subscriber sucess, but while job subscriber running there was error subscriber can not connect Distributor. please advice
Anything to be aware of if I'm using named instances? I assume SQL Browser service will need to be enabled and running for the wizard to find them on the domain? I haven't done it yet, I'm just looking for possible road blocks prior to moving forward. Also, the default instance has replication turned on and running. Would you recommend creating new snap, dist and log domain accounts if these servers already have replication running and working on the default instance?
Hello CS,
Named instances with SQL Browser is fine. Keep in mind that replication cannot occur if the versions of the instances are more than 2 versions apart.
As to the 2nd question since I do not know how the default instance has been setup with secure Windows accounts I could not tell you. If they are setup with the proper Windows accounts you are fine, if it has not been setup with proper Windows accounts then I personally would redo everything so as to limit the attack vectors on your servers. But I am paranoid and assume everyone is out to get me.
@@odintree5704 Yes, the default instance has 6 domain accounts setup all with different passwords. I followed your video! rpl_ts_****_snap, rpl_ts_****_dist etc etc. then I did rpl_pd for production. so the default instance has secure windows accounts with different PW's. I wish I could download this video, by far the best one i've found for sure. absolutely love it and SUPER informative...so thank you very much.
@@odintree5704 also, i found that if you setup a named instance but no SQL browser running and just connected to it via SSMS via a static port (I removed dynamic)....that when you try to connect to the dist DB from the publisher, it does NOT connect with the Password. So I flipped on the SQL browser service, reconfigured the all replication to use servername\instance_name and the publisher was then able to connect to the distribution DB. UNLESS you know another way I can make the port work so i don't have to have SQL browser running?????? From the documentation i've found, i'm under the impression that you will need to have SQL browser on to discover the named instance in order for replication to work....but i'm not 100% on that. It would be GREAT to not have SQL browser on, but I think it needs to be. Unless of course I build all new servers but at this point i'm not sure i want to do that.
Hello CS,
Could you tell me if you received an error message when trying to connect without the Browser service.
@@odintree5704 So in your video, say you wanted to add another DB to the publisher. How do you proceed? Do you use the same Distributor database or do you create a new one one? I'd be interested in that process because i'm in that situation right now. I have a publisher, remote distributor (subscriber on the dist). mine is a 2 server setup. Last time i did this i messed something up.
What Ports need open between server 1,2,3 ? Does all 3 servers need to talk to each other?
The publisher needs to talk to the distributor. The distributor needs to talk to the subscriber. As for the ports it depends on your network setup. How many routers (firewalls) are being traversed?
Port 1433 (default instance) - standard database engine -
Port 80 if connecting over HTTP (if the standard HTTP has not been changed)
FTP if you are using the web sync - Port 21 or UDP Port 137,138 possibly TCP 139 and possibly port 445
Need to know your network configuration -
I would just try it first with port 1433 and see what happens.
@@odintree5704 if publisher -> distributor -> subscriber is one way port 1433, then why we need to create shared folder for repldata? I think we need publisher distributor ports open both ways.
Also can publisher have it's own subscriber for different replication/subscriber, so kinda one to many replication.
to me it looks like publisher has to start clean and shouldn't have any existing publication.
okey but how can i connect btween two sql ?
Do you mean between two different RDBMS.You can do a simple google search and get the following instructions for an oracle publisher
learn.microsoft.com/en-us/sql/relational-databases/replication/publish/create-a-publication-from-an-oracle-database?view=sql-server-ver16
Or you can try Goldengate which has been purchased by Oracle.
distributor password you setup at 12:26, when and how this password used in next? I didnot find when to use this password and how to user this password ? any idea about this password, thank you
GOOD tutorial. but i've problem. i only have 2 server for publication and subscriber. can i create distributors in the same server with publication? thank you.
Yes. It is common to have the publisher and distributor on the same server.
@@odintree5704 thank you. I've another problem. When i want to create new user login in sql server subscription, i cannot connect to domain distribution server because my server don't use domain. Can you tell me how to create and join domain to connect other server in other pc? Thank you
@@egawulandari4403 how is your network setup?
My network setup to connect other pc is using ip address to local area network. And all pc is not using domain name. If i want to create domain name is impossible because i didnt know the password of my pc windows account. How can i create domain when i forget the user password? I still cannot solve it
@@egawulandari4403 Sounds like you are3 running a Windows Workgroup setup. Which is basically a Peer to Peer network.
If you cannot access the Windows Admin account to create accounts for your replication agents then try the following options:
1. You will need to use SQL server authentication rather than Windows authentication - so set your authentication to mixed mode
2. You can try creating some SQL Server Login accounts or just use current user for the agents (not secure because you are probably the SQL server admin and will give the replication agents full access).
Try this and see. Do you not have a network admin that knows the window server passwords?
hello are your 3 servers on 1 machine? how can we do on seperate physical machineaz? also how do we get the domain local name? how do we add sql agent? some have some dont for my servers thxz
there is a lot of steps cant we make a batch file?
These are 3VM's so yes technically they are separate servers. You would have to talk to your IT department to get an idea of the network configuration - for domains and AD's. SQL Server Agent is installed during server install but not on express. If you need to you can do a repair and it should fix it for you.
hello so how exactly do we do repair sql installtion? maybe in the sql installtion software link i think its in the main menu--also i just doing local self testing not in an actually work it setting--Lisa thxz alot for the replies---PS also so we need to disable firewall for servers to ping each other and exchange merge shard sync data info? i stil cant ping seperate physical machines
@@pjmclenon I would not disable the firewall, I would just open a port - default port is 1433 but if you use named instances you should use static ports and open the ports you specify.
hello--okay i will try this
Voice is too low