SSRS 2008 Setup Option for Dynamics AX 2009

With deploying Role Centers, and specifically around SQL Server Reporting Services (SSRS) 2008 for Dynamics AX 2009, we have seen all kinds of existing whitepapers, blog entries, etc. That help get Role Centers up and running.

Reporting Services is a part of the underlying technology that enables Role Centers to work.

With Dynamics AX 2009 and SQL Server 2005, as part of SSRS setup we had the IIS Pool Identity that we set to the .Net BC. We also had to setup an execution account that the Reporting Service executed under, and that had correct Kerberos Security SPN's created.

With SQL Server 2008, Reporting Services, we no longer have a SSRS site in IIS, and therefore no longer an AppPool identity to set for the .Net BC. What does you do then?

Well if the .Net BC account is also your execution account for SSRS, what the service 'logon as' is set too, then your fine. However what if you Want a seperate account for your .Net BC, that is different from the account used for esxecution of the Reporting Service?

This is where the execution account, in the Reporting Services comes into play. Wait though!

In the setup, have we not seen where when this is set, we see issues with Role Centers, specifically SSRS based web parts not working? Yes, but with SQL Server 2008 Reporting Services, and this desired setup, then we have to supply the execution account be that of the .Net BC.

Doing so enables us to have a seperate account for the execution of the SSRS service, that is different from the .Net BC account, while still enabling Kerberos security work and Reporting Services Web Parts to be fired correctly that point back into Dynamics AX.

So in short.:

• You can have a seperate account that runs the Reporting Service, that is different from the .Net BC account.


• In SQL Server 2008, no AppPool identity to set.


• Instead of AppPool identity, setup the Execution Account as the .Net BC account.


After having correct Kerberos Security, with SPN's setup, then this will enable the desired mentioned configuration.

One other point with SQL Server 2008 Reporting Services, do everything through the 'Reporting Services Configuration Manager'. This includes restarting the services, and setting accounts and settings. It's not recommended that you restart the Reporting Service, through the Service MMC. Doing so cause order of service startup actually to sometimes mess up.

That's all for now, but check back soon as more to come!

"Visit the Dynamics AX Community Page today!"

What is SQL Server BI anyway?

One of the major focuses for a lot of companies these days, even in these tough times, is BI. I have wrote about this recently, specifically with the move of PerformancePoint as being part of the SharePoint offerings.

Direct link to my last post here: Dynamics BI: BI for the Masses

That post gave a real broad picture into what BI will look like from an overall Microsoft BI strategy.

However, what is BI in terms of SQL Server? Why is this important to understand?

Well Dynamics, and therefore Dynamics AX, makes use of SQL Server to run on top of. And more and more SQL Server features are being tightly integrated and very much a part of Dynamics AX solution.

Since this is the case, it makes sense, that one needs to understand BI from SQL Server point of view. In doing this, then the incorporation can better be unnderstood, and the use of SQL Server BI solutions inside Dynamics AX can be better made use of.

So what is BI in terms of SQL Server?

BI for SQL Server is made up of several key technologies / terms. They are:

• SQL Server Reporting Services (SSRS).
  


• SQL Server Analysis Services (SSAS)
  - Multidimensional Data
  
  - Data Mining
  


• SQL Server Integration Services (SSIS)
  

These technologies make up what can be used to create BI solutions from SQL Server's point of view. Each of the above links goes to the home page found on MSDN, which contains to wonderful amounts of information about each of these topic area's.

SSRS can be used to create functional / procedural reports, where SSAS can be used to mine data, create predication based cubes, and other OLAP data dimensional cubes from transactional data. SSIS can be used to ETL, or Extract, Transform and Load data from many, many sources into a SQL Server staging or Datawarehouse that can then be used by SSRS and SSAS for reporting and knolwedge management about a businesses operations.

These are the key to understanding BI solutions from SQL Server's point of view, and answer the question What is BI in terms of SQL Server?

Next I will move forward with these topic area's for help in understanding them, and answer the next question in line: Why is this important to understand?

Check back soon, and see you later!




"Visit the Dynamics AX Community Page today!"

Blog Spotlight: SQLSkills.com - Kimberly L. Tripp

Convergence is wrapping up as I type, with most people thinking about getting back home and getting a little rest. There has been a lot of good information coming out from convergence, so look for my wrap up next week on that.

Now, as promised, I am getting back to some SQL post. This time, what I wanted to do was actually point out, and do a Blog Spotlight for a great SQL resource. I am talking about Kimberly L. Tripp from SQLSkills.com

Her Blog can be found here: Kimberly's - improving my SQL skills, through your questions - blog

She is just a great resource, and post on a regular basis. She covers internals well, and has a post up right now talking about database maintenance best practices. This post is Part I of a series she is doing right now, and I recommend you check it out.

