Saturday 2 January 2016

AX 2012 SSRS - Unable to find appropriate service endpoint information in the configuration object

AX 2012 – Reporting Services Extensions rollback - Unable to find appropriate service endpoint information in the configuration object

When I am trying to install the Dynamics AX Reporting Services Extension on the SQL server reporting server, installation is getting rollback due to the error.  I continuously found following error message in log files. 

Error: Unable to find appropriate service endpoint information in the configuration object.

Solution:

SQL Server reporting services communicate with dynamics AX through WCF Services. When I open the application integration framework in Dynamics AX -> System Administrator -> Setup -> Service and Application integration framework -> inbound ports ->BIservices are deactivated. So, I have followed the following steps to resolve it.




Activated the BIServices as shown in the below below mentioned  screenshot.
Then, Open the AX Client Configuration in the Reporting services server and Refresh the business connector configuration.


That’s It!!!

AX 2012 - A call to the Microsoft Dynamics AX SRSFramework Service failed.

SSRS Report Deployment in AX 2012 - A call to the Microsoft Dynamics AX SRSFramework Service failed.


When i was working on my AX 2012 production server configuration and try to publish the reports using the Microsoft Dynamics AX 2012 Management Shell using the following command

Publish-AXReport -ReportName *

Publishing the reports are failed and following error was thrown.

"A call to the Microsoft Dynamics AX SRSFramework Service service failed."



Solution:

To resolve the issue, perform the following steps 

  1. Stop the AOS Services(dynamics AX application service) in Application Server and Reporting server services in DB Server or separate reporting server.
  2. Go to the Following path:  C:/Users/UserName/AppData/Local
  3. Take the Backup all AUC extension files from the local folder and delete these files from Local folder


    4.  Start the Reporting Server services and AOS Services
    5.  Now try to deploy the reports from powershell

Thats it!! now you can deploy the reports successfully...

Saturday 26 December 2015

AX 2012 Retail - Schedule and Automate backups of SQL Server databases in SQL Server Express

Schedule and Automate backups of SQL Server databases in SQL Server Express


Dynamics AX Retail POS mostly uses SQL Server express per store database. SQL Express version is attractive because it provides a lot of value and free.  This is especially true for retailers that have many stores as the cost of database software licensing would quickly become cost prohibitive. The use of SQL Express requires careful planning and consideration for ongoing maintenance and an understanding of how the database will grow.

SQL Server Express editions do not offer a way to schedule either jobs or maintenance plans because the SQL Server Agent component is not included in these editions. Therefore, you have to take a different approach to back up your databases when you use these editions.

This article describes how to use a Transact-SQL script together with Windows Task Scheduler to automate backups of SQL Server Express databases on a scheduled basis

Follow these 3 steps to back up your SQL Server databases by using Windows Task Scheduler:

Step 1:

Use SQL Server Management Studio Express to create the following stored procedure in master database:

USE [master] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
 
-- ============================================= 
-- Author: Microsoft 
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
 
Create PROCEDURE [dbo].[sp_BackupDatabases]  
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200) 
AS 
 
       SET NOCOUNT ON; 
           
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
           
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up
             -- If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
           
            -- Filter out databases which do not need to backed up
            IF @backupType='F'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','ReportServer','ReportServerTempDB','master', 'model','msdb')
                  END
            ELSE IF @backupType='D'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','ReportServer','ReportServerTempDB', 'master', 'model','msdb')
                  END
            ELSE IF @backupType='L'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','ReportServer','ReportServerTempDB', 'master', 'model','msdb')
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
           
            -- Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000) 
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                  
                       
            -- Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs
 
      WHILE @Loop IS NOT NULL
      BEGIN
 
-- Database Names have to be in [dbname] format since some have - or _ in their name
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
 
-- Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  
 
-- Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = 'F'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'D'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'L'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
 
-- Provide the backup a name for storing in the media
      IF @backupType = 'F'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
      IF @backupType = 'D'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
      IF @backupType = 'L'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
 
-- Generate the dynamic SQL command to be executed
 
       IF @backupType = 'F' 
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
                  END
       IF @backupType = 'D'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                  END
       IF @backupType = 'L' 
                  BEGIN
               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        
                  END
 
-- Execute the generated SQL command
       EXEC(@sqlCommand)
 
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
 
