AX for Retail POS database size caveat

When deploying the AX for Retail POS solution, there should be a SQL DB installed at the store location. SQL Express can be installed to meet this requirement without adding to the cost of deployment for licensing (since its free!). For the most part, there won't be an issue with this. However, you need to know the issues you'll encounter in a solution so you can anticipate them.

For larger AX implementations, data growth can be an issue at the store DB as retail rollouts or data grows the size of the store side DB.  SQL Express 2012 has a DB limit of 10 GB as of 6/29/2014. 

There are a few options to address this each with their own pros and cons. One way to correct the issue is to buy a license for all SQL Server instances at all of the stores. It's a good solution if you're Scrooge McDuck or Mr Monopoly and don't care about costs. Another option is to limit what stores see what data (e.g. by region). Understand this has a con as well in that not all stores will have visibility to all of the other stores data. Yet another option is to customize the deployments to look at two different databases for the data. This is really tricky and I don't think justifies the cost compared to other alternatives. But its an option! There are other options like a central SQL box and virtualizing the stores DBs referencing that one SQL DB but then you'll still have other concerns like IO, network speeds, etc.

This blog post won't discuss a solution as there are many with each one fitting certain situations. There are even more possibilities out there I haven't even thought about. But hopefully this post will help some people start to think about the limitations proactively and start to brainstorm on their own.

-------------------------------------------------------------------------------------------
Here are some more technical details around limitations for those interested:

1. Constrained to a single CPU (in 2012, this limitation has been changed to "The lesser of one socket or four cores", so multi-threading is possible
2. 1 GB RAM (Same in 2008/2012)
3. 4 GB database size (raised to 10GB in SQL 2008 R2 and SQL 2012)

The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server

However, as mentioned in the comments and above, the database size limit was raised to 10GB in 2008 R2 and 2012. Also, this 10GB limit only applies to relational data, and Filestream data does not count towards this limit (http://msdn.microsoft.com/en-us/library/bb895334.aspx).

How To: Remove Collaboration Workspaces created from AX 2012 Enterprise Portal (EP)

AX has the ability to create collaboration workspaces for various records in AX (e.g. project, opportunity) automatically from within records in AX and have them available on a SharePoint workspace site.

The workspaces will show up in the workplace home page setup location in Figure 2 below using the template also in that configuration. You can get to the settings by going to System Administration > Setup > Enterprise Portal > ‘Collaboration workspace settings’.

That’s great that they’re there and they’re very useful, but once those workspaces are no longer needed, how can we get rid of them?

 

Figure 1 – The SharePoint AX EP site with collaboration workspaces added

Figure 2 – Legal entity settings for collaboration workspace system generation

While there is no ‘correct’ way to do this, keep in mind that the idea of this is to make AX managed SharePoint subsites for collaboration. That being said, the sites used were intended to be created, linked, and deleted from within AX. It doesn’t mean that they have to be.

To SharePoint administrators, they might go into SharePoint designer, open the link where the collaboration site is (Figure 3), right click on the site, and select ‘Delete’ (Figure 4). This works but it will create an inconsistency with data between AX and what actually exists. This will yield an HTTP 404 Not Found error when users attempt to get to the specific information that doesn’t exist (Figure 5).

The above would work but you SHOULD go into and update the individual records’ information that refer to this URL. That way, it the system won’t indicate that a site doesn’t exist.

Alternately, the site can be either deleted or ‘unlinked from within AX. Unlinking is nice as it will keep the subsite available for repurposes or archival reasons. For archives though, I’d still keep it… Removing the workspaces is as easy as the below. The bullet points I made should explain how it all ties together.

Navigate to Sales and Marketing > Common > Opportunities > All Opportunities, select the opportunity with the collaboration workspace. On the ‘General’ tab in the ribbon, click the drop down for ‘Collaboration workspaces’. There are four options (Figure 6):

·         Create collaboration workspace

o   Creates a collab workspace for the record.

o   This is a ground up workspace using the template specified in the

 

·         Link collaboration workspace

o   Allows a user to link an existing collab workspace to that AX record

 

·         Delete collaboration workspace

o   Deletes the entire collaboration workspace from SharePoint

o   The site will not show up in the EP list in Figure 1

o   If another record in AX is also using that collab workspace and the user attempts to delete the workspace, AX will only unlink the collab workspace from that record, not delete it

o   The Internal/External URL fields will become blank

 

·         Unlink collaboration workspace

o   Does not delete the collab workspace from SharePoint but removes the association with that specific record

o   The site will still show up in the EP list in Figure 1

o   If the workspace is linked to other records in AX, the association will still exist for those other records

o   The Internal/External URL fields will become blank

Some extra side notes:

Each object, in this case an opportunity can only have one collaboration workspace URL associated with it: an internal and external.

As hinted above in the bullet points, multiple entities in the system can be linked to a single collaboration workspace.

Figure 3 – Opening the workspace where the collaboration workspace is located

Figure 4 – Deleting the SharePoint Collaboration Workspace subsite in SharePoint Designer

Figure 5 – The data in AX will still show the collaboration workspace but the site does not exist (HTTP 404 Not Found error)

Figure 6 – Opportunities record in AX with the options for collaboration workspaces