I also recommend you read back through some of her other post. This is a great resource for all around SQL Server skills, how-to and information. This is not directly related to Dynamics AX, but these skillsets and information that you can learn from her post apply to the Databases and Database Servers that Dynamics AX instances run on.

So thanks Kimberly for doing such a great job over at SQLSkills.com and please keep the good posts coming!

Check back here for more, as I do a wrap up of convergence, continue on the SQL Server 2008 path, book reviews, and much more! See you then!




"Visit the Dynamics AX Community Page today!"

Book: SQL Server 2008 - Administrator's Pocket Consultant

Recently I got the SQL Server 2008 - Administrator's Pocket Consultant book from Amazon.com.

I have reviewed it, and it's just a great handy book to have for anyone that does work with SQL Server 2008. It covers a great amount of topics, including the new Policy Based Management that was introduced with SQL Server 2008.

I recommend having the around, for quick, easy access of exact how to's on SQL Server 2008 DBA related tasks. I mean, know one can recall every little thing about SQL Server, and this helps you get the task done fast.

Check it out, and check back soon. A lot of great post coming up...




"Visit the Dynamics AX Community Page today!"

SQL Sever 2008 - Performance with Dynamics AX 2009 - Resource Governor

Alright, so lets get back to the SQL Server 2008 and how it's new features can be used with Dynamics AX 2009 to help with the performance of an instance.

Now with most any instance, the key to performance is in understanding query execution, query plans, and being able to tune these with indexes, and such. This of course is after you have done your benchmark and made sure you have enough RAM, CPU and HD Spindels running for you and correctly running.

These still apply to a DAX 2009 / SQL Server 2008 instance. However as I mentioned before, there are a good bit of new features in SQL Server 2008 to really help an instance out.

On this post, let's look at the: Resource Governor

So lets think of a make believe instance of DAX 2009. Just deployed, with EDI at this company, MRP, and nightly integrations flowing out of DAX 2009. Sounds typical these days for a lot of instances.

Ok, so you have all of this running, you do the normal SQL performance tunning and you notice that the nighlty integrations that flow out of DAX are causing some delays to the MRP process and also the same thing happens all the time with the EDI transactions and jobs that are being ran.

So we have run away query's going on here, and what can we do about it?

Well in SQL Server 2008, the resource governor can be used. This is the first version of SQL Server that you can now differentiate between workloads! What a concept huh?

So now you can take the resource govenor, create resource pools and workload groups, even policies that make use of classifier functions, to limit how much certian, specific incoming spids and their workloads can have access to the SQL Server's resources.

So you can make your MRP and normal DAX SQL traffic have higher priority to SQL Server resources, while placing the integrations and EDI into a resource workload group and pool that could limit the max amount of resources those could ever have. This way you can manage these, and stop these run away queries from ever hurting your normal day to day or high priority Dynamics AX processes.

Of course you should do this anyway, and then get with the EDI vendor and the development of the integrations so they can improve their processes to work better with the limited resources. ;-)

So the resource governor is something totally new to SQL Server 2008, and is a new tool that we can use to help manage and improve our Dynamics AX 2009 instances with.

Check back soon for more!




"Visit the Dynamics AX Community Page today!"

Looking into SQL Server 2008

Alright,

Now that Dynamics AX 2009 SP1 has been released, that means that SQL Server 2008 is offically the go to Database server for Dynamics AX 2009.

Of course you can run on SQL Server 2005 still, but I want to focus on SQL Server 2008 for a little while and talk about some of the benefits that it offers.

In this I want to go right to the performance enhancements that comes along with SQL Server 2008. This is where we can really see some real area's that a Microsoft Dynamics AX instance can really get some real mileage out of.

So with performance, let's focus on some key area's. These area's starting out will be:

• SQL Statement / Query Performance.


• Management of Resources on the SQL Server.


• Optimizing Storage with SQL Server

Each of these area's have some new improved items in SQL Server 2008, and I want to go into each. In doing so I also want to talk about how this will benefit a Dynamics AX 2009 instance and what this means for current and future customers.

So check back soon, as I dive into SQL Server 2008 for you, and walk into a new and faster Dynamics AX world!




"Visit the Dynamics AX Community Page today!"

Why upgrade to SQL Server 2008 with DAX 2009?

So some of you may be wondering, why should we be pushing Dynamics AX 2009 on SQL Server 2008? What would be the big gain of moving from SQL Serve 2005 to SQL Server 2008 or even from SQL Server 2000?

Well one of the biggest reasons for clients, is if they have already a Dynamics AX install base, with a database that keeps growing and growing.

In SQL Server 2008, they will gain performance but also have the ability to compress their database by 40-60%, and still keep those performance gains! This was benchmarked some time back and recently again via Microsoft information.

