Sync Group - A good solution to synchronize SQL Databases using Windows Azure infrastructure

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 this is the options that we have available on a SDS.

HUB
One of the databases that forms the group needs to be the hub. The hub represent the master node of the group, from where all the data propagates.
Synchronization Direction
Once we add the hub, we can add more databases to the group. In this moment we will need to specify the synchronization direction. In this moment we have 3 options:

• From the Hub – All the changes that are made in the hub are replicated to the rest of the databases from the group. In this configuration, when data are different, the hub will win. Changes in the databases are not written to the hub
• To the Hub – Al changes from the hub are not written in the databases. All changes that are made in the databases are written in the hub
• Bi-directional – The synchronization is made in both ways – from Hub to databases and from databases to hub

Synchronization Rules
From the portal, we have the option to select the tables (and columns) from the hub table that will be synchronized. In this way you don’t need to have databases that has the same schema. The most important thing is to have the tables that you want to synchronize in the same schema/format.
Remarks: We don’t need to replicate the database schema to all the databases. Once we select the tables and columns that we want to synchronize (from Hub schema), all this tables will be replicated in the rest of the group.

Conflict Resolution Policies
When we are creating a hub, we have two option for conflict resolution.

• Hubs Wins – In this case all the changes that are written to the hub will be persisted and in a case of the conflict, the version that is on the hub will be the ‘good one’
• Client Wins – In tis case the changes that are written on the slaves (non-hub database) will win and the change from the slave will propagate to the hub and to the rest of the group.

For more information about this conflict resolution policies I recommend to search on MSDN.

Synchronization Frequency
The synchronization between the databases of a group is not made in real time. We can select the time interval when the synchronization needs to be made. This time interval can be between 5 minutes and 1 month. Also, we have a button that can trigger the synchronization action.

On-premise SQL Server
To be able to use this feature on SQL Server you will need to download and install SQL Data Sync. This is a tool that will integrate this functionality on SQL Server.

Logs
All the synchronization action that are between group nodes are logged. Using this information we can determine how long the synchronization action took, what nodes were synchronized and how the action ended.

I think that this feature has a lot of potential. Why? Because database synchronization can be made very easily now. This feature can really add value to your application with minimal costs and headaches.

Read More

Simple load balancer for SQL Server Database

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 was to distributed the load on all the machines we decided to replicate the data on all the SQL instances and create a simple load balancer from code. We divided the tick time (timpstamp) with the number of instances and based on this we selected the SQL instance that will be hit. A simple mechanism that can be very easily managed for the PoC. Of course the final solution will be more complicated, but for a PoC this solution was perfect. In 10 minutes we had a load balancer  :-).
We checked the load of each machine and we can say that the queries were pretty good distributed. The load of each machine was almost the same (+/-10%)

What others solution we could use?
Master-Slave – In this case we have multiple instances of SQL servers. Each slave can be used for reading operation and the master will be used for writing operations.
Sharding – The solution that we used to resolve our problem
AlwaysOn – The base concept is the same one as for Master-Slave
Transaction Replication – Is based on a snapshot and when data is changing, all the subscribers are notified about the change. You can filter individual database objects and publish changes to subscribers
Peer-to-Peer Replication – Is based on Transaction Replication, but with some new features
Change Tracking – Is a very base and simple tracking mechanisms.  The only thing that is notifing you is that a specific row had change. You don’t know any information related to the old or new value
Change Data Capture – Writes in the logs all the changes that are made over a table and are used to track and synchronize the rest of the database instances

Conclusion
We have a lot of solutions on the marker and for SQL Server. Based on our needs we needed to select the best one for us. I was surprised to see that a simple load balancer, written in 5 lines of code can do a pretty good stuff.

Read More

SQL - UNION and UNION ALL

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 everywhere. For thesecases we need to use an optional argument that UNION operator have. The ALLargument displays the duplicates rows also. Using this argument with UNION willpermit to get the result that we expected from our queue.

SELECT Name, count(*) AS Count
FROM
(SELECT Name FROM EuropeClients
UNION ALL
SELECT Name FROM AsiaClients)
GROUP BY Name

Maybe the example was not the best one. But I would like to emphasize that theUNION don’t return the duplicates rows, but UNION ALL do.
Also, before using anykind of SQL commands try to understand 100% what they do.

Read More

How to secure connection string of a SQL server in a Winwdows Azure application

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 we store on the database passwords, salaries and so on.

Normally between our company and our client we have a SLA. Also another SLA exists between our company and each developer, but this is not enough. If we can we need to secure access because we need to reduce the risk of someone accessing our client data. If you think that you will increase the database security by encrypting the database password from connection string, you need to know that this is not truth. When someone will be able to access the machine where the application is hosted that we can decrypt the connection string, is only a matter of time.

We want to encrypt the connection string of production environment only for our team (development or testing team). Maybe we have a source control where we store the configuration file and we don’t want to have this information stored in clear text. The solution that I will explain in the next lines can be used only with the condition that out team doesn’t have access to the production servers. In some conditions, the only think that we want to let them to have access is to make a “fast” deploy of the application but without any remote access rights on the Windows Azure machines.

I propose a solution that is based on certificates. Because a certificate can have a public or a private key, we can share this information in such way that the team can only create and prepare our application package, but without any rights to decrypt or use the connection string of the production environment.

Our team (development for example) will have only the public key. Based on this key they will be able to generate the application package that will be used for deployment. Our SQL engineer for example will have access to the public key of the certificate. Based on the public key, the connection string will be encrypted. Any person that will have the public key will not be able to decrypt the connection string.

On our production environment we need to install the private key. Only the person/environment that has the private key of the certificate can decrypt the connection string.

In the next part of the post I will try to explain each step that need to be done to encrypt the connection string and how to generate the certificate.

The first step is to create a signed certificate. This certificate can be created in different methods. One method is to use the “makecert” command from Visual Studio. A password will be required to enter when you will run this command. This password will be used to secure the generated key that is created when the certificate is generated.

akecert -r -pe -n "CN=mysecureconfig" -sky exchange "mysecureconfig.cer" -sv "mysecureconfig.pvk"
pvk2pfx -pvk "mysecureconfig.pvk" -spc "mysecureconfig.cer" -pfx "mysecureconfig.pfx" -pi mysecretpassword

 "mysecureconfig.cer" represents our certificate that will be created (the public key). This file will be used by our SQL administrator to encrypt the connection string.

"mysecureconfig.pvk" represent our private key that need to be installed only on the production environment. Try not to store this file in the same location where you have the source of our application, because in this case the developers will have access to this key and they will be able to decrypt the connection string.

The next step is to generate a certificate of type .pfx. This will be used to import our certificate and the private key on Windows Azure. Now we have the .pfx file and we can import the certificate to cloud. To do this, we need to access the “Certificate” tab from Windows Azure. On this page we can upload our certificate. At this step, we will need to introduce the certificate password that we entered when we created the certificate.

The output of this import will be a thumbprint. This thumbprint need to be added to our application configuration file. Based on this thumbprint, our application will have access to our private key and will be able to decrypt the connection string.

At this step I want to remind you that the SQL administrator will need only the public key. The private key doesn’t need to be shared with anyone. Also, the database user from the connection string needs to have only the right that is needed by our application.

The third step is the most interesting one. In our configuration file of our application we need to add our connection string with user and password. Besides this information we will need to add a new provider that will be used to protect our data (in our case the connection string).

<configProtectedData>

    <providers>

      <add name="PKCS12ProtectedConfigurationProvider" 

           thumbprint="myThumbprintFromWindowsAzurePortal"
           type="Pkcs12PrLinkotectedConfigurationProvider.

                      Pkcs12ProtectedConfigurationProvider, 

           PKCS12ProtectedConfigurationProvider, 

           Version=1.0.0.0, Culture=neutral, 

           PublicKeyToken=34da007ac91f901d"/>

    </providers>

  </configProtectedData>

If you don’t have this provider (PKCS12ProtectedConfigurationProvider) installed on your system, you can download it from the following address: http://archive.msdn.microsoft.com/pkcs12protectedconfg.

Before the last step, I want to make a recap of what we need to have on the machine that wills encrypted the connection string:

1. The certificate that we created installed (only the public key)
2. The PKCS12ProtectedConfigurationProvider provider installed
3. The provider that is used to encrypt the data added to the configuration file
4. The connection string added to our configuration file (with user and password in clear – for now only)

Now, we can do the last step – to run from Visual Studio command prompt the following command:

aspnet_regiis -pef "connectionStrings" "." -prov 

"PKCS12ProtectedConfigurationProvider"

What does this command do? This command will encrypt the connection string based on our certificate. The only location from where this connection string can be decrypted is the machine that has the private key installed.

After this command is run, in the configuration file we will have the connection string encrypted. Don’t forget to add the following assembly to your solution: “PKCS12ProtectedConfigurationProvider.dll”, because the web role or the worker role will not have provider preinstalled.

Using this method, we can hide the database credentials from the production or testing environment from the development team. Even if they will see the connection string, they will see encrypted data and without the private key they cannot see the user and password.

What do you think about this solution? What other solution did you used for this case?

I decided to translate one of my blog posts in English: http://vunvulearadu.blogspot.ro/2012/06/how-to-secure-connection-string-of-sql.html

Read More

How to secure connection string of a SQL server in a Winwdows Azure application

English version: http://vunvulearadu.blogspot.ro/2012/06/how-to-secure-connection-string-of-sql_18.html 
Ca si in orice aplicatie, cand dezvoltam aplicatii pentru Windows Azure care sa foloseasca SQL Azure, apare problema securitatii string-ului de conexiune la baza de date (in special la parola). In urmatorul post o sa incerc sa va ofer cateva solutii pentru aceasta problema.
De obicei, cand ajungem spre sfarsitul unui proiect, iar aplicatia noastra se afla deja in productie, ne punem problema cum putem sa separam baza de date care este in productie cu cea care se foloseste pentru development sau pentru testare. Separarea se face destul de usor, folosind connection string-uri diferite, dar nu vrem ca cei de la testare sau din dev. sa aibe access la baza de date din productie, acesta putand sa contina date senzitive pentru utilizator.
Vreau sa atrag atentia ca nu avem mereu nevoie de acest nivel de protectie, deoarece in mod normal intre toti angajatii exista un SLA semnat, dar pentru a proteja clientul si a scadea cat mai mult orice risc este nevoie sa ascundeti aceasta informatie. In schimb daca credeti ca o sa cresteti nivelul de securitate prin encriptarea parolei bazei de date, sa stiti ca acest lucru nu este deloc adevarat. In momentul in care o persoana are acces pe serverul unde aplicatia voastra ruleaza, el poate gasica pe acesta si cheia secreta pentru decriptarea parolei, este o chestiune de timp.
Encriptarea parolei de la baza de date (a string-ului de conexiune) este folositoare pentru a ascunde echipei de dezoltare sau celei de testare credentialele bazei de date din productie, cu conditia ca acestia sa NU AIBE ACCES la mediul de productie.
Varianta prezentata de mine se bazeaza pe certificate. Echipa de dezvoltare o sa aibe cheia publica, pe baza careia poata sa faca pachetul pentru deploy. Pe baza acesteia string-ul de conexiune este encriptat. Administratorul bazei de date SQL o sa aibe cheia publica pe baza careia o sa encripteze string-ul de conexiune si o sa il adauge in fisierul de configurare. Odata parola encriptata, pe baza cheii publice nu se poate decripta string-ul. Pe mediul de productie o sa existe un certificat instalat care contina atat cheia publica, cat si cea privata. Doar cel care are acest certificat poate sa decripteze string-ul nostru.
In urmatoarele randuri o sa incerc sa descriu pe scurt fiecare pas care trebuie facut. Primul pas este crearea unui certificat semnat, acest lucru se poate face din diferite locatii. O varianta este sa folositi comanda din Visual Studio "makecert". In momentul cand o sa creati acest certificat o sa fiti nevoiti sa introduceti o parola care o sa fie folosita pentru a securiza cheia primata.

makecert -r -pe -n "CN=mysecureconfig" -sky exchange "mysecureconfig.cer" -sv "mysecureconfig.pvk"
pvk2pfx -pvk "mysecureconfig.pvk" -spc "mysecureconfig.cer" -pfx "mysecureconfig.pfx" -pi mysecretpassword

"mysecureconfig.cer" reprezinta certificatul vostru (cheia publica), care o sa fie folosit de catre administratorul bazei de date pentru a encripta parola. "mysecureconfig.pvk" reprezinta cheia privata care trebuie sa existe doar in mediul de productie.
Al doilea pas e sa generam un certificat de tip .pfx care o sa fie folosit pentru a putea importa certificatul si cheia privata in Windows Azure.
Odata ce avem aceste certificate create, este nevoie sa importam certificatul mysecureconfig.pfx in Windows Azure. Pentru acest pas este nevoie sa intram pe portalul de Windows Azure, sa selectam tab-ul "Certificates", iar acolo sa incarcam certificatul nostru. La acest pas o sa fie nevoie sa introducem parola care am setat-o in momentul cand am creat certificatul. Dupa ce upload-ul s-a facut cu succes, o sa vi se genereze un thumbprint. De acesta o sa avem nevoie in fisierul de configurare din aplicatia noastra. Pe baza acestuia aplicatia noastra are acces la cheia privata pentru a putea decripta continutul encriptat.
Din momentul in care avem acest certificat, adminstratorul bazei de date poate sa isi instaleze certificatul (cheia publica) si sa encripteze string-ul de conexiune. Nu uitati ca userul care accesza baza de date trebuie sa aibe doar minimul de drepturi necesare si nici unul mai mult.
Acuma vine partea mai interesanta. In fisierul de configurare a aplicatiei noastre este necesar sa adaugam string-ul de conexiune, impreuna cu user si parola si un nou provider pentru protectia datelor. Acesta ar trebui sa arate in felul urmator:

