Introduction

Solution

·        ConfigurationInfo

·        ReceiveLocationProxy

Happy BizTalking.

Introduction

SQL Server Migration is the process of stirring a previous implementation of SQL Server to a newer version, different location (or both) due to  a strategic decision (such as SQL Server Consolidation). In this post I’m trying to highlight some of the important aspects that we need to consider Before, During & After having an SQL Server Migration. This post is intended to be a general guidance to help you create a migration plan. Additional useful references are provided at the end of this post to give you more details.

Migration Paths

The first thing that we need to consider when having a migration is to define the Migration Path. To define a proper Migration Path we need to specify the following:

• Data Source: To understand the SQL Server Version and Capabilities of the current SQL Implementation (such SQL Server 2000, 2005 or Enterprise, Standard or 32bit, 64bit or SP1,SP2 … etc.)
• Data Destination: Is the intended SQL Server Version that you would like to reach (ex: SQL Server 2012 Enterprise)
• Supported Path: Are the steps and actions needed to your destination. The below example show the possible supported path to reach SQL Server 2012:

SQL Server 2005, 2008 & 2008 R2 can directly migrate to SQL 2012 after achieving the below Service Packs:

• SQL Server 2005: SP4.
• SQL Server 2008: SP2.
• SQL Server 2008 R2: SP1

However SQL Server 2000 needs to be upgraded to SQL 2005 (or later) before reaching SQL Server 2012, below Service Packs must be installed:

• SQL Server 2000: SP4 is required.

Migration Strategy

After defining the Migration Path you need to define the Migration Strategy. Below are some details of the Migration Strategies supported by Microsoft SQL Server:

• In-place: Using the SQL Server Setup program to directly upgrade an instance of SQL Server 2000, 2005, 2008 to SQL Server 2012. The older instance of SQL Server is replaced.

• Side-by-side: Using steps to move all or some data from an instance of SQL Server (2000,2005, 2008) to a separate instance of SQL Server 2012. There are two main variations of the side-by-side upgrade strategy:

• One Server: The new instance exists on the same server as the target instance.
• Two Servers: The new instance exists on a different server than the target instance.

Migration Methods

So after defining the Path & Strategy the next logical question that comes in mind is how to do the migration, and its exactly what we mean by Migration Methods. Below are some methods that we can used to Migration the SQL Server Database (for clarification purpose):

• Backup/Restore
• Detach/Attach
• Manual schema rebuild and data export/import
• Log shipping
• Copy Database Wizard

Note:

You also need to consider other SQL Server Components (such as SQL Server Reporting Services, SQL Server Analysis Service & SQL Server Integration Services) which falls outside the scope of this post.

Application Supportability & Reconfiguration

Most of the time SQL Server Database uses and Application that handles the database in the backend (such as SharePoint, Exchange, Custom Developed Application … etc.), so we need to answer the following questions before proceeding with the migration:

• Does the Application Supports the new SQL Server Version (such as SQL Server 2012)?
• What are the reconfiguration needed from the application vendor to reactivate the application (such as connection strings, custom code … etc.)?

Migration Backups

Before preceding with the migration we need to plan properly for database backups. We need to perform a full backup of each database in an instance (including system databases) at the following points in the upgrade process:

• Make a backup of the user databases and data after all users are out of the system and before the upgrade process has begun. Back up all system databases at this point. Make sure that the media is intact so that you can restore the backups if necessary.

• When the upgrade is complete, but before you do any configurations or changes, perform backups under SQL Server 2012. This lets you easily roll back to a point where the SQL Server 2005/2008/2008 R2 upgrade completed successfully but where an error was introduced after that point.

VLDBs

Many SQL Server databases are in the hundreds of gigabytes or terabytes range. This presents special challenges in any upgrade process because you have to account for constraints in time and hard disk space when you deal with large amounts of data in a short window of maintenance time. Databases in the terabyte range can potentially take days to copy over a network—even the fastest networks. These VLDBs might be mission-critical databases powering the largest systems in your business and might tolerate very little downtime. When an upgrade has to occur over a weekend, you might have to use several techniques and put in many preparation hours to meet the required time frames for success. You might have to revise the upgrade window if the upgrade will not fit within it.

