December 6, 2007 at 12:59 pm
I have been working with SQL Server for over twelve years. like to have best practices on third party software. I am unable to find anything that pertains to MS Dynamics AX. I am new to Dynamics and my company is in the process of implementing Dynamics AX.
SQL Server Edition: SQL Server 2005 Enterprise SP 2
I have a few questions so bare with me.
1. From a DBA standpoint, Dynamics AX allows the database schema to be
modified by developers. This isn't usually best practice and can ultimately
cause performance and data integrity issues. What is the best technique to prevent schema
changes to Dynamics AX so that the DBA can review the changes?
2. Dynamics AX builds its own SQL statements to the database. I don't see
any SQL Server Stored Procedures or Views. How can I best find the "What?"
db calls are being made and if performance becomes and issue, how do I modify
the SQL on the client side? I already use SQL Profiler, but that can be
different depending on the ERP Module.
3. Does anyone have any SQL Server "Best Practices" for maintaining and administering Dynamics AX?
I was hoping others in the SQL Server Central site has had some exposure to MS Dynamics AX and could shed some light on "How?" they have managed there servers.
Thanks
Greg
December 7, 2007 at 9:09 am
I haven't managed AX, but I did admin a Dynamics site about 5 years ago. Some of this may or may not apply.
We removed "sa" from Dynamics even though MS claimed it was necessary. The only thing it was needed for was adding users to the db (along with a login). We just had the CFO send us a note, we'd create the login and user, and it would then appear in the Dynamics interface.
My guess is the same thing will apply here and this same idea could be used for schema changes.
As far as SQL. My guess is the programmers at MS don't follow the SQL Team's recommendations any more than the guys before them and build dynamic SQL everywhere. Since your chart of accounts gets modified and you could possibly have lots of schema changes to handle different business rules, this is how a few accounting packages handle things. Whether they should or not is another debate, but I'm guessing that what happens here.
Not sure how to handle this. Best you can do is experiment with the front end and queries and see if there's a way to influence things. Most likely you'll be on a constant index management/change effort to keep up.
December 7, 2007 at 3:09 pm
Thanks for your reply Steve. I always enjoy reading your responses. As for the post, I do think I am going to be on guard with Dynamics AX and change control. I had thought about adding a database trigger to the server looking for changes to the production database and non-sysadmins to prevent the action from occurring. Do you know if a DB Trigger is a "BEFORE" or "AFTER" trigger?
I really don't care "What" they do to the development server, but I do care what happens on Production. Other things I have heard so far is to:
Disable to "ddladmin" privs for the AOS (Business Object Layer) Domain accounts.
I guess DAX has its own Tracing methods. I am used to Profiler, but something is better than nothing. At least I will be able to see how DAX is preparing SQL to the Server. I haven't implemented anything regarding the Tracing and prefer Profiler, but when it comes down to it, the best tool for the job.
Supposedly, Microsoft has produced some "Best Practices" for DAX development. Whether the developers here at my job are adhering to BP, I don't know. I haven't received the file yet.
Thanks again,
Greg Grow
December 8, 2007 at 4:10 am
if it's anything like Navision you have my sympathies. Hopefully you should be able to restrict sysadmin and dbowner rights - that at least may help. You can still enable dml triggers or write a process to check for changed objects linked to a report or alert.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 10, 2007 at 3:01 pm
Yes, it is Navision. Same product. I don't have a whole lot of information as I am new to this particular ERP solution. However, I am trying to grasp the DBA Role with Navision or Dynamics AX. I will probably implement Versioning requirements and scripting to production.
Since the environment is flexible, I am not sure how to implement Partitioning or mulitple file groups. I expect our go-live to be anywhere from 500GB to 750GB. It could reach 1.6 TB if we bring over all of our legacy data. With tables this size, I am thinking about separating some of the tables or indexes onto there own physical disks. But i am not sure how Navision (Dynamics AX) can handle the multiple filegroups.
Thanks
Greg
December 10, 2007 at 4:11 pm
you might want to contact me off forum - you're probably heading for a whole load of hurt and if your developers are not sql savvy you may have some major headaches. Big Navision databases need really hefty hardware - most navision people think 100gb is large , I administered a terabyte of data. I know each navision implementation is different but this product, in version 4, has some major issues with sql server 2005
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 10, 2007 at 4:19 pm
I sent a private message, asking for contact information. Our "Var" is in the process of upgrading from 3.3 to 4.2 DAX. I would like to have an offline conversation. Considering, this tool is unlike what I am used too. The more information I have the better. DAX is on SQL Server 2005.
Thanks
Greg
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply