Replication

Question

As I understand it, V1 of SQL Azure does not support replication.

Is this something on the roadmap moving ahead?

---

Owner, Quilnet Solutions

Answer 1

Hello,
The question we have to ask is "why would we need Replication in the cloud"?
Checkout Azure whitepapers at: http://www.microsoft.com/windowsazure/whitepapers/
and more spricific http://go.microsoft.com/?linkid=9686976

I can't figure where is it written, but as far as I know about SQL Azure is that your SQL Azure server databases are automatically (fully transparently for us) replicated over 2 other SQL Servers running in cluster (on different hardwares). So if any hardware failure occurs, other is ready to serve your requests.
So what would you do with another replication ?

Also, note that SQL Azure is a "scale out" but not "scale up" solution (http://en.wikipedia.org/wiki/Scalability ).

Basically if you need replication for some business processing (data mining, analysis services) you can setup a SSIS project that will get the data from your SQL Azure server(or servers) and fill it to your datawharehouse, or even create a datawharehouse in SQL Azure.

Answer 2

Basically if you need replication for some business processing (data mining, analysis services) you can setup a SSIS project that will get the data from your SQL Azure server(or servers) and fill it to your datawharehouse, or even create a datawharehouse in SQL Azure.

Yeah, we are looking to replicate data between SQL Azure and and our data center. Is there information on setting up SSIS or is that whats in the URLs above? We do not use SSIS currently.

Thanks,

---

Owner, Quilnet Solutions

Answer 3

Hi,
Well, there are no whitepapers or samples or walkthrous on how to use SSIS (Sql Server Integration Services) with SQL Azure, but (with V1) you can do a data transfer between tables in the cloud and other tables. Basically you create a DataFlow task where you hare ADO.NET Source (SQL Azure) and ADO.NET Destination (Other SQL Server Instance) - you configure everything you want as usual in SSIS and you have your package ready.
Unfortunatelly you are still unable to use a Transfer DataBase task.
Currently there is no support for SMO (Sql server Management Objects), but it will come later. With support for SMO I tihink you will be able to do a "Transfer SQL Server objects" task, which will make our life easier in terms what you are trying to achieve. This task will let you choose what kinds of object you want to be transfered, and you will not have to create a separate task for each table.

So at the end - what I personally tried and achieved is create a simple SSIS project. Create a DataFLow task, Added ADO.NET Sources and ADO.NET Destinations for each table I want mirrored locally. Execute the task and look over the updating graphics. You can define what to do with existing records, with errored records (when some error has occured either in selecting or inserting).

You can actually also use the command line bcp tool to dump the data locally, and then use it again to push it to your datacenters. However (I haven't played a lot with it though) I am not sure if it support some kind "incrementive select" - you can define the actual SELECT statement for dumping the data, but it will be pain to work like that.

SSIS project types come with Business Intelligence studio. Which comes with SQL Server installation (not the Express one).

Answer 4

Is this a one way connection, in that I can either take data from SQL Azure to my data center or from my data center to SQL Azure but not do it bidirectionally?

What I am trying to do is allow any changes to the database on either end, our data center or SQL Azure, to be available to the other regardless of which side changed the data. We are trying to implement a "field-friendly" version of our database that we can allow our field workers to connect to (SQL Azure side) but allow our internal desktop workers, in our building, to connect to the local data center we have and make changes here. Regardless of which side is changed and what is changed, both sides should have the same data.

Can this be done with the use of SSIS? We are NOT interested in making our internal desktop systems connectable directly to Azure at this time.

---

Owner, Quilnet Solutions

Answer 5

Well,
Now its more clear. But this clarification gave another light over the question.
Well it still can be achieved with SSIS, but you will have to have a bit more complex package. And you also have to be aware that this is not a real-time syncing. SSIS creates a package that you choose (or run a scheduler) when to execute. So you will have ot keep in mind that you database is not always up-to-date.
However when you mention the business case you are trying so solve - have you checked out the Sync framework (http://msdn.microsoft.com/en-us/sync/default.aspx ). As I was not sure whether it will be supported by Azure I just read that " the Microsoft Sync Framework team has teamed up with SQL Azure Database Labs to bring you several synchronization related incubations " which will be interesting to explore and test.
Actually you can use the Sync Framework with SQL Server compact edition (on the field workers' PDAs) and implement some "synchronization logic" (with the great help of Sync Framework.

Answer 6

Yeah, sorry for not being clear the first time.

We understand that the SSIS would be on a schedule, which is not a problem. We don't require real-time data on both ends. We would probably use SSIS to "sync" 4-6 times an hour (10 to 15 minutes). By time a field worker would require information that was added from the internal clients it would probably be synced up since any data changes would not be accessed until at least 15 to 30 minutes after the change.

Unfortunately we are unfamiler with both SSIS and the Sync Framework. We know the name and that SSIS is part of the SQL product and the Sync Framework is used for several things but we have not yet implemented them.

Which would be easier/quicker to learn and set up with Azure?

---

Owner, Quilnet Solutions

Answer 7

Hello,
It is hard to say for what technology the learning curve is "better", but if I am developing an application for mobile agents I would definitelly go for Sync Framework, because field workers might not have connectivity at all time. The will just need to sync once a day, or more often depending on the business needs. You can also use sync-framework for desktop workers, but if you are going to make syncs so often (10 to 15 minutes) than it is better to go for SSIS and explore the features it offers. I would suggest that you begin learning SSIS it has realy lot of features.
Although there are some limitations I think this would best suit your needs, and we can count (hopefully) that better and mroe robust support is coming out (I mean the support for SMO in SQL Azure).

Answer 8

Thanks!

---

Owner, Quilnet Solutions

Answer 9

I would need to use replication to scale out reads, in other words have more than one server in SQL Azure which i could serve data requests for and therefore scale the web applications. normally you can use master-slave replication and double the number of slaves to double your read through put of your web applcation and keep them all in sync with replication only worrying about replication lag before all data is in sync. How would you scale out your application with Azure without replication?