Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 269 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
NAV 50 SP1 & SQL Reporting Services
deeply
Member Posts: 6
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
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
0
Comments
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
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool
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.
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 ...
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool
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
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
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 ...
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool