emeadaxsupport: AX Retail: Retail store maintenance and SQL Express

[Blog bot]

Источник: http://blogs.msdn.com/b/axsupport/ar...-database.aspx
==============

Description

Here are some tips to do maintenance on your store database running SQL express and how to shrink the size of the database.



Store transaction cleanup

It is important to cleanup the store database as many store databases in smaller shop only runs SQL Express editions. SQL Express has a 10 GB limit in size and when reached the system stops.

Since most customers are running pull job on very regular time schedule like every 15-30 minutes, the transaction data exists in the HQ database and makes the data in the store obsolete.

In the Dynamics AX Retail module we have an option to set the number of days these transactions should exists on the store database.

POS Functionality profile
- Days transaction exists

Affected tables

RETAILTRANSACTIONTABLE

RETAILTRANSACTIONTABLEEX5

RETAILTRANSACTIONBANKEDTENDERTRANS

RETAILTRANSACTIONDISCOUNTTRANS

RETAILTRANSACTIONINCOMEEXPENSETRANS

RETAILTRANSACTIONINFOCODETRANS

RETAILTRANSACTIONLOYALTYPOINTTRANS

RETAILTRANSACTIONORDERINVOICETRANS

RETAILTRANSACTIONPAYMENTTRANS

RETAILTRANSACTIONSAFETENDERTRANS

RETAILTRANSACTIONSALESTRANS

RETAILTRANSACTIONTENDERDECLARATIONTRANS



The data deletion will be executed for every closing of the shift on the POS.



Prerequisites:

The functionality is not implemented before KB2831335 and the required Dynamics AX POS build is

Dynamics AX 2012 build 6.0.1108.4260

Dynamics AX 2012 R2 build 6.2.1000.785



NB: Always implement the latest kernel build to get the latest hotfixes.

SQL Database maintenance

It is a good practice to do maintenance on the store database. Regular rebuilding and updating of indexes improves the performance.

In SQL Express the SQL server Agent is not available and therefore maintenance jobs can´t be scheduled.



Another way to Schedule a DB maintenance

Windows Task Scheduler can be used to run a program like a command file (cmd). Make a new task and point the CMD file and add the
schedule.

SQL server has an interfase SQLCMD, which is available in SQL Express







CMD file

Create a new text file and add the code inside and set the extension of the file to CMD

SQLCMD –d yourdatabase -SLocalhost\SQLEXPRESS -HLOCALHOST –i c:\temp\sqlmain.sql



SQL JOB (sqlmain.sql)

Create a new text file and add the code and set the extension of the file to sql

DECLARE @Database
VARCHAR(255) = 'YOURSTOREDB'

DECLARE @Table
VARCHAR(255)

DECLARE @cmd
NVARCHAR(500)

DECLARE @Statement
NVARCHAR(300)

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''

-- create table cursor

EXEC (@cmd)

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table



WHILE @@FETCH_STATUS = 0



BEGIN

SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD;'

EXEC sp_executesql @cmd

SET @Statement = 'UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN'

EXEC sp_executesql @Statement

FETCH NEXT FROM TableCursor INTO @Table

END

CLOSE TableCursor

DEALLOCATE TableCursor





For SQL Express you would need to allow remote connections, enable the TCP/IP, Named pipes protocols in the sql configuration tool and start the sql browser.







Author: Kim Truelsen, MS Escalation Engineer

Date: 10/1- 2014






Источник: http://blogs.msdn.com/b/axsupport/ar...-database.aspx