Dynamics GP, SQL 2008, Windows 2008 and 64bit

Wow, it’s been over a month since my last post.  I guess between a week and a bit of unplugged (from work at least) vacation and the resulting crush of undone work afterword got me out of the swing of things.

Due to the upcoming end of support for GP version 8, we’re in the midst of a lot of version 10 upgrades.  For those clients still on version 8, they’re often on older hardware, OS and SQL versions and so are often moving to new hardware and are looking for OS and SQL Server guidance.  Should they go to SQL 2008?  What are the advantages of 64bit? 

These questions have come from our clients and functional consultants (the people may not be able to write a recursive common table expression but actually know what deferred revenue is).  Rather than simply continue to answer queries from clients and consultants on a one-off basis I sent the following in an email to our consultants and thought I might share it.

Why 64bit?

32bit Windows can only access up to 4GB or RAM.  In addition, 2GB is reserved for the OS with only 2GB remaining for applications.  This means that at  a base level, 32bit SQL Server can access at most, 2GB of RAM.  There are some system gymnastics that will allow you to access up to 3GB on Windows Server Standard or 7GB on Windows Server Enterprise but due to the required gymnastics these are not ideal.  In addition, due to the fact that a 64bit OS processes data in 64bit rather than 32bit chunks, it can process more data faster.  No gymnastics are required.

The 64bit version of Windows Server Standard(2003 or 2008) allows you to address up to 32GB of physical RAM.  The 64bit version of Windows Server Enterprise allows you to address up to 1TB of physical RAM.

So how does this help with SQL Server?

The more of a database that SQL Server can load into memory, the faster its performance will be.  If it can cache an entire database into memory then the only time it has to access the disk is for writes.  All reads are done from memory and thus considerably faster than reading from disk.  In an ideal world you would want to have enough free memory to load all of your commonly used databases.  So if you have 4 3GB databases, you would ideally want to have 12GB or RAM plus the RAM required for the OS and any other applications.  In addition to being able to address this amount, a 64bit SQL Server will have improved performance due to the 64bit data processing itself.  In fact, there should be performance improvements even when running 64bit SQL on a server with only 4GB or RAM.

How does 64bit SQL Server affect my Dynamics GP implementation?

64bit SQL Server itself should not present any issues with a Dynamics GP implementation.  GP will see 64bit or 32bit SQL as the same.  However, due to the fact that SQL Server 64bit must be installed on a 64bit OS there are issues with regards to installing the Dynamics GP client on the SQL Server.  Some components, most notably the FRx client are not supported on 64bit Oss, even though they support connecting to a 64bit SQL Server.

So what about the Dynamics GP client and components?

In the past, we’ve generally installed a “server” install of the Dynamics GP client on the SQL Server.  This includes the Dyndata file share that is used by the workstation clients.  Unlike earlier versions Dynamics GP, there is no longer a separate “server” install of the client.  All clients are equal and all “server” functions can be performed from any client workstation with little to no adverse affects on performance. 

As a best practice, it is always advisable to avoid installing additional applications on the SQL Server for the following reasons:

• To maximize the amount of resources  (CPU, RAM and disk) available for SQL itself.
• To reduce or eliminate the need to remote desktop directly into the SQL server for security reasons.  This also reduces the possibility of inadvertent reboots or system changes of a production SQL server.
• To reduce the possibility of additional applications or services interfering with SQL or other services and causing instability issues on the SQL Server.
• Avoiding issues with clustering if the SQL server currently is or is planned to by clustered.
• Avoiding issues with installing the client on a 64bit OS.
• To allow the SQL hardware to be upgraded/replaced without affecting the Dynamics GP install (except for possibly the client ODBC connections)

So where should I install the Dynamics GP client?

It is recommended that a “server” or “administrative” copy of the Dynamics GP client be installed on a dedicated application server.  This application server should be a 32bit OS.  This is where the Dyndata folder will reside as well as additional components such as eConnect, Web Services, RDM etc.  In addition, it is recommended that a copy of the client’s current version of Microsoft Office (at least Excel) also be installed on this server for the purposes of troubleshooting GP/Office functionality.  Also, a copy of the SQL Server Client Tools (SQL Server Management Studio and SQL Server Business Intelligence Studio) should be installed on this server so that most, if not all, SQL administrative functions can be performed without remote desktopping into the SQL server.

