Contents



Case Study: Building an Enterprise Ecosystem with Microsoft Dynamics ERP and SQL Server 2014 Replication for Scalability and PerformanceSQL Server Technical ArticleSummary: This paper covers the following scenarios:How to use transactional replication in SQL Server 2014 to distribute the transactions from Dynamics AX clients across multiple nodes. Because data is maintained across the nodes in real-time, transactional replication provides data redundancy, which increases the availability of data, includes data available for more efficient performance analysis.How to understand the specifics involved while leveraging transactional replication to build highly scalable Enterprise ecosystems in Microsoft Dynamics ERP. Deliver high performance and scalability without customizing the AX out-of-box features.Transactional replication is typically used in server-to-server workflows that require high throughput. These include scenarios, such as improving scalability and availability, data warehousing and reporting, integrating data from multiple sites, integrating heterogeneous data, and offloading batch processing. This white paper describes a distinct scenario and associated patterns where transactional replication is leveraged in Microsoft Dynamics ERP. It also covers the challenges and best practices when considering transactional replication to build enterprise solutions specific to Enterprise Resource Planning (ERP), as well as the performance analysis at different stages.This content is suitable for developers, architects, and database administrators. It is assumed that readers of this white paper have basic knowledge of SQL Server 2008, 2012, or 2014 as well as SQL Server administration experience.Author: Prabhakaran Sethuraman (PRAB), MicrosoftTechnical Reviewers: Prabhakaran Sethuraman (PRAB), Microsoft | Santosh Padhy, Microsoft | Pavel Majstrov, Microsoft | Karthik Sankaranarayanan, Microsoft | Jon Acone, Microsoft | David Stahlkopf, Microsoft |Kent Oldenburger, Microsoft | Mandi Ohlinger, Microsoft | Jason Roth, MicrosoftPublished: October 2015Applies to: SQL Server 2008, SQL Server 2012, and SQL Server 2014CopyrightThis document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, can change without notice. You bear the risk of using it. This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You can copy and use this document for your internal reference purposes. ? 2015 Microsoft. All rights reserved.Contents TOC \o "1-3" \h \z \u Contents PAGEREF _Toc434150796 \h 3Introduction PAGEREF _Toc434150797 \h 4Loosely Coupled Extended ERP Platform: Case Study PAGEREF _Toc434150798 \h 6Context: PAGEREF _Toc434150799 \h 61. Design Plan PAGEREF _Toc434150800 \h 91.1Key Decision Points PAGEREF _Toc434150801 \h 91.1.1Failover Clustering PAGEREF _Toc434150802 \h 91.1.2Database Mirroring PAGEREF _Toc434150803 \h 101.1.3Log Shipping PAGEREF _Toc434150804 \h 101.1.4AlwaysOn PAGEREF _Toc434150818 \h 101.1.5Transactional Replication PAGEREF _Toc434150819 \h 112. Design Objectives PAGEREF _Toc434150820 \h 122.1 Architecture Design PAGEREF _Toc434150821 \h 122.2 Environments for Performance Analysis PAGEREF _Toc434150822 \h 152.3 Design Considerations: PAGEREF _Toc434150823 \h 163. Conclusion PAGEREF _Toc434150824 \h 17Appendix PAGEREF _Toc434150825 \h 17Acknowledgements PAGEREF _Toc434150826 \h 17References PAGEREF _Toc434150827 \h 18For more information PAGEREF _Toc434150828 \h 18IntroductionReplication [1,2] is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Transactional replication can be leveraged in Microsoft Dynamics ERP to build highly scalable loosely coupled Integration ecosystems at enterprise level without doing any customization in ERP. This paper describes the following:Using Transactional Replication-SQL Server 2014, to distribute the transactions from Dynamics AX clients across multiple nodes. Because data is maintained across the nodes in real-time, Transactional replication provides data redundancy, which increases the availability of data, including data available for efficient performance analysis.Understand the specifics involved while leveraging transactional replication to build a highly scalable integration system in ERP to deliver high performance and scalability without customizing the AX out of box features.This white paper describes the scenarios and the pattern where transactional replication can be leveraged in Microsoft Dynamics ERP. It also includes the challenges and best practices when considering transactional replication to build enterprise solutions specifics to ERP, as well as the performance analysis at different stages.This content is suitable for developers, architects, and database administrators. It is assumed that readers of this white paper have basic knowledge of SQL Server 2008/2012/2014 and SQL Server administration.Objective:An essential part of Microsoft’s IT mission [3] is to provide highly scalable and sub second response time to integration partners. The major challenge in ERP systems is to handle increased transaction volume by providing sub-second performance when dealing with more direct integration partners. In order to address this challenge, it is strongly recommended to not customize boxed ERP products like Dynamics AX; instead build a surrounding ecosystem and related extensions using cloud services and data platform high availability features. This technical case study shows how a company can use SQL Server 2014 database replication in Microsoft Dynamics AX to enable loosely coupled complex integration ecosystems. This provides the following benefits:No need to rewrite the entire code if Dynamics AX schema structure gets changed due to an upgrade to the latest version. Results in zero impact to integration partners.Flexibility to create indexes on highly-utilized tables without impacting Dynamics AX OLTP DB.Performance tuning that is independent of Dynamics AX OLTP DB.Use of latest SQL features like Column store index, In-Memory computing to achieve response-time goals for Integration partners.Extended DB that is loosely coupled with Dynamics AX OOB schema structure, mitigating risk during AX upgrade.Extended DB that does not require all articles from Dynamics AX OOB, reducing the data storage size including processing costs by not syncing all undesired articles.Extended DB that is the centralized system for Integration partners, servicing their requests and replicating the data modifications periodically to Dynamics AX OLTP.An extended platform that can be implemented in either on-premises or in the Cloud.Server-side business logic that is written as a procedure in extended DB, according to best practices. This also makes it easy to accommodate the integration partner’s requirement as well.This paper focuses on the scenario where the SQL High Availability feature of SQL Replication is leveraged to scale out rather creating an extended platform for AX OOB. This provides a highly scalable enterprise ecosystem and covers the challenges and best practices to consider when using SQL Replication to build enterprise solutions. As customer requirements is in line with the above mentioned objective, we can see the detailed evaluation including approach by considering the technical in the detailed section.Loosely Coupled Extended ERP Platform: Case StudyContext:Microsoft Premier Business uses the Microsoft Dynamics AX ERP system to manage customer agreements from inception to completion. Microsoft Dynamics AX is customized to create contracts for the Microsoft Products with their premier Customers by leveraging the project management and accounting module. Products can be software, hardware, or any other items that Microsoft produces, and for which they provide support services. Services define the type and level of support that a customer receives. Services are used as a basis for creating customer contracts. The Microsoft Sales team gets data from the Dynamics AX system to calculate the revenue earned by providing services and support to premier customers.A contract is an agreement that defines the services to be provided for specific products and/or sites. A contract contains an entire agreement including all schedules and line items. From a hierarchical perspective, a contract is a grouping of schedules according to the needs of a customer’s organization. The relationship of contracts to schedules is always one contract to one or many schedules. A contract is comprised of schedules, which are comprised of line items. The contract has general information about the company or division and the type of contract; the schedule carries information about the service offering and contains the line item details. A contract can have one or more schedules, and each schedule can have one or more line items.A schedule contains information about a service that was purchased, such as Alliance Support for High Availability, Premier Alliance Support for OEMs, Professional Support for Developers, etc. A schedule represents a single support service (or offering) billed to a single entity, and it contains the entity’s list of authorized contacts. The relationship of schedule to site is always one schedule to one site.A line item is a detail element of a contract. A line item defines the specific deliverables of a service offering, such as the number of incidents, the specific service, etc. Each line item is individually priced and can be added to the schedule as additional services are purchased. Each line item contains the support program (SKU), the incident count, the specific site that is covered by this schedule, the start and expiration dates, the price, and any adjustments that have been made. The line item number is a sequence number within the schedule. A line item is linked to a case during the entitlement process. Below is the hierarchy of contract, schedule, and line item for reference.In order to get Microsoft Support for their products, the customer has to buy a contract with Microsoft for a specific time period and for a specific type of support for that product. Microsoft creates a contract that meets the customer’s required support type and duration. Once contract setup is completed in Dynamics AX, customer support systems like CRM and other online systems (Web) invoke the Dynamics AX system in order to check the eligibility of the customers in regards to:Whether the customer is entitled to get support. Whether the customer has enough incidents to get support. Whether the requested support product is part of the contract.For example, the call center might have a "screen pop," which is a small application that is connected to the phone system. This application, which is a type of CRM, automatically senses who is calling. By the time the agent answers the phone, the app produces a screen on the agent’s computer that lists important information about the caller, such as:What is the contract they have purchased?Is the contract active?What is the product/service package associated with the contract?How many incidents remain for every service package?Is the customer qualified?Is the caller a qualified person to create a service request (SR)?An effective CRM or customer support system should be highly responsive and scalable, which contributes to a quality customer experience. Based on this requirement, contract data is managed through the Dynamics AX system and all the customer supporting systems are integrated with Dynamics AX in order to perform the customer eligibility check. It also provides the ability to update some of the non-core functional attributes, such as adding new contacts, updating email addresses, etc.Because of the high volume of service request calls from customer support systems to Dynamics AX, there can be degraded performance in terms of fulfilling customer requests. This high volume also impacts Dynamics AX OLTP performance too. The main problem that we wanted to address is how to design a loosely coupled extended system from Dynamics AX OOB to support all customer support systems eligibility calls rather than building those eligibility scenarios on Dynamics AX OOB. For customer support and service, the data is critical for Dynamics AX Integrations partners such as CRM and other external customer-facing web applications. While there are many possible ways to alleviate this problem, we wanted to evaluate the best option for using a SQL Server high availability/disaster recovery technology that would address the problem plus provide a strategic platform for the future. We proposed and implemented the following architecture design that best fit the needs, budget, expectations, and skill set. The current strategy uses an Extended database from Dynamics AX which does not require all articles from Dynamics AX. This reduces the data storage size, including processing cost, by not syncing the unnecessary articles. Extended DB is the centralized system for the Integration partners which serves the integration partners requests and replicates the data modifications periodically to Dynamics AX database. To accomplish this goal, we used transactional replication in SQL 2014 to redesign our architecture and to take advantage of the scale-out capability of replication.In the following sections, we will go through the steps and processes the project team used to evaluate and decide on the best course of action to address customer requirements as mentioned in the objective section to build scalable enterprise solution.1. Design PlanThe second phase in the governance model is the Plan Phase. The goals of this phase is to evolve solution concepts into tangible designs and plans so they can be built in the develop track. The main focus is to design a loosely coupled integration data platform where integration partners eligibility scenarios can be designed. The platform should be highly scalable and provide sub-second performance. Key Decision PointsSQL Server AlwaysOn technologies [3] were designed to ensure maximum uptime of mission-critical applications. Because each customer has different demands and may or may not want to employ a specific technology, there is no uniform AlwaysOn technology that will fit every customer need. It is crucial that SQL Server provides customers a choice when it comes to high availability and disaster recovery. The following AlwaysOn technologies were considered and evaluated against the entire set of customer requirements:Failover ClusteringFailover clustering [3] is a combination of one or more physical disks in a failover cluster (formerly known as server clusters or MSCS) cluster group, known as a resource group, that are participating nodes of the cluster. The resource group is configured as a failover cluster instance that hosts an instance of SQL Server. A SQL Server failover cluster instance appears on the network as if it were a single computer, but it has functionality that provides failover from one node to another if one node becomes unavailable. While failover clustering could have been utilized to ensure high-availability within the data center, it was the customer’s requirement that failover clustering not be used in the final solution. This was due to the additional hardware costs associated with clustering (certified hardware plus SAN) and their requirement for multi master nodes. Because the customer did not want to utilize failover clusters, it was eliminated as a possible solution.Database MirroringUnlike failover clusters, which are a hardware solution, database mirroring [3] is primarily a software solution for increasing database availability. Database mirroring maintains two copies of a single database that must reside on different server instances of the SQL Server Database Engine. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. Because the mirror server is unable to serve read/write requests while the principal is still available, database mirroring was eliminated as a possible solution.Log ShippingLog shipping [3] enables you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. Log shipping does not provide the ability for the secondary server to act as a primary server because it is in either a NORECOVERY or STANDBY state. This prevents the secondary server from being used for any write requests while the principal server is available. Because the secondary server is unable to serve any write requests while the principal is still available, log shipping was eliminated as a possible solution.AlwaysOnOne of the most compelling reasons to use SQL server 2012 or higher version is the AlwaysOn [2,3] Availability Group feature. AlwaysOn Availability Groups enable high availability and disaster recovery for multiple SQL Server databases without the need for a shared storage SAN. Replicas can be used to offload backups and reporting workloads from the primary SQL Server instance. AlwaysOn Availability Groups provide high availability for multiple databases, and they can make use of multiple secondary replicas. Each secondary SQL Server replica has its own copy of the protected databases. AlwaysOn Availability Groups continuously synchronize transactions from the primary replica to each of the secondary replicas. This replication can be configured as synchronous or asynchronous to support local high availability or remote disaster recovery.One of the advantages of AlwaysOn Availability Groups is that the secondary replicas can be used for reporting and backup. With SQL Server 2014, the availability of secondary replicas has been enhanced to allow read workloads to continue to run even in the case of lengthy network failures or the loss of quorum for the Windows Server Failover Cluster. The additional secondary replicas can be used to distribute read workloads and provide enhanced recoverability.Although there are secondary replicas in AlwaysOn which replicates the entire database from the primary node, there is no option to choose only the desired articles to replicate to the secondary node. This fact led to challenges in our solution, because we needed to design a loosely coupled system with filtered articles and attributes from the primary database. This was important, because we needed to easily do customization independent of Microsoft Dynamics Database. AlwaysOn Availability Groups do not support filtering out articles, and they also lack the ability to add additional indexes in only one specific node. Also writing data in a secondary node is not supported. For these reasons, AlwaysOn Availability Groups were eliminated as a possible solution.Transactional Replication Transactional replication [3] provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Transactional replication propagates consistent changes in near real-time. This enables applications that require scale-out capabilities to distribute the transactions from clients across multiple nodes. Because data is maintained across the nodes in near real-time, Transactional replication provides data redundancy, which increases the availability of data.Transactional replication meets the following customer requirements:Provides an architecture that is not based on Microsoft failover clustering.Minimizes downtime and business impact to users.Supports adding additional indexes without including them in the publisher database. This helps us to build our own custom logic and performance tuning to meet the defined performance SLA.Provides an architecture that will ensure redundancy of key tables.After these requirements were met, it was then possible to meet the last two customer requirements by using basic one-way transactional replication from the core database to the read databases:Provides an architecture that will enable read databases to be refreshed more frequently and eliminate the need to log into multiple read databases to update content.Leave the current read database and architecture intact, because these are already redundant.2. Design ObjectivesPerform a proof of concept to leverage transactional replication in SQL Server 2014 to scale out Dynamics AX Database.Improve the performance and scalability by adding additional indexes and by building custom logic to handle read/write scenarios from customers.Explain the improvements observed when implementing transactional replication, such as performance observed on the replicated database versus the Dynamics AX database.2.1 Architecture DesignThe current idea uses an Extended database from Dynamics AX which does not require all articles from Dynamics AX thereby we can reduce the data storage size as well as reduces the processing cost by not syncing unnecessary articles. Extended DB will be the centralized system for the Integration partners which will serve the integration partners requests and replicate the data modifications periodically to the Dynamics AX database. To accomplish this goal, we used transactional replication in SQL Server 2014 to redesign our architecture and to take advantage of the scale-out capability of replication. The centralized database can be designed to stay either on-premises or in the cloud.In the current scenario, client applications talk to the extended database for any read/write calls. This acts as the centralized system for the integration partners. All the custom eligibility logic specific to integration partners is hosted in the centralized system in the form of procedures. In case of any write calls, the data is posted to centralized system from which it will be posted to Dynamics AX database in an asynchronous manner through Enterprise Service Bus.This solution addresses all of the challenges, including:Scaling out the AX database by filtering desired articles and attributes, reducing the storage size.Building custom solutions/procedures in the extended DB.Adding additional indexes to improve performance but not impacting the dynamics AX OOB database.Using an extended DB that is loosely coupled with AX DB. There should not be any impact even if the product schema structure changes.Figure.1 Current Architecture of Dynamics AX OLTP DB with Extended DB through Replication [Simplified Architecture]Another advantage of this loosely coupled architecture is to leverage the same solution to host it on the Cloud as well. Cloud implementation of the same is shown in the below figure [2].Figure.2 Current Architecture of Dynamics AX OLTP DB with Extended DB–Cloud Architecture [Simplified Architecture]Recapping the benefits of this architecture design.Loosely coupled Extended DB is designed through transactional replication.Read/Write calls are handled through the centralized system.Custom logic/procedures, including performance tuning, are done in the extended DB without impacting the Dynamics AX DB.Reusable solution which would work both in On premise as well as Online (Cloud) as well2.2 Environments for Performance AnalysisWe have used the following environments for doing performance analysis to determine the efficient solution.System?Technology StackServer NameRAMSecondary MemoryProcessor SpeedDynamics AX OLTPSQL Server 2014 R2 & Windows Server 2012 R2AGLAXOLTP0916 GB500 GB2.35 GHZTable 1: Dynamics AX: SQL Server versions and complete system configurationsSystem?Technology StackServer NameRAMSecondary MemoryProcessor SpeedAX Extended (Replicated DB)SQL Server 2014 R2 & Windows Server 2012 R2AGLAXExtended0116 GB900 GB2.35 GHZTable 2: AX Extended System (Replication): SQL Server versions and complete system configurationsThe performance analysis of transactional replication vs. Dynamics AX is evaluated as: Performance analysis without extended DB (Solution designed on Dynamics AX DB).Performance analysis of new solution by leveraging transactional replication. The following table shows the execution time of benchmark tests (in minutes): Figure 4: Performance (in Seconds) comparison of AX (OOB) vs. Extended System (Replication)By leveraging transactional replication , we are able to design the loosely coupled ecosystem through extended DB to support integration partners with sub seconds response time. The loosely coupled solution provides 1600% performance improvement when compare to Dynamics AX OOB to support 30K user calls. So, this is really a significant win as far as future scalability for integration partners and the high volume of calls. 2.3 Design Considerations:Using the transactional replication [1, 2] requires an understanding of the hardware you will be running on, the performance of your data flow, and the nature of the data involved. Transactional replication isn’t for everyone as far as AX implementation is concerned. But for those who are willing to think through these things, there can be significant benefits.? Consider using transactional replication when the following occurs:? 3. ConclusionWe compared the performance results of pre and post design. The results are positive, and transactional replication offers a real solution to scale-out scenarios for ERP databases, providing a loosely coupled ecosystem for high performance and scalability.AlwaysOn could be the possible solution if secondary node supports write requests, but the AlwaysOn Solution has the below restrictions there by the alternative option is considered to meet the customer requirements.AlwaysOn secondary server is unable to serve any write requests while the principal is still availableAdditional indexes cannot be added in secondary node alone in AlwaysOn architecture so there is less opportunity for performance tuning in order to meet the SLA of <= 3 seconds.It is not recommended to add any additional indexes to Dynamics AX database to scale up our custom logic for integration partners because it would impact the AX (OOB) workflow and hence replication option is considered for scale out and performance.AppendixAcknowledgementsMany thanks for the technical information and input provided by Santosh Padhy and Pavel Majstrov. We would like to acknowledge the leadership and support provided by Karthik Sankaranarayanan and Kent Oldenburger. Special thanks to the Microsoft community on the Internet who have taken painstaking efforts and provided useful information available anytime, anywhere.References[1] Warwick Rudd, Expanding AlwaysOn Availability Groups with Replication Publishers, “SQL Server Blog” in SQL Server Technical Article[2] SQL Server Replication, “SQL Server Technical” in Microsoft MSDN[3] Using Replication for High Availability and Disaster Recovery: A SQL Server 2008 Technical Case Study and Best Practices, Manaphan Huntrakoon, Nicholas Dritsas, MSDN Article For more information: SQL Server Web site: SQL Server TechCenter : SQL Server DevCenter Did this paper help you? Please provide feedback. On a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given this rating? For example:Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason? Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?This feedback helps improve the quality of white papers released. Send Feedback ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download