Architectural Scenario #653 - SQL Server Replication

When I first got to my current job the setup was two production database servers that had over 100 databases between them. Data was imported every morning from files that dropped in a folder by our headquarters that were in zip files. The data import job was set to run at a specific time and imports the data into a database that existed on both servers. This is the main database that is referenced by all the other databases. So the import job would import the data into the database on one server then copy the data to the other server once it was finish with the first one. I thought this was crazy.... Needless to say we had problems. If the files are late being dropped the ETL import job would fail. If the first import failed the other server would not get updated. This was a 1980s setup. People there is something called "Replication". I was told SQL Server Replication has problems, it's too difficult to setup and support, backups and restores will be horrible, etc, etc, etc. But what Replication does solve is data availability and as long as the ETL import worked on Server A. I can pretty much be confident the data will get to Server B. Now my situation was pretty unusual. The database on Server B we import into does not get updated throughout the day. This is important. With replication, your subscription objects (tables/views/stored procedures) that are setup with replication cannot be updated. So to make a long story short, I ended up setting up Transactional Replication. Why? Because it used less space on the disk then the other types of replication. I know people are like that's it. That's what it came down to. I tried Snapshot. Took way too much space to store the snapshot. Merge didn't make sense to do since our import does that. We just need to sync a data across two severs and transactional worked. Replication Myths It's difficult to setup - I disagree. I can set it up in less than 20 minutes. Microsoft makes everything easy. It's difficult to support - I disagree. We have been using it now for a couple of years now and I can count on one hand the number of issues we had. Backups and Restores are difficult - I disagree. Backups no problem. Restores are not bad. If you have to restore a database, lock everyone out, remove the replication from the database, restore the database and then set replication back up. If you have done this as many times as I have it's not difficult. Just practice it in a test environment first. For the most part I recommend it if it makes things more efficient. Now we just have to worry about one import job. If that succeeds we can lay back and relax. Let Replication do the work. Types of Replication - https://docs.microsoft.com/en-us/sql/relational-databases/replication/types-of-replication?view=sql-server-2017