As noted, this should ideally be a dedicated server so that the Dynamics GP install and the various components do not adversely affect or are not adversely affected by other applications installed on the server.  Since many of our clients have implemented virtual environments, and this is a perfect candidate for a virtual server (despite Microsoft’s statements to the contrary), it should not be an additional burden to create and support this environment.

When is it appropriate to recommend a single server environment?

If the client has a small number of users (<=10) and it is not expected that the total size of all of the active databases (Dynamics or other applications) will exceed 4GB over the life of the installation and the client is not in a virtualized environment and so would require additional hardware and OS licenses for an application server then it would be appropriate to recommend a single server environment.  This environment would be required to be 32bit OS and 32bit SQL Server.

Is a two server environment the most I’ll need?

No.  If you have a particularly high transaction environment on your SQL server, such as high transaction Dynamics environment or other large, high transaction databases other than Dynamics GP, then it would be appropriate to split off other components of SQL Server, such as Reporting Services and Analysis Services to their own dedicated servers.    Note that in a clustered SQL Environment, Reporting Services and Analysis Services would be required to be put on servers other than the SQL Server.  If this were the case then the Enterprise version of SQL Server would be required.

What about SQL Server 2008?

Now that Microsoft has released Service Pack 1 for SQL Server 2008, more clients may be looking to deploy it to take advantage of some of its new features.  Most of the commonly used components are Dynamics GP, including SRS reports, are supported on SQL Server 2008 when using SP2 or SP3 of Dynamics GP or the particular GP component.  FRx is currently not supported on SQL Server 2008, however, it has been installed and is working correctly on SQL Server 2008 in the Caturano Dynamics GP environment.  Microsoft is currently evaluating FRx’s compatibility with SQL Server 2008 and expects to support it in FRx 6.7 SP 11, due out in July, 2009.

When might I recommend SQL Server 2008?

SQL Server 2008 has a number of new features that some clients my find compelling including:

• A (reported) 10-15% performance improvement over SQL Server 2005.
• Transparent Data Encryption that encrypts the entire database, log and backup files.  This feature might be of interest to clients that store credit card numbers, 1099 vendor SSNs, employee payroll information etc. and may be subject to Payment Card Industry Data Security Standards (PCI DSS) or the Mass Privacy regulations going into affect 1/1/2010.
• Built in data (including data viewing) and configuration auditing for clients that might face compliance issues (PCI, SOX, HIPPA, Mass Privacy)
• Completely rewritten SQL Server Reporting Services with improved performance (particularly multi-user performance) and new more user-friendly report builder for report authoring.

What about Windows Server 2008?

Like SQL Server 2008, Windows Server 2008 has passed the initial service pack test (it’s currently on SP2) and so many clients may be migrating to it or at least setting up new hardware/VMs with it.    Like SQL Server 2008, some components, most notably FRx are not supported on Windows Server 2008.

Can you give me a quick reference on component support?

Sure.  The following components are supported on 32bit only.  Thus if you use any of these components, they must be installed on a 32bit OS, ideally an app server:

• Rapid Implementation Tools (including Rapid Configuration and Rapid Migration)
• Business Portal for Microsoft Dynamics GP 10.0, Internet Information Services server
• Catalog Maintenance
• Microsoft Dynamics Security Synchronization Utility
• Business Portal Migration Utility
• Analysis Cubes for SQL Server 2000 (client and server).  Note that this is for SQL Server 2000.  The Analysis Cubes for SQL Server 2005 are supported on 64bit.
• Benefit Self Service Suite and Certification, Licensing, and Training Manager for Business Portal
• Personal Data Keeper
• Workflow
• Workflow Software Development Kit
• Workflow History Archiving Utility
• Microsoft FRx Reporter 6.7
• Microsoft FRx Forecaster 6.7
• Microsoft FRx Web Port 6.7
• Enterprise Reporting 7.5
• SmartTags
• eExpense
• Field Service Anywhere
• Microsoft Dynamics Solution Accelerator for Compliance Management

The following components are not supported on Windows Server 2008 (Assume SP3 for all supported components).  Thus if you use any of these components, the app server must be running Windows 2003:

