Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

Pushing Data to and from the Cloud with SQL Azure Data Sync

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
[TechEd 2013—Pushing Data to and from the Cloud with SQL Azure Data Sync] [Christopher Woodruff] Good. Sorry about that. So we'll get started now. This talk is about pushing data to and from the Cloud with Azure Data Sync. We're going to be looking at primarily how to push data from an unpremised database up to SQL— well, Windows Azure SQL database. It used to be called SQL Azure, and so I'm used to calling it that. I may slip back into that, but it's the database in the Cloud. My name is Chris Woodruff. I'll quickly go through this. You can download the slide deck later. I am a Microsoft MVP in Visual C#. I work at Telerik as a trainer and then you have some contact information up there, and I do a podcast called "deepfriedbytes," and I have some stickers up here if anyone wants some afterwards. We will get right into it. The first thing I want to do is, how many people in here have used Windows Azure? So a good deal. How many people have used Windows Azure SQL databases? A couple. Okay. The goal for this talk overall is to get you guys to dunk your toe in the Azure pool. What I'm going to show you today is very simple, but there's a lot of things that we can do with Azure, so primarily what I'm going to show you is we can now backup our databases in the Cloud very easily, so we don't have to do tape backup as much. You can still do tape backup, but think of this as just another way of storing your database and keeping it safe. So we're going to go through an overview of what SQL Data Sync is— or Azure Data Sync. We're going to understand the benefits from it. We're going to show a demo going through the Azure portal. Then for all the developers in here— all the .NET developers—we're going to show if you don't want to go through the portal how you can use .NET code and write your own syncing program to do the same thing that we're going to do through the portal. And then finally, we're going to talk about the scenarios around the uses and places that you can extend this out to and use it for syncing other types of data. So you can see that I flashed up the definition of the Microsoft Sync Framework. I didn't want to have to read off that, so you guys can actually take a look at that in the slide deck or just look it up at the end. I've got some resource links. But primarily everything that we're going to be doing today is using the Microsoft Sync Framework. So the Azure Data Sync team is using that in the Cloud, and so we're going to use that through the portal, and then we're going to use it locally when we write our C# code to do that syncing, so has anyone used the Microsoft Sync Framework before? Okay, so we're going to try something new today. In this new idea about data in the Cloud, there's a lot of things that we have to sync up. We all have data. We get data to and from a lot of different devices. You guys all got your Surfaces, I hope. You guys didn't have to wait in line for 6 hours the last couple days. You can get data on those. You can get data to servers that you guys have back in your enterprises. You get data on laptops, so what I'm going to be showing you is primarily a way to do on-premise databases. I'm going to scenario around servers and then a database in the Cloud. But there's nothing to say that you can't use this to sync up with sales people in your organization to have local databases on their machines that sync back up to the Cloud and then sync back down to a server. So there's lots of different ways that we can use this technology. A benefit of using Azure Data Sync is we're going to have complexity without code, and we're going to have not a lot of complexity, but there is some, and some of it through the portal is abstracted away, so you guys don't have to do very much work. If you do, we can drop down to that—like I said—that C# code and become very specialized. The other is we can leverage the Azure platform. I'm a consultant and also a trainer, so primarily in my career, I always try to find ways for my customers and my clients to have things cheaper and have things that perform very well and that have the least amount of maintainability. So we're trying to leverage the Azure platform because, one, it's affordable. It scales, and I hope you guys have been to other Azure talks that talk about the benefits of the entire platform. I'm not going to go in through that, but I'm going to show you one way that we can leverage the Azure platform to help ourselves with data. So let's get right in to this and show a demo. So I've got here is I have the Azure, and I'm going to increase the size of this so everyone can see this. Is that good? Can everyone see that in the back corners and everything? Okay. What we have is we have some databases, and I've got a few databases up in the Cloud. The one that we're going to be working with today is Chinook. Chinook is a database that anyone can grab off of Coplex. It's a demo database that involves a hypothetical music store, so it has things like the music, the artists, the albums, tracks, and it also has a business side with employees, customers, invoices, and so forth. I like to use it a little better than Northwind or AdventureWorks because it actually has some interesting data for me because I like music, so we'll be looking at that and inserting artists, music artists, and stuff like that. So this database I have in here, so I'll show you what it looks like through Management Studio. I do have—and we can go right out to our Azure instances— our databases through SQL Management Studios. The same way that you deal with databases today and work with them through Management Studio, you can do the same thing with SQL Azure. Because of the time constraints and how long it takes to provision some of these technologies and some of these instances we're going to be showing, I'm not going to show everything. I'm not going to go end-to-end. I'm going to show you how to do stuff, but I've already set this up last night so it wouldn't take the 15 to 20 minutes to get everything provisioned. What we're going to do is, you'll see that out here we have this sync preview. Now Azure Data Sync is still in beta. It hopefully will come out of preview at some point in the near future, but it's very stable and works very well. If we go out and select that, you'll see that we have an agent. I'll move this over. We have an agent and a data sync group. Now those work in conjunction and in the Cloud, but we need to install something on our servers. So if I go back and I open up this, we have what we call the Data Sync Agent. The Data Sync Agent will get installed on your server. It runs as a Windows service. Basically what it's job is, it's an intermediary between your on-premise database and the sync framework—the sync server up in Azure. So we come in, we will set this up, and let me run this again. Let me make sure I have Internet. Okay. So I'm going to go on and we're going to just kind of take a look at this. Just know that you will install this Sync Agent. You will set it up. You will connect it to your databases that you need to. And then what that will do is it will reach out. If you could see in here, it says "Submit Agent Key." You will get the agent key, and we'll show you how to do that in a second. You will get the agent key from your Azure portal. You will edit in here. At that point, the 2 sides will be connected. They'll find each other, and they'll be able to have a connection. I brought my laptop in from the speaker room, and something must have disconnected while I was off the network. So if we go back to the sync agent that's on the Azure side, we will—I'll show you how you create that. Basically you'll say sync new sync agent. You'll give a name for it, and I'll just call it 'test.' You'll give a region that you want it to exist in. That's basically picking the data center around the globe that you want that sync agent to be. Now I recommend that you pick a location that's very close to the SQL Azure databases that you're going to be working with. So I wouldn't install your databases and have your databases in North U.S. and have the sync agent in Europe. That might not be the best performance. So everything for me is set up in North Central U.S. Then you just say okay. So what you'll get is you'll get this ability, and then basically it's not very complex. You'll see a property. You'll see some data around it. You'll see the last time it was synced with the local sync agent. There isn't a lot to it. A lot of the magic comes from the data sync group itself. So the data sync group is the technology that says, "I want to sync up this database and this database, and I'm going to go through this sync agent." So I will show you how that is set up. Again, we have to give it a name. We have to give it a region, and the key is no matter what you do, you have to install this in the same region as your agent or you won't be able to see it. These data sync groups cannot see past their own data center. So if I come in here and say test and I leave it North Central U.S., you'll come up to this hub—the sync hub. The sync hub—all it really means is the database that will be synced up with your on-premise database in the Cloud. So I'm going to choose a database. I'll say Chinook. I have a database called Chinook that's installed up in SQL Azure. You have to give it the user name, so when you install and get a new instance of Azure, you have to give it a user name and a password so that you can authenticate for certain services. So if I say cwoodruff and I give it the password, it'll wait. You'll see that we have a little indicator, and now we have a green box that just verifies the user name and password. Down here, we have this conflict resolution. What the conflict resolution means is if there's a conflict, who's going to win? So we have that a lot in our databases today. Yes, sir? [inaudible audience question] Yeah, so the question is, is there a way through code to do conflict resolution on a table level? So yes, there is. You can't really do it in here, but you can do it through code. Most of the time I just say Hub Wins because I want my hub to store the data, and if there's a conflict I want it to be the winner if I'm going to sync it up with other databases outside of my on-premise. So I'm going to say next. It will come up through the next step of the wizard, and it will add the reference database. The reference database is your on-premise database. Now it doesn't have to be. You can sync up 2 SQL Azure databases. There's nothing to say that you can't do that. You can sync up any 2 databases that you want. But for this I'm going to come down, and you'll see that we have all of our SQL Azure databases that are on my server in the Cloud and also we have the database that we can get to through the sync agent, and that's the one that we pick. And again, for this we don't have to give a user name and password because the agent's already been authenticated and it's already been set up. There's nothing to do around that. I'm not going to say okay because I've already done this. I'm just going to go out of this. I just wanted to walk through the wizard for you guys. If there's any questions, please ask. I like to make sure that everyone kind of keeps up to speed with me and with everyone else. So at this point, we have a data sync group. We can come in and we can see that the status is good on both. But when you first start this up and create this, you'll get a status of not ready. Not ready just means it hasn't been configured to do everything and to get the synchronization started. So how we do that is we come into configure, and you'll see that we can turn this automatic sync on and off. We can also set up a sync frequency. We can sync in minutes, hours, days, and months. So if I said minutes, the lowest frequency at this point we can do is 5 minutes. For a lot of people, that is a constraint that they can accept. This is one kind of end point that we would go beyond using the portal and we would look at doing more of an inline code .NET to do our syncing because there is no frequency limit to our C# code in using the sync framework. And then we'll just come down here, and we will see that we have our credentials that we set up in our wizard, and we can move on to our sync rules. Sync rules really just mean what part of the schema are we going to set up to use with this synchronization. Now we can do whatever we want. We can come in here and easily say select all columns and all tables. Or we can come in and just pick a table and pick what we want. So there is some flexibility in this to say what we want to sync up. I did everything because I want everything to be synced up. We could just say all I really want—because this database is going to feed a web app that will be public facing, so I just want to do albums and artists and genres and list track and track. And that's fine. You can do it any way that you want. So we set that up, we save it, and at this point, it's going to take about— depending on the size of your database, it's going to take some time to get this provisioned. This is the part where I wanted to get this all set up so that you wouldn't have to sit here and wait for the entire thing to be provisioned. We can also come in here and we can see properties. Properties are only read-only properties. It's kind of like taking a look at the sync agent. It just tells us the last time it was synced, the location, your subscription that you installed this sync agent on, and so forth. I'm going to actually raise this up so I can see a little bit more of the screen. There we go. Then we can come in here and see logs. The logs will occur whenever a sync happens or if you go in and change the sync rules. If anything changes with this sync group, the log file will track it, and it will give you the information. Over here, the message—it's kind of nice. If you click on this little file icon, basically it will copy the message to the clipboard on your local machine and then you can take a look at it. If there was an error or some type of message that you wanted to read, you could bring it up in the text editor. Does anyone have any questions at this point around here? Sir? [inaudible question from the audience] Yeah, that's a great question. Do you know what? You got me. I'll figure that out and I'll let you know right after this. You come up and we'll look at it. I don't want to kind of go off in doing searches. But I'll take a look, but it probably is 443. It is an encrypted connection. Go ahead. [inaudible question from the audience] Yeah, so it does a transaction, and if it hits any errors— one, if it has a conflict it goes by what you've set up as who wins that conflict. So if it's hub and that transaction initiated in that data was coming down, so this is a two-way binding also. We can have one-way binding or a two-way sync, so I set up a bi-directional. As the data is coming back and forth, depending on which way it's going, it resolves it in a certain way based on the conflict criteria that you set up. Now if it hits a major error where maybe it got disconnected or something was a major issue, it will roll back based on that transaction, and it will give you an error, and things will be just like they originally were before the sync. Okay. Let us see if somehow we got this— and I'm going to see if I can fix this really quick. So as I'm waiting, did anyone get their surfaces this morning? How long was the line? Okay, that's awesome. I'm looking at getting mine this afternoon. So if we take a look at services, this service will exist down in the— as a Microsoft sync, and I don't know why it was— it says it's running, so let's stop it and restart it and see what we have because I want to show you guys what this looks like running correctly. Okay. There we go. So you can see our sync agent has the ability that we can register more data bases. We can un-register. This is where you would give the agent key that we set up. Let's take a look, and I didn't actually show that. So over here in our sync agent, down below it says manage key. Basically I've already set it up so there's already been a key registered, but this is where you would get your key to manage your access. You would say generate, you could copy it, and then you would fill this in. That key that was generated—you would fill into this pop-up dialog, and you would say okay. If you ever want to check to see if your service can connect to the Azure sync server, all you have to do is say ping sync service and it will let you know if it can get connected. So it'll say successfully pinged SQL data sync. And really that's it. There's nothing you have to do on this side. Like I said, it's just a Windows service. At this point, let us go through Management Studio and I want to go out and add—yeah, the other thing is you'll see that we have a lot of data sync tables, so when this gets provisioned on both sides, you will get some additional tables on both sides that get created to facilitate the data sync. So if we come in here and we say I want to insert a new artist— I'll tell you what. Someone give me the name of a rock and roll band that you guys like. Bruce, give me the name of a rock and roll band. Ted Nugent. Okay. A guy from Michigan, so he would pick that. Okay, so we're going to insert Ted Nugent into the table. Now this is on our local database. We're going to come back to our agent—back to our group, I mean. And in here we can force a sync, so down below we can say sync. So I'm going to force a sync to happen. You'll see down below it says processing. It's just indicating that something went on. I'm going to come over here to logs, and you'll see that at 10:40 a sync happened and was completed successfully. If you want to, you can copy that, you can bring that up in a text editor, and you'll see that nothing was applied. So let's see if that data actually moved over. Let's go up and take a look at our Chinook database that lives up in the Cloud. And I'm going to say I've just got simple select star from the database, and we'll go down and Ted Nugent does show up at the bottom, so sir, can you confirm that Ted Nugent shows up? Okay. Good. So Ted Nugent is, so let's go from the other side and— well, do you know what? We probably don't need to. Anyone—you guys want to go from the other side and show it syncing from the other side? We can do that, too. So I need another music artist or band. Anyone have—as it comes up—anyone have a favorite artist that they want me to insert into here? U2. Awesome band. Okay, so we say execute. We'll come back out to our portal. We'll say sync. You'll see that we have a synchronization successful. As you're doing this stuff, it keeps around the past things that occurred that you're working with, so you can actually come in here and just say dismiss completed and it will get rid of those previously completed. So it looks like it happened, so let's go back to our table. Let's close—go back to our local and go out to artists. And we're going to say select, and we're going to execute here, and we're going to go down to the bottom and, again, sir, can you verify that U2 is at the last? Okay, he did verify U2. So that's how simple it is to do data sync through the portal. It's not very complex. It's not very hard. All you have to remember is you have a hub and you have your local database, your reference database, and that's it, so let's go back to the slide deck. This is just a little work flow of what happened when we did this data sync. Hypothetically, we could have a Chinook admin application that people are adding new artists and albums and tracks into our database, and what that does is that data goes into our Chinook database that's on-premise, and when the synchronization gets fired off, the data sync agent will work with the SQL data sync server that's up in Azure, and it will send that data to the SQL database in Azure, and potentially we could get that data out through maybe a Chinook music store website. This is a great way—great scenario that we could show for using data sync. So best practices—I always like to talk about some of the gotchas— some things that you have to remember about the technology that we used. With these agents—and this is the local—on the local side on your server— always use least-privileged accounts with network service access. Ideally, Microsoft recommends that the agents be installed and run on a separate machine from your SQL server. And then do not register your on-premise databases with more than one agent because if it happens that 2 syncs happen at the same time, you could kind of get into a hairy situation. So only register one—each database with one agent. So best practices for a database would be— if you don't have to, don't sync up all of your tables in your database. You don't have to sync up everything. Sit down and develop a plan of what data you really need up in the Cloud because it does take time. It does depend on the traffic that you have at your enterprise or your business or wherever you're doing this. It could depend on how far away you set up your data center that you install all this on in Azure. So if you're syncing up with data and you want this database in Asia because you have a team of people that need to get to this data and the latency on it coming from Asia back to the United States is long— you could have this database in Asia and sync up, and it just would take a while for that synchronization to happen. All tables in the sync group—any table that you are going to sync up needs a primary key because it needs to track that the change is based on something, so you need to have a primary key. And I always say test, test, test before production. So test out your sync rules. Test out different scenarios. See if you can get errors. I always like to test as much as I can. It's always better to be safe than sorry. [inaudible question from the audience] The biggest error that I've seen is if you go in and you have to change something. So in here, to make a change you have to refresh your schema, and if you were to—say you added a new column or a new table to each side and you want that column of a table or that new table to be synced up, you have to sync, re-sync, and refresh that schema. Just be careful. Sometimes it does cause issues if you don't understand what you're refreshing in the schema. You'll get errors—things maybe that you accidentally deleted so there'll be an error that says it can't find a certain resource, and your sync will just keep error-ing out because it's expecting a table or a column or a type. So if you have a conflict between types between your 2 databases, that will cause errors. Sometimes it will just time out, so sometimes it will just time out between your on-premise and Azure because of any number of reasons, and that will cause an error. Yes? [inaudible question from the audience] The question is, can you do this to sync up multiple on-premise databases? You can but there has to be that hub—that SQL Azure database as a hub in the center, so you can't sync up— you can't use this to say I want to sync these 2 databases that are on-premise and then use an agent to go in between. There has to be that hub in the center, and you would just set up 2 agents and 2 data sync groups. One would go from your U.S. on-premise database up to Azure, and another one would go from your Asian database up to Azure. You just have to set up 2 sets of groups and agents. Yes? [inaudible question from the audience] Well, you would have—it depends on the error. If it times out—most of the time if it times out, I've found that it didn't cause any schema issues or data because that transaction didn't occur so it did roll back. So more than likely, it won't cause issues on the next sync, so you could just wait for the next sync to happen or you could come in and do a sync on your own. You won't get notified though. That's a feature; so before anyone asks, if there is an error there is no way to get notified. It won't email you. It won't notify you in any way, so it's a thing that we're asking the data sync team for— some kind of notification. The next part is I'm going to drop down individual studio and do C#. If you are not a developer, you will not hurt my feelings if you leave. The next part will be some code. It won't be very complex code. It's only about 50, 60 lines of code, but who are developers in here? Okay, good deal. Okay. Let me show you how to do this with .NET code. So in the resources in the slide deck, there will be a link—[inaudible question from the audience] Exactly. So if you do it in code—and we'll talk about that—the exceptions that you receive—so at the end of the slide deck, if you download it tomorrow or whenever it comes out, there's a link in there for the Microsoft Sync Framework installer. I would recommend you always install the 32-bit installer. We all probably have 64-bit OSs. The 64-bit installer does not work for me, so I'm just going to let you know that always install the X-86. You install that. It's very simple. This is just a simple console app that basically runs, and it has 2 arguments. One argument says set up, and I have an argument that says sync. So you have to set up and provision this synchronization before you can go beyond and do an actual sync. Just like when we went through the portal we had to provision everything and get everything set up. So in here I have 2 connection strings. I have a connection string to my server, and I have a connection string to my local database. Also I have a scope name, and we'll talk about that. Basically, that's my sync group name. Basically my main—I'm just going to be looking for either set up or sync, and if it's set up what I'm doing is I am creating my scope based on my scope name. You can see my scope, and I am—today through this demo I am going to sync up the artist table and the album table. So at this point, you can sync up just the tables that you want. So I'm going to add those DB sync table descriptions to my scope. And in here, I'm going to check to see. I'm going to set up a SQL server provision based on my scope and the SQL server connection for my local database, and I'm going to see if that scope exists. So I'm going to make sure that if that scope does exist I'm just going to fall out and say that my SQL server database has already been provisioned for sync. If it hasn't, then I'm going to do a provision for that, so basically I'm going to come down and create a SQL sync scope provisioning object based on each one of my—each side. So I'm going to set up the sync on my SQL server side, and then I'm going to set up the sync for my SQL Azure side. Basically it's the same code but just with a different SQL connection. And that is all I really need. At the end I'm going to close my SQL connections. This will set up those 2 tables— the artists and the albums—to have this type of synchronization going on. And don't worry. This code will be downloadable from Channel 9. When I do a sync—so up here if I say do a sync test-sync, it will call my synchronization. Basically again, I will have my SQL connections, and then I'm going to set up this sync orchestra. Basically I'm going to set up an orchestration that will tell the synchronization how I want this synchronization to happen. So in here, I can say basically that the sync direction order can be a download. It can be download and upload. It can be upload or upload and download. So in this, you can have specialized syncs, too. I could come in and create a web service or an application that says I want all of my data from my on-premise database to go up to the hub—up to the Cloud—but I don't want anything from the hub to come back down or any which way. Or you can tell it in what order that you want. So I can say I want things uploaded then downloaded or I want things downloaded then uploaded. So if you really want to get very specialized, this is the way to go to do your synchronization. In here, you would just—after you set up your orchestra, you would just say synchronize. I wrapped it in a show statistics because I just show some statistics at the end that say if there are any errors, how long it took, how many changes upload, and how many changes download there were. But basically that's it, and at the end, I just close the 2 SQL connections. So let's run this. I'm going to come in here and I'm going to say set up just as—to add a command line argument through Visual Studio. I'll run this. So basically it's saying provision—ooh. Okay. Interesting. Let me just catch—20 minutes ago this worked. Okay. So, okay. It says it's already been set up. Let us see if we've synced this. I knew the demo gods would get me at some point. And it error-ed out. So I'm not going to dig in to figure out what went wrong. Something happened on the provisioning. [inaudible comment from the audience] So that's an interesting point. I set that up. If you—let's take a look about that because that's a good point to have. If I come in here and I have Northwind set up in here, you have to—there are firewall rules that you have to set up to allow access from your local IPs or from the servers — your server's IPs that are on-premise— so yes, so who—you saw—okay, thank you for seeing that. It was a little quick for me, but yeah, you have to come in, and what you'll do is, once you're in a database, you'll see that you'll set up Windows as your firewall rules for this IP address. If you select that, it will come up and say the current IP address is not included in the existing firewall rules; so basically somehow my IP address was— [inaudible comment from audience] yeah, it refreshed in here since I did that, so now that firewall rule is set up. And if I were to come back in here, it would probably give me—let's try this again after I— and see if—okay. So provisioning—so basically on my SQL server side, that was already provisioned and now it's provisioning SQL Azure for sync. And that was done, so I can stop that, and let us add one more artist. Sorry, I'm going to have you guys give me one last artist before I let you go. [inaudible comment from audience] Oh, I can't do that. Sorry. It's not acceptable. It has to be rock and roll. [inaudible comments from audience] Pink? You know what? You know what? I'll do Pink because—so we have Pink. She's a very angry woman, and I don't want her to come to TechEd and beat me up, so I will put Pink into the database. So let's come in here and change my argument to sync and run this. I'll cross my fingers that everything works. [inaudible comment from the audience] Yeah, we can take a look at what we're doing in the back. Basically we're not setting up triggers on our tables. We're setting up—we're provisioning and setting up some change— some tables to track the changes, and the application or the sync will do the work on each table that it is configured based on the sync rules. So there aren't any triggers set up, no. [inaudible question from the audience] I didn't insert it in the 3 tables. When I did that insert it said use Chinook, so that's one statement. So it was 3 statements I did, so it just inserted it into one table. So this takes a few minutes depending on how much— how many people are running bit torrents or watching videos or watching videos from the last 2 days. But in the end, what you'll get is you'll get that statistic that will show what— how many changes and if we had errors and stuff, so let me run that. I'm going to go back to the slide deck. So I'm going to talk about—this gentleman over here asked, what happens when things go wrong? Now on the .NET side, if there's a conflict between the on-premise database and the SQL Azure database, you will get—an ApplyChangeFailed event will get fired. So basically that event will get raised, and the details about the conflict will be in the argument object— the Args object—for that event. So you'll be able to catch that event and then work based on it. So that's kind of one thing that the portal version of data sync does not have that this code-based does have. So what's the difference between SQL Azure Data Sync and our Custom Data Sync with SQL Azure? There really isn't a lot that's different. Basically on the custom data sync, we just have more control over what we can do. It's using the same technology, so I always say that the Azure Data Sync meets about 80, 85% of my needs. And if I reach a certain scenario that it needs— say it needs to be fired—synchronization for one table needs to be fired off every 15 seconds, you know what? I can drop down into a custom data sync, and I can build that as a web service, and we can get that working in a matter of a couple days through testing and verification. There isn't a lot of difference. The difference is just, one, you're going through a web portal, and two, you're actually building code. I'm going to cut back before we get into scenarios and see—okay. So in here this worked. It says that the start time and the end time— upload changes applied 624. So basically this was going through and doing a whole other sync between the 2 sides, so on one side it was looking at 624 records. And on the other side it was looking at 625, so we can see that there was a difference between the 2 sides, and there were no fails on either side. If I take a look and go back to my SQL Azure database or my Azure SQL—Windows Azure SQL database and I take a look at the artists again— and it takes a second to bring this up— let's see if I just run this again from here— I added another one, so let's go—okay, so we can run this, execute this. Basically Pink is down at the bottom along with a second U2, so we must really like U2 in this database. Basically that's how we work with—on a code level. Let me go back and run through this. The last thing I want to talk about are scenarios. There's lots of different scenarios that you could do. This first one is what we demo-ed today— is I have an on-premise database. I want to sync it up to a database on Windows Azure, and that's what I want. I just want it to be backed up so if—heaven forbid—I lose data locally on-premise, I can just set that back up to sync back up from the hub, which is up in the Cloud, and back down on-premise. A great scenario around this is this is another safe way—a fail-proof data backup plan— for organizations. I find that this plan—this kind of just backing up your database— is a great way to get customers and clients to use SQL Azure for the first time and to get them comfortable with using the Cloud. Next scenario could be a mobile data syncing. So this is kind of what the gentleman—he's left now, but this is the scenario he came up with. If I had data in a database in my row headquarters and I need to sync that out to other databases maybe in other countries, I could use this or else other databases that are on local, smaller devices— like laptops and tablets and smartphones— I could use this scenario also for this. You would have to—from the mobile side, you would have to code that syncing because that sync agent can't work with those type of devices because of that local sync agent. [inaudible question from the audience] No, so this is—primarily Microsoft Sync Framework is just for the Microsoft stack. And the last one is, if you want to, you can sync up an Azure database to another Azure database. So you could set up—maybe if you pushed all your databases up in the Cloud, you could have a master database that held all of your data and another database that held the data that just customers would see That way, you could be safe from if something happened or if the application that you developed against the customer— against the customer database somehow let people in—it got hacked— it could be a good way not to let them see your sensitive, private data. So you could get these 2 synced up and only show certain— or have certain data in each one. Any questions? I'll try to answer anything if anyone has any questions left. We have about 14 minutes. Go ahead. [inaudible question from audience] Well, what it's really saying is that's how many records it went through between those 2 tables. It just verified and—yeah, what—I mean really what it does is when you do a sync it will take a look, and that's why there has to be primary keys on all the tables. It takes a look at all the primary keys from one side and from the other side and finds the differences and then synchronizes based on the direction that you set up through that orchestration. [inaudible question from the audience] That's a great question. I'll do it so people can hear it online. What if you had a primary key that matched on both sides? One is I would build my databases not to have that type of primary key. That's why if I'm going to build databases that are going to be synced, I usually use a GUID for the primary key or I provision a segment of my—if it's an integer a big int— and I do an identity—yeah, I just give each database a certain range. But primarily, I would just use a GUID, and I wouldn't have that problem to begin with. If you do run into that problem, the sync isn't going to know because it finds—when it goes through, it's going to find that there are primary keys on both sides that match, and it's going to assume that that data— [inaudible comment from audience] Yeah, it depends on your direction, but if they match and the number of records match on both sides, you're not going to get a sync at all. Yeah, back here. [inaudible question from the audience] The data sync through the portal does not at this point. You would have to drop down and use code and basically use the Microsoft Sync Framework. No, the portal doesn't work with SQL CE or any non-SQL server database at this point. [inaudible question from the audience] Yeah, it all depends. It would depend on—I don't know any competing technologies. I just know this is for the code side—the custom data sync— you don't have to pay for any service. It's completely customized so you can control the design of it. From the Azure side, I'm not going to go into a lot of the pricing here because it changes, and I don't try to talk about Azure pricing very much in these talks. But from the Azure Data Sync, it's really affordable to sync up your databases into Azure, so that's—from my point, I use it as an affordable way to backup my database. [inaudible comment from audience] Sure. You could use mirroring but that mirroring— I don't know if you could really mirror up into Azure per se. There's a lot of features in SQL Server that haven't been brought into Windows Azure SQL database yet. So I wouldn't say that that's a feature that's out there, so it's a little more—think of it as up in the Cloud. SQL Azure is a lot—gives you a lot of the capabilities from a logical standpoint of your database. A lot of the physical side of the database they're not going to bring in because you're not actually going to get into the server of that— the underlying server that that database exists on. [inaudible question from the audience] Yeah, that's not in Azure yet. The question was the replication that is in the full version of SQL Server 2008 and 2012. No, those features are not up in Azure SQL databases at this point. [inaudible question from the audience] The load that it puts on the initial sync depends on the sync rules that you set up. So if you do every table and there's 100 tables in your database, yeah, it's going to put a load on there. It's going to walk through each table that's set up through the sync rules or through the code that you set up. It won't lock those down a huge amount for a long period of time, but it could be up to minutes for the initial— [inaudible question from the audience] It does table-by-table, and it uses intermediary tables that are set up on each side to hold those records, and then it will sync based on those tables that are created for the changes. So it won't put a huge amount on your primary tables. [inaudible question from the audience] I can show you what it looks like after you set all the provisioning up on the table. Here's all of the data sync tables. [inaudible question from the audience] Yeah, it actually tracks—has a table for each table that you're tracking in your— or in your sync rules. [inaudible question from the audience] Yeah, with the other database you could do that. I could have basically—so the question is, do you have to have—to start—two equal databases? And no, you don't have to. I could have a full database that's on-premise, and I could have a database that's empty but with the same schema or with the same schema that I set up through the sync rules. So if I had a database up in the Cloud that held a segment of my tables that was based on my on-premise, the sync rules I set up between—the sync tables I want to sync— yes, they have to match up from a schema standpoint. But the entire database does not have to. It won't create. It won't replicate schema, so if I add a record in here it won't go up and add the record then bring the new data in. I have to keep the schemas matched on both sides. [inaudible question from the audience] You have to add a table to both. At that point, you match up the schemas on both sides then you go back through the portal—and I'll show you. We have a couple of minutes left. You go through the portal, and you will come down and you'll say sync rules, and this will go out—basically, down below, you'll see that there's a refresh. That's where you'll refresh that schema, and we'll give you the ability to say you want to refresh the schema based on the hub or based on the reference database. And you'll say one or the other and it will update the schema that it's tracking through the portal, and then you can add and adjust your sync rules based on that change in the schema. But it's not automatic. [inaudible question from the audience] It depends on what you base your schema on. So if I delete a column on my on-premise and I refresh the schema based on that, it will change the schema that it's tracking to be without that column in what you see here in the portal. You have to be careful. You have to plan this out. You have to make sure you're not going to cause any issues going forward if you make changes and you want to update those sync rules based on changes to your database. We have a couple minutes. Let me come back into my slide deck. This project—if anyone's on GitHub, this project is out on GitHub for anyone to use, so there's the link for this project on GitHub. [github.com/cwoodruff/TechEd2013ChinookSync] It will also—I'll give it to the people that run TechEd, and hopefully we can get it up as a downloadable resource from Channel 9 when this video gets pushed up. The track resources—here is some stuff. You can download this slide deck later on. Just some links out to Data Sync, SQL Data Sync on MSDN, Windows Azure SQL Database on MSDN, some of the Windows Azure Developer Guidance. I like to make people aware that there is a developer guidance around Windows Azure. And then the last two are for the Microsoft Sync Framework. The first one is just the webpage for MSDN. It has a lot of tutorials and samples. And the last one is the link to download the SDK for the Sync Framework. And like I said, I recommend that you install the 32-bit version of that. [inaudible question from the audience] [The demo project can be found at: github.com/cwoodruff/TechEd2013ChinookSync] If you just go out to my GitHub— if you just go GitHub.com/cwoodruff, you'll see that repository. It will list out all my repositories that I've created out on GitHub. You'll see it. It's the only TechEd one out there. And if you do want to contact me, there's some of my contact information. [[email protected]] [[email protected]; Skype cwoodruff; http://chriswoodruff.com] My email—that's my work email for Telerik. My personal one is just [email protected] My Twitter, my Skype, and then my blog— so I have some—I need to blog more, but you'll find some interesting stuff on my blog. And you can download this from the slide deck after it's up. Please pull out the evaluation—let me know what you liked about this talk, what you didn't, and I appreciate you guys coming and sitting through my session. Thank you. [applause]

Video Details

Duration: 1 hour, 14 minutes and 54 seconds
Country: United States
Language: English
Genre: None
Views: 7
Posted by: asoboleva99 on Jul 9, 2013

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B207#fbid=kG7OLm6xV3l

Langauges to MT: Fra, Ger, Spa, Bra, Kor, Jpn, Rus, CHT, Ita

Caption and Translate

    Sign In/Register for Dotsub to translate this video.