END

Step 2:

In a notepad, write the batch file code depending upon your scenario. The SQL ID used for backup should have at least the Backup Operator role in SQL Server.


Example: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

//File Name: Sqlbackup.bat 
//@backupType = 'F' : F means Full Backup
//If your instance is SQLEXPRESS use \SQLEXPRESS. If it is defaulut mention \ only
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’YOURDB’, @backupType='F'"

 
Similarly, you can make a differential backup of  YOURDB by pasting in 'D' for the @backupType parameter and a log backup of YOURDB by pasting in 'L' for the @backupType parameter.

Step 3:

Schedule a job by using Windows Task Scheduler to Run the batch file that we created earlier in step 2. To do this, follow these steps:

1. On the computer that is running SQL Server Express, click Start, Serach for Task Scheduler, and then click Task Scheduler. 

In the Task Scheduler click Create Task.


2. In General Tab, Type SQLBACKUP for the name of the task, Run whether user is logged in or not option.


3. In Trigger Tab then Click New, click Daily and Specify information for a schedule to run the task. (As recommend that you run this task at least one time every day.) Then slect enabled option, and then click Ok.



4. In Action Tab then click New, Click Browse, click the batch file that you created in step 2, and then click Ok.


5. Run the scheduled task at least one time to make sure that the backup is created successfully.



That's It!!!!

Make sure that there should be space on the drive to which the backups are being taken.


 Source: Microsoft

Monday 14 December 2015

What’s New in CU 10 for Microsoft Dynamics AX 2012 R3


What’s New in CU 10 for Microsoft Dynamics AX 2012 R3


This Article describes new or changed functionality, and updates, that were included in AX 2012 R3 cumulative update 10. Most changes were made in the Warehouse management, Transportation management, and Retail areas of Dynamics AX.

Warehouse management enhancements


  1. Improvements to replenishment 
    • Wave demand replenishment now recognizes existing min/max replenishment work as supply when evaluating if it should create new replenishment work. Cancelling wave based replenishment work will now unblock sales order work automatically. Demand replenishment will now consider all lines in the replenishment template even with different directive codes in situations where the entire demand quantity is not assigned to a put location. The directive code on work templates with the work order type Replenishment is now editable.
  2.  Improvements when viewing inventory on hand
    • A new field called Available physical on exact dimensions has been added to the On hand by location form. This field shows the available physical quantity for all the dimensions displayed on the screen. There have been performance improvements to the on hand stored procedure to use better query plans and make sure it is only called when necessary. A new clean up job has been added under Inventory management > Periodic > Clean up > Warehouse management on-hand entries cleanup. This job will delete records in the InventSum and WHSInventReserve tables for closed on-hand entries.
  3. Enhanced serial number functionalities
    • Items with a tracking dimension using serial numbers in the sales process can now be used with warehouse management processes. It is now possible to validate sales serials using the mobile device as well as the ability to indicate unreadable serial numbers. You can also enter sales serial numbers using the pack station and for sales order returns. Additionally, you can open the form that shows the recorded serial numbers from the Load lines tab on the Load details form. It is also possible to register serial numbers from the Picking list registration form, which will open the form for capturing sales serial numbers related to the sales line
    • In the manufacturing process, when consuming serialized components, it is now possible to postpone the registration of the serial number until production consumption. This will save time in warehouse processes as the serial will not be needed to be registered during receiving of serialized components.
  4. Improvements to inbound processes
    • The behavior of the mobile device menu item with the work creation process set to License plate receiving has changed. It will now only register the arrival of a purchase order and create put away work. It will leave the created work in an open state. If you want to use the old process, where the put away work was also conducted at that point in time, use a mobile device menu item with work creation process set to License plate receiving and put away. During the process of put away work using the mobile device, we have added several new options for exception handling for the warehouse worker. Three new buttons have been introduced during put away work; Split put, Override LP and Skip. They can be used to split items to multiple locations, consolidate items on a license plate at a location, or skip the current work line and proceed to the next put away line. Additionally, the Cancel button will now be available in all put away screens. 
    • During purchase receiving using the mobile device, the unit of measure will be defaulted to the UOM setup on the GTIN number. When using the Delivery schedule form on a purchase order line to split the delivery of a purchase order to different days, the load lines will also be updated. Performance when registering product receipt and querying for a put away location has been improved when using Volumetric and Stocking limits constraints for locations. Now the amount of full locations in the query should not affect performance. 
  5. Improvements to outbound processes
    • Over picking using the mobile device has been enabled during sales and transfer order picking work. Over picking will only be possible if there is enough inventory available at the current pick location and if it is not already allocated to other work. To enable over picking, both the work user and the mobile device must be set up to allow over picking. 
    • During picking on the mobile device, you can now use the Full button any time during picking to indicate that you cannot pick any more lines. It is now possible to block release to warehouse and creation of work for sales orders that exceeds customers credit limit. Performance of closing of containers during pack operations have been improved by avoiding some client/server calls. Performance when having containerization as part of wave posting been improved and optimized when scaling number of sales lines per order 
  6. Improvements to work templates
    • A new section of parameters has been added to the Work template form called Work header maximums. They can be used to split a work header when the sum of work lines exceeds the quantity specified.
  7. Integration to project and Integration to manufacturing