<configProtectedData>
    <providers>
      <add name="PKCS12ProtectedConfigurationProvider" thumbprint="myThumbprintFromWindowsAzurePortal"
           type="Pkcs12PrLinkotectedConfigurationProvider.Pkcs12ProtectedConfigurationProvider, PKCS12ProtectedConfigurationProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=34da007ac91f901d"/>
    </providers>
  </configProtectedData>

In cazul in care nu aveti acest provider instalat (PKCS12ProtectedConfigurationProvider) il puteti instala de la urmatoare adresa: http://archive.msdn.microsoft.com/pkcs12protectedconfg
Sa recapitulam de ce avem nevoie pe masina pe care urmeaza sa encriptam string-ul de conexiune:

1. certificat instalat
2. PKCS12ProtectedConfigurationProvider instalat si inregistrat in Global Assembly Cache
3. string-ul de conexiune adaugat in fisierul de configurare (in acest moment acesta nu este inca encriptat)
4. providerul de protectie a datelor adaugat in fisierul de configurare

Intr-un command promt de Visual Studio este nevoie sa rulam urmatoarea comanda:

aspnet_regiis -pef "connectionStrings" "." -prov "PKCS12ProtectedConfigurationProvider"

In acest moment string-ul nostru de conexiune la baza de date o sa encriptat si suprascris in fisierul de configurare.
Sa nu uitati sa adaugati assembly-ul "PKCS12ProtectedConfigurationProvider.dll" la solutie, deoarece in web role sau in worker role acesta nu o sa fie instalat.
Prin acest mod, echipa de dezvoltare sau de testare nu o sa aibe acces la baza de date din productie, iar string-ul de conexiune nu o sa le folosesca la nimic.

Read More

DB error not handled by the web application

Incercam astazi sa caut care este cea mai buna ruta pentru a merge din Cluj-Napoca in Arad, iar domnul Google m-a dus la urmatoarea adresa url:
http://mersul-trenurilor.infoturism.ro/mersul_trenurilor_arad_cluj-napoca.php
Pagina returnata avea urmatorul continut:

SELECT DISTINCT traseu.id, traseu.ora, traseu.id_tren,
traseu.id_statie, gari.nume FROM traseu, gari
WHERE traseu.id_statie = gari.id
AND id_tren
IN (536,534,1834,1843,1766)
ORDER BY traseu.ordine, traseu.id1054
- Unknown column 'traseu.ora' in 'field list'

Rezultatul returnat mi s-a parut destul de dragut, mai ales ultima parte a sa. In mod normal ar fi trebuit sa vedem o pagina de eroare frumoasa, dar in schimb apare un SELECT, care ne expune o mica parte din baza de date.
O persoana cu putina imaginatie poate ar putea sa execute o comanda SQL precum un DELETE.
In cazul in care lucrati la o aplicatie de orice fel (in special web) nu uitati sa tratiti mesajele de eroare intr-un mod corespunzator, iar la un end-user sa nu afisati niciodate query din baza de date.
Un mesaj generic este mai mult decat suficient pentru un muritor.

Read More

Windows Phone 7.5( Mango) support SQL CE

Windows Phone 7.5( Mango) suporta SQL CE(SQL Compact Edition). Acest feature era de mult timp asteptat. Acuma sa vedem cum se foloseste.
Baza de date poate sa fie pusa in doua locatii:

1. isolated storage
2. installation folder

In functie de locatie si de parametrii, stringul de conexiune poate sa aibe urmatoarea forma:

• Data Source = 'isostore:/MyDB.sdf; - cand baza de date este in isolated storage
• Data Source = 'isostore:/MyDB.sdf';Password='1234'; - cand baza de date este in folderul unde aplicatia a fost instalata si este encriptata cu parola 1234

La stringul de conexiue putem sa setam si alte valori precum culture-ul( "Culture Identifier") si daca este case sensitive( "Case Sensitive").
Trebuie sa tinem cont ca avem cateva limitari pe Mango cand vrem sa folosim SQL CE:

• fisierele sdf sunt stocate si deschise din isolation storage
• daca dorim un mecanism de ORM este nevoie sa folosim LINQ2SQL
• T-SQL queries nu este suportat( nu putem sa avem tranzactii)
• o referinta la System.Data.Linq trebuie adaugata
• pentru definierea modelului in acest moment avem doua optiuni SQLMetal pentru Windows Phone Mango sau code-first. By default nu avem un tool grafic pentru definirea acestor mapari.
• formatul la string-ul de conexiune este unul specific

Versiune de SQL CE care este suportata este SQL CE 4.0. Se poate lucra direct cu EF 4.1.
Saptamana urmatoare o sa revin cu un exemplu intreg.

Read More