Replicating a Database for Reporting

myanilkumar

Hi All,

We are using SQL Server 2008 and Navision as front end. The users dont want to run the reports in Navision from Production DB. We want to create the database for reporting purpose in SQL Server and must replicate Production database to that reporting databse. We need to create a Scheduler for replicating the database.

What is the best method to do this? Please advise.

Regards,
Anil

navinb

Hi,
For NAV databases ,snapshot or transactional replication can be used .Merge replication doesn't ensure transactional integrity,so it must be avoided.Also pls consider log shipping before committing replication.sometimes replication changes the time columns in the NAV tables to GUID on the replicated tables,making the database unusable by NAV.And do not use replication on SIFT tables.
Replication must be avoided if possible ,and instead things must be done with views,or integration services or dataports if possible.But it can be used for reporting purpose !! .


Navin

Lars_Westman

I think the easiest way would be to use log shipping, but keep in mind that the restore requires exclusive access to the standby database so any connection to the standby databas must be disconnected during the restore. So it depends much on the frequency You need to update the standby server.

An alternative could be to look at the features that are coming in SQL2008R2. There You have cached datasets so the report data can be cached after the report is run for the first time or scheduled by a job. Here's some information about SQL2008R2 http://blogs.msdn.com/sqlrsteamblog/default.aspx

/Lars

stryk

Hi!
I would also suggest to go for either Log SHipping or Asynchronous Mirroring (if you run SQL Server Enterprise) to set up a remote Standby-Database. Have in mind that with LS the Clients are disconnected whenever a new TRN is restored.
So additionally you could create "Database Snapshots" from the LS/Mirror database (all read only)! Of course, the Snapshot does not automatically reproduce the transferred Transactions after it was created (actually that's the idea of a snapshot).

Hence, you could have a smart redundant "failover" solution ("Warm Standby") plus a separate Reporting system!

Cheers,
Jörg

David_Singleton

myanilkumar wrote:

The users dont want to run the reports in Navision from Production DB.

Why not?

Lars_Westman

David Singleton wrote:

Why not?

That's a valid question from David. When You compare the load You put on SQL running reports from inside NAV with SSRS it must be a really big difference between the two. Take the Inventory Valuation for example. It can take hours to run on a 50GB db with a lot of Inventory Leder Entries. A similar report runs under the minute in SSRS. I have not done any measurements on the load on SQL, but it must be dramatically lower than the cursors NAV is using to fetch the data.

/Lars

David_Singleton

Hey Anil, just to clarify; I am not saying you shouldn't do this, and in fact I think its a good idea. BUT its important to know WHY the users are doing this before giving advise.

Also my next question would probably be "How big is the database?"

David_Singleton

And the question after that... "which reports".

myanilkumar

Hi All,

Thanks for the Valuable replies. The users dont want to run from Production DB because they worry about performance issues.

Some of the reports are Excel reports run from NAV, Some are SQL reports.

Please help me to provide some info or any links about How to configure Log Shipping or replication.


Regards,
Anil

stryk

Basically all you need to know about LS could be found in the SQL Server "Books Online", see also http://msdn.microsoft.com/en-us/library/ms187103.aspx or http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx