Cloud as a Story - Vunvulea Radu

Just realized a few days ago that there are so many flavors of SQL inside Microsoft Azure that you need to be careful when you decide which one to use. In this post, I want to present the most important flavors of SQL that you can have inside Azure and to define a demarcation line between them. 1.0 SQL Server inside Azure VMs (IaaS) The classical IaaS approach where customer needs to manage the VMs and the SQL Server instance that is running on top of Azure. The list of tasks that you need to do when you run your SQL instances on top of Azure VMs are the same as you would have the SQL Server instances inside your own data-centers. From license, to backup and restoration procedures, all of this needs to be managed by yourself. 2.0 SQL Database (PaaS) The current offer of Azure related to SQL on the PaaS environment is more complex than you would expect. In this moment there are 3 options available, that cover most of the uses cases that you might have. SQL Database (Singleton)...

In this post we will talk about things that you should know and take into account when you want to use SQL Data Sync from/to Azure SQL. What is SQL Data Sync It’s a synchronization service offered around Azure platform that is allowing us out-of-the-box to synchronize multiple databases that can be inside Azure or on-premises. SQL Data Sync it is one of the services that makes our lives 10x time easier. The coolest thing on this service is that you can make the configurations from Azure Portal. This is useful especially when you want to try the service or make a fast configuration. From UX perspective, the experience is wonderful and when you want to automate the process, you  can use PowerShell. Base Concepts Synchronization Group represents a groups of databases that you want to keep in sync. You’ll have all the time a Master database used to sync between all the other databases. In the Master-Slave topology, the Slaves are called Sync Members and  the master is calle...

There are times when you make mistakes, big mistakes like… …deleting an Azure SQL Database… In this post, we will take a look on what are the steps that needs to be done to recover and restore a deleted database. We are in the context where you have a Standard database, without any special features related to backups. Trigger I just realized that I deleted the wrong database and I do not have any custom backups mechanism configured. What should I do? Time is crucial  Time is one of the most important factors. The backups of deleted databases is stored for a limited time. The time window depends based on the instance type. In this time window, you can restore a deleted database without any kind of problems. The retention policies for backups is 7 days for Basic and 35 days for Standard and Premium. Azure SQL Server created automatically backups to your databases. These backups are used to restore a deleted database. Don’t forget that as for on-premises backups, things can g...

When writing a PoC you need to keep it as simple as possible and prove that from a technology perspective the project vision is feasible and is the right one. One of the fundamentals rules of a PoC is that it needs to cover things that are not general truth (e.g. You don’t want to prove that ASP.NET MVC can render HTML or expose an REST API). Keeping a PoC as simple as possible can become a problem when you want to use customer data not only mocks data. When you have customer sensitive information, which should not be visible even to the development team you might end up in a strange situation. The problem is not related on how you can do this. The biggest problem is the effort that you need to invest to create the deployment scripts or the automation mechanism that would allow the customer to deploy the solution in an isolated environment, where development team would not have access. This effort might require extra development effort that you don’t want to include in a PoC. I...

SQL Azure is a great service when you need a location to store a relational database and you don't want to manage the infrastructure that is behind it. In only a few minutes, you can get a powerful database ready for your needs. In almost all projects life cycles, there is a moment in time when need to run one or more Load Tests. For each scenario, you may need a different database setup. In the above example, we have 3 different scenarios that we want to test. This means that, we need to load 3 different bacpac files. If the database is relatively small (10MB) than we will not have any kind of problems. But with a large database, the restoring process can take some time. Behind the scene, a database restoring process is complex and CPU intensive, because of this it will require time and CPU. This will not be a problem for a database of type P1 or P2, where the restoring process is fast. But, for a S0 or S1, we may need to wait for even a few hours, until our backup is resto...

Two in one. In today post we will talk about two different topics: An example from real life that shows why unit tests are important An odd behavior of SQLite combined with Entity Framework  Why Unit Tests are important This days we had to update an application with the latest and greatest version of Entity Framework and SQLite. After a few hours of working we manage to update SQLite and EF to the new version and we manage to make the application to compile and start. Now was the moment of true, to check if the unit tests are still green. And surprise, some of them are red, even if the application seems to run without any kind of problem. It seems that in the new version of EF or SQLite the behavior or functionality of a specific feature was changed. Odd behavior of SQLite and Entity Framework Before the update: PRAGMA foreign_keys = ON; DELETE FROM CarsHistory WHERE (julianday(datetime('now'))-julianday(CarBuyDate))>360 Executed as a SQL command from Entit...

Yesterday we had in Cluj-Napoca the last Codecamp event of this year – Winter Codecamp . There were two session about SQL and NoSQL solutions on cloud (Microsoft Azure). The first session was about SQL Database as a service and what are the features that are no available. The second session was dedicated to NoSQL world and the new document database that is offered by Azure as a service – DocumentDB. At this event more than 90 people participate, that dedicated a full afternoon to SQL and NoSQL. Codecamp and all attendees would like to send a big thank you to our sponsor Endava , which made this event possible. Below you can find the slides from the two sessions and some pictures. See you next year! Slides: Database-as-a-Service - how to fire your DBA and deploy new databases quickly, securely, and cheaply Silviu Niculiță DocumentDB - Another NoSQL Solution for cloud infrastructure Radu Vunvulea Pictures:

There is a great news for people that use Azure SQL Database. Elastic scale is available in preview phase. I expecting this feature from some time and now I’m happy that is available to us. The biggest advantage of a cloud solution is scalability and pay as you go. You pay a resource only when you need it without paying the period of time when you don’t use it. Over this features, a service like automatically scaling can be added very easily. This is applicable when we are talking about web sites, web/worker roles where things are not so complicated. To be able to scale the data-tier or an application you need to be able to use sharding (joining multiple resources, in our case splitting a big database on multiple databases). This feature was enabled on Microsoft Azure in a very smart way and without adding additional costs to the end user. You will pay the Azure SQL instances that you use. For each shard you will need to use a new instance of Azure SQL Database. Elastic scale is e...

Working with Windows Azure becomes something that is more and more pleasant. In this post we will see how you can synchronize multiple databases hosted on Azure or on premise using Sync Group. First of all, let’s start with the following requirement: We have an application that contains a database that needs to be replicated in each datacenter. What should we do to replicate all the content in all the datacenters? A good response could be Sync Group (SDS). Using this feature we can define one or more instances of SQL Databases (from cloud or on premise) that will be synchronized. For this group we can specify the tables and columns that will be synchronized. Creating a SDS can be made very easily from Windows Azure portal. This feature can be found under the SQL DATABASES tab – SYNC. I will not start to explain how you can create this group, because it is very easily, you only need to know the databases server address, user names and passwords. I think that more important than th...

Two weeks ago I started to work on a PoC where the bottle neck is the database itself. We had a lot of complicated and expensive queries over the database. Because of this, if we wanted to have good performance we had to find a solution to have more than one instance of SQL Server. Because the solution was on Windows Azure, we wanted to test different configurations and solution. We wanted to see what the performance is if we use 1, 2 and 3 instances of SQL endpoints. Not only this, we had different times of SQL endpoints –Azure SQL (SAS), Virtual Machine with SQL Server and SQL Azure Azure SQL Premium. (SAS but with dedicated resources).  The good part in our scenario was that the data don’t change very often. We could start from the assumption that the database will be updated only one time per day. Because of this we could use different methods to create a load balancing from our SQL instances. Because the time was very limited and the only think that we needed w...

I think that all of us used until now UNION in a SQLstatement. Using this operator we can combine the result of 2 queries. For example we want to display the name of all our clients.In our database the clients are stored in separate databases based on theregion. To be able to display the name of all our clients we need to execute aquery on different table and combine the result: SELECT Name FROM EuropeClients UNION SELECT Name FROM AsiaClients Each time when we execute this call everything is fine. Inone day, the marketing team observes that there are clients that appear inEurope, but also in Asia, but when we execute this query we don’t have anyduplicated data. This happen because UNION removes any duplicates rows.Because of this, if we want to count how many time a client name appears, itwill not be possible using just UNION. SELECT Name, count(*) AS Count FROM (SELECT Name FROM EuropeClients UNION SELECT Name FROM AsiaClients) GROUP BY Name For this query, the Count column will be 1...

As in any application, when developing applications for Windows Azure that use SQL Azure, the security of connection string appear (especially database user password) when the application begin to be deployed on the testing or production environment. In this post I will try to offer a solution about how we can secure the connection string. Usually when we get to the end of a project and our application begin to be deployed on the production environment also, we need to separate each database (development, testing and production). For safety reason we need to use different connection string for each environment that we used. In this moment we don’t want to allow development team to access the production or testing environment. Because of this we need to secure the connection string. Why is this so important? For example what happen if we are in maintenance with our application and the developer have access to sensitive information? We don’t want this to happen, especially when w...