I have 2 SQL server instances, on different servers. One is Production instance and gets lots of reads and writes daily. Another one is for external applications to use a copy of few of the tables from production sql instance. At the moment we have a SSIS job which does compare and update once in a day from production sql to the external sql server. It takes 15 mins and affects the performance of production sql server. We want to increase the frequency of the data being replicated, so that it is more or less synchronous on both servers. What would be a better approach? increasing the frequency of the SSIS package or creating replication or using log shipping? Any answer is really appreciated. Thanks.
SSIS v/s Replication v/s logshipping
3.4k Views Asked by come2searcher72 At
1
There are 1 best solutions below
Related Questions in SQL
- SQL schema for a fill-in-the-blank exercise
- Hibernate: JOIN inheritance question - why the need for two left joins
- What's supposed to be the problem in this query?
- Compare fields in two tables
- How to change woocomerce or full wordpress currency with value from USD to AUD
- Dynamic query creation with Array like implementation
- SQL query to get student enrolled in this month in a course - Moodle
- SQL LAG() function returning 0 for every row despite available previous rows
- Convert C# DateTime.Ticks to Bigquery DateTime Format
- Use row values from another table to select them as columns and establish relations between them (pivot table)
- SQL: Generate combination table based on source and destination column from same table
- how to use system's environnement variables in sql script
- PHP fetchAll on JOIN
- Multitable joining in Sql
- How to display name starting from 'z' by using BETWEEN cmd only?
Related Questions in SQL-SERVER-2008
- Why does the following script throw database still in use when selecting the master database first?
- Return Duplicate Rows
- SQL Data entry - finding sequence to enter info
- Why is this query in SSMS showing columns/tables as numbers?
- Query from multiple tables based on the table name?
- How to Let Python Connect to a SQL Server Database
- Need to fetch Minimum value for Each Id and make the BidSuccess column to 1
- Spring Boot Scheduled Annotation Job Issue
- Use different WHERE clause based on condition?
- Error when inserting special characters such as Ñ and accents á, é, í, ó, ú. using BULK OPENROWSET from python
- Delete all records except the max value of a group
- How to use SQL Server stored procedures in React js express server
- Loop through date in SQL Server and exclude the dates within a range of one week
- Find amount greater than the average amount
- Update trigger - both INSERTED and DELETE hold same value after update
Related Questions in SSIS
- Skip null rows in SSIS of excel file
- SSIS error: delimiter for column "X" is not found - started happening after migrating package to newer SSIS
- Copy or Move Data from one Database to other Database and have one more destination option
- Odata source inside ForEach loop
- Extracting data from SAP ERP into SQL Server
- Get the URL from C# script used in ssis
- Write rows on destination even when an error occurs?
- Visual Studio 2022 Task script editor : Impossible to modify
- SSIS package writes empty CSV file
- SSIS flat file destination generating more digits than in the database
- SSIS remove $ format from csv
- Handling inconsistent record delimeters in ssis
- SSIS Data Conversion Error: Overflow Issue in Automated Job Execution
- SSIS to Snowflake connection
- SSIS - OData Connection to Sharepoint List Failed Validation Error 0xC020801F
Related Questions in DATABASE-REPLICATION
- SQL Server Remove Replication from Table
- Postgresql Database Replicate Using Docker-Compose File
- SQL Server Replication - Transactional
- AWS DMS Task Migration Mapping Rules - Add-Column using metadata
- Changing binlog_format in MariaDB slave
- Master to Slave Replication pausing
- Kafka mirror maker: Data in some topics are not replicated
- ProxySQL as a backend for Wordpress results in login out user from wordpress
- How can I minimize data loss & data transfer when I failback with the requirement that I promote the old primary back to its primary status?
- Postgresql Replication Slot grows in GB suddenly
- How to replicate data from 1 VM into another VM on-prem with linux/ubunto server
- Replication postgresql database error connection between master to standby
- Is there a specified order for data replication among different partitions in Cosmos DB?
- SQL Server replication: Need to remove articles from the publication
- What does the value 30 indicate in the type column in the MSrepl_commands table?
Related Questions in LOG-SHIPPING
- Filebeat | how to define a unique index for different log paths?
- AWS RDS slow task status update during native restore
- Having blocking on logshipping msdb.dbo.log_shipping_monitor_history_detail, when I am looking where it is initiated I only see SQL Server log shippin
- Is it possible to sync real-time 2 Database on 1 SQL Server?
- Transaction log shipping - Standby/Readonly database keeps getting stuck 'Restoring'
- How do I ship logs from SharePoint in almost real time to a fileshare using PowerShell?
- SQL Server Log Shipping: how to change a target database to stand by?
- Log shipping, logs are copied to the second server folder but not uploaded to the database
- The database is already fully recovered. RESTORE DATABASE is terminating abnormally
- Log Shipping, secondary server error. Error 5 - Error 3201
- Azure AKS in-container logs to Azure Logs/Azure Sentinel
- Is uncompressed transaction log file restore process faster then compressed in SQL SERVER
- Can you SQL replicate tables from a logged-shipped secondary DB to a separate third DB?
- Log shipping restore job failing after a couple successful runs
- Setting up PostgreSQL 13 log-shipping replication between 2 CentOS 8 vagrant boxes,
Trending Questions
- UIImageView Frame Doesn't Reflect Constraints
- Is it possible to use adb commands to click on a view by finding its ID?
- How to create a new web character symbol recognizable by html/javascript?
- Why isn't my CSS3 animation smooth in Google Chrome (but very smooth on other browsers)?
- Heap Gives Page Fault
- Connect ffmpeg to Visual Studio 2008
- Both Object- and ValueAnimator jumps when Duration is set above API LvL 24
- How to avoid default initialization of objects in std::vector?
- second argument of the command line arguments in a format other than char** argv or char* argv[]
- How to improve efficiency of algorithm which generates next lexicographic permutation?
- Navigating to the another actvity app getting crash in android
- How to read the particular message format in android and store in sqlite database?
- Resetting inventory status after order is cancelled
- Efficiently compute powers of X in SSE/AVX
- Insert into an external database using ajax and php : POST 500 (Internal Server Error)
Popular # Hahtags
Popular Questions
- How do I undo the most recent local commits in Git?
- How can I remove a specific item from an array in JavaScript?
- How do I delete a Git branch locally and remotely?
- Find all files containing a specific text (string) on Linux?
- How do I revert a Git repository to a previous commit?
- How do I create an HTML button that acts like a link?
- How do I check out a remote Git branch?
- How do I force "git pull" to overwrite local files?
- How do I list all files of a directory?
- How to check whether a string contains a substring in JavaScript?
- How do I redirect to another webpage?
- How can I iterate over rows in a Pandas DataFrame?
- How do I convert a String to an int in Java?
- Does Python have a string 'contains' substring method?
- How do I check if a string contains a specific word?
Increasing the frequency of the SSIS package will achieve what you want but at the cost of increasing the impact to the production system (you have already indicated that this is an issue) so this doesn't sound an ideal solution.
Log shipping is probably not a good solution as the secondary database will be unavailable for users while the log backup is being restored, meaning depending on the frequency that you are planning to run this, it would not be a suitable option either.
You could use transactional replication if you are just copying records from a few tables to a reporting database (or similar), it will give you the most up to date data with a pretty minimal impact on performance of the production box. It does introduce a bit of complexity for recovery etc, but if you make sure you read up on and understand the implications it is probably the best solution.