Setup und Configure Office 365 Project Web App
27. December 2017 Leave a comment
Here you can find my video on how to setup Project Web App, link with Project Desktop client and how to setup a SharePoint subwebsite with a synchronized task list
Inplace Upgrade of Windows Server 2012 HyperV to 2016
20. August 2017 Leave a comment
There is no upgrade path for Windows Server 2012 (non-R2) HyperV role to Windows Server 2016 HyperV. The easiest way would be to setup a new Windows Server 2016 and activate HyperV. However, there may be reasons to perform an inplace upgrade. This is an experience report doing so
Prepare for the Upgrade
First make sure there are no virtual machine related files left on C: drive. Move the configuration files and especially the virtual hard disk files to another drive. I recommend to make list where the VHDX files are place. This can easily be done via PowerShell
Get-VM –VMName * | Select-Object VMid | Get-VHD | select Path | ft
Next uninstall the HyperV role using the Server Manager. Simply select remove roles and features and select the HyperV role. This step requires a reboot and your server will come up a simple plain windows 2012 server.
After HyperV was uninstalled make sure to remove NIC teaming in case you are using this feature. I recommend to deactivate all but one network adapter. This can also be done using the Server Manager.
Perform Inplace Upgrade
Insert a disk or mount the Windows Server 2016 image. Start the upgrade process. When asked choose to keep all your data and apps. This will preserve your data and applications e.g. the RAID Manager software. The setup wizard warns you that an inplace upgrade is not the preferred way to setup server 2016. Accept and proceed. The upgrade will take a while and requires some reboots.
Setup HyperV on Server 2016
After the upgrade process has finished, its time to setup HyperV again. First configure the NIC teaming again. Activating the HyperV role in Server 2016 is almost the same as in Server 2012. This can be done by using the Server Manager installing new roles and features. Activating HyperV will require a reboot. After the reboot configure a virtual switch so your VMs can access the network again.
Import the Virtual Machines again
By default HyperV on Server 2012 has no clue about the VMs in former Server 2012. The virtual machines have to be imported manually. This can be done using the HyperV console. First, provide the folder where the virtual machine configuration is placed. Afterwards choose to directly register the VM.
Next provide the folder where the virtual hard disk files are placed. Don’t get confused because you don’t see the actual VHDX files in the selection dialog. The import wizard will check if the virtual hard disk named in the configuration file can be found in this folder. If the wizard can’t find the virtual hard disk file, take a look a the list of vhdx file paths generated by the PowerShell script.
When all the VMs are imported the upgrade is almost finished. The the network connection of the virtual machines and make sure they can access the network via the newly created virtual switch. Removing the virtual network adapter and adding the virtual network adapter again can help 
Perform some tests, maybe reboot the server again and install the latest updates.
Performance Optimization by using Included Columns and Field Selects
25. April 2017 Leave a comment
Since version 2012, Dynamics AX supports included columns in indices although SQL Server supports it for quite a long time. Here are some examples how and why it is good practice to use included columns in an index. I’m using Dynamics AX 2012 R3 Cu12 on Windows Server 2016 and SQL Server 2016 with Contoso Demo data for this example
Cluster Index.
The cluster index can be defined using multiple fields and is used to defined the order of records stored in the table. Even more important is the fact, that if a table has a clustered index all the data is stored in the table, i.e. the cluster index IS the table!
Take a look at the space allocated by the indices. About 219 MB are used to store actual data and 167 MB are used to store index information
The following SQL Statement reveals the size in detail
SELECT
ind.name,
SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM
sys.indexes ind
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
INNER JOIN
sys.dm_db_partition_stats AS s ON s.[object_id] = ind.[object_id]
AND s.[index_id] = ind.[index_id]
WHERE
t.name = ‘INVENTTRANS’
GROUP BY ind.name
order by IndexSizeKB desc
The table data is stored in the TransOriginIdx
| name | IndexSizeKB |
| I_177TRANSORIGINIDX | 226992 ~ 221 MB |
| I_177OPENITEMIDX | 63720 |
| I_177STATUSITEMIDX | 34312 |
| I_177ITEMIDX | 24872 |
| I_177RECID | 23416 |
| I_177DIMIDIDX | 22192 |
Index Usage with Field Select
Here is an example of a select statement with field select on the InventTrans table
while select ItemId,DatePhysical
from inventTrans
where
InventTrans.ItemId == ‘0001’ &&
inventTrans.DatePhysical >= str2Date(‘1.1.2011’,123){ .. }
The trace parser reveals the actual SQL Statement sent to the database
What happens is what you would expect, SQL uses the ItemIdx for this query
Only 5 logical reads where necessary
Select Non-Index fields
When the query selects fields which are not part of the index, SQL server has to perform a lookup in the Cluster Index for each record identified by the ItemIdx to get all the other fields. For example the Voucher and Qty are not part of the ItemIdx.
213 logical reads were necessary to fetch the data
This can get even worse, when performing the lookup becomes to expensive. This can happen when the query returns a larger number of records. For example, when querying for another ItemId. In this example SQL server does not use the ItemIdx anymore, but performs a search in the clustered index instead. The ItemIdx became completely useless for this query.
SQL server required 1345 logical reads to fetch the data!
Included Columns
Since version 2012 Dynamics AX supports the definition of Included Columns for indices. These columns are not used to sort the index. These are just fields which are stored within the index to avoid costly lookups in the clustered index. In Dynamics AX you just add columns to the index and set the property IncludedColumn to Yes.
You can find the included columns in SQL server when viewing the properties of the index
When the statement from above is executed again, SQL server can use the included columns from the index and does not perform costly lookups in the clustered index.
Only 6 logical reads are required to fetch the data. This is a huge optimization compared to the 1345 reads without included columns.
SQL Server 2016 SP1 and Dynamics AX 2012 R3
7. February 2017 7 Comments
Here are some ideas on SQL Server 2016 SP1 and Dynamics AX 2012 R3
Enterprise Features in Standard Edition since Service Pack 1
There was a major change in Service Pack 1 for SQL Server 2016. While most cool features were Enterprise-Edition-Only for a very long time, many features like Column Store Index and Compression are now available for Standard Edition too. Have a detailed look at this Blog. SQL 2016 also introduces new features like the Query Store and Power BI Integration with Reporting Services
Reporting Services
SQL Server 2016 Reporting Services require Dynamics AX R3 CU12 and an additional KB3184496 hotfix. Otherwise the installation will fail. The typical AX user won’t see the difference between SSRS 2016 and older versions. However, there are some features that might be interesting for us AX folks too, namely Power BI Integration.
Right now (January 2017) Power BI Integration is not so useful. You can place your Power BI files at the SSRS, which is actually only a better alternative to place the .PBIX file on a file share. However, it is said SSRS will be able not only to store but also to render Power BI files On Premises. This might be interesting for customers who are not willing to use Power BI in the cloud.
Right now in SSRS 2016 SP1 you can pin SSRS reports to your Power BI (Online) dashboard. This means, you can integrate your SSRS reports in Power BI. This might not sound very useful for Dynamics AX users. Why should I pin an invoice to a Power BI dashboard? But if a customer is already using SSRS for reporting, this might be a good option to start with Power BI and reuse the existing reports. Some Dynamics AX reports with OLAP data source can also be pinned to the Dashboard.
There is a Power BI Button in the SSRS report portal
This will pin your report to one of your Power BI (Online) dashboards
Query Store
This is a very useful feature. All of us are familiar with performance problems reported by some users. The problem is to identify and reproduce the query which performed badly and find the reason. Query Store can be used to store information about such problem-queries, like the SQL statement executed, the used execution plan, etc. In SQL Server Management Studio you can view reports based on execution time, logical and physical write/reads, memory usage, etc.Query Store therefore is a very useful feature in SQL 2016 to identify performance issues.
Column Store Index
Column Store Indices were introduced in SQL Server 2012 too speed up aggregation queries (e.g. sum). However, CSI hat a lot of limitations and was an Enterprise Edition features till 2016 (non SP). In SQL 2016 SP1 we can now use CSI in combination with Dynamics AX at our customers who have licensed Standard Edition of SQL Server.
In contrast to traditional Row Store Indices where records stored in 8 KB pages (e.g. CustInvoiceJour records), CSI store column values (e.g. LineAmountMST) together in 8 KB pages. Therefore aggregation functions can perform faster because less pages have to be read.
Here is an example:
select CustGroup, year(InvoiceDate) as YR, sum(LineAmountMST) as Amount
from CustInvoiceJour
group by CustGroup, year(InvoiceDate)
When executing this query against a Dynamics AX Contoso Demo database, 2158 logical reads were required.
Next, create a non-clustered Column Store Index on the fields CustGroup, InvoiceDate and InvoiceAmountMST which are used in the query
The same query now utilizes the Column Store Index to fetch and aggregate the data. The IO statistics show that less reads were required to get the result. The query performs faster than with the traditional Row-Store index.
Be aware that Dynamics AX removes the Column Store Index from the database when you synchronize the data dictionary. This might not be such an issues in a production environment. When you deploy a new application version from Test to Live, make sure to recreate all lost CSI.
Stretch Database
With stretch database you can migrate cold data (aka. existing but hardly not used) from your on premises expensive high performance storage to the cloud. This means you can split the data in large table and move old records in SQL azure. The application doesn’t recognize this split. Only if you query cold data, it will take longer to fetch the result. This sounds good. however there are some very crucial show stoppers.
- You can’t UPDATE or DELETE rows that have been migrated, or rows that are eligible for migration, in a Stretch-enabled table or in a view that includes Stretch-enabled tables.
- You can’t INSERT rows into a Stretch-enabled table on a linked server.
So right now, this feature is not useful for Dynamics AX on premises installation
SQL Backup Restore fails due to insufficient free space
26. May 2016 Leave a comment
A customer recently tried to restore a Dynamics AX database backup from the Live system to the Testing environment. The SQL Server data disk had 17 GB space left, and the size of database backup (.bak) file was about 11 GB.
However, SQL Server refused to restore the database because of insufficient free space.
The reason was the file layout in the original Database. Typically, the database files and log files pre-allocate space to avoid costly file operations when the content of the database grows. In this case, the database file had 20 GB space and the log although the content of the database was only about 11 GB.
When a backup is created, SQL only backups the content of the files and adds additional information about the file layout. When the database is restored, the database files will be allocated like in the source database. Therefore 21 GB were needed but not available on disk.
The solution was to increase the storage on the Test system, restore the database and afterwards shrink the database file.
Using Azure Automation to copy Live Transaction DB to Test
30. January 2016 Leave a comment
Updating the Test system with actual transactional data from the Live system is a common task. This can be accomplished using scripts. However, in a hybrid IT environment you might want to organize, manage and monitor all your on-premises and cloud based scripts at a central place. Azure Automation is the platform to do this.
Prerequisites
- Dynamics AX Live and Test installation on Windows Server 2012 R2
- Azure Account (all services can be used for free in basic mode)
Azure Automation
In Azure Portal create a new instance of Azure Automation. When the instance was created, use the key to display the keys and URL. You’ll need this to connect your on-premises server with Azure Automation.
Operational Insights
Azure Automation can be instantly used to script your cloud-based datacenter. However, to manage the on-premises environment you have to connect your local systems with the cloud. This is done via Operational Insights.
- In Azure Portal create a new instance of Operational Insights
- Logon to Operational Insights Portal and start the “Get Started” checklist
- Add the Automation Solutions
- Connect the Dynamics AX server by downloading and installing the Agent for Windows
At your on-premises server open PowerShell in admin mode and go to the agents installation directory e.g.
cd "C:\Program Files\Microsoft Monitoring Agent\Agent\AzureAutomation\<version>\HybridRegistration"
Import the Hybrid Registration module and register the server in Azure Automation.
Import-Module HybridRegistration.psd1
Add-HybridRunbookWorker –Name <String> -EndPoint <Url> -Token <String>
In this example I’ve used the Name parameter value “Dynamics” at the Add-HybridRunbookWorker Cmdlet. This creates a runbook worker group called “Dynamics” with one assigned on-premises server.
On-Premises configuration
Create a folder where to backup the Dynamics AX database. In this example I’m using a directory on the local system drive C:\AxTemp (which of course is not best practice). By default the Ops. Insights Agent runs as Local System. Make sure to give the account “NT Authority\System” appropriate rights in your SQL Server installation to access the Live DB and Test DB.
Runbook
In Azure Portal, go to Azure Automation > Runbooks > Create a new runbook > Choose runbook type “Powershell”. Add the following code to your runbook.
echo "Stopping Services"
Stop-Service -Name AOS60`$01
Stop-Service -Name AOS60`$02
echo "Creating Backup from Live"
Invoke-Sqlcmd -Query "backup database [Live] to Disk = N’C:\AxTemp\Live.bak’ with copy_only" -QueryTimeout 0
echo "Restoring Backup to Test"
Invoke-SqlCmd -Query "restore database [Test] from DISK = N’C:\AxTemp\Live.bak’ with file = 1, move N’R3Demo1‘ TO N’C:\Data\Test.mdf’, MOVE N’R3Demo1_log‘ TO N’C:\Data\Test_log.ldf’" -QueryTimeout 0
echo "Cleanup Backup"
Remove-Item -Path "C:\AxTemp\Live.bak"
echo "Starting Services"
Start-Service -Name AOS60`$01
Start-Service -Name AOS60`$02
This will stop both AOS instances. Make sure the AOS service name fits your environment. The service name can be found in the services mmc. For example my Live AOS is named AOS60$01. Make sure to use the ` to escape the $ sign in the AOS name.
The script will then create a backup from database “Live” to C:\AxTemp\Live.bak. If your live DB has another name, change it to fit your name.
Next the script will restore the backup to database “Test”. In my case the logical name of the database file is called “R3Demo1” and it’s log is called “R3Demo1_log”. Make sure this fits your installation. You can find the names in SQL Server management studio, by checking the database file properties.
Finally the script removes the backup file and restarts both AOS
Runbook Execution
In Azure Automation Runbook editor, save the actual runbook code and click publish.
At the Azure Automation main page go to runbooks, select your newly created runbook and press the start button. On the next page select execution using a Hybrid Worker and select your worker group. In my example it’s called Dynamics (see PowerShell screenshot)
This will submit your runbook to the on-premises server and execute it. You can check the execution by monitoring the C:\AxTemp directory where the backup will be placed. Don’t worry about the warnings when the workbook finishes. Starting the AOS’ takes a while and results in the typical message “service is not responding”.
More
- Azure Automation supports timing of runbooks, so you can create a batch in the cloud to copy your data on-premises
- This example uses a single server installation. However, the script can easily be modified to run on different servers
- Using the Invoke-SqlCmd Cmdlet you can do all the cleanup work like changing the reporting server instance etc.
Publish Dynamics Ax documents to SharePoint
12. November 2015 Leave a comment
A typical requirement is to publish documents like Invoice, Purchase Order, etc. to an DMS system like SharePoint. However, in Dynamics AX 2012 and lower there is no built-in functionality to publish these documents to SharePoint. But this requirement can easily be achieved without coding.
Prerequisite
- Install (at least) SharePoint 2013 Foundation incl. SP1. Foundation 2013 SP1 includes all the filters required to process PDF files and a basic search center. By default the configuration wizard creates a new team website collection.
- Make sure the WebClient window service is installed and running. If not, you may have to install the User Interface and Desktop Experience Feature on your server.
Document Library and Content Type
In SharePoint open the Settings 
and open the Site Settings. From the User and Permissions group, open “People and Groups”. Select the Group “Owners”, Click “New” and add the Dynamics AX AOS Service account.
In SharePoint create a new Document Library. On the team website open Settings 
(right, on top) > Add an app > Name the document library “Invoice”.
Go to the newly created document library. Open the Library ribbon, and edit library with SharePoint Designer. SharePoint Designer is not installed by default, and you may have to download and install it first.
After SharePoint Designer was installed, click again on “Edit Library” with SharePoint Designer. Within SharePoint designer, select Content Types and create a new Content Type. Call it “Invoice” and make sure to select the parent content type form “Document Content Types” and parent content type “Document”. Save your modifications.
In the navigation, select Lists and Libraries and open the Invoices library. Make sure the option “Allow management of content types” is enabled. At the “Content Types” group at the bottom, open “Add” and select your newly created Invoice content type. This will add the content type to the document library.
Select the Invoice content type at the content types group. At the ribbon select “Set as default”. Now every new document uploaded to the library will be an Invoice type. Save your modifications and go back go SharePoint.
At the Invoices document library in SharePoint, open the Library Ribbon and choose “Open in Explorer”. This will open a new instance of windows explorer, showing the content of the document library.
Click in the address bar and copy the address.
Post and Publish Sales Invoice
Open Dynamics AX 2012 and go to “Accounts Receivable” > Periodic > Sales Update > Invoice. Change the Quantity to Packing Slip. Make sure the options “Posting”, “Late Selection” and “Post Invoice” are enabled.
Open “Select” and make sure the selection is configured to identify all Delivered sales orders. Click OK and save the selection.
Open Printer Setup > Invoice. Select File as Target and choose File Format PDF. Open the File Name Dialog and provide the Document Library Address as Target. Repeat this step for the Invoice (Copy).
Back at the Posting Invoice Dialog, open Batch processing (at the bottom). Enable batch processing and modify the recurrence to “No End” and repeat all 5 minutes.
Test
In Dynamics AX create one or more new sales order and post the packing slip. Wait until the Post Invoice batch job runs. The sales orders should be invoiced. Go to SharePoint and open the Invoices document library. There you should see the uploaded PDFs.
Open the SharePoint Central Administration and go to Application > Manage service application > Search Service Application.
Select Content Sources and start the crawl manually. This will force SharePoint to index the PDF files in the Invoices document library. By default the crawler indices the documents on SharePoint periodically without manual interaction.
Open SharePoint Team Website. In the Search Box, type the Name of Customer. SharePoint will present a list of found documents related to the customer.
(Optional) Confirm and Publish Purchase Orders to SharePoint
Create a new document library with an purchase order content type as default:
Go to SharePoint, open the settings 
menu and choose to add an app. Select document library and name it Purchases. Navigate to the new document library, and from the Library Ribbon, select “Open with SharePoint Designer”. In SharePoint designer create a new content type, based on a document content type, and name it Purchase. In SharePoint Designer navigate to the libraries, select the newly created Purchases library and make sure the option “Allow Management of Content Types” is enabled. Below, at the content types group add the Purchase content type you’ve created. Finally select the content type, and from the ribbon in SharePoint designer select “Set as Default”.
In Dynamics AX go to the Procurement and Sourcing module > Settings > Procurement and Sourcing Parameters > and activate Change Management. This option is required to run the purchase order approval workflow.
Next go to Procurement and Sourcing > Settings > Procurement and Sourcing Workflows. Create a new workflow based on the Purchase Order Workflow template. In the Workflow Configuration, add the Approve Purchase Order task to the workflow, between start and end.
Provide meaningful texts for the Workflow Submission instruction. Double click on the Approve Purchase Order taks to open the details. There assign Step 1 to a user or a user group. For testing purpose, you may assign the approval to yourself. Save and activate the workflow configuration.
Create a new purchase order. The purchase order starts in the approval state Draft. Submit it to the workflow for approval. This will change the state to “In Review”. Go back to the Purchase Order List Page and refresh the data. It may take a few minutes, depending on your workflow batch jobs configuration, until the purchase order is assigned to you for approval. When the purchase order is assigned for approval, approve the purchase and wait a few minutes until the purchase order has reached the state approved.
Go to Procurement and Sourcing > Periodic > Purchase orders > Confirmation. Make Sure the Options Posting, Late Selection and Print purchase orders are active. From the Select button on the right, make sure only Approved and Open purchase orders will be selected.
Open the Printer Setup > Purchase Order > Choose print to file as PDF and provide the path to the Purchases document library on your SharePoint. Repeat this for Printer Setup > Purchase Order (copy).
Finally, open the Batch dialog using the button on the lower right. Make sure to activate batch processing and set the recurrence to no end and recurring pattern to 10 minutes.
After a few minutes the purchase order state has change from Approved to Confirmed, and you should see the generated PDFs on SharePoint. These documents will be included within the next search crawl and can be found using the search box at your SharePoint website.
(Optional) Enhance Search Experience
Open SharePoint Central Administration and click the group Application Management. Go to “Create Site Collection” . Make sure your are using the same Web Application as the Team Website. Call the new site collection “Search” and provide an URL including like http://YOUR_SERVER/site/search. Make sure to select the “Basic Search Center” template from the Enterprise template group. Click OK, this will create a new basic search center.
Open the URL you have provided in a web browser. You should see an almost empty web page with a search box.
In SharePoint Central Administration go to “Application Management” > “Manage Service Applications” > “Search Service Application”. Provide the Search Center URL at the property “Global Search Center URL”. This will instruct SharePoint to redirect all search to the Search Center.
Open the Search Center URL in a browser and start a search e.g. for a Customer. This will open the result page. On the top right open the settings 
and click on “Edit page”. This will switch the page to edit mode. On the left side, click the drop down of the refiner web part and click “Edit Web Part”. This will open the Edit Dialog on the right.
Click on “Choose Refiners” and make sure SPContentType, File Type and Last Modified Time are selected. Remove all the other refiners from the select. Click OK in the dialog, Apply in the Web Part Configuration Dialog on the right and Stop Edition in the ribbon on the top left.
When you use the search center the next time, the result page will allow you to refine by Content Type like Invoice, Purchase Order, etc., File Type like PDF, Word, etc., and Date.
SQL Server Transaction Log Shipping does not restore .TRN files
15. April 2015 Leave a comment
Recently a customer was concerned to implement a fail over strategy for the Dynamics AX database. SQL Server 2008 R2 Standard Edition was in place. The underlying hardware was not capable to handle database mirroring. Therefore, log shipping and manual failover was chosen. However, the job on the second server refused to restore the .trn files. We managed to reproduced the problem in our lab environment with two SQL Instances (SQL1, SQL2) and a Northwind Demo Database.
Log Shipping Configuration
There are 2 SQL Servers in place, called SQL1 (primary) hosting a Northwind Database and SQL2 (traget). There are two file shares on the secondary server. LSBackup is used to backup .trn files from the primary server. LSCopy is used by the secondary server to copy .trn files and restore these files to the replicated database.
The backup at the primary server SQL1 was scheduled to run every 5 minutes. Files older than 1 hour in the LSBackup folder will be deleted.
The target database at the secondary server SQL2 was initialized by the configuration wizard
The copy job at the secondary server SQL2 was scheduled to run every 5 minutes. This job copies .trn files from the LSBackup folder into the LSCopy folder. Files older than 1 hour in the LSCopy folder will be deleted.
The restore job at the secondary server SQL2 was scheduled to run every 5 minutes.
Log Shipping Behavior
The log shipping configuration seemed to be correct. The backup job on the primary server SQL1 placed the backup and .trn log backup files in the LSBackup folder. The copy job on the secondary server copied the .trn files to the LSCopy folder.
An initial check of the restore job history showed no errors.
The Problem
A detailed look at the job revealed that the .trn files were not restored. The backup job reported “Skipped log backup file” because it “Could not find a log backup file that could be applied to the secondary database”"
The Reason
A maintenance plan was in place on the primary server SQL1. This maintenance plan took a full backup at midnight and a transaction log backup every 15 minutes. The interleaving transaction log backups resulted in holes within the transaction log chain. Therefore the restore job on SQL2 was not able to apply the copied transaction log files.
The Solution
At the Northwind DB properties on primary server SQL1, the transaction log replication setting to SQL2 was removed. The wizard also removed the copy and restore jobs at SQL2. The replicated Northwind DB at SQL2 was removed manually. The transaction log shipping configuration was deactivated at the Northwind DB on SQL1. All files in the LSBackup and LSRestore folders were deleted. The maintenance plan was deactivated.
Next the transaction log shipping configuration was redone, as described above. Again the target database at SQL2 was initialized by a full backup of the Northwind DB from SQL1. Finally the secondary server SQL2 was able to restore the .trn files.
Data Upgrade Problem: Failed to create session
2. December 2014 Leave a comment
I was recently upgrading an application to Dynamics AX 2012 R3 Cumulative Update 8. However, the data upgrade scripts didn’t load properly. AX presented an error “Failed to create session” and there were no batch jobs scheduled.
There ware 3 partitions in this installation
- initial Initial Partition
- ps Public Sector
- ext Extended
A detailed look in the dbo.UserInfo table revealed that the User Admin was only linked to the initial partition. However, the user requires access to all three partitions in order to create update scripts for these partitions.
The solution was to manually add 2 additional rows to the dbo.UserInfo table for the User Admin and link each row to one of the missing partitions. You may alter 2 existing users in order to get a valid RECID, or just increment the last RecID by 1 and delete the records after the upgrade scripts are finished.
Smoked! Consumer SSD in 24/7 Server environment
16. October 2014 Leave a comment
When a customer asks for storage systems, SSDs are a frequent option to boost performance. However, you’ll definitely face the question why enterprise SSDs are so expensive. Especially in an SMB, where you have to discuss each Cent, the question arises if it would be possible to use cheap consumer SSDs instead. Most storage systems use SAS drives while consumer SSDs have SATA ports. Depending on the controller you might not even be able to include a SATA SSD in your storage system.
However, I had such a situation where a SSD was used as direct attached storage within an IBM x3300 M4 server system. SQL Server was native installed on the machine and an Ocz Agility 3 SSD was used for tempdb only. After 22167 hours of usage (923 days), the first signs of problems showed up. Tempdb files were corrupt and the OS reported I/O errors accessing the files.
Conclusion
Using SSD in a storage system or direct attached storage in a server will significantly improve the system performance. However, don’t use consumer SSD in your system. They will probably fail within 2 – 3 years and you don’t get support for 24/7 environment. Enterprise SSDs a more expensive but more durable. Depending on your support contract you get spare parts up to 5 years.
Markus Nöbauer
