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
- Can MVC.NET prevent SQL-injection at razor or controller level?
- SQL server not returning all rows
- When dealing with databases, does adding a different table when we can use a simple hash a good thing?
- Creating a parametrized field name for a SELECT clause
- Combine two rows based on common ID
- Column displays each count
- Slick query for one to optional one (zero or one) relationship
- Aggregate and count in PostgreSQL
- MAX and GROUP BY - SQL
- SQL statement for a tricky 2 table query
- How to create nested selects with sql?
- Pull and push data from and into sql databases using Excel VBA without pasting the data in Excel sheets
- Best Practice for adding columns to a Table in Oracle database
- SQL FIFO STACK using two tables
- SQL Query - Order by String (which contains number and chars)
Related Questions in SQL-SERVER-2008
- How to create separate rows for each unique value in source data
- How to Multiply all values within a column with SQL like SUM()
- Concatenated string in column alias
- Column value divided by row count in SQL Server
- MSSQL Bulk Insert CSV - Multiple columns include commas
- String to DATETIME with TimeZone
- Compare each records of same table in sql server and return duplicates
- 2008R2 SQL Code for case when using the SUM?
- Separate string into columns
- Pass parameters to sql agent job step (Transact-SQL Script)
- using Case get values from different table in sql server
- How to use Replace an unknown length of characters in SQL Server?
- Join Multiple table without using joins
- call an sql function by name returned by select
- How to create a check (Table A.Id and Table B.Id and TypeId = 1)?
Related Questions in SSIS
- Data streams in case of Merge
- SSIS Package Component Intermittently Failing
- How to run an SSIS package having excel source on a server where excel is not installed using SQL Server Agent Job
- SSIS exit code error with batch file
- excel datasource returning nulls with sql command
- String to DATETIME with TimeZone
- Error executing SSIS Package
- SSIS Stops at a Particular Record Count
- How to know expected completion time of SQL Server SSIS job?
- Accessing parent parameters from child package SSIS 2012
- Is there a way to export huge amount of data (more than a million rows) from SQL Server to csv?
- ssis - No value given for one or more required parameters
- Split CSV file based on first column value changes and load into destination table in SSIS?
- Automated file import with SSIS package
- SSIS ETL parallel extraction from a AS400 file
Related Questions in DATABASE-REPLICATION
- RavenDB - null reference exception after resolving conflict?
- How to handle sequences in Bucardo Postgresql multi master
- Replication Data Issue
- How to do sharding and replication of Orient DB database
- Cassandra Table needs a lot of Storage space
- Remove replication from an MS Access Database?
- Can't start postgresql replication
- Looking for a substitute to access replication
- Create replica set for MongoDB in Linux
- Replication Issues for client machines connecting to Master in mysql
- How to get time offset between SQL Server master and replica?
- Switch MySQL slave to master - BINLOG_FORMAT
- MongoDB how to become master
- multimaster mysql replication position
- Sql Server Replication Errror: Login failed for user 'NT AUTHORITY\ANONYMOUS
Related Questions in LOG-SHIPPING
- Log Shipping Transaction Log Backup Job runs continuously
- log shipping process (archive_timeout)
- Log shipping setup
- PostgreSQL - using log shipping to incrementally update a remote read-only slave
- Automate Log Shipping Failover
- Simple SQL Failover Plan? Log shipping? Mirroring?
- SQLServer log shipping file size limit
- sql server log shipping to remote server in different network
- Sql Query to determine when log shipping last took place
- How to keep log-shipping up after failover (mirroring) in sql server
- AWS RDS slow task status update during native restore
- Filebeat | how to define a unique index for different log paths?
- Azure AKS in-container logs to Azure Logs/Azure Sentinel
- Temporal Tables and Log shipping
- SQL Server 2016 reset recovery mode from 'Full' to 'Simple'
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 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.