BizTalk 2013 Installation and Configuration – Install SQL Server 2012 (Part 7)

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.

To install SQL Server 2012:

  • 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 SQL Server 2012 Installation center
  • On the Setup Support Rules screen, review the information and then click “OK”
BizTalk Server 2013 SQL Server 2012 Setup Support Rules
  • On the Product Key screen, enter your product key and click “Next”
BizTalk Server 2013 SQL Server 2012 Product Key
  • On the License Terms screen, select “I accept the license terms”, and then click “Next”
BizTalk Server 2013 SQL Server 2012 License Terms
  • On the Product Updates page, the latest available SQL Server product updates are displayed. Select “Include SQL Server product updates”, and then click “Next”
BizTalk Server 2013 SQL Server 2012 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, click “Install”
    • The System Configuration Checker verifies the system state of your computer before Setup continues.
    • If prompted to restart the computer, click “OK”
BizTalk Server 2013 SQL Server 2012 Install Setup files
  • On the Setup Role page, select “SQL Server Feature Installation”, and then click “Next” to continue to the Feature Selection page.
BizTalk Server 2013 SQL Server 2012 Setup Role
  • On the Feature Selection screen, 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 SQL Server 2012 Feature Selection
BizTalk Server 2013 SQL Server 2012 Feature Selection Shared features
  • On the Installation Rules page, Setup verifies the system state of your computer before Setup continues, and then click “Next”
BizTalk Server 2013 SQL Server 2012 Installation Rules
  • On the Instance Configuration screen, select “Default instance”, leave the rest of the default values, and then click “Next” to continue.
BizTalk Server 2013 SQL Server 2012 Instance Configuration
  • Review the information on the Disk Space Requirements screen, this page calculates the required disk space for the features that you specify and compares with available disk space. Click “Next” to continue.
BizTalk Server 2013 SQL Server 2012 Disk Space Requirements
  • On the Server Configuration screen, 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 SQL Server 2012 Server Configuration
  • On the Database Engine Configuration screen, 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 SQL Server 2012 Database Engine Configuration
  • On the Analysis Services Configuration screen, 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 SQL Server 2012 Analysis Services Configuration
  • On the Reporting Services Configuration screen, select “Install and configure”, and then click “Next” to continue.
BizTalk Server 2013 SQL Server 2012 Reporting Services Configuration
  • On the Error and Usage Reporting screen, leave the default values, and then click “Next”
BizTalk Server 2013 SQL Server 2012 Error Usage Reporting
  • On the Installation Configuration Rules screen, review the information and resolve any issues, and then click “Next”
BizTalk Server 2013 SQL Server 2012 Installation Configuration Rules
  • On the Ready to Install screen, review the information, and then click “Install”
BizTalk Server 2013 SQL Server 2012 Ready Install
  • On the Complete screen, click “Close”
BizTalk Server 2013 SQL Server 2012 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.

17 thoughts on “BizTalk 2013 Installation and Configuration – Install SQL Server 2012 (Part 7)”

    1. Thanks for your comments! And I complete understand you, even I had some troubles understanding parts of the official documentation (needs to be improved and clearer)

    1. Hi Sandro

      Thanks for providing this excellent description. There is just one little thing I was looking for and couldn’t find. This is related to server memory settings in SQL server configuration.
      Do you recommend to keep the default settings?
      There are discussions about keeping the default memory settings for dedicated SQL server DB engine installations only and adjust the settings in case of other applications/services installed on the same server. In these discussions, SSRS, SSIS and SSAS are even rated as other services and here we have BizTalk and IIS installed in addition.
      Example forthis kind of discussions may be http://www.sqlservercentral.com/blogs/sqlchicken/2012/02/28/monday-morning-mistakes-not-setting-memory-limits/.

  1. Hi Sandro,
    I am planning to install BizTalk 2013 on SQL server 2012 and Windows server 2012.

    Plan is to migrate existing BizTalk 2010 on SQL server 2008r2 and Windows server 2008r2 to the new instance.

    I saw there is planned release of Biz talk 2013 r2. I could not find any documentation which specifies the advantage of BizTalk 2013r2 over Biztalk 2013. Is there as reason why I should do the migration of the solution on Bizxtalk 2013 r2 untill when its released?

    Thanks

    1. Hi Neeraj,

      Microsoft goal in terms of Release Cadence of BizTalk Server is to have one major release every alternate year (the last one was BizTalk Server 2013, so you may expect a new major release in 2015) and one minor release every alternate year (that will be designated R2, so the next on is BizTalk Server 2013 R2). The major release will have new features and themes and minor releases will have bug fixes, platform alignment and customer asks.

      You can see the new features in BizTalk Server 2013 R2 here: http://msdn.microsoft.com/en-us/library/jj248703%28v=bts.80%29.aspx#BKMK_NewR2, but basically it will be ship with a better JSON support (the new JSON schema wizard), SFTP adapter improved and authentication improvements for service bus, upgrades for the Healthcare Accelerator and of course new platforms support: SQL Server 2014, Windows Server 2012 R2 and so on.

      We don’t have yet the release that, but that will be soon… so if you can wait for this release I would advise you to wait and use this last one, in terms of advantages between 2013 and 2013 R2, that aren’t too much, this is a minor release but again you will have some improvements and better JSON support. The Mainstream Support of 2013 R2 version is exactly the same of 2013 version: until 2018

  2. Thank You so much for this blogpost Sandro! One question, do I need SQL server enterprise edition or will it work with standard edition if I have more than one BizTalk server and not using BAM?

    1. Hi Âsa,

      You can use SQL Standard Edition however notice that BAM real-time aggregation (RTA) is not supported in the Standard Edition of SQL Server. To use BAM RTA, you must install SQL Server Enterprise Edition, otherwise you can use SQL Standard Edition.

      Also you need to take attention to some of limitation of the Standard edition for example it only supports up to two nodes in a failover cluster

  3. Hi Sandro,

    Thank you for your great articles.

    I am currently installing a new instance of BizTalk Server 2013 R2. The BizTalk databases are to be hosted on a SQL Server 2012 SP1 cluster. While configuring BizTalk using the BizTalk Configuration Wizard, everything works fine except for the BAM Tools and Alerts feature. I am seeing an exclamation mark icon next to the BAM Analysis Database. The associated error message resembles to the the following: “…OLAP permissions required for BAM Databases…”

    I am SysAdmin on the Database instance and Admin on the Analysis instance.

    Any hint to solve my problem would be greatly appreciated.

    Thanks

    1. Hi Shah,

      Difficult to say but:
      The service account used for the OLAP service should have db_datareader permissions on the BAM Star Schema database.

      And if you wish to configure BAM Alerts, you must have already configured SQL Server Database Mail feature before you try to configure BAM Alerts.

      1. Hi Sandro,

        Thank you for your reply.

        I finally succeeded in fixing my problem. I added the BizTalk Admin AD group in the Administrators group of the SQL Server Cluster node on which Analysis Services was running.

        Cheers
        Sh@h

  4. To be more precise, here is the exact error message which I was getting.

    “Error configuring OLAP permissions on BAM databases. Probable causes are – The configuring user is not an administrator on the system where analysis services is installed or remote administration is not allowed through the firewall on that system. Refer “http://go.microsoft.com/fwlink/?LinkID=395168&clcid=0x409″. (BAMTools)”

    I described the solution in my previous post (see above).

    Cheers,
    Shah

Leave a Reply

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

turbo360

Back to Top