BizTalk Server 2013 R2: Installation and Configuration – Install SQL Server 2014 (Part 7)

This next posts will focus on installing the BizTalk prerequisites and perform the necessary configuration on the BizTalk Server machine.

Before installing BizTalk Server or its prerequisites, make sure you have installed the latest critical Windows updates from Microsoft.

Install SQL Server 2014

BizTalk Server provides the capability to specify a business process and also a mechanism by which the applications used in that business process can communicate with each other. SQL Server is the main repository for this communication mechanism. For optimal performance, Microsoft recommends using the Enterprise Edition of SQL Server.

Note: Using SQL Server Express Edition is not recommended or supported. The Express edition does not include certain features needed by BizTalk Server.

Note: BizTalk Server supports SQL Standard Edition version. However to use Business Activity Monitoring real-time aggregation (BAM RTA), you must install SQL Server Enterprise Edition because BAM real-time aggregation (RTA) is not supported in the Standard Edition of SQL Server.

Note: To fully use the BizTalk Server SDK or deploy BizTalk Server applications from a Visual Studio, you should install the SQL Server Development Tools

Note: BizTalk Server supports all case-sensitive and case-insensitive SQL Server collations except for binary collations. Binary collations are not supported.

To install SQL Server 2014:

  • Insert the SQL Server installation disk into the DVD-ROM drive. The setup program will begin automatically. If prompted for automatic installation of prerequisite software, click “OK”
  • On the SQL Server Installation Center, click “Installation” and then click “New SQL Server stand-alone or add features to an existing installation”
BizTalk Server 2013 R2 SQL Server 2014 installation center New SQL Server stand alone add features existing installation
  • On the Product Key page, enter your product key and click “Next”
BizTalk Server 2013 R2 SQL Server 2014 product key
  • On the License Terms page, select “I accept the license terms”, and then click “Next”
BizTalk Server 2013 R2 SQL Server 2014 license terms
  • In the Global Rules page, the setup procedure will automatically advance to the Product Updates window if there are no rule errors. Otherwise, review the information and resolve any issues, and then click “Next” to continue.
BizTalk Server 2013 R2 SQL Server 2014 global rules
  • On the Product Updates page, the latest available SQL Server product updates are displayed. If no product updates are discovered, SQL Server Setup does not display this page and auto advances to the Install Setup Files page. Otherwise, review the information and resolve any issues, and then click “Next” to continue.
BizTalk Server 2013 R2 SQL Server 2014 Product Updates
  • On the Install Setup files page, Setup provides the progress of downloading, extracting, and installing the Setup files. If an update for SQL Server Setup is found and is specified to be included, that update will also be installed.
    • The System Configuration Checker verifies the system state of your computer before Setup continues.
    • If prompted to restart the computer, click “OK”
  • On the Install Rules page, another rule check will be run to ensure everything is in place so the setup will be successful. If a rule check fails, the setup application will provide the corrective measures to take so installation may proceed. Click “Next” to continue.
BizTalk Server 2013 R2 SQL Server 2014 Install Rules
  • On the Setup Role page, select “SQL Server Feature Installation”, and then click “Next” to continue to the Feature Selection page.
BizTalk Server 2013 R2 SQL Server 2014 setup role
  • On the Feature Selection page, select the following features, and then click “Next”
    • Database Engine Services
      • SQL Server Replication
      • Full-Text and Semantic Extractions for Search
    • Analysis Services
    • Reporting Services – Native
    • Shared Features
      • SQL Server Data Tools
      • Client Tools Connectivity
      • Integration Services
      • Management Tools – Basic
        • Management Tools – Complete
BizTalk Server 2013 R2 SQL Server 2014 feature selection
BizTalk Server 2013 R2 SQL Server 2014 feature selection
  • On the Feature Rules page, Setup verifies the system state of your computer before Setup continues. The Feature Rules page will automatically advance if all rules pass. Otherwise, review the information and resolve any issues, and then click “Next” to continue.
BizTalk Server 2013 R2 SQL Server 2014 Feature Rules
  • On the Instance Configuration page, select “Default instance”, leave the rest of the default values, and then click “Next” to continue.
BizTalk Server 2013 R2 SQL Server 2014 instance configuration
  • On the Server Configuration page, you can specify login accounts for each SQL Server services. You can assign the same login account to all SQL Server services, or you can configure each service account individually. You can also specify whether services start automatically, are started manually, or are disabled (Microsoft recommends that you configure service accounts individually to provide least privileges for each service).
    • Leave the default login account to all SQL Server services
    • Configure all of them with the startup type: “Automatic”
    • And then click “Next”
BizTalk Server 2013 R2 SQL Server 2014 server configuration
  • On the Database Engine Configuration page, select “Windows authentication mode”, click “Add Current User”, and then click “Next”
    • Authentication Mode: Select “Windows authentication mode”. If you select Mixed Mode Authentication, you must provide a strong password for the built-in SQL Server system administrator account.
    • Specify SQL Server administrators: You must specify at least one system administrator for the instance of SQL Server. To add the account under which SQL Server Setup is running, click “Add Current User”
