NAV 50 SP1 & SQL Reporting Services

deeply

Hello everyone,
i have the following situation:
Server NAV-SRV with SQL 2k8 and NAV 5.00 SP1
Server REPORT-SRV with SQL 2k8 and SQL Reporting Services
Database NAV "duplicated" with log shipping from NAV-SRV to REPORT-SRV

Problem: When i use a report with a data source on the "standby" NAV database on REPORT-SRV i get error messages that i have no access to the specified table. (User has rights in NAV to do so).
If I change the data source to the NAV-SRV i can open the report with no error message (before I synced the NAV Users to SQL)

Question: How can i sync the NAV Users inside the Database on REPORT-SRV to the SQL users so i can open the table on that local Database?

Thx for your help ....

Sven

stryk

Hi,

well, databases in pogress being created with Log Shipping (STANDBY) or Mirroring (RECOVERY) are unavailable.
If you use SQL Server ENTERPRISE Edition you could create a "Database Snapshot" from such a mirror-db!
Snapshots can be accessed - read only - e.g. for reporting etc.. See "Books Online" about details.

If you don't have EE but STD, then you could use the SQL site "Replication" features ... also, see "Books Online".

Regards,
Jörg

deeply

Hi Jörg,

thx for your quick reply.

that was what the same that i tought. But somehow when i am using the Domain Administrator, which has all rights per defualt on that server your are able to view that report, event the DB is in standby. If you look with the Management Studio to this db it states that is standby/readonly.

So in my opinion i assumed that it is a rights issue that i am not able to open that table with a normal user ....

Please correct me if i am wrong.

stryk

Well, actually you/everyone can access a database in STANDBY mode read-only.

The difference with accessing as admin or normal user is this:

Normal users don't have any access-rights on the SQL Server except the role "public" (which just contaisn very basic rights). A "public" user cannot read any data from the database (regardless if Standby or Online).
With NAV the users are connecting through an "application role" which grants the SQL site user-rights.
If such "public" users should access the db outside NAV - e.g. Reporting Services, ODBC, etc. - you have to configure the proper rights on SQL Server site!
This might help you: http://dynamicsuser.net/blogs/stryk/archive/2010/02/16/extended-database-hardening-nav-sql.aspx

Administrators bypass this authentications, hence, they have full access-rights.

But the trouble with log-shipped databases is actually this: even though you can access (read only) theSTANDBY database, you will be disconnected when a new TRN file is restored ... This makes it rather annoying to work with such a db ...

deeply

Hi Jörg,

thx for the good explanation it makes it more clear to me. Ok so using the log shipping method is a bad idea. Do you have another idea how i can structure it.

I want to have a copy or clone (1 hour difference is ok) to feed our Reporting Server. Since Reports should me be fed with max 1 hour old data and doing it in real time to our NAV database is very bad for the performance.

Would you recommend snapshots or replication or something else? Performance should suffer not too much.

Thx for your answers.

Sven

kriki

[Topic moved from 'NAV/Navision Classic Client' forum to 'SQL General' forum]

stryk

Well, again, it depends on the SQL Server Edition ...

If you have ENTERPRISE I would set up something like this:
Create a "Warm Standby" database with "Asynchronous Mirroring" (or "Log Shipping") for failover reasons, then create a "Database Snapshot" of the "Mirror DB" for the reporting (drop snapshot afterwards).

If you have STANDARD I suggest this:
Create a "Warm Standby" database with "Log Shipping" for failover reasons.
Create another "Reporting Database" which only contains the data you need for the reports/analysis. Use "Transaction Replication" to update the reporting database periodically.
Again, basically you could use the logshipped db, but when a TRN restore is progress user are disconnected. But depending on the TRN frequency you maybe could organize the workflow:
e.g. TRN restore happens hourly (full hour hh:00), so reporting is possible from hh:10 to hh:59 ... I'm not sure if that is feasible ...

To be discussed in detail ...