Can we use Secondary database to perform all operations after log shipping?

1

Hi all,

I have used log shipping process to synchronise two database every one hour. Can i access the database (I mean all operations including write operation) when log shipping is done. I have this doubt because while configuring, it is said that we have to select two database state (No operation or Readonly mode). This is only applicable during log shipping process or all time

Thank You

2

If the target is in readonly than you can see everything. Note that the connection to the database will be dropped when log shipping runs (once an hour). You will not be able to write to the target database. A different type of replication is needed for that.

If you mean can you access the primary database the answer is yes. Log shipping does not hinder use of the primary.

3

Thank You. The connection to the secondary database will be dropped once in every hour. This I understand. But "You will not be able to write the target database" that means, all time the secondary database cannot be used for write opertation or when the log shipping process is taking place once an hour. Also suggest some other replication mechanism.

4

What are you wanting to write to the secondary database?

You can't write anything to the tables which are being log-shipped from the Primary Database (they would get out of SYNC) ... so to my mind that doesn't leave anything else, hence my question about what you want / were hoping to do.

If you want to write some OTHER data - into different tables, which are "associated" with the tables being log-shipped, then you could put them in a different database and associate them with the tables in the secondary-database (being log shipped and thus Read Only)

5

Other types of replication: Transactional, Merge, Snapshot.
https://msdn.microsoft.com/en-us/library/ms152531.aspx

6

Hi Kristen,

I understand ur point. My requirement is Have to synchronize data from one database to another such that the second database is available for all operations. This is not for Disaster recovery process.

Consider this scenario : D1 database is in live. We need a D2 database which also contains same data as DB1 and should be synchronized every 30 minutes. We will use this database DB2 for some integration purpose. Hence DB2 sholud allow both read and write operation. Please suggest some solution for this

7

You need full blown, bi-directional, replication for that. Its a complicated solution ... are you sure you need that and cannot work around it in another way.

I think you said this would be all on one server ... in which case I can;t see a reason for needing two, "duplicated" and synchronised, databases. Can you explain what problem you are trying to solve, that would enable folk here to recommend solutions.

8

Yes, the db is in live in which data will be populated from SAP. the same data is integrated and pushed into salesforce using DELL BOOMI. So instead of using that live database, we have to use another database which contains same data atleast every half an hour so that the integration process will be taking place. Live database will be safe. Thank You

9

To get a reasonable answer you are going to have to provide a lot more information.
The following questions spring immediately to mind:

  1. Are you happy to just move SAP data to SalesForce or do updates in SalesForce need to be written back to SAP?
  2. How many tables to you want to copy from SAP to SalesForce?
  3. Are the SAP tables copied directly into the SalesForce DB or do they need to transformed?
  4. Does your SAP support agreement allow you to add triggers to SAP tables?
    etc
10

If the SAP data tables will be Read Only, but other tables (not part of Replication / Log Shipping) will be Read Only, I would create one database as a secondary Read Only copy - either Log Ship (if the disconnect is not a problem for you) or simple Replication.

I would then create a second database which contained all your updateable tables

If the SAP database is on the same server, and you only need read-only access to those tables, then why not just reference them direct? You could have VIEWs (with Read Only / SELECT permissions) or you could use Synonyms (which make it easy to change the location / name of the SAP database, in future, if that becomes necessary)

11

That's a good point. We have had to export tables with BCP and import them into another database, rather than directly querying the data (even to "export" it to the other DB) because that was all the licence allowed. It takes about 10 hours overnight to run the BCP, and less than 1% of the data changes each day ... there are also Disk Full issues and other "breakdowns" which disrupt the user's day from time to time ... Client definitely should have paid for the "direct access" licence IMHO .. but it was $Lots :frowning: