SQL Server Data Tools for Visual Studio

hi this is Robert grain I'm the host of visual studio tool box you know I've been doing this show for quite some time the first episode that I have up on the screen here was May 2011 and we're on pretty close to our one hundred and fiftieth show and you know I've done all of them myself with one exception Seth Juarez once came on and co-hosted not worked fine and I decided recently that yes I'm obviously going to continue doing the show but that I would let some others have some of the fun and so I've decided to ask a few people to co-host the show every now and again Demetri laylan who's going to do the episode you're going to see next is going to do a bunch of episodes Donovan Browns going to do some of the ALM and DevOps stuff we'll see who else may show up as a co-host in the future so just to let you know that the show goes on I'll still be the main host I'll still be doing most of them but we're going to have some additional co-hosts and I'm really looking forward to that so today's episode will be Dimitri Leyland as the co-host and I will see you next time on visual studio toolbox hey and welcome to an episode of visual studio toolbox my name is Dmitry Ryland and then as Robert announced me a second ago I'm going to be a co-host and future episodes starting with this one and I'm really excited to have Kevin Kona with me from the sequel server team a Kevin hi welcome to toolbox thanks very much we've been working for a while together and we've been talking about this video it's awesome we're finally here so what we're going to talk about today yes so today we're going to talk about using sequel server data tools and Visual Studio to make it easier to develop your applications that interact with databases okay and specifically sequel server right I mean that's the scope I remember doing evangelism for these tools you know I'm familiar with them for a long time and a question I often get is the sequel server data tools work with like Oracle or my sequel or something else well it's it's for our product right so that's the first thing to say and it's free a lot of people think another thing we have to get through some skew of some Visual Studio thing right this thing's available to everybody right that's right it's 100% free it's built into Visual Studio ever since Visual Studio 2012 and with Visual Studio 2015 obviously we've added a lot of new and improvements and features that work great there yeah and you guys ship all the time right this is something it's really a live project we're not we're not talking about something we ship for developers like a long time ago or anything like that this has been around for how many years yeah it's been around a while but it's now shipping monthly not even every three months but monthly updates so our current release I'm actually going to show you what's coming out in about a week or so plus and and like that new features added in I can point those out if you want but but every month come back and check and you'll see some new things yeah and it's I had to look down that's a it's October 24th so week from October 24 because when publishing the video folks might might already have access to what you that's right so so so by the time this goes live it should be up and ready released 16.5 ok awesome well let's get into we've got a lot to cover and why don't you kick us off great ok so first I just wanted to give some context so couple of quick slides here and I want to talk to you about what a lot of people see when they're when they're used to developing write which is you do your application in source control everything's well managed but often in a real environment the database development is is kept separate sometimes it's a different team a DBA manages it and when they're doing that it's often not checked into source control usually they'll have a set I'll never have never I've never now chicken something to sort of it it never happens often it you end up developing directly against stage or sometimes even production you make your changes right there you hope and there's like a data person on the team the kadhi ba or a database guy or the staging server person they often like I mean how many times have I had this in my career right they they manage the staging environment if somebody needs to deploy some sequel they may deploy there yeah you come in next day your stuff is Andrea code isn't working unless you get latest or something you know it's always been kind of messy so this helps with that yeah and it's hard then to track your changes know when things are going to happen it gets disconnected so it slows down your development there's a lot of issues with this and that's where we're you know a lot of people are there today and we want to move them away from that there's some clear issues there I mean you don't know what's going to work against production you might not be able to see all of the things that are in your database and see when things break because with application code you compile you get a lot of so information from that spoiled visual studio developers and myself we want to see all the things at build time right as much as possible it can be caught and yeah we like to have everything's checked in part of the solution and get latest and start working in the morning as a developer right those those are the things that most of us take for granted for everything else except Austin sequel right Jekyll becomes that one thing and this is the solution for that yeah that's right and so and so I wanted to go through what we think would be very useful for you and what well a sequel server data tools provides so when you're looking for a better approach than this what you want to look up for key features improving productivity are being source controlled friendly just like your application being a repeatable process so multiple times you should be able to roll the set because that's really critical and and most enterprises will want to have that kind of staging verification process nothing like missing some column and a table having the wrong data time just just because you know things weren't as organizing your project yeah and especially the deployment part is very important because it might be there in your local database but if the script doesn't have it then then it's all missing and you're in trouble staging my break or eventually even worse your project break exactly continuous integration and deployment is great so whenever you check-in you should be able to run a deployment make sure it all works ideally run some tests against it and do all that back goodness as well just like you would for your application I mean the reason why that works is because SSD T did this tool right by having you install into the studio brings T sequel into your solution is a project in your solutions part of it that's that's one of the biggest things that we're trying to help folks with I think you know again having that disconnect having C could be something else up has been a pain but this is right there that's right that's right and and then one of the great things sequel server data tools does this makes it very easy to find out about your code fixers discover what's wrong which can be very difficult sometimes with sequel and an understanding the impact of your changes it's another really good one and we're going to mention briefly alternatives to sequel server data tools in the next slide things like the understanding the impact of changes using schema compare which will show later they're great for anybody who's developing with sequel even if they don't use the sequel projects that we're going to show okay awesome okay so moving on and there are two main solutions so one is the sequel projects that we're going to talk that's where you have everything in T sequel its source controlled and and it's a project based solution the common alternative is a migration framework like entity framework migrations right so you can use sequel server data tools with entity framework we're going to show that today but the migrations framework what it does is it takes a set of changes so for every time you want to update the data part of your application you would write a new migration that goes from one state to the other so that's a great technique to do similar things to sequel server data tools clearly I'm a little biased and thinking that you should try it sequel server don't work in the EFT and yeah this is DT team but they're both valid paths right that's just a complementary in many ways exactly and if you're already using a migration framework approach you may well want to stick with that but use some of the complementary tools that we're providing yeah I think you know as a baseline for SSD T what I always talk to customers myself I mean I use a SSD T for my side projects it's it's the number one thing is T sequels right there with me it deploys with me it's part of my build process part of my CI process other things you know how you how you get from T sequel version one to version two in some environment that's a whole different conversation yeah and the other thing that we've seen with sequel projects that's great is oftentimes people start with very app centric code and as their their application scale-up grow bigger they want to make it faster more performant they start putting a lot more logic into the database like yeah that's where something like solutions you know proving you know queries and everything else yeah take some effort unfortunately and that's where you get the real benefits from sequel projects because you'll start to understand a lot more and make it a lot easier and more productive to develop your store procedures your views all of your program ability objects there okay so so yes so that's the the background for why we want people to try at sequel server data tools we hope they will and and now we'd like to demo it if yes possible let's jump in we love demos and toolbox great okay so I've actually taken an application that you wrote a while back called a health health clinic that I can't take the full credit I mean I wrote like 90 p.m. no I did not write this application my team did don't see that that is correct we we wrote it back for connect last year the the developer fall moment that we have it's available in github so you just pull down from github and jump right into this yep so I pulled it down github this is an asp.net application backed by entity framework right now and I'm going to go and take that and start using an SS DT project to do the sequel part of that okay awesome okay cool so just briefly to show you what this does it's got a login page over here if you're a user you'll get some information and the important part that we'll take note of hopefully this works great is you've got information about patients about doctors about all these different concepts that are there and one of the long scenario that we're going to do here is right now it only works for doctors but if you can imagine this businesses expanding it becomes multidisciplinary and adds in physical therapists and other type of professional change that the ability to change that database off and this is a great time to come back to your app and and start using sequel server data tools for that part of it I think a lot of developers will admit that this is the one time in the project lifecycle we always get a little bit nervous you know like changing code that's easy changing database in here where's where's my DBA you know what scripts or who's using EF what project technology we using here so yeah this is awesome okay awesome so going into Visual Studio this is set up into a number of different projects the important ones here are the web front-end which we'll look at and if you look here the one thing that we need to know from here is if we go to the app settings of JSON this is going against a local DB instance locally so you can obviously override this but I created this local DB instance just to keep things nice and separated there's a database there yeah that makes sense and you know it's worth saying folks for watching this SSD T works with everything right every version of sequel server we can think of whether it's developer edition local DB sequel DB in the cloud right you guys don't don't care you work with sequel server in other words running that's right and local DB is built-in it's installed with Visual Studio so it's right there we updated it in update 2 to support sequel Server 2016 and so we've continued upgrading that and we'll we'll keep shipping sequel server data tools with the latest versions whenever they how far back does SSD T work with sequel server 2000 2005 so so for the database projects its sequel server 2005 there are business intelligence projects just to pitch that we've unified the installer Micra sequel Server data tools so that if you do have business intelligence just update sequel server data tools from inside visual studio you'll have the option to install those and they are also backwards compatible 2008 and up for analysis and reporting services and 2012 and up for integration services alright that's great so the other part of this that's obviously interesting to us at the moment is the data project so this out of gifts office yeah not not quite okay I haven't explored the wonders of that so I'm sure you have it well we built a lot into it but I'm glad we found the sequel piece for this demo yeah so in here you can see that each at table has its own repository here so this is a doctor's repository um it's it's done in the entity framework way it's it's lovely plain old C sharp object and and it's got a whole bunch of information there but you know for me it is actually kind of hard to understand exactly how this maps to a database so so what I actually want to do is go and view the data part of this from from a database and pull it into a project system so that's what we're going to do next so I'm going to stop this demo app for the moment so that I can just get a little bit more sort of control and if you haven't started with sequel server at data tools before one of the things is you might be using server Explorer to connect straight to your databases we'd recommend you use sequel server object Explorer here so we power the the functionality and server Explorer it's just a little more limited and doesn't have all the options the sequel store object Explorer is more specific to sequel server has more functionality you install SSD T it's right there in your in your view options might as well use it exactly so going here and you can see that I've already connected to this server and the database is ready to go over here it's been populated with some sample data as part of the app which is all great and and I could right click here and create the new project but since I just want to organize it correctly I'm going to create a project first and pull in the schema of that database to understand what it looks like yeah and this is already we're talking about SSD cheap run right so this is a solution does not have SSD t project in it we're going to add one that's right and there's two ways right you can add it manually fruit first on the right side or you can go from the left side to the right side exactly exactly so let's do that and if we just go to add and new project we go and we'll a sequel server one here I'm just going to make sure that this is under the source folder and I will call this to be consistent my health data sequence since it's our sequel project okay so this has created a blank sequel project things to note when you create this for the first time if you open the properties you set a target platform here so this is quite important this is how we validate that all the syntax and code that you're using this is your guild validation exactly so this is where build validation comes from you can see all the versions listed here this is classic as your sequel database obviously with v12 huge new surface area and that's the default will that will eventually go away but for now again for compatibility reasons we'll keep it in but yes we're using sequel server 2016 locally so we'll leave it at that and we're going to import at the schema data in so if we just go import you can import a bag of scripts if you have your code like that if in those scenarios you're you were running and you had everything managed and it won't pull in alter scripts just creates and we'll go over that in a minute and a DAC pack is just when you do a build just like you get a DLL out for sequel projects you get a DAC pack which is just the the single file containing all of your schema information sorry and it's the thing you can handover in a scenario where you don't have access to the production or some QA environment you can have over the backpack that team could deploy it and you can be sure that they deploy the backpack your sequel changes made it out to the environment that's right yes and that gets checked into that gets as part of the build you can publish it then you can pull down as part of a release flow and we've actually got blogs and doing that for Visual Studio online which is great yeah that's awesome mean I remember even a few years ago when I was still in the field at Microsoft I would run into customers they didn't know about that part of it or the jewels this is really powerful stuff because we could spend time just on that little littlefoot's thing but let's give me anything but for now I just pulling in the database so so we want to pull in all the data so I'm just going to choose the connection I have actually connected this before so it's in my recent history but if I hadn't if you go to the browse over here and all of your local servers get helpfully listed so I'm just going to go down and pick my health from there connect and start yes so there's a lot of options right we we don't have time to talk about all the import options so the build validation options things you you can configure based on your project needs the folks shouldn't feel like we have some special need and this thing won't work for me check it out maybe it will right yeah there's a huge amount of configuration obviously you won't go into all of it today but but pretty much everything is configurable or extensible cool so what are we showing you here okay so now that this is finished you'll notice that a folders been added and underneath it are all of our tables so it is a simple application it's just a set of tables right now you can see for example we showed the doctor before if you go in here and I'll just minimize this to to give this room feel it yeah you can see at that since it's a table we have a very good designer makes it very easy to make changes up here and any changes are reflected in the sequel code so if it's not in the designer you can easily just type the sequel to make it all work which is correct I remember like really kind of rediscovering this for the first time in my side project I was working on and and this was so awesome like I'm seeing this and like oh man I want to talk about all the school stuff and editor that we have so maybe you can describe some of the capabilities of this view because this view itself is is were for sure right yeah yeah it really is and even some people use sequel server management studio and if they love the table designer there this is a dot on steroids it's a better version it's more modern it's great and all the columns are listed one by one up here you can change data types as units highlighting down here the actual sequel is units right it's moving in you can check a lot of the settings your keys your indexes your constraints are all shown up here again clicking on it brings us right to the primary key down here which is great and you can bootstrap adding in new keys adding in indexes etc right from here one of the things people might not know is that the properties window down here also has configuration we're not going to really go into that in detail today but you can alter a lot of the settings right from there so again if you don't know UT sequel this is a very comfortable safe environment to kind of learn and develop especially with the table name it works for sorts of people's you know level of exposures the sequel server yeah and it does the violation for you if you like break some T sequel down there and edit so you can edit yes right yeah yeah for comma and yeah great one actually to show you is what happens if I reference something that doesn't exist right because that's that that's a gray one and it comes up red and tells you this foreign key has an unresolved reference so it says this thing doesn't exist it's broken yeah as long as it's part of this project in in the solution this will find that this will validate it yeah exactly and just to show what happens if you try and build this which as we mentioned building before building will do all the validation that you'd expect and now that there's an error here it will say built failed brings up the ER list with this if I double-click it brings me straight to where the ER is which is really great it makes it very simple to fix things as you go and what you'll usually find is if you're renaming things if you're changing the structure that will happen a lot and you'll get hours or warnings in some cases that tell you hey there's a problem here and T sequel as the language is very tolerant so if you do that on the database side it'll happily change the table break the view break the store procedure and you'll never know if you worked with sequel you've been doing yeah yeah it's another mission area so obviously we will want to revert this back for now and and the other part to show is that since we know now and let's just verify that this does build yeah since it does build we're going to check this into source control so as we mentioned the great thing then is you get all of your history all of your change log all put in just like your application and as you make changes they can run in in in combination together yeah awesome okay so let's go over to team explore and see I've seen people use this in a way where they don't want the sequel building is part of their project that's fine you can make a solution of your own or some variant doesn't if this thing it's an optional but but just having somebody manage this as part of their ALM life cycle becomes very cool yeah yeah exactly and often what will happen is one person is still the developer one or two and the other people pull it down and as part of their published grip they just have it published the database for them so they're always in sync with the database developers instead of having some special mechanism to catch up and they special mechanism you mean that email you have to check in the morning yeah the sequel attached to it exact that has never happened so we'll just create a new branch for this obviously and create it and we're going to go into our changes here and I'm just going to very quickly do initial project commit okay and commit all obviously you can sync this up as usual to you know the else you do online yeah exactly so that's great so that's building and that's kind of step one just to show you how that works but it's not very useful unless you actually publish it somewhere right so so the next thing I'd like to show is what happens when you try and publish to an existing database just like the one we've already come from and also to brand new one because we were talking about having dev stage in production the the magic behind this is that everything is as you sell create scripts but when it comes time to publish it will actually generate the correct alter statements to make it look the way it should so for the existing database we should see very few changes just maybe some settings where we had default settings and we didn't pull those in and for a brand new database it'll just create it straight out so how about I show you that yeah that's it great okay so if I choose to publish to my existing one we'll just go here and go publish just like you publish by the way you know your asp.net websites it's very similar and again you just need to save what's your target database so since I already connected to it again it's super quick this is where I'll show you those huge amount of options that you have this is a scary button to click whenever it says advanced and I remember the first time I went in here was a scary moment to be told like oh boy yeah database development is complicated at the key things to look at here are we will by default block you if you try and do a change that would cause data loss so that's obviously your your p0 scenario the time you would uncheck this are basically when you're just in your local environment and you're doing a huge amount of changes and just want to tear down restart yeah that's what I do in my in my local dev environment right I would never want to do it I'd be careful there yet decided there's another that's right that's right you know you can backup you can do transactional deployment if you want again these are great options for when you're deploying to production you don't really need them locally and one of the key ones is I'm choosing what to drop or ignore by default we'll leave things in the day basse that aren't in your project just say there's a scheme that somebody else is using you don't want to touch it we'll ignore it but if you do want a little bit more control or you want to punish your teammate or that's right leaving deploying so that's not in here that's true turns out you turn this on so once you're comfortable with the process everyone's on board you'll turn this on and but what you might want to do in those cases is that don't drop users logins or permissions since those are often managed by your DBA and so we've added that still should be separate exactly and so for people who haven't looked at this in a while that's something that we've added in the last last year or two is the ability to say not drop your users tables etc which is which is really powerful but for now I'm just going to get out of this and I always go and try and save save my profile so I'm just going to hit create profile here and I'll generate the script to show you what would happen so again this is going against the existing database you can see I was practicing before yes yeah I'll just clear this edge so that it's not quite so big yeah okay so when we get this first off if you click the preview window you get a very helpful thing showing what's happening and in this case as you'd expect almost nothing's happening we just pulled it back in nothing's changed users comparing between what's in T sequel what's in the Devin Bahr and it's the exact same so here you go and there's a little bit of bootstrap code here just to set some properties as you can see but really I used all snow to skip those once it starts with the actual database it does nothing so it's happy anything yet nothing yet it's all good so what happens if we deploy to something that does somewhere that doesn't exist already so let's go publish and this would be for me I often end up doing a little bit of database development before plugging it back into the application so again this is where I'd have my destructive no don't worry about block on data loss etc one yeah so go in here and instead I'm going to go to a different locally be instance I'll just go to this projects V 13 that's built in again when we create a project we have our own dedicated server just so that we don't interfere with asp.net applications etc then that might be expecting it on unusually MS sequel local TV yeah makes sense okay so I am going to call this my health again and and delete that Oh okay I'll create this profile as well and it I forgot to be like this name in my right health there we go and I'll just save it and again I'll generate the script and if you can publish directly and we'll just save the script for you but you know oftentimes somebody wants to verify what's happening so we're just showing that so maybe here with this initially was that we keep generating scripts and they can manually running them yeah I get very comfortable very quickly especially for local dev and they just let this thing publish out yeah it makes sense and then you can create the script and compare notes to somebody or something or to show the changes you're about to pull push maybe to the you know shared environment or whatever exactly yeah so if you look at the preview here you'll see that there's a huge amount of actions to create everything for the very first time so in this case it's going to create and once we make changes later we'll show you what happens and that's we're saying I mean again you saw this in the beginning but I want to be clear kind of for the for audience which is there's just tables in this particular example yeah but this works with functions and users and and create you know views and whatever right opens in a database all the database objects there's no like Devi subject that this thing wouldn't have pulled in it just that our example doesn't have a complex exactly exactly so I'll just run this to publish it up to that database okay and by the way what's interesting is we've got some warnings here on maximum key lengths etc so we're not going to cover those but assign some times maybe want to redesign your app and that's where having control at the database level can be can be sure I'm the only one from my team here I'll take it we screwed this one up I guess we should have hired a DBA there we good no no the whole point of this tool is you get to you don't need to have your dedicated DBA quite as much anymore they're free to deal with the complex stuff you can do all of your day-to-day applications yes I remember that my one of the first companies where I used an early version of this back before Microsoft even right many years ago and the DBA actually like start to love this when I showed them the first time they were all skeptical they were used to SMS you know that's how they did things they would manually check these check things in source control it was really you know hectic process then I started showing him Visual Studio the person never even used v as much in their life they used to be a developer like you know a long time ago and they switched on the using this as the primary tool because it's just so powerful even for a DBA so it's not just a developer tool yeah yeah it really helps and once you get used to it it can give huge power a lot of DBAs yeah they happily move over and some some have to be persuaded but that's where you can either give them a script that you've generated as the hand off point or you can give them the DAC pack and let them use a command-line tools to deploy it that way what you get for me it was actually interesting it was actually the reverse scenario like from a mentality perspective from a developer you have to convince them that this gives you the advantage of having T sequel in your project and you can work as a team better part of a lemn DevOps awesome right developers community dissolve on that from the other side DBAs if you show this to them that the selling point is not just everything I just mentioned but the fact that they can help the head of five and run the app that we developers run and sometimes DBS don't have that capacity right they they just don't do that deft day and they are right they just do T sequel and a big organization this allows them to actually have the local dev environment they could test the T sequel change the same way we as developers could change it they become even more part of the team so I think this these two benefits both sides quite quite well that's right but it doesn't it doesn't force you like you can you can have this just for one side of the fence you could have it for nobody you know nobody wants to use it but I think it works well for both yeah in a user test so how about now we've got everything working and we know how it's going how about we make some changes this yeah let's do it okay so I think I mentioned to you but this is currently very focused on doctors and what happens when you want to change that so so for me I had to think about this there's a lot of different ways to do that I decided that I really like having the simple table storing everything for a doctor here but really it's becoming healthcare provider rather than doctor okay so I don't want to break the existing app and so what I'm going to do is kind of stage it so I'm going to change this and make it a B healthcare provider and change all of that but I'll provide a view that's filtered to just the doctors and so finally everyone's familiar with sequel but it's just view over a table we'll just simple query to filter it down yeah we'd have to change behind a view therefore we'd have to change your code right exactly yeah that's a really good suggestion in general we should do a whole like T sequels you know how do you put a dev with T sequel in this tool and being honest I'm not the best because as with many people who I primarily code c-sharp mighty sequel skills are a lot worse than the people who'll be using this tool so if I do mess up on the T sequel please be considerate on that part I'm not that's not my expertise so the first thing I'm going to do is just add in a new column which is going to be called provider type and this is just going to be the name I could use some set of types here or an enum but what I'm going to do instead is I'll just use a description so it's going to be n VAR car 50 should be completely fine and all allowed nulls because you know for the existing ones were just going to assume anything yet that smells a doctor and I'm going to be using this enquiries so one of the things that I'm going to do is I'm going to add an index on it because you wanted to perform pretty well this makes it super simple so I'll just add a regular index I'm going to put it on this provider type column so I'll put that in here and once I do that and it will come in here and obviously saying red because you know it doesn't know which column I want to put it on so I'll just type in provider type okay so this is going to be the thing that separates eye doctors from physical therapists in this example yeah okay so the other thing and something that's really great add that we can show is um I want to rename this so it's no longer right to call it a doctor but I don't want to do anything complex I don't want to lose my data or anything like that so I'm just going to use an rename function in here so just like your c-sharp projects we have a couple of great refactorings rename is there and it will actually do the rename while preserving all of your data avoiding data motion you can move it to a specific schema you can expand the wild cards which I'll show you or do fully qualified names which is great if you've got some confusion over things so I'll just rename here and call this healthcare provider so this is this is our continuation of spoiling the sequel to deliver yes exactly so once we do this what's great is you actually get to see every word that's affected by that change so you can see here that we already had an index on it it's getting renamed now the constraint that we had is getting renamed and is there anywhere else just another constraint so that's where all the table is used so I'm going to apply that and those changes are added there's also by the way a refactor log X file down here that's where these refactorings are stored since databases are stateful I'm also going to rename this doctor ID but first I do want to make sure it's like hey what's the impact of this going to be so I'm going to go and one of the great things here again spoiling the developer is that I can just find all of the references to this so I know whether it's safe or a crazy idea to do this it's truly able to do that because against part of the solution understands the context of the database engine and again this is just got tables at the moment but if you've got views and stored procedures in particular being able to see exactly where in the stored procedure it's used may be then verify all of your parameters are correct it's great this is been very useful so so here you go and you get all the symbol results just like anything else you can see here that it's used in the clinic appointment and home appointment and if I do look at that I will see here that it's actually a column here called doctor ID so if I do want to change the name on this I'll probably end up changing in a bunch of places which I'm going to do so let's um let's change the name on this so again I'm just going to refactor rename it yep so refactor works at different you know levels mhm yeah we can do it the column level awesome yeah so this is going to be a provider ID okay again we get shown exactly where it's going to be affected it does actually get affect take affect in those things so apply and then because I saw those other ones were there I'm going to refactor rename those as well and just go in here provider ID okay and I think there was one more so let's just find all references again since I am very lazy sometimes and here we go this one okay perfect and if I go in here I'll just go and that should be all of them and so what's great about this is you see how quick it is to make all of these changes oh yeah and there's just a lot of a fear when you're doing this against a live database or what usually happens is you try and make the first change it earns out because of all those other references get the long list of errors and where you run the app and it crashes which is the more realistic yeah now if you have this part of your solution exactly so the validation that you're getting wouldn't I be much greater even without using refactor but your fact is even better that's right I will point out though the one thing that I didn't do here was update the code in the application to handle this so when we're creating our view we're just going to have to make sure that we're still using that that same thing so son that now that we've done that I'm just going to build because I am paranoid and what's great is you get this very quick validation loop through that it works all good happy so developers are paranoid because code is after us yes no when when somebody's trying to break your build that's right so so I'm going to add in the view net so that for the application should be transparent going back to it so again as you mentioned all of the item items really are there so I'll just show you a new item just the wealth of things programmability security all of the latest things if we go to tables and views and what these are just templates right so just get you started quickly exempt everything in sequel that's scrim pool everything and everything has a template so you can see the new memory optimized table since 2014 are there great improvements by the way there and temporal which is proving really popular which is it stores a record of all the changes to your date over time so that's been super proper feature oh I could have used that you know maybe five years ago yeah you guys a catch-up yeah no it's it's a it's great you should definitely try it out and with more features that's right so I'm going to add this and I'm going to call it out of you though and I'm going to call it doctor since that's what it is now representing see if you are in my team I'd send you an email be like you didn't put it in a folder called use come on I know I know but I'm for today we're going to be a little lazy on it it's so lazy but but it's worth showing that and this aspect like there's no enforcement of table structure or anything like that this is purely for like even when you're importing about you can check different import options crimes write folders for tables or other other settings more flat structure if you like it yeah we've got a couple defaults there that that tend to be what people like yeah but no one organized it anyway you want after that yeah so so here again you've got the usual intellisense here so if I just go health care provider it comes in as I mentioned I want to filter this dance I'm just going to do a where clause so it was provider type again I love intellisense yeah and I'm just going to see MIT's doctor but since all of the existing ones are there I'll also do provider type is no and I'm going to seem that anything knows that I'm we're like blasting past the intellisense of this but but this is full intellisense that's smart enough to understand the structure in which your database is created right so it knows that there's a table it knows as a column you don't know there's a view of function again all these different things we could have had in this application and that's how at build time it will validate so if you try and do something where you're using the wrong data type we will flag that will either give an ERF it's going to break or will flag it if if it's not so those kind of really semantic intelligent advisors are great to give you all of those earths and in this case the other thing is that we wanted to change it so we don't want to break the app so we need to keep that original name the same so you can see here you get the list I'm too lazy to type of it so I'm going to use another refactoring which is to expand wildcards which is great you can see it all comes out and you can see provider ID is there I'm just going to format this looks right folks out there in the universe raise your hand wherever you are if you've typed this out manually yeah yeah we've all tried yes this is great and then all I need to do here is to keep it all together I'm just going to bring this back to be doctor ID I will escape it which is just a good practice as you can see we try to enforce that pretty heavily as well to avoid any strangeness and there we go that that's our view um one thing I actually forgot to do before so before I publish this I'm going to want to validate this I'm just going to move some data across very quickly from the other server so again this can be hard right to D&E is yes unless you have the right tool I mean this is a lot of manual work exactly so I just want to verify that this happened this is going to work before I make any changes it's the best time because once I've changed the structure it can be a little hard and it opens so there's a couple of things to say here first of all if you needs data right to be functional in this particular case so that makes sense we need to bring the data over but the other thing is that all of this change that you're making here locally you can go and see which tables you modify which you know files a c-sharp files you've modified you can see the changelog and so this is still part of your of your change said that you could decide even to undo if this goes really badly and I've had this happen to be one time in my career where I went down the rabbit hole of like you know six hours of changes and it stopped myself and I said what am i doing this is this is the wrong approach and an eye on the checkout and everything including TC carries in the SS DT and it was just so beautiful I mean I made a local copy just in case I wanted to go back to Phase II but you you have that flexibility and if you had your SSMS window open in your Cheney's sequel somewhere even if it's in the first control becomes this ya can be so hard to undo you just don't know what you're doing where is here we've done all this and and yet because we're offline nothing's happened yet we've gotten all the same validation we would have but but it's all just ready to go validation Siri changes are you can decide to check it in it's part of your work items Association all that exactly and when you are setting up and bootstrapping your test environment you're usually going to have to pull over some data so I'll just show you very quickly schema compare will hopefully get to although we're running a little low on time data compare data compare lets you compare the date to databases now just this is relatively new right doesn't this has been Amy's been around and tell people more about this because I think this is one of the most hidden things and this is the G is really cool feature yeah it was it's an interesting story it in the older older versions it was there then briefly when we came out with sequel server data tools as its own offering it was it wasn't there but but within a few months we caught up we were certain developer named Dmitri BER numbers then yes come on I actually do there I'm sure you guys had your reasons yeah yeah there was it was just a lot of work a lot of changes and so it just took a you know we wanted to make sure that we got in first equals Visual Studio 2012 screw this up right you can scrub data compaction you don't want people's data being wrong because Microsoft in it yeah but it does like you to compare basically all different types of records I'm just going to choose the target which is that other database that I created in local DB here that's the one hopefully unless I oh well the good news is I can always hit cancel which is great yeah and I will go and browse back to it on my hat local DB instance and make sure that I have the right one as far as like demos go on toolbox I think you've done really well not too badly we're so jinx that never mind it says giggle bad now ah well we'll see I mean the good thing is we did change this up so that it doesn't freeze anymore which is great so got to show that you can just cancel out of things very yeah yes so now that we have that the thing to point out is the tables do have to be the same so they have to have the same key for comparison purposes and and once you have that you can list them I'm going to compare everything but if you have large large tables that are say a hundred gigabytes in size yeah this could take a while yeah so do we consider that a be considered if you're comparing against as your another cloud provider as well that this is pulling it onto your own machine so you know if what it goes do dress and yes exactly so just be aware of that but obviously locally it's very quick to show it and what you can see here is all of the the differences so if we click on this one you can see that as you'd expect we didn't put any data in the new one you've got a bunch of data in the existing so this is our original database here in the source and then the target is the new be exact fade in that it hasn't exactly and so what I want for for my testing is just a couple of these so I'm going to start unchecking some of them and what I want and I'll just make this a little smaller since the screen is bad is I'm just going to do doctor and I'm going to do the tenant ID which I discovered earlier is the thing that everything else refers to how about to us that probably needs old all the things that's Reverend doctor was repeating so the data can be inserted in a bad way exactly so so it does it does know to order the tenants first but it will it won't on it won't make it be checked so that is something to be aware of but if I now update the target and hopefully this will work is it earlier great if I hit refresh what you'll see now is for the doctor all the things are identical which is great and again if you've made changes it will just show you the changes and it's showing zero or not because there's no records it's showing zero because there's no differences exactly - exactly target and also you could have generated script here and so if you again want to take that script based approach or just you're going to hit that button look at the script then just hit update it's yeah you can always do yeah and it's great for reference data if you want to have your scripts generated for you once you put some of it in it can be great way to just yet script at that reference data huge okay so so we're done with data compared now we've got our data across and I can actually start testing my view so let's see what happens before I make my changes so I'm going to make the changes to the database over here I'll just hit refresh since we added it great and now that we've got that in there if I look at the doctor table again viewing data is very simple it's super easy to edit this which is really great and you can see all the data there there's a bad eight rolls worth of data so hopefully whatever we've done will make a rose worth of data changes so here is my publish for I believe local DV you can rename this great so I'm going to start trusting this and I'm just going to hit the magic publish button the magic button yes so this does all the same steps so you still have a preview to give you the high level summary you still have the script but it will just automatically publish it at the end and tell you all right it worked yeah what one thing that I remember myself like missing it first was these blue links on the right here if you have especially realize I have one of those lights in a city for you displays a white screen this could be free far in the right clicking those those take you to the individual scripts and such but again they are great especially the preview I like because I don't have to figure out the sequel I just get a very short summary but something that makes sense and again if you look at what happened there by the way actually we should go into that briefly and we renamed these four things yeah we altered the table and we created the new things and it did it all in the right order if there's anything complex it will say drop constraints do all the things it needs but in this case if I just see the script it's pretty clean script which is great we're usually pretty smart about these things so we rename the doctor rename it at doctor ID to provider ID so a bunch of just very simple renames and that's something sequel server supports that will just change it without any data movement or I mean and it's a very verbal script rather generates it does all the print statements so it's running eggs SSMS let's say let's say you give it over to your DBA whatever your teammate they can see what's going on I really love how this tool is yeah and it is smart enough to just alter the table after that create an index on that then create the view and it does it all on the right order which is really great and this bit at the end is just a the refactor log so that traces the steps so if you try and apply it again it knows that you these renames great so that was awesome and now I'm just going to testing so I wrote this and hopefully it will work so we can now view if I just refresh this I should see that my doctor has gone away and health care provider is is here instead so I'll just feed the data on that as you can see it still has all of the data yeah but what's funny is this one hopefully this will work and if I hit refresh on this oh no it's not happy with that because it was a table yes because it's doctor yeah interview but if I go down to the views and views are also editable the same way which is great and and I goes actually a good error because it doesn't assume that just be just because yes and they would name the same yeah that it's the same thing and it's a valid scenario it's like look there's no more doctor table yes go bring a view version of this side exactly and so now I'm just going to start muddling with this data a little bit so what I'm going to do is I'm just going to make sure what I would do is make one of these a doctor and make sure it still works once I tab off of the row it will just automatically commit it up so that should still work and but now if I change one of these so that they're no longer doctor but instead a physical therapist what I'm hoping for is yes we'll see how close it is yeah we can fix it up later and if I go over here and I hit refresh you'll see that it's missing at row 7 yeah because it doesn't mean you query right exactly so so so what's great about this then is that everything else is the same my application will continue but now I can add a new page is very simple based on the other health care provider type and I can expand my app add I'll check this in now and and go from there and I've just one last feature to very quickly show which is a which is schema compare so so as with everything you can see your history over here all the objects that we have so I'll just say refactored to support against spelling is fun when you're typing live so if I was graded and spelling from my job I wouldn't be here there you go we all have to be good at yeah so I'll just close you down and then there's just one last thing at which we mentioned would be useful for everybody so this has been really powerful and you can see how this all works and if you're using entity framework you just need to not do the auto migrations and instead I have part of your script be that you when you're publishing just publish this beforehand yeah but I mean I think this tool has like various use cases right you can easily just can do a sanity check from one environment to the next from one local database to the next you can use it to replace migrations you can use it in some instances where something went wrong you know there's all sorts of I think yeah awesome news great that's it and and so the last one then is is when you do want to be able to understand things and see what's changed one really powerful thing is schema compare so you get your history through get our two TFS integration you get that great history file by file but what you don't know is headed that compared to the real database and here you've right clicked on your and your databases if you project now has these various sequel related that's right and so schema compare will let you compare the your project to any database to databases to each other or even those backpacks we mentioned extra that you know so somebody gives you a random DAC pack you can very you can actually use this as a DBA to actually verify what's going on and understand all of the changes that are going to happen in a very simple intuitive way yeah and I've done this in real life like I in again my own development like I'm just one guy working on the solution so I keep all my DAC packs from every deployment and I just did it that way at the time wasn't using EF migrations and I and I had an issue and I had to compare back to a previous deployment I did and I found out what measure was like so quickly otherwise I'd be sitting there like what changes do I have imagined it's not even source control oh my gosh yes or is the zip file somewhere and some sharepoint site you have you ever had that I I have and actually I'll just show you so I'm just going to choose this database first and I'm going to go up to my original database over here on asp.net and hit OK I'll actually show you one just quick tip and technique for how to make that handy if you do want to reference a couple like say hey here was my original or major impactful changes to look back you can very quickly go and where is this hidden it there should be snapshot so snapshot will just create a dot pack which is the backup at this point in time before you you can then that have that checked in and that's great if you want a bootstrap it for these kind of comparisons so if you're and it's based on your project raise them pretty crazy here you can also create the same snapshot of a database inside the environment and I've had that in Reverse where again like man I've worked so much of this till I forget how my chair would this is DT there was a scenario where the production environment is having an issue I got a snapshot from the DBA I didn't have access to the environment I compared it and right away I saw something was missing from a deployment script for somebody think it wasn't didn't make sense but sometimes you just get a fix the issue first yes and then keep going right exactly yeah no so it's really great it will work against pretty much any database you have if you just want to quickly like that get it out in a in a pretty secure way small it's simple yes ma'am so that's snapshots a bonus feature for people since I hadn't planned to show that yeah and now when we do schema compare the first thing I'll say is I really hope that this looks good because on my machine is the only one that sometimes doesn't show this by default but it was friendly to me and decided to be nice which is great okay so this shows all the changes that have happened so you can see here that this has changed the provider ID the constraint just a regular you know diff viewer here so you can see exactly what's changed see the the darker color is where something is is different on both sides so you can see that these have been altered you know altered again here with slightly more changes and this one here again and again it's it's smart enough to know it was health care providers named dr. yeah and finally on the view this is a brand-new view so yes the icon indicating here like we have a pleasant show then this is new you have a little edit like you can change one one day we make that a computer or keyboard if people forget what pens are eventually yes the other cool thing is that update button right like you can change a full fix they issue there yeah and so you can update it if the target is a project or database you can directly update it as with everything you'll start noticing there's always a script button there so if you're worried it's always there and and again there are a huge amount of options again for this pretty much very similar options um oh the one part that I would like to show as well is what happens if you try and disable something so if I try and undo this it will get unchecked and so different ones like this this is great so I've used this when I find the change but that's not the change I want to push like maybe I'm in the middle of two things you know the one thing that's keeping me from when you forward some test environment I just unchecked the thing that I'm going to fix later it's not breaking so custom empowering the user right that's what I'm trying to do yeah so that's it and that's really at the high-level tour of most of the features and just a couple things that this is you know a small part of what this does it's got a database unit testing the ability to add in advanced static code analysis rules some of which which is extensible so you can pull in if you've got your own set of business rules you can embed them as logic into your project so that whenever you build you'll get flagged if you've got naming issues etc among you know many other things here so so do check it out and and and also if you are still a sequel server management studio user I also realize that the latest version of that is ID as well it's it works again monthly also works against again older versions of sequel server all the way back really well and and so you know the sequel tools are thriving well worth a look and and this will hopefully help you in your day-to-day development all right Kevin thank you so much for being in tool box and thank you for everybody who watched it I hope you find SSD T useful we'll put some links to the tools to the example we show to anything that was relevant we'll get all up there and you know we'll have you back on there's a lot more to talk about yeah right thanks very much thank you

Channel: Microsoft Visual Studio

Views: 46,650

Keywords:

Id: 9WH_r7M4QhM

Length: 52min 16sec (3136 seconds)

Published: Wed Oct 26 2016