BizTalk Server 2013 R2 SQL Server 2014 database engine configuration
  • On the Analysis Services Configuration page, select “Multidimensional and Data mining Mode”, click “Add Current User”, and then click “Next”
    • Server Mode: Select “Multidimensional and Data mining Mode”. Server mode determines which memory and storage subsystems are used on the server. If you plan to run multidimensional cube databases on the server, choose the default option, Multidimensional and Data Mining server mode.
    • Specify which users have administrative permissions for Analysis Services: you must specify at least one system administrator for Analysis Services. To add the account under which SQL Server Setup is running, click Add Current User.
BizTalk Server 2013 R2 SQL Server 2014 analysis services configuration
  • On the Reporting Services Configuration page, select “Install and configure”, and then click “Next” to continue.
BizTalk Server 2013 R2 SQL Server 2014 reporting services configuration
  • On the Feature Configuration Rules page, this is another step to checks if everything is fine, setup will automatically advance to the next page if all rules pass. Otherwise, review the information and resolve any issues, and then click “Next”
BizTalk Server 2013 R2 SQL Server 2014 Feature Configuration Rules
  • On the Ready to Install page, review the information, and then click “Install”
BizTalk Server 2013 R2 SQL Server 2014 Ready to Install
  • On the Complete page, click “Close”
BizTalk Server 2013 R2 SQL Server 2014 complete

Note: Once again, after this installation, I like to do a Windows update, this step is not necessary.

Related links

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

9 thoughts on “BizTalk Server 2013 R2: Installation and Configuration – Install SQL Server 2014 (Part 7)”

  1. On the feature selection page is indicated that “SQL Server Data Tools” should be selected.
    -Shared Features
    -SQL Server Data Tools

    I cannot find this option (it is not in the provided screenshot)
    b.t.w: This is a great article which really helps!!

  2. Hi everybody, i post here because i think most of you have already used biztalk and this post was quite usefull for the installation of biztalk. I would like to know something about the licence of biztalk server as :

    I have seen that the difference between enterprise and standard is the applications number.
    I haven’t tried biztalk yet so i have limited knowledge in the software.
    Tell to me if i am wrong but with my understanding :
    an application is a set of tasks, for example i have a flow that i would like to integrate in biztalk:
    – i export data from my erp
    – then biztalk format this data in edi format
    – then biztalk send data to the partner
    – then biztalk create a log for the processus
    – then biztalk send to me an email to tell to me if something wrong
    i have ton of flows as this one and i would like to know if this one is considering as one application in biztalk?
    So if i have 5 flows with 5 file transfer by ftp per example i am out i must take the 40k$ for the enterprise licence right?
    thx to your answer and don’t hesitate to ask to me question or precision.
    regards,

    1. Hi Frederic,
      Standard edition has a limitation of 5 Applications but you need to understand the concept of applications in BizTalk – https://msdn.microsoft.com/en-us/library/aa560585.aspx :
      “The BizTalk application is a feature of BizTalk Server that makes it quicker and easier to deploy, manage, and troubleshoot BizTalk Server business solutions. A BizTalk application is a logical grouping of the items, called “artifacts,” used in a BizTalk Server business solution. Artifacts are described in more detail later in this topic.”
      This means you can only have 5 logic containers of BizTalk Artifacts, however inside that application (or logical container) you can have unlimited artifacts: ports, schemas, orchestration and so on.
      Also, there is no association between Visual Studio project and a BizTalk Application, i.e., you can have several different BizTalk Visual Studio Solutions deployed to the same BizTalk Application.
      Nevertheless, Standard edition have other important limitations that you need to be aware:
      • Limited to 8 Cores, single server
      • Single Message Box
      You can have a look to the BizTalk Server 2013 Licensing Datasheet and FAQ for more details
      So if you have 5 orchestrations (flows) with 5 file transfer by ftp per example you can deploy them in the same BizTalk Application or divide them in a maximum number of 5 BizTalk Applications and you will pay $2,485 per Core License (in a max limit of 8 cores)… so if you have a machine with 4 cores you will pay $9,940.
      If you have 100 orchestrations (flows) with 100 file transfer you still pay the same if you divide/deploy them in a maximum number of 5 BizTalk Applications.
      Standard edition will not allow you to create more that 5 BizTalk application – logical containers

      1. ok cool, but one message box means that i can create only one report of the message sent by days for example or is it something else?

  3. sorry i have just found it, one and unique messagebox means that all my messages go through one container and are routed by it. If they are many different flow, i cannot have really simultaneous transfer, there are all in a queue because of the unique messagebox.

    But can i have collision problems with a unique messagebox?

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top