So for those wondering, yes there is a very great reason, well several: Performance and Compress, just to start.

Check back soon!






"Visit the Dynamics AX Community Page today!"

What's new in SQL Server 2008

Not sure if everyone has seen this nice write up yet over at Microsoft Technet, but it's a really good write up of what's new in SQL Server 2008 from different perspectives.

At a glance:
-More powerful management
-Improved performance and scalability
-Better security and availability
-Changes for developers

Check out the write up by going here: TechNet: What's new in SQL Serve 2008

I recommend everyone start to learn about SQL Server 2008, espcially with Dynamics AX 2009 released and ready for use with it. Check back soon!





"Visit the Dynamics AX Community Page today!"

Business Intelligence in Dynamics AX 2009 (Part I)

When you think of Business Intelligence in Dynamics AX 2009, what comes to mind? There will actually be several ways to get data, reports, and information feeds that help build one's business intelligence from their given Dynamics AX 2009 instance. What I want to cover in this post, is what Microsoft is calling Business Intelligence in Dynamics AX 2009, talk about Microsoft Office PerformancePoint Application and working with Dynamics AX 2009, as well as using Applications like Excel for retrieving data from Dynamics AX 2009 and creating Ad Hoc Business intelligence reports and information applications.

First lets start with what Microsoft is calling Business intelligence in Dynamics AX 2009. In order to do that, let me point out the MSDN home for Business Intelligence in Dynamics AX 2009:

MSDN: Dynamics AX 2009, Business Intelligence

In looking over this, it's actually similar to Dynamics AX 4.0, in what is considered to be Business Intelligence, which revolves around OLAP cubes, creating OLAP cubes, and working with / processing OLAP cubes from Dynamics AX that can be defined and created from the Transactional Data in your Dynamics AX instance.

With that said, Microsoft has done a Lot under the covers to SQL Server 2008, specially in regard to OLAP and OLAP services, processing engine, etc. So this new benefits can now be used in Dynamics AX 2009. There is a good write up on what all is new and improved in SQL Server 2008, Analysis Services located here: Cube Squared: Migrating Analysis Services to the SQL Server 2008 Platform, Part 1 of 2

So in Dynamics AX 2009, there are improvements already in what is referred to as Business Intelligence, and for sure you should be installing Any new Dynamics AX 2009 instance on SQL Server 2008, so if you do, then you will see a lot more benefits and improvements to Analysis Services and Business Intelligence together!

Next post I will talk about Microsoft Office PerformancePoint and using that with Dynamics AX 2009 to create and define Business Intelligence Processes! Check back soon!



"Visit the Dynamics AX Community Page today!"

Dynamics AX 2009 & SQL Server 2008

In my last post I talked about, how we are just now starting to scratch the Dynamics AX 2009 surface. And with the soon to be release SQL Server 2008 Database Product, I thought it was a perfect time to start talking about the two together.

I did some digging around, and came across some nice published information about Dynamics AX and SQL Server 2008. First off we have this recently published SQL Server 2008 Benchmark document:

Microsoft SQL Server 2008 - Benchmark Report

When looking at the above benchmark, do a search on the page for 'Dynamics AX'. Doing so will bring you to the following quoted statement:

"Record scale improvement of 70% in throughput, scalability and response time
Benchmark tests demonstrate record scale showing up to 70 percent improvement in throughput scalability and response time; maximizing performance while minimizing database growth using SQL Server 2008 database compression."

And Dynamics CRM benchmark information was just right below that:

"Record scale at 24,000 concurrent users with sub-second response rate
Benchmark tests demonstrate that record scale at 24,000 concurrent users with sub-second response rate was achieved with Microsoft Dynamics CRM 4.0, SQL Server 2008, and Windows Server 2008 for enterprise-level workload. Learn more"

Now, lets take a look at the offical Press Release for Microsoft Dynamics AX 2009: (Microsoft Dynamics AX 2009)

"- Enhanced security, reliability and scalability though integration with Microsoft SQL Server 2008. For example, Microsoft Dynamics AX 2009, through Microsoft SQL Server 2008, can compress the database size by 40 percent to 60 percent, which can lead to lower hardware and data maintenance costs."

So there is for sure a benchmarked reason why clients would want to upgrade to Dynamics AX 2009 & SQL Server 2008:

- Huge increase in throughput, scalability and response time.
- Ability to use compression, out of the box, to minimize hardware cost

And this seems to be the case for Dynamics AX 2009 in general. The overall architecture has improved greatly (see my article on MSDynamicsWorld.com: Scaling of the AOS), and now we can scale up and out, and take advantage of newer technology offerings. This also means that a client would gain a lot of performance benefit for switching from SQL Server 2005 to SQL Server 2008.

Check back soon!




"Visit the Dynamics AX Community Page today!"