Pre-Migration Tasks

Below are some operational tasks that needs to be considered in your plan before starting the migration:

• Rollup All Needed Service Packs, Updates & Hotfixes.
• Run DBCC CHECKDB on all databases to make sure that they are in a consistent state.
• Verify Backup of  All System & User Databases
• Run the Upgrade Advisory Tool

Post-Migration Tasks

After the completion of the migration, the below steps are required:

• Integrate the new SQL Server Instances & DBs into the application and database server environment.
• Determine whether the upgrade was successful (Agree on a success criteria with the application original vendor).
• Decommission and Uninstall the unused servers

Additional References:

Below are some useful references that provides more information about the items mentioned in the above post:

• SQL Server 2012 Upgrade Technical Guide
• Maximum Capacity Specifications for SQL Server 2008 R2
• Maximum Capacity Specifications for SQL Server 2012
• Hardware and Software Requirements for Installing SQL Server 2008 R2
• Hardware and Software Requirements for Installing SQL Server 2012
• Microsoft® SQL Server® 2008 R2 Best Practices Analyzer
• Microsoft® SQL Server® 2012 Best Practices Analyzer
• Microsoft SQL Server 2008 R2 Upgrade Advisor (Feature Pack)
• Microsoft® SQL Server® 2012 Upgrade Advisor (Feature Pack)

Introduction

In this blog post I will present a very simple solution to integrate with Apache ActiveMQ platform. Actually I got this question from a friend I could not believe how easy it is to integrate with this platform using the new BizTalk 2013 WCF-WebHTTP adapter. The adapter provides many features out of the box that really makes the integration engineer life much easier and better.

In this scenario I have installed Apache ActiveMQ on a private VM, created a test queue and made sure that I am able actually to send messages to that test queue. The target then was to create a way to receive messages from this queue to BizTalk server.

Discussion

The following procedure is what I did to perform this POC.

1-     First I started by installing ActiveMQ and to my surprise that was super easy. So I downloaded the binaries from here and I also downloaded the JDK from here. I installed the JDK then extracted the ActiveMQ to any folder of my choice.

2-     I then had to set the JAVA_HOME environment variable (I do not know why the JDK setup did not do this) but I did that manually to point to the JDK folder as C:\Program Files\Java\jdk1.7.0_45

3-     I then started the ActiveMQ platform by browsing to the folder where I extracted it and then typing the command:

4-     Then I opened the new administration portal using the URL (http://localhost:8161/hawtio) then I logged in using the username admin and password admin.

5-     Clicked on “ActiveMQ”

6-     Then I created a new Queue called “Testing” by clicking “Create” then “Create Queue”

7-     Click on the queue on the left then click “Send” then make sure you change the format to XML type in a valid XML message and finally send the message.

8-     If you now go to the browse of the queue you will find that the message is ready for pickup as per below:

9-     Now the ActiveMQ supports the REST API to be able to pick up messages (or post them) the address is as follows (http://localhost:8161/api/message/Testing?type=queue&clientId=BTSconsumer). So note this address as we will use it later in BizTalk.

10-  Now let’s go to the BizTalk administration console. I created a new send port to be able to pick up messages from the Queue using the WCF-WebHTTP adapter. So I created a new send port and configured it as follows:

11-  Then click configure and change the address and operation as follows:

12-  One key point here (and a new feature that I really like about this new adapter) is that if you are using the HTTP GET operation then you cannot sent anything in the BODY of the HTTP request. But BizTalk out of the box have to send a message so the adapter solved this by adding a key feature called suppress outbound messages body for verbs GET, so you need to configure this as below:

13-  Now I need a way to issue a GET verb using this send port. So usually what you would configure is a scheduled task adapter to schedule checking of messages from the queue. In my case I wanted something simple and without installing anything else on the VM. So I configure a messaging approach to issue a GET verb after I send an empty file through a file receive location and then created another file send port to pick up the final message received form the Queue and write it to a folder.

14-  Started everything and BINGO worked like a charm J I have sent the message to the queue and then I drop the empty file to the input folder to issue a GET verb and finally the sent message is written to the output folder as follows:

Conclusion

The conclusion here is that REST is very powerful and makes you do amazing things. I really think everything should support REST and the other very important conclusion is that I need to thank the BizTalk product group to support this out of the box in BizTalk Server 2013 J

introduction

In my prior post, I have listed some of the APIs that you can use for developing applications where PubSub pattern fits well. In this one, I will give talk little bit more in detail about one of which is Service bus for Windows server and then share some practical guidance.

Body

We are living an era of connected applications. And service bus for Windows server is one of the API/platform you can use to connect applications that may be dispersed and hosted on different platforms (Windows, Linux) in a one/way or bi-directional fashion. It is very like Windows Azure service bus, but differs as being hosted on your own data center.

Figure #1: The service bus for Windows server platform stack

Service bus services that runs on each ring node:

o Windows Fabric: Core clustering technology that manages a “ring” of nodes in the farm.

o Service Bus Message Broker: Manages the send and receive operations from Service Bus queues, topics and subscriptions.

o Service Bus Gateway: Serves as protocol head for supported Service Bus protocols. The gateway also performs security validation on incoming requests.

o Service Bus Resource Provider: Handles management requests from the Windows Azure Pack portal.

o Service Bus VSS Writer: Discovers and automates backup and restore operations using Microsoft Volume Shadow Copy Service (VSS) technology.

Recommendations:

• Make sure it is highly available (HA): HA can be fully satisfied only when both service and database layers are HA. Service layer HA can be accomplished by having at least 3 servers in the ring. In regards to database HA, there are different ways of doing that depending on your case (db version, disaster-recovery plan, etc.) You can check my blog posts about single site failover clustering here  for SQL 2012 Server.  Please note that Service bus v1.1 supports up-to 5 servers in the ring
• Make sure it is scalable: You need minimum 2n message containers where n is the number of servers of the service bus farm ring. So, HA and scalable service bus implementation requires 3 servers and 6 message containers (database instances) at least.    
• Security considerations:
• Client applications can access service bus for Windows server resources only after a successful authentication which is through either a Shared Access Server (SAS) or Active Directory (AD) windows credentials. Certificate used for message security and if auto-generated certificate option selected during the installation, it must be imported to all the clients. Otherwise, it will raise STS related exceptions.
• Design considerations:
• Service bus for Windows server is used for message brokerage. If relaying functionality required, then you may consider Windows Azure service bus instead. Here are a brief overview of both messaging methods (for more details, please visit here):
• Relay: Both parties must be online, supports different approaches (one-way, bi-directional, etc.)
• Message Brokerage: No parties need to be online, asynchronous communication, best suites for http type communications. Service bus queues, topics, and subscriptions are central pieces of message brokerage.
• Evaluate topic with many subscribers vs. topic with less subscribers case and use of subscription filter rules.
• Attention to service bus quotas defined here 
• Performance considerations:
• Please visit here listing best practices for performance improvements: Async calling and enabling client batching bring noticeable performance benefits.
• Consider adding rules for subscriptions that filters messages accordingly. Visit here for details
• Disable (or let it be default) the features of topic, subscription, queuing that are not required per your needs, such as Sort Ordering, Lock Duration, Require Session, Is Read Only, etc.
• Debugging considerations:
• Make sure all required service bus and database services are up running.
• Make sure the client has the imported certificate (for STS)
• Check firewall rules for ports used by the services
• Attention to object null reference exception and API exceptions (sending message to a topic which has not have any subscription cause exception, or creating a topic that its path already in use (another topic), etc. 

Conclusion

Here, in this post, I have shared some of my learning from past experience. Hope it helps. As side note, as an alternative, you can play with Windows Azure service bus then plan & deploy the same functionality on your own environment if you will.

References:

• Service Bus for Windows Server (Service Bus 1.1)
• Best Practices for Performance Improvements Using Service Bus Brokered Messaging
• Windows Azure service bus