Transportation management enhancements 

  1. Create scheduled routes
    • Scheduled routes can now be generated from a route plan, where the segments of the scheduled route correspond to the hub configuration associated with the route plan. A batch job is used to create the scheduled routes depending on date range, particular days of the week and a load template. The scheduled routes can be used with Load building workbench in order to create optimal loads for the routes.
  2. Improvements to auto-release to warehouse process
    • hen using the auto-release to warehouse process it is now possible to consolidate multiple transfer orders based on To warehouse and From warehouse into the same shipment. 
  3. Integration to project 

Retail enhancements 

  1. Global refund tab on the Register form and the “Global refund check” report on the Retail store transactions and Online store transactions forms have been removed.
  2. Variant specific sales price is shown when a product variant is selected during product search or inventory lookup in POS. Previously the product base price was shown.
  3. This enhancement lets user select Infocode groups in open drawer store operation under AX functionality profile
  4. Retail POS SDK now allows for customization of the item types that are allowed for return.
  5. If the primary address on customer is marked as private address, the sales order created for a customer with this private address cannot be picked up at the retail store.
  6. Print Gift Receipts in Modern point of sale (MPOS): Added gift receipt feature at MPOS for: 
    • Sales Transactions 
    • Show journal, both Gift receipt preview and print  
  7. The changes in the hotfix enable look up of another store's customers using real-time service.
  8. Introduced alternative algorithm for calculating discounts, which is enforced if predefined max number of calculations steps/loops is exceeded. In this case, point of sale will switch to the marginal value algorithm, which does not guarantee the best discount but provides much better performance characteristics. This feature is configurable in Retail Parameters
  9. Enable Extended Login registration in MPOS, This operation provides an interface that you can use to enroll or remove workers in extended logon using peripherals. Supported peripherals include barcode scanners and magnetic strip readers
  10. Retail MPOS installation was not supported on the Windows 10 Long Term Service Branch. With this hotfix we have added this support extending the supported operating systems for MPOS to the following: 
    • Windows 8.1 Update 1- Server 2012 R2 Update 1 
    • Windows 10- Windows 10 Long Term Service Branch



Source: https://mbs.microsoft.com/files/public/CS/AX2012R3/WhatsNew_MicrosoftDynamicsAX2012-R3-CU10.pdf


Friday 4 December 2015

SSRS Deployment Failed - The "DeployToReportsServerTask" task failed unexpectedly


Error # 1:

The "DeployToReportsServerTask" task failed unexpectedly.
System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)
   at Microsoft.Dynamics.Framework.Deployment.Reports.DeployToReportsServerTask.Deploy(IEnumerable`1 transitiveReferenceClosure, DeploymentLogger logger, Boolean restartReportServer)
   at Microsoft.Dynamics.Framework.Deployment.Reports.DomainBoundHelper.Deploy(IEnumerable`1 transitiveReferenceClosure, DeploymentLogger logger, Boolean restartReportServer)
   at Microsoft.Dynamics.Framework.Deployment.Reports.DomainBoundHelper.Deploy(IEnumerable`1 transitiveReferenceClosure, DeploymentLogger logger, Boolean restartReportServer)
   at Microsoft.Dynamics.Framework.Deployment.Reports.DeployToReportsServerTask.Execute()
   at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
   at Microsoft.Build.BackEnd.TaskBuilder.<ExecuteInstantiatedTask>d__20.MoveNext() C:\Program Files (x86)\MSBuild\Microsoft\DynamicsTools\Microsoft.Dynamics.Framework.Design.Reporting.Modeling.targets 466 6 VendorDetailsReport


Error # 2: An error occurred : Access is denied.

If User Account Control (UAC) is enabled on the machine, close the application, right-click the application, and then click Run as administrator. C:\Program Files (x86)\MSBuild\Microsoft\DynamicsTools\Microsoft.Dynamics.Framework.Design.Reporting.Modeling.targets 466 6 VendorDetailsReport

Error # 3: The deployment was aborted. You do not have privileges to deploy to server: SINGU-VIRTUAL. For deployment, you must have administrative rights to the SQL Server Reporting Services (SSRS) server. Contact your administrator to deploy. C:\Program Files (x86)\MSBuild\Microsoft\DynamicsTools\Microsoft.Dynamics.Framework.Design.Reporting.Modeling.targets 466 6 VendorDetailsReport


Solution:

Open Visual Studio with "Run As Administrator" and open the project. Try to re-deploy the solution again


Sunday 12 April 2015

Microsoft Dynamics AX Named User License Count - AX 2012

If you want to compare the data, Based on your license purchase history with Dynamics AX user number, There is a report under System admiration > Reports > Licensing > Named User License Counts. This report contains information about the users who are assigned to security roles in Microsoft Dynamics AX. The report analyzes the roles and privileges that are assigned to each user and then calculates the number of licenses that are required for each user type.

Please follow the attached steps to Check/Count Named User in Dynamics AX 2012.

1. Go to the System Administration >> Setup >> System >> Server Configuration and Enable the Batch Server or define your Batch Server and enable it in case you have more than one AOS and you dedicate one of them as a Batch Server.



2. Define the Batch Group from System Administration >> Setup and Make Sure Batch Server is Running.

3. Click on Batch Jobs from System Administration >> Inquiries and Make Sure that the Batch Job “Named User Count” is available

4. If not, add the below job and then run it create the batch job.
static void LicenseMinerCreateBatchJob(Args _args)
{
    SysUserLicenseMiner::createBatchJob();
}

5. Click on the Batch Task and Make Sure that the Batch Job is assigned to the Batch group you had enabled it in Step 1 and Click on the recurrence of batch job and configure it weekly or monthly once.



6. Then Run the Report under System Administration >> Reports >> Named User License Count






Friday 28 March 2014

AX 2012 Performance - Configure SQL Server and Storage


This Article provides information about how to configure database and windows server for Microsoft Dynamics AX. To achieve optimal Microsoft Dynamics AX performance, we must correctly configure the database infrastructure. 

You must have knowledge in following areas:

·         Windows Server administration.
·         SQL Server administration. Specific areas of knowledge include advanced configuration options, memory management, performance management, and troubleshooting.
·         Microsoft Dynamics AX system administration.

Recommendation on Tune DB operations:

#
Recommendation
Reference

1
Enabling trace flags 4136 ,1117 and 4199 on SQL server.
T-4136 : Disable parameter sniffing
T-4199 : Activate query optimizer fixes
T-1117 : DB files growth
2
Disabling index hints from AX configuration and thereby allowing SQL to decide the best way to process the query.
Best Practice
3
Making sure that entire table cache is not enabled for tables which get frequently updated.
Best Practice
4
SQL Server service account is not granted SE_MANAGE_VOLUME_NAME right
Best Practice - Space created by Delete to used
5
Reducing the MAXDOP setting to 1 and test it.
Best Practice
6
Change Minimum SQL to 0 to 60 GB
Best Practice
7
Making sure no of tempDB files = No of logical processors. It must be 24 data files
MS Best practice : ensuring Temp files used by DB for temp table operations

Recommendation on I/O Storage and Physical Structure tuning:


#
Recommendation
Reference
1
DB File Growth is having auto-growth setting to trend of increase in size and It recommended to set to 10%
Best Practice
2
Making sure we have primary key/clustered index on each table in AOT for enabling record caching.
Best practice : Ensuring table scans are avoided









IOPS for AX 2012:

Benchmark reports of Microsoft, which the performance study done in AX 2012 with SQL 2008 R2. Based on the report I have attached the snapshot of IOPS and CPU utilization.
The below study is done for 5000 users. I also attached the detailed report which has  transaction details, hardware details and system configuration.

You can also see the same report from the below link,

http://technet.microsoft.com/en-us/library/hh536205.aspx




Recommendation on Platform related Tuning:

#
Recommendation
Reference
1
Disable 'Power Saving' setting on DB server and change to 'High Performance’. Taking reference from MS on performance improvement , power saving will reduce sql performance by 50%
Best Practice
2
Exemption of AOS processes from real time antivirus scanning  1) AX32.exe 2) AX32serv.exe
All the application folders
Best Practice
3
Disable Scalable Networking Pack on Application Servers

4
Install MS latest hot fixes on Applications servers  to update Processor Driver.

5
Disable Debug Settings
Best Practice
6
Disabling Error Reporting in AOS servers
Best Practice



Minimal SQL Server infrastructure

The configuration of Windows Server and SQL Server greatly affects the performance of the Microsoft Dynamics AX database. This below recommendations for the configuration of Windows Server and SQL Server.
The configuration recommendations are based on the following assumptions:

·         You are using a dedicated server that runs SQL Server.
·         You are using a single instance of SQL Server that is dedicated to running the Microsoft Dynamics AX production database.
·         MS recommend that you store your test and development databases on a separate server from the production database.



Configuring the Microsoft Dynamics AX database

Microsoft recommend the following settings for the Microsoft Dynamics AX database. we can use SQL Server Management Studio or the appropriate ALTER DATABASE statement to configure these settings.


·         Set COMPATIBILITY_LEVEL to 100.
·         Set READ_COMMITTED_SNAPSHOT to on. Performance testing has shown that Microsoft Dynamics AX performs better when the READ_COMMITTED_SNAPSHOT isolation option is set to on.
·         Set AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS to on. Set AUTO_UPDATE_STATISTICS_ASYNC to off. Performance testing has shown that Microsoft Dynamics AX performs better when the options have these settings.
·         Make sure that the AUTO_SHRINK option is set to off. When database files are automatically shrunk, performance of the database degrades. 

Configuring the server that runs SQL Server

For SQL Server, we recommend the following configuration settings for the SQL Server service.
·         Run the SQL Server service under an Active Directory domain account that has the minimum necessary privileges.
·         Confirm that the account for the SQL Server service has been granted the Lock pages in memory privilege.
·         Configure the account for the SQL Server service for instant file initialization. Instant file initialization is only available if the account for the SQL Server service, MSSQLSERVER, has been granted the SE_MANAGE_VOLUME_NAME right. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy.
·         Enable the TCP/IP network protocol. Depending on the edition of SQL Server that you use, this protocol may be automatically installed during installation. For instructions,
Disable hyperthreading. This step must be performed in the BIOS settings of the server. For instructions, see the hardware documentation for your server.


Configuring the instance of SQL Server

Configuring max degree of parallelism

The max degree of parallelism option is a setting that affects the entire instance of SQL Server. Microsoft Dynamics AX workloads generally perform better when intra-query parallelism is disabled. However, the upgrade process benefits from parallelism, as do activities that are used exclusively for batch jobs or maintenance. Use the following settings when the system performs maintenance activities or an upgrade:
·         Before an upgrade to a new release of Microsoft Dynamics AX, or before a large number of maintenance or batch activities, set max degree of parallelism to the smallest of the following values:
·         8
·         The number of physical processor cores
·         The number of physical processor cores per non-uniform memory access (NUMA) node
·         When the Microsoft Dynamics AX database is used in a production environment, set max degree of parallelism to 1.
Use the following statements to set the value of max degree of parallelism.
Examine the output from the second sp_configure 'max degree of parallelism' statement, and confirm that the value has been changed. In the following query, the first sp_configure 'max degree of parallelism' statement sets the value of max degree of parallelism to 1. The second sp_configure 'max degree of parallelism' statement returns a value of 1.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE; 
GO
EXEC sp_configure 'max degree of parallelism', 1;
RECONFIGURE;
GO
EXEC sp_configure;

Configuring max server memory

SQL Server dynamically acquires and frees memory as required. Typically, an administrator does not have to specify how much memory is allocated to SQL Server. However, the max server memory option can be useful in some environments. Make sure that sufficient memory is available for the operation of Windows Server.

Monitoring available memory

Make sure that sufficient memory is available for the operation of Windows Server. For example, make sure that you run a dedicated instance of SQL Server on a server that has at least 4 gigabytes (GB) of memory. If the available memory for the server drops below 500 megabytes (MB) for extended periods, the performance of the server may degrade.

Allocating storage for tempdb

MS recommend that you determine the total size of the data files and transaction log files that are required for the tempdb database, and that you set a specific value. Do not use automatic growth, or autogrow, setting for space management. Instead, use autogrow as a safety mechanism, so that tempdb can grow if tempdb files use the space that was originally allocated to them. Follow this process to determine the number and placement of data files.
·        Determine the number of processors that are available to SQL Server. Unless you are using an affinity mask, this number is same as the total number of processors that you see on the Performance tab of Windows Task Manager. When hyperthreading is not enabled, each processor corresponds to a processor core. Affinity masks and processor cores are beyond the scope of this topic.
·        Based on performance testing of the OLTP workload for Microsoft Dynamics AX, we recommend that you maintain one tempdb data file per processor. 
·      Isolate tempdb on dedicated storage, if you can. MS recommend that you move the primary data file and log file for tempdb to high-speed storage, if high-speed storage is available. The Microsoft Dynamics AX database runs in read committed snapshot isolation (RCSI) mode. In RCSI mode, row versions are stored in tempdb. By creating multiple files for tempdb data, even if these files reside on the same storage device, you can improve the performance of tempdb operations.
·        Determine the size of the tempdb data files and log files. You must create one primary data file and one log file. Determine how many additional, secondary data files you require for the tempdb data. For best results, create data files of equal size. The total number of data files must equal the total number of processor cores. The aggregate size of the primary data file and all other data files must equal the total data size that you determined for the tempdb database.
·        Resize the primary data file and log file for tempdb. Move the primary data file and log file to dedicated storage, if dedicated storage is available. The primary tempdb data file cannot be moved while the instance of SQL Server is running. To complete the move, you must use an ALTER DATABASE statement and restart the instance of SQL Server.
·      If space is available on the drive where tempdb files are allocated, do not configure the autogrow property for data files and log files as a percentage. Instead, configure the autogrow property as a specific number of megabytes. If you can, configure the data files and log files to grow by 100 to 500 MB, depending on the available space. Monitor the data files, and when they grow, adjust the original allocation to prevent automatic growth later. If the autogrow property is configured in megabytes instead of as a percentage, the allocation of space is more predictable, and the chance of extremely small or large growth increments is reduced.
·        Monitor the tempdb data files and log files to make sure that theyare all sized correctly, and that all data files are of equal size. Use SQL Server Management Studio or a transact-SQL query to view the database properties. Verify that all the data files are of equal size, and that they have the same size as the value that you originally provided. If one or more files have grown, adjust the initial size of all files.

Plan database storage

1.     Characterize the input/output (I/O) load of the application. The I/O characteristics depend on your business requirements, and on the Microsoft Dynamics AX modules and components that we deploy. To determine your I/O characteristics, answer the following questions:
·         What is the read ratio versus write ratio of the application?
·         What is the typical I/O volume, or I/O per second (IOPs)?
·         How much of the I/O is sequential, and how much is random?
2.     Determine the availability and performance requirements for the database system.
3.     Determine the hardware that is required to support
4.     Configure SQL Server properly.
5.   Track the performance as the workload changes

Configuring physical storage

·         Create the tempdb database files, data files for the Microsoft Dynamics AX database, and Microsoft Dynamics AX log files on disk arrays of type RAID 1, RAID 0 + 1, or RAID 10. Microsoft recommend RAID 10 for these files. Do not use RAID 5.
·         Store the data files for the Microsoft Dynamics AX database on separate physical stores from the transaction log files.
·         Store the tempdb data files on a separate physical store from the data files and log files for the Microsoft Dynamics AX database.

·         Store other database files on separate physical stores from the data files and log files for tempdb and the Microsoft Dynamics AX database.


Reference from Technet  articles and personal resources