• Rapid Implementation Tools (including Rapid Configuration and Rapid Migration)
• Analysis Cubes for SQL Server 2000 (client and server).  Note that this is for SQL Server 2000.  The Analysis Cubes for SQL Server 2005 are supported on 64bit.
• Workflow.  Note that Workflow will be supported with the release of Microsoft Office SharePoint Server Service Pack 2. 
• Microsoft FRx Reporter 6.7.  Note that it is expected to be supported on the release of SP 11.
• Microsoft FRx Forecaster 6.7
• Microsoft FRx Web Port 6.7
• Enterprise Reporting 7.5
• SmartTags
• Field Service Anywhere
• Microsoft Dynamics Solution Accelerator for Compliance Management

The following components are not supported on SQL Server 2008 (Assume SP3 for all supported components).  Thus if you use any of these components, you must use SQL Server 2005 (64bit or 32bit is fine):

• Rapid Implementation Tools (including Rapid Configuration and Rapid Migration)
• Analysis Cubes for SQL Server 2000 (client and server).  Note that this is for SQL Server 2000.  The Analysis Cubes for SQL Server 2005 are supported on 64bit.
• Microsoft FRx Reporter 6.7.  Note that it is expected to be supported on the release of SP 11.
• Microsoft FRx Forecaster 6.7
• Microsoft FRx Forecaster 7.0
• Microsoft FRx Web Port 6.7
• Enterprise Reporting 7.5
• SmartTags
• Field Service Anywhere
• Microsoft Dynamics Solution Accelerator for Compliance Management

Can you give me a quick summary of the environments?

Absolutely.  Here they are:

Recommended Standard Environment:

• 2 Servers
• Server 1 (SQL Server) – Windows Server 2003 or 2008 64bit, SQL Server 2005 (or 2008 based on compatibility) 64bit.
• Server 2 (Application Server) – Windows Server 2003 32bit, Dynamics GP full client install (“admin” or “server” install), all required components (FRx, eConnect, Web Services), Microsoft Office (clients current version), DynData file share, SQL Server Client Tools (SQL Server Management Studio, SQL Server Business Intelligence Studio).

Low Volume Environment:

• 1 Server – Windows Server 2003 32bit, SQL Server 2005 32bit, Dynamics GP full client install (“admin” or “server” install), all required components (FRx, eConnect, Web Services), Microsoft Office (clients current version), DynData file share.

High Transaction Volume Environment:

• 3 Servers
• Server 1 (SQL Server) – Windows Server 2003 or 2008 64bit, SQL Server 2005 (or 2008 based on compatibility) 64bit database engine only.  Do not install Reporting Services or Analysis Services.
• Server 2 (Application Server) – Windows Server 2003 32bit, Dynamics GP full client install (“admin” or “server” install), all required components (FRx, eConnect, Web Services), Microsoft Office (clients current version), DynData file share, SQL Server Client Tools (SQL Server Management Studio, SQL Server Business Intelligence Studio).
• Server 3 (Reporting & Analysis Server) – Windows Server 2003 or 2008 64bit, SQL Server 2005 Reporting Services(or 2008 based on compatibility) 64bit (32bit if reporting from external 32bit ODBC sources such as MySQL or MS Access), SQL Server Analysis Services, SQL Server Integration Services.

High Transaction Volume/High BI Volume Environment:

• 4 Servers
• Server 1 (SQL Server) – Windows Server 2003 (or 2008 based on compatibility) 64bit, SQL Server 2005 (or 2008 based on compatibility) 64bit database engine only.  Do not install Reporting Services or Analysis Services.
• Server 2 (Application Server) – Windows Server 2003 32bit, Dynamics GP full client install (“admin” or “server” install), all required components (FRx, eConnect, Web Services), Microsoft Office (clients current version), DynData file share, SQL Server Client Tools (SQL Server Management Studio, SQL Server Business Intelligence Studio).
• Server 3 (Reporting Server) – Windows Server 2003 or 2008 64bit, SQL Server 2005 Reporting Services(or 2008 based on compatibility) 64bit (32bit if reporting from external 32bit ODBC sources such as MySQL or MS Access).
• Server 4 (Analysis Server) –Windows Server 2003 or 2008 64bit, SQL Server Analysis Services, SQL Server Integration Services.