Microsoft SQL Server 2005 and Microsoft Dynamics GP
Updated: May 23, 2006
The data platform of choice for Microsoft Dynamics GP deployments, Microsoft SQL Server 2005, offers enhanced information retrieval and reporting that increase availability, performance, and ease of operation for business intelligence, data management, and developer productivity.
On This Page
Summary of benefits
Leading TCO: Microsoft SQL Server 2005 offers a compelling total cost of ownership (TCO) for Microsoft Dynamics GP implementations. SQL Server 2005 is licensed per processor, not per core, thereby reducing the database cost by a factor of three or more.
Enhanced architecture/application availability: Microsoft Dynamics GP databases have thousands of indexes, and new Microsoft SQL Server 2005 online indexing capabilities increase application availability. The enhanced SQL Server 2005 Database Tuning Advisor can now provide useful insight into tuning Microsoft Dynamics GP applications, taking into consideration the high number of indexes being used at any time.
Rich data-management capabilities: Microsoft SQL Server 2005 comprehensive data-management capabilities include advanced data-mining, integration services, business intelligence, and high availability and manageability.
Superior data backup and recovery: Microsoft SQL Server 2005 backup capabilities protect data against hardware incidents or failures. Microsoft Dynamics GP administrators can speed recovery with fine-grained error repairs, online backups, and controlled checkpoints.
Tight security controls: Increased internal controls can improve data protection and support Sarbanes-Oxley compliance. When implemented in a Microsoft Windows Server 2003 domain using Microsoft SQL Server 2005, Microsoft Dynamics GP provides stronger security functionality for enforced password expirations and secure password rules.
Screenshots: Microsoft Dynamics GP used with Microsoft SQL Server 2005 enables firm enforcement of complex password policies and expirations for tighter security and Sarbanes-Oxley compliance.
Powerful performance-monitoring: Microsoft SQL Server 2005 provides Microsoft Dynamics GP administrators with automated and enhanced monitoring and tuning tools that help ensure 24x7 operations and optimal performance.
Flexible business intelligence: Across your organization, users can build data warehouses of any size as well as RoleTailored access to the data they need, the way they want it, with OLAP (online analytical processing) cubes, and to Microsoft SQL Server Reporting Services.
Data management
Powerful monitoring and tools
Microsoft SQL Server 2005 provides Microsoft Dynamics GP administrators with automated and enhanced monitoring and tuning tools that help ensure 24x7 operations and optimal performance.
The Database Tuning Advisor (DTA) offers significant improvements over the Index Advisor. The DTA is well-suited to processing the thousands of indexes existing in typical Microsoft Dynamics GP implementations and allows the database administrator (DBA) to quickly analyze and implement database index improvements.
Surface area configuration tools improve manageability and security by giving administrators more control over the "surface area" of local and remote instances of SQL Server 2005. Administrators can easily enable services, network protocols, or other features of Microsoft SQL Server 2005 or disable those that are unused.
Enhanced backup and recovery
Microsoft SQL Server 2005 backup capabilities protect data against hardware incidents or failures. Microsoft Dynamics GP administrators can speed recovery with online backups, and controlled checkpoints.
Online backups now allow Transaction Log backups to be performed at the same time. This is an advantage for Microsoft Dynamics GP installations with 24x7 operations or ongoing batch processing during evening/off hours.
Multiple improvements lead to faster failover time for Microsoft Cluster Solutions (MSCS) as well as for synchronous database-mirroring with failover.
Security
Microsoft SQL Server 2005 enforces password complexity and password expiration—helping ensure a more secure database with policies that are easy to set up and maintain.
Microsoft SQL Server options for individual users that can be set from within Microsoft Dynamics GP force users to adhere to password policies (such as minimum length, complexity, and history) and password expiration (requiring users to change their password on a regular basis).
Implementing these features through Microsoft SQL Server 2005 allows Microsoft Dynamics GP to enforce domain policies that may already be in place and not force administrators to define and monitor other policies.
Extending the platform
Microsoft SQL Server 2005 provides a single, powerful, highly productive and integrated development environment for extending, operating, and integrating Microsoft Dynamics GP deployments.
A single platform enables extending and monitoring the database, developing Microsoft Dynamics GP database procedures, business intelligence reports, ETL (data extraction, transformation, and load), and data-mining.
Management Studio, an integrated tool suite, helps DBAs easily manage the database environment.
High availability
Enterprises running Microsoft Dynamics GP business applications need 24x7 availability. Microsoft Dynamics GP deployments support the new capabilities of Microsoft SQL Server 2005 that enable highly available environments.
Database-mirroring with transparent failover helps ensure that a hot-standby Microsoft Dynamics GP database copy is rapidly available in the event of hardware failure. It also allows for geographical redundancy, with network traffic being encrypted by default.
Database Snapshot allows for recovery from operator error by instantly creating persistent, read-only copies of the Microsoft Dynamics GP database at a given point in time.
Online indexing within Microsoft SQL Server 2005 makes it easy to create a new index, defragment and rebuild an existing index, or drop an index while the Microsoft Dynamics GP installation remains online. These features significantly reduce the typical downtime needed for database maintenance and are critical because a typical Microsoft Dynamics GP implementation has hundreds of large indexes.
The right team to deliver next-generation solutions
Microsoft Dynamics GP and Microsoft SQL Server developers work together to create solutions that leverage critical customer data to meet specific customer needs.
As a result of this alliance, Microsoft Dynamics GP has selected Microsoft .NET as the best way to deliver the new generation of Web services applications. Microsoft Dynamics GP customers can take full advantage of .NET-compatible tools for Microsoft SQL Server, Microsoft BizTalk Server, Microsoft Visual Basic, SOAP, COM (Component Object Model), and XML (Extensible Markup Language) to permit all these applications and data sources to interact with Microsoft Dynamics GP.
Businesses using Microsoft Dynamics GP can now improve their decision-making, streamline business processes, and strategically manage their growth because they can have confidence that their business system will grow with them. Microsoft Dynamics GP is tightly integrated with Microsoft SQL Server, Microsoft Windows operating system, and Microsoft .NET enterprise servers—creating a business system environment that is easy to use, lowers the overall cost of distributed computing, and enables businesses to harness the power, flexibility, and award-winning functionality of Microsoft Dynamics GP.
Microsoft Dynamics GP has leading Microsoft SQL Server benchmarks, demonstrating the scalability of the Microsoft platform. Microsoft Dynamics GP supports 32-bit and 64-bit Microsoft platforms.
Performance
Superior transaction processing
Microsoft Dynamics GP workload tests proved that Microsoft SQL Server 2005 is able to take full advantage of new developments in hardware architectures, enhancing the ability of Microsoft Dynamics GP to handle substantial transaction volumes for large customers.
A high-volume benchmark test included processing transactions continuously while at the same time other activities such as the printing of payables management checks, receivables management month-end process of aging, statements, and paid transaction removal were performed. Within eight hours, 1.6 million business transactions were entered.
New Transact-SQL (T-SQL) statements can provide a significant performance boost for Microsoft Dynamics GP queries.
Leading TCO
Microsoft SQL Server 2005 offers a compelling total cost of ownership for Microsoft Dynamics GP implementations.
SQL Server 2005 is licensed per processor, not per core, reducing the database cost by a factor of three or more.
At no extra cost, businesses can take advantage of Microsoft SQL Server 2005 comprehensive data-management capabilities, including advanced data-mining, integration services, business intelligence, high availability, and manageability.
Enhanced architecture/application availability
Microsoft Dynamics GP databases have thousands of indexes, and Microsoft SQL Server 2005 new online indexing capabilities increase application availability. The enhanced Microsoft SQL Server 2005 Database Tuning Advisor can now provide useful insight into tuning Microsoft Dynamics GP applications, taking into consideration the high number of indexes being used at any time.
SQL Server 2005 allows support for up to 4 gigabytes (GB) of memory in 32-bit implementations and nearly unlimited memory in 64-bit implementations, enabling customers to deploy large Microsoft Dynamics GP implementations.
Partitioned tables and indexes increase performance and scalability. A Microsoft Dynamics GP installation can configure SQL Server 2005 to segment a very large table (and its associated indexes) into multiple, manageable pieces.
Business intelligence: Flexible, fast access and analysis of data
True business intelligence is the result of giving employees and management the information and tools they need to transform data into insight. Microsoft Dynamics GP users can reap the benefits of integration with Microsoft SQL Server 2005 to access and analyze the information they need, the way they want it.
Generate reports from within Microsoft Dynamics GP using SQL Server Reporting Services—the reporting engine for Microsoft Dynamics GP—to author, manage, and deliver both paper-oriented and interactive, Web–based reports.
Build and analyze data warehouses of any size.
Organize and use data efficiently with more memory and speed—resulting in faster decision-making.
Structure data and tables from Microsoft Dynamics GP into multidimensional cubes for easier viewing.
Next steps
Get more information about what you can accomplish with your business using Microsoft Dynamics GP—and start empowering your people. For more information:
