Blog: Server Back-End for Mobile Apps: How to Develop and Deploy to SQL Azure

“There is no such thing as a SQL Server team anymore.
There is, in fact, no code base called SQL Server.
There’s only one code base, which is the Azure database code base.”

Satya Nadella,
President of the Microsoft Server and Tools Division

Some general information about the project:

The project development process was divided into several stages:

1. Data Modeling. The development process was based on “Model First” approach where we would first build a model with the help of Visual Studio native model editor. Based on this model Visual Studio would  generate a script, which gets deployed on SQL Server. It’s worth mentioning that the generated script was already compatible with Azure, although we used SQL Server 2012 while developing this project.

2. Data Access Layer. The next stage was writing the data tier using the Entity Framework (EF) for database access. We secured this access mechanism functionality with Unit tests, which emulated client app’s queries, in order to cover the right level of access to the actual service and business logic.

3. Web API for the back-end. E-Commerce solution development with several end-points for HTTP queries from the client side. (separate endpoint for each client app). As a part of this solution we have developed the following types of client apps:

• iOS and Android apps for consumers, with a number of various features;
• Android application for stores representatives, with ability to manage consumers’ requests in real time;
• WPF client, designed for administrators, with multiple management and editing rights and features.

While developing the mentioned above client apps we’ve implemented a number of interesting and special features:

• Protobuf (protocol used for description of the objects participating in client-server transfer) was used for description of objects and messages for service contracts;
• Google Cloud Message Service was used for feedback mechanism with mobile apps, which was implemented to send messages directly to mobile devices.

4.  Business Logic. During the business logic development, we have added several stored procedures, which would be imported to EF model already from the database. These stored procedures are responsible for geo-based objects search.

The main methods we used:

• on database level:
  STDistance —  point A to point B distance calculation;
  geography::Point — point calculation based on its coordinates (latitude and longitude);
• on service level we used similar features, also available in Spatial Library.

Here are some examples of how these features were implemented in the business logic of this E-Commerce application:

• given the customer’s coordinates and the search radius set the user is able to obtain:
  – list of the nearest stores;
  – list of the nearest products;
  – list of the nearest surroundings;

• given product description, price range, and maximum distance to the store- the user is able to obtain a list of suitable products.

5. During the QA stage of this project we created stored procedures for filling DB with valid test data as well as cleaning procedures.

6. The final service has been deployed on VM with Windows Server 2008 R2 for further testing from the client side.

Migration of E-Commerce solution to the cloud:

After evaluating the potential loads and comparing them to the data obtained in the stress testing, we came to a decision to use Windows Azure as hosting of this application and SQL Azure as this app DB. The key decisive factor for us in this case was high and quick performance of the cloud services, as well as scalability- ability of the server to quickly and smoothly dynamically adjust to the required parameters.

During the application deployment to the cloud we have encountered a number of challenges:

• First, SQL Server was using sp_msforeachtable and sp_msforeach_worker procedures for testing data generation- procedures, which were practically missing on SQL Azure. Meaning that on SQL Server 2012 they were located within Master DB context, and SQL Azure did not allow switching in-between several DBs within single session. This happened due to the fact that on SQL Azure different databases can be physically located in different places, therefore in order to switch in between these DBs you need to disconnect from one database, and then connect to a different one. So we also couldn’t take advantage of SQL Azure USE, which is used to switch between DB contexts.
• Second, for various calculations within this application we used geo-types of data (with the help of libraries such as Microsoft.SqlServer.Types.dll and SQLServerSpatial.dll). As we have discovered at a later stage these libraries were not supported by Windows Azure. However some of the advices posted on Alastaria blog have helped us to solve this problem.

Undeniable advantage became the ability to deploy this project to Azure Instance (free-of-charge and used for testing):

• This gave us an opportunity to familiarize ourselves with Azure and its functional and support abilities, and helped us to decide in its favor;
• This certainly has helped us to proceed with the development while keeping in mind Azure specifics and incorporate it in other projects for easier deployment.

Conclusion

When deploying to Cloud it is important to pay attention to certain functional differences between cloud and on-premises servers, and in some cases you will be forced to find elegant solutions to overcome the fact that SQL Azure today doesn’t support all the features and functions of SQL Server 2012.

 Having said that Microsoft has made an open statement that cloud solution would become a dominant architectural solution for IT companies within the next decade, and all of the offered solutions would be at least hybrid. Microsoft has also assured SQL Azure users that maximum amount of time and resources would be devoted to its improvement and upgrades to achieve the complete features package of SQL Server 2012. This only means that cloud deployment is a growing and promising direction, which developers should consider when working on different projects.