Everybody does things a little differently and this is one of the things I LOVE about the podcast — chatting with others about how they get work done. In this episode I chat with Tom Norman about how he uses Visual Studio for source control, deployments, and keeping his sanity. We explore the setup process and how we have a reverse 80/20 rule when it comes to importing databases with Visual Studio.
“It’s not server-based, it’s Database Project, so you’re going to import your database and it will actually bring in all of your objects.”
“The golden copy really should be in your Source Control, not in your production server.”
“There will be challenges, but don’t let that stop you from moving forward. The benefits outweigh the effort.”
“It provides more transparency to the entire process.”
Listen to Learn
01:31 Compañero Shout-Outs
03:24 SQL Server in the News
05:45 Intro to the guest and topic
06:22 Making changes in the environment with scripts is devops
08:27 Keep lookup data in Source Control and be logical with your scripts
11:48 Using the Community Edition of Visual Studio to do migrations in Source Control
13:04 How the DBA can get objects into Source Control and get their team on board
17:07 Security, database roles and groups
20:56 How this helps DBAs, whether or not they’re “lone”
22:50 Branching and merging
25:53 Tom’s deployment process
26:58 Does your code know which environment you’re in?
29:57 Tom’s process development
32:53 Ramp-up or starting time?
35:48 There is a partial script to pull your security out for you. Use it.
39:54 If all the machines in all of your environments have the same configuration, there are fewer errors
41:30 SQL Family Questions
45:35 Closing Thoughts
Kenneth Fisher’s crossword puzzle: https://sqlstudies.com/2015/08/03/sql-crossword/
About Tom Norman, Database Architect / DBA, KPA.
In 1998, Tom changed his career focus to begin working with SQL Server. He has worked in all aspects of SQL Server including Administration, Database Development, BI and Reporting Services. He has worked in the Finance and Compliance industry. His experience has included International deployments. Tom is the Leader of the PASS Virtualization chapter and the past President of the Denver SQL Server User Group.
Music for SQL Server in the News by Mansardian
Carlos: Compañeros, it’s good to have you on the SQL Trail again! This is Carlos L Chacon, your host, and this is Episode 136. Today we’re talking about continuous deployments. This is an extension of a conversation that we had with John Morehouse in Episode 128. Tom Norman, who is our guest today, approached me and wanted to chat about his experience using Visual Studio. Some of the nuts and bolts of that experience and that ability to be able to move data or objects from one environment to the other. Not only up the chain, but down as well, and so we’re excited to be talking with Tom. Tom is out of Raleigh, North Carolina and currently serves as the leader of the PASS Virtualization Chapter, so we’re excited to have him on the program today.
We do want to give a couple of Compañero Shout-Outs. The first to Tahir from the United Arab Emirates. Kevin Feasel, a regular on the program, but actually came up to Richmond from Raleigh to present, and so it was nice of him to do so. And Nicki Tinson from across the pond. Nicki is actually going to be on the program here, in a little bit, and we’re excited to connect with her and to be able to hear from her, here, shortly.
I do want to give an update on the SQL Trail. So compañeros, for those who may not know or who are new to the program, October 10th through the 12th, we are hosting a SQL Trail event. This is the East Coast gathering for data professionals that work in the Microsoft Stack. We’ll be doing that in Richmond, Virginia. On the 10th, we’re going to start at 2pm and that first day is ultimately dedicated to getting to know each other, connecting, trying to find your people and also choosing sessions or content that we’ll be talking about the next two days. The 11th will be, I won’t call them traditional, but they’ll be what you might expect from a conference in the sense of sessions or presented content. But on the 12th we’re going to be doing some hands-on labs and I can go into more details next week, however, I can say that it will center around Azure Data Services, and so we’re really excited about that. We do have a couple of other things that are in the works, and hopefully we’ll be able to announce those soon. Registration is now open and again, we are limiting this to 40 people. We hope that you’ll take advantage of this. Come on out, come to Richmond, particularly if it’s a little bit closer for you and you can’t make the journey out to some of these other places. We’d love to have you and connect with you there.
For a little SQL Server in the News! Now compañeros, I was a little bit stumped by this, at first, because it talked about SQL Server running on SUSE Enterprise Linux. Now SUSE was actually one of the first Linux offerings that Microsoft had around Linux, so I wasn’t quite sure that I understood this announcement until I dug a little further, and now what they have done is SUSE is actually giving away, if you want to run SQL Server on SUSE, you can do so for free for one year. They will wave all of the fees associated with the software and patches and you get free patches for the year, if you’re going to run SQL Server. I’m not sure exactly how they’re going to decide whether you’re running SQL Server or not, but you can use that free for 365 days. Now, they are giving a discount on the SQL Server software, so you still do have to pay for that, but it’s 30% off. So, if that’s something that you’d like to try or you’re using in your environment, that might be something interesting for you. I am interested to know how– they’ve talked about the number of downloads they’ve gotten from the Linux repository, particularly in containers, was the number, and they say five million downloads, so, five million docker pulls for SQL Server 2017 on Linux. Obviously, there could be more installations of just SQL Server on Linux, but from the docker perspective, obviously that’s one of the key metrics that they’re looking at. Obviously, they’re seeing some adoption, whether that’s people just testing it out and whatnot. I am interested. No one that I’m working with is currently taking that route. I know some of the, Tracy, who’s been a previous guest on this program, they’re doing quite a bit to try to help those who want to learn more about SQL Server on Linux and some of the challenges there with then having dual environments. I know there are people trying to tackle some of those problems, but I am interested to kind of see who those customers are that are adopting it. I think a lot of them already have Linux. I’m not sure that this offer is going to convince anyone that they should go ahead and migrate to Linux, but it’ll be interesting to see what happens. The offer from a SUSE perspective is 365 days. You have to go through Microsoft to get it by the end of the year, if that’s something you want to take advantage of.
Our show notes for today’s episode are going to be at sqldatapartners.com/deployment or sqldatapartners.com/136. With that, let’s go ahead and get into the conversation with Tom.
Carlos: Tom, welcome to the program.
Tom: Thanks, Carlos, it’s a pleasure to be here.
Carlos: Yeah, it’s always great chatting with fellow compañeros, and particularly ones that come to me, like, “hey, I heard this episode, but I feel like we missed this certain segment.” Or, “there’s another take on it that I want to give,” and so that’s why we wanted to have you on today. One of the scenarios that you present is that I have multiple environments and I want to make changes to those environments, and I want to do so in a controlled way via scripts. So, that could be dev, QA, UAT, prod, whatever environments you have, I want to be able to do that with the scripts. Now, that sounds a lot like devops and that’s kind of our subject today.
Tom: Well, it definitely is devops. I never thought of it that way. I’ve been doing this for over 17 years and it’s very important to me to make sure that when we’re testing and deploying that we are going after and doing it the same way that we’re going to present it in production. Because if we’re not, we’re not fully testing the products.
Carlos: Sure. Now, when you talk about testing the product or testing your code, you run the gamut as well. We’re talking about objects, the normal suspects, tables, views, jobs, policies. But you also talk about users, so security, and then you’re also talking about data in some instances. I think that’s one that can be a real gotcha or pain point for a lot of folks.
Tom: Yeah, in our environment, we actually, I fully believe to really test the product, that you need to obfuscate the data and bring it down out of production. Of course, there’s some caveats there, depending upon the size of your database and how frequently you might be able to do that. But the thing of it is, we all know that the scenarios, as a dev person will say, “well, it works on my machine”. But there’s certain data aspects that will also cause your code to blow up and if you don’t have those in a QA environment, or being able to replicate them, then you have a hard time alleviating the bugs in production.
Carlos: Sure. Now, principally, we’re talking about lookup data here, right? We’re not necessarily talking about the transactional stuff, per se, although I’m assuming that you could have some default code, perhaps, in there. But really, what we’re talking about is things like those lookup values that need to remain consistent, or I think a good example is codes. So, if I have, and maybe ‘products’ is a little too general, but department codes might be an example that I need to keep consistent in the environments. This is the type of data that you’re talking about keeping in Source Control and deploying when you make changes or updates, right?
Tom: Sure. You’ve got some reference data within your database and it may not be able to be administered through the UI of a product. So, you could definitely create scripts that would, when you create them, you would actually make them where they could be repeatably deployed, and that’s a very important from the standpoint because that script that you run, very well may run multiple times. So, you would basically go check, for example, to see if the product code or department code was already in the database. If it is, then insert it, and you might possibly update it if there’s other fields, also. So, you’re kind of looking at those things that you might want to update the department number, for example, unless that’s your primary key, and then you, of course, would leave it alone.
Carlos: Sure, or they move, you get a new building, things like that.
Tom: Right, exactly. You know, location, and stuff like that.
Carlos: Now, this is one of those things where I think maybe, I don’t know if eye-opening is the right word, but I think a lot of times, we’re looking for an easy button. I’m not saying it’s bad, but if you’ve used the Redgate Data Compare, I think we get into that mode as administrators, like, “oh, well I’ll just move the data over that way”. This requires a slightly different thinking process to that. I think if anyone’s ever used that to look to try to manage data between environments, even if it is that lookup data, you know that it doesn’t take long for that to get all gobbledy-gook, because somebody inserts a record, and doesn’t go through the compare process and now all of a sudden, IDs get mixed up or, particularly if you’re using identity columns. That’s the big one. And then if that reference data then gets used, then it’s very difficult to keep a handle on, and so I think what’s interesting here is that you talk about that lookup data, you have to have some logic in it. Your scripts can’t just be inserts scripts, for example. You have to do some checking, some interrogation of that. There has to be some logic in there.
Tom: Right, there definitely does, because you want to definitely have the same data going all the way through because a lot of times your .net code or your developer is accessing that identity column. If the numbers are not pulling back the exact same data, then you could have a problem there. You very well may turn around and do a SET IDENTITY_INSERT ON to basically make sure that you can control the identities if that’s what you need to do.
Carlos: Right. Now, maybe we jumped the gun a little bit here, but folks might be saying, “well, hey, I can’t do this. I don’t have the right tool sets to be able to do this.” but one of the reasons I wanted to chat with you today is that you’re doing this in Visual Studio, and I think, unless things have changed, you’re actually using the Community Edition of Visual Studio, which is free, to integrate with your source control to be able to do these migrations, is that right?
Tom: I am using the Community Edition. Of course, you’ll have to look at your company and see what your licensing schema is. I actually have MSDN. I could run Enterprise, but I prefer to run the Community Edition, because as a database developer, not a web developer, I only need a few aspects of the code.
Carlos: Sure, just doesn’t get quite so large.
Tom: Right, and the main thing that you need if you’re going to be using the Visual Studio would be SQL Server Data Tools, for short, SSDT. You would download that for the particular version of Visual Studio that you’re running. That will then give you all of the things that you need to basically create a very nice deployment pattern through Visual Studio.
Carlos: So, we’re all familiar, or I guess when we particularly think about devops, we normally think about going from the lower environment to the higher environments. So, what would somebody need to do, or what steps would the DBA need to take to first get their objects into Source Control and maybe help getting their team on board with using this?
Tom: It’s a very nice tool, Visual Studio is. Once you actually install it, of course, and get SSDT in there, you can go up and basically do a file import your database and when you import your database, it will create a new database project.
Carlos: Right, you’re going to get one project for every database, if you had multiple on on the instance?
Tom: Yes. Yes, absolutely. You have one project per database because it is Database Project. It’s not server-based, it’s Database Project, so you’re going to import your database and it will actually bring in all of your objects. There’s several different options you can check. One of them I like to check is to take the case identifier off and when you look at your project, you’ll go to the properties of your project. The case identifiers, basically, this is one of my pet peeves with Microsoft is that in Visual Studio SQL Server Database Project, they’re case sensitive by default and SQL Server Management Studio or SQL Server itself is case insensitive. So, when you bring those objects in, because you’ve brought them in the first time, you’re good, but as soon as you start using them, and if you type capitol I, capitol D, it’s not the same thing as capitol I, lowercase d, so that’s one thing I check off. But you can very quickly insert your code into Database Project and the really cool thing is, if you get your Source Control also hooked up to it, whether it be TFS, Git or whatever, you can check the box at the bottom and it will automatically put your source code into Source Control.
Carlos: Ultimately, that’s one of the reasons why we’d have to be doing this or that may be one of the goals is so that we can then have this golden copy, basically, or a way to be able to compare what it is we’re making the changes to. We don’t always want to be comparing back to production, we want to be comparing, really, back to Source Control.
Tom: That is very true. When I’m presenting this class at SQLSaturdays and stuff, it’s amazing when I ask the question how many people have their database code in Source Control and it’s very few, and that’s kind of alarming to me because the golden copy really should be in your Source Control, not in your production server.
Carlos: Right. I think there are some challenges. Tables and views are one thing, users or security is another complexity. I know it was for me. For example, when we had a project and we were trying to do this for our customer, we actually just went ahead and left security out just because there was kind of gobbledy-gook. We didn’t do a great job of trying to, I guess, harmonize some of that, so that’s, I think, a common obstacle that people will get to that point when they import their database, they’ll start seeing security errors and they’ll be like, “eh, forget it.”
Tom: Yeah, and that’s especially true if you’re using lease writes and how we handle database security is really a very good way. That is, basically, that all objects that you are assigning security to, you assign them to a database role. Never assign them to the actual service account or group or person. Assign your permissions always to a database role. When you do that, what that allows you to do is when you go between environments, I could turn around and take the service account that’s running the QA server and assign it to that database role. Then I can take the service account that I’m running the product with to the UAT server and assign it to the same database role and then prod the same thing. What this allows you to do, basically, is that you’re then applying the exact same security all the way across the board, which gives you the same testing platform so you don’t end up with security issues in production that you haven’t already faced in UAT or QA.
Carlos: Right, because it’s all role-based there.
Tom: It’s all role-based, yes.
Carlos: That’s another great point. I think one of the obstacles that folks will run into when we think about putting database objects into Source Control is that their processes may have to change. I think security is the great example of that. If you’re used to giving permission to users, then you’re going to run into a few more obstacles, particularly if those users change in the different environments. I think ultimately, as you just mentioned, the benefit is easier ability to be able to test that, and you then get to control who has access based on those roles. But again, it’s another set of process that people have to go through, and that can be challenging.
Tom: That’s very true. It’s almost like when you’re assigning security in the database, if you’re just assigning security, Carlos says, “I need access to the database”. Well, I don’t want to give Carlos personally access to the database, I would rather have him an AD group. Even if he’s only a group of one because you always know someone else’s going to come back later on and be added to that same group. So, I’m looking at things from a group level and that’s really what a database role is. You’re really looking at a database role as it’s a group that’s going to have this access.
Carlos: Yeah, now does that include the default roles? A common one, which I think a lot of people, at least if they’re going to use roles, they’re going to look at something like db_owner, is that an acceptable role or is that a role that you use for these permissions?
Tom: Oh, absolutely. I do use database owner. I wish I had more lease writes within the database, but as we all know, if you give them database owner at least they can only destroy their own database. They can’t destroy the whole server. I use database owner, I use database reader, I use database writer. The interesting thing, Carlos, in my UAT and prod environment, I basically have a group set up for database owner, read/write and read-only. What that allows me to do is that if someone needs to be escalated to see a production issue, then they can put in a ticket and get into that group and I don’t have to do anything as a database guy. I can be on vacation and “hey, just throw them in the read-only group, I’m good.”
Carlos: Yeah, you’ve then extended the team, if you will, so now the Windows folks or whoever can help with that. Again, you’re lessening the role that you have to play for some of these deployments, should there be a problem.
Tom: Right. I know that as database guys, we like to take vacation and at least, I like to disconnect from work when I take vacations.
Carlos: There you go, you might be that unicorn there, Tom. One thing I guess we should point out, you’re a lone DBA, correct?
Tom: I was until January. We hired a second guy. But I’ve been a lone DBA for over 5 years, so without this process in place, there’s no way I could keep up with everything. When I first walked in the door, it was an utter mess. They couldn’t even tell me, they would have three stored procedure scripts of the same thing labeled 1, 2 and 3. I’m going “which one do you deploy? Which one’s going to production?” They couldn’t’ tell me, so I really had a real mess to clean up.
Carlos: I’m curious, because I think that one argument you might make is that in a lone DBA scenario, I don’t want to say it’s easier, but there’s less people. You’re kind of a kingdom of one or a fiefdom of one. Now that you’ve added a second person, challenges that you’ve experienced or now that you have this process set up, has it been much easier to integrate a second person?
Tom: Bringing him in, at first, he was kind of going, “okay” but of course that was part of our interview process, too, as I was asking him about it and stuff. But now that he’s used it, he loves it.
Carlos: Okay, for sure, so not everybody’s using it, so “this is our process, or the way we do things.”
Tom: Right, and the thing of it is, a part of devops, you’re going to have to merge and you’re going to have branch when you’re coding things because you’re going to have hot fixes and you’re going to have long running projects that might take longer than a sprint. So, you know, we will actually branch and merge and then of course, in our environment, we have continuous integration and deployment into our QA environment with it. So, when we check something in, you’ve got to say, “okay it’s ready.” It’s going to go to QA at that point.
Carlos: So, branching and merging. I think more and more of the DBA folks, of course the developers that listen to this show, that’s old hat to them. They’ve been doing that since they were freshmen in college or wherever they learned their coding. But this can be a challenge for us knuckle-dragging Neanderthal DBAs who didn’t come up, I mean I definitely took some coding classes in college, but from a branching and merging perspective, this is kind of new to me. So, challenges that are there when you think about branching and merging. When we talk about branching, what we’re doing is we’re taking a copy and we’re saying, “okay, I’m going to work on this apart from what is currently in Source Control,” or the current version, you might say, “and I’m going to start making my changes there.” What challenges have you found from a database perspective, when you’ve started to do that?
Tom: Well, branching and merging is really a hard concept and it’s one that I struggled with, at first. How do I make sure I don’t step on someone else’s code? You’ve got to look at it that what we do in our shop is every little feature or every little thing that we go do, for example, if I wanted to go write some code to say that the email has to be unique when it goes into the database? Well, if I write that code, I actually pull a new branch off and label my request or my change and say, “okay, this is going to be to do a unique merge” and I’m pulling it off of a development branch that is ongoing. We then have a master branch, basically the production gold copy that’s out there. But the development branch is basically the one that’s ongoing that we’ll cut and slice off of when we are ready to do a release. We basically are, constantly, for every little feature, we’re breaking off that little piece and then we’ll go work on it, get it running well on our local machines and then we’ll go in and actually merge it back in. Then we’ll do our integration testing at that point.
Carlos: You like to keep those as small as you can? Those change sets as small as you can?
Tom: Yes, as small as you can. Keep those change sets very, very small. Go do whatever you need to make that change, and then check it back in immediately and then going. In that way, you reduce the amount of branching or merging that you might have to do.
Carlos: Right, and it’s easier to keep in mind, when you’ve worked on it, “okay, now this is what I’ve changed. Let me push that back in.” So, when it does come to that merging, you’ll know what’s important and what you can say, “no, we don’t need that stuff.”
Tom: Right, exactly. It also gives your product managers the opportunity to turn around and say, “I want this little piece to do.” So, we’re an Agile shop, so we work on small chunks and try to release them as quickly as we can.
Carlos: So, then one of the benefits, or one of the hopes there, is that the deployment then becomes easier. Again, we were talking with John, he mentioned his deployment process, “hey, I’m leaving, call me if you have problems.” What’s your deployment process look like?
Tom: My deployment process, we are actually using Octopus, so once we check it in, like I said, in QA, it automatically goes, we check it into that dev branch, it automatically goes over to QA and they can start looking at that. Our UAT environment is more controlled in the fact that what they will actually do is when we are ready to go to UAT or they’re ready for the user substance testing, they’ll hit a button to say basically, “go deploy this code to it,” and it’ll go deploy it. It’s the same thing with production. When we’re finally ready, everybody says, “yes, we’re good to go”, then we’ll hit a button and basically push everything to production, which makes it very nice because it’s basically one button, go. Push it.
Carlos: When we talk about deploying to production, one of the things you mentioned that I thought was interesting, ingenious, but again requires another mindset shift is this idea of, “I want to know what environment I’m deploying to,” beyond just the, “hey, deploy to this environment,” so when the code executes, the code needs to know what environment it’s deploying to. So, you put in some checks there, to make sure, “hey, am I in dev or am I in QA or I’m in prod, I might act differently.” Maybe that’s just post-deployment, so maybe I’m getting ahead of myself. But talk to us a little bit about that idea of your code knowing which environment you’re in.
Tom: I definitely want to know the environment that we’re in. I have a database in each one of my environments called the admin database. It’s my database as a DBA. Basically, in there, it will tell me the environment that I’m sitting on so that I know if there is some script that I want to run, whether I want to run it in that particular environment. A good example would be an obfuscation script, that even though the obfuscation stored procedure sets in the product database, everybody’s thinking, “oh my word, what if you run it?” Yeah, there’s actually an environment check right at the very beginning of it. It says, “if this environment equals production, return, get out, immediately.” That’s the first thing that’s in the code base that you see is that little environment check. But there’s also the environment check also in Visual Studio that basically says, “am I in dev, QA, UAT, prod, whatever the case may be?” because in that particular environment, what you’re doing, basically is determining what security am I going to apply to that server? Am I going to apply this QA security or am I going to apply the UAT security or am I going to apply the dev security or production? It’s just a flag that you’re kind of flipping. It’s almost like, my new DBA that’s helping me out, he turned around and said, “hey, Tom, if you put this debug flag in there, and then turn it on and off, you can actually put code in there to basically return stuff to the screen in a stored procedure.” I was like, “oh hey, that’s smart. Why didn’t I ever think of that?” So, this is just another way to turn around and determine where you are, because sometimes you really do want to know what environment you’re in before you run something.
Carlos: Sure. Did that ever give you the heebie jeebies, putting that obfuscation script in prod?
Tom: Oh, it does kind of give me the heebie jeebies, especially when I have to update it, and I’m putting it in production, I’m going, “uh, you’re not going to run, are you?” But no, I’m very comfortable with it. I’ve actually been doing this type of stuff, believe it or not, for 17 years, so yeah, I’m dating myself there, but yeah, I’ve been doing devops-type of stuff for about 17 years, now.
Carlos: What about, we’re going to call them ‘special objects’, supporting objects. We talked a little bit about jobs, which would be at MSDB Objects, but are you doing the same thing with SSISDB? Packages, basically.
Tom: Yeah. We actually have within our SSDT Database Project, we actually have our SSIS packages, we have reporting services packages. We don’t do any table partitioning yet, but our goal would be to put the table partitioning scripts. My take on it is, is that anything within the database should have a script and should be checked into Source Control, because you never know when you’re going to have to do a DR or rebuild that thing. The nice thing about especially security is, I know what security is supposed to be on there, because it’s scripted and it’s within Source Control.
Carlos: Now, security, I guess would be one exception, but what about the argument, “well, if I had a DR environment, I would just restore”?
Tom: Are you going to restore master in there? That’s a little bit of a challenge, to restore master or MSDB or SSIS. Hopefully, if you are going to restore, you could definitely do all that, but you would have to make sure that your versions are the same, your service packs are the same, you’re on the same CU level. Do you have that all documented? Most of us, I would say, don’t have it really documented the way we should.
Carlos: I’m sure you’re right. I am curious on the packages perspective, because SSIS packages are stored in xml, it’s very difficult. I haven’t seen a great example of a comparison, like a table, for example, much easier to say, table on instance A has one column that table on instance B does not have and highlights that for you. In the xml, the package is much more difficult to do that. Are you trying to compare code or package versions at that time, or is it really just a “hey, I’ve made a change, I’ve got to push it up”?
Tom: It’s more of I know that my golden code is in Source Control, so I know that the right code is up there. And because I know the right code’s up there, I just change it and I just push it. I don’t try to do a comparison.
Carlos: There you go, because you could always go back if you needed to.
Tom: Exactly, and that’s the beauty of Source Control that people really don’t see. Hey, if I really just screw up a stored procedure or a SSIS package or whatever it may be, whatever object you’re working on, you can just go to history and go get the latest version and start over.
Carlos: Yeah, interesting. One of the things we did talk about with John was ramp-up time or starting time. You mentioned you’ve been doing this for 17 years, tell me how long it took you to get set up, get started. Obviously, this is, I won’t say ‘evolving’, but a continual process. You wouldn’t necessarily say that it’s done, because again, we’re calling it devops, where it continues to change. But what kind of time do you remember it taking for you to say, “okay, I get there, I have stored procedure 1, 2, and 3,” to “okay, here is the process that I’m going to use to release to prod”? What kind of timeline are you looking at, there?
Tom: You know, using Visual Studio itself, I’ve only been doing that one for about a year and a half. I’ve been doing the devops stuff, because I roled my own about 17 years ago. The thing of it is, what really got me to where I said I’ve got to come up with some kind of consistent deployment process, is I was tired of going to production and having, we’d go to production, we’d have an error, or we would have all this one huge script, we would do a last-minute change to a stored procedure in that big old script, and guess who wouldn’t put it in there? Me.
Carlos: You’ve got 50 changes that you’ve got to make and then you’ve got to go and figure out, “okay I’m making a change to change number 27, now I’ve got to make sure I get all that write stuff correct.”
Tom: Yeah, and I got tired of doing that. I was going, “there’s got to be a better way”, so that’s when I just started delving into, “how do you go about doing this?” You know, if you really go start studying it and you start kind of looking at it, whether you use a Redgate tool, whether you use Visual Studio or whatever, there’s got to be a better way. You’ve got to realize that your deployment, to be successful, needs to be very, very consistent. If it’s not consistent throughout the environments, then how are you really testing what’s going into production and preventing production errors?
Carlos: Yeah. That migration to Visual Studio, then, approximately how long did that take you, would you estimate?
Tom: Considering that you can right click import your script, I had it all within Visual Studio within a matter of two hours.
Carlos: Yeah, okay, that’s step one of a thousand.
Tom: That is correct. That is correct. But it gets you probably 90% there. That’s the interesting thing is it does get you about 90% there.
Carlos: Well, if you’re willing to ignore security, and like we talked about, the lookup values, the reference values. I feel like those are the hard things to get there. I guess I’m looking for a total time, total project time getting all of that stuff in.
Tom: Right, if you start adding all of the other stuff in, it took me, I started with security. Security, the nice thing about it is, we do have a couple of blog posts that basically will show you how to do security. They actually give you a partial script to go pull your security out and do it for you.
Carlos: Oh, very nice. So, I didn’t read that entire article, but getting into that, I know security probably warrants its own podcast discussion, because it can be a challenge. But that’s very interesting to know that there is a script that will assist you in that.
Tom: Yeah, there’s actually a partial script that will assist you. Now, me being the DBA that likes to make sure everything’s okay, I hand-coded it. Now, that took me a while to do, to get all of the security there. I probably would say that that took me a good three to four months to get all of the security for all the environments and get them to the database roles and stuff to do it. But it’s exactly like the partial script would do for you. So, my advice to you, let the partial script do it for you. Don’t be the DBA that, “I’ve got to make sure it’s right. Let me code it myself.”
Carlos: There you go, lessons learned from a compañero.
Tom: Lessons learned.
Carlos: That’s one of the things, and I appreciate you sharing that, because that’s one of the frustrations I have when you go to conferences or you listen to the marketing material, it’s like, “oh, it’s so easy”. But like you say, yeah, it might get you 90% of the way there, but that last 10% is really the hardest part, is what takes forever, and what derails a lot of projects. Giving real-life experiences of, “this is how long it took me to make this process happen”, and then help set the expectations for those are going to take that on. And then they don’t get frustrated after the three hours, they’ve imported their objects and then they spent another hour on it, like, “huh, I wonder why I’m not finished.”
Tom: Yeah, yeah, the server-side objects is the part that probably takes the longest, because that’s really where you do it. Server-side objects, because this is Database Project. I also do all of my server objects, and the way I’m doing those are in post- and pre-deployment scripts. Mainly post-deployment scripts.
Carlos: Now, give me an example, you’re talking about server objects, you’re talking about like the master in MSDB stuff, or.
Tom: Exactly, the master in MSDB stuff, so you would have, for example, your log-ins. You have some log-ins that aren’t in the database, that are SA on the system. An example of ours would be Octopus. Our Octopus Tentacle deployment process actually has to have full rights on the server, so we have it there. Or you have, say, the SQL Server admins. There’s a log-in for us. I’ve actually got that log-in there, and then I give them server rights. I give them SA rights. So, the thing of it is, the nice part about it is it’s all scripted and so all I’ve got to do is just basically run the security against prod and if I’ve got a brand-new machine I’m bringing up, guess what? All of my security is going to be there. I don’t have to guess about it. I don’t have to think about it. Then also, I do my jobs and some other things I do in there. I have found a couple things, though, I cannot do through Database Project, but I still put them in Source Control and that is things like spconfig. If you’re going to do something with spconfig, then you do have to manually run those, but those are so limited that that’s a lot easier than running a hundred scripts or more.
Carlos: Sure. That would be an example or scenario where you could just create a script to say, “here’s the changes I need to make,” and then run them as needed.
Tom: Then they’re still in Source Control, so I still know I need to run them, also.
Carlos: Exactly. You have that reference copy. That is another interesting point, and I know we’re running short on time here, but this idea that I can also go back down. Normally when we talk about devops, we’re talking about going from the development environment up to the prod, but that’s a great example of being able to say, “hey, I either want to spin up a new environment, or for whatever reason, I want to test my production process. Can I recreate it? Boom, let me try that process here.”
Tom: You know, the one thing that we didn’t really mention is the thing that I like to do is within Database Project, and within having all of these server objects and everything, I like for my dev machines to look exactly like prod as far as all the configurations, everything that I’m going to tweak in a production environment, I’m going to tweak on the dev server so that when he’s developing and testing, and even on QA and UAT servers, exactly the same, I want them all to be exactly the same configured because I don’t want someone to come back and say, “well, it works on my machine.”
Carlos: Yeah, there you go, so it helps get you around some of that.
Tom: Yeah, so just make sure that we want to, that I feel is a very important process is to make sure that we are exactly the same. I don’t know how many times people have had errors because the configuration’s not the same.
Carlos: Right. Yeah, well said. Awesome, great discussion, Tom and thanks for sharing your experience there. should we go ahead and do SQL Family?
Carlos: Here we go. We’re switching this up a little bit, or adding a few questions, I should say. You get to be our guinea pig here, for us. All-time favorite movie, with the caveat ‘that you wish to discuss publicly’.
Tom: Well, you know, that’s okay, I can let people know. I prefer chick flicks. My all-time favorite movie, probably, is Mama Mia. I love the musical, I love all the music in there. It almost got me up dancing in the aisle.
Carlos: Yeah, I’ve actually seen that play and the play has the same effect on you there. The one city or place you would most like to visit?
Tom: I think the one city that I would like to go visit that I haven’t gone to, I lived in Europe for 18 months, so I got to go visit a lot of European cities, but I never did really get into Dusseldorf as much as I would have liked to have gotten there.
Carlos: Okay now, that’s in Germany?
Tom: In Germany, yes.
Carlos: Very good. That’s another city I have not been to. I haven’t been to the country of Germany, so you’ve got me there. A food that reminds you of your childhood? Comfort food.
Tom: Shrimp. And the reason I say shrimp is when I was young, my dad, we had an 18-foot boat and we had a shrimp net. We would go shrimping all the time and just pour in the shrimp in our cooler and bring it home and boil it up or fry it up and, oh my word, just yeah, very good.
Carlos: Okay, so it doesn’t matter necessarily how it was prepared, just any kind of shrimp?
Tom: Any kind of shrimp.
Carlos: Very nice, there you go. Yeah, man, I can imagine experiences out there on the shrimp boat. Getting to SQL Server, how did you first get started with SQL Server?
Tom: Well, I actually was a FoxPro guy. I did a time and attendance for Continental Airlines and stuff and worked with it. Then I went to work for another company and I was in the QA department with FoxPro and they switched over to SQL Server, so that’s kind of how I got into SQL Server.
Carlos: If you could change one thing about SQL Server, what would it be?
Tom: Consistency. Like I was mentioning earlier, please make SQL Server Data Tools defaults the same as SQL Server defaults.
Carlos: Gotcha, there you go, yeah, so that experience is the same for whoever is using the tool.
Tom: Right, right, you don’t have to go figure out what you need to tweak to make them work the same.
Carlos: What’s the best piece of career advice you’ve received?
Tom: Years ago, when I was a junior DBA, it was to go to the SQL PASS Conference, and I went, and haven’t looked back since.
Carlos: How many times have you been?
Tom: I’ve probably been about 9 times.
Carlos: Wow, there you go. Very cool. Tom, our last question for you today. If you could have one superhero power, what would it be and why do you want it?
Tom: I think it would be Superman, and it’s mainly because of the flying of Superman. I love to fly, my son’s a commercial airline pilot. I just love to fly.
Carlos: Now, do you ever get into a small plane?
Tom: I have gotten into a small plane. When my son first got his pilot’s license, we flew with him in a four-seat plane, so I’ve been in one. I didn’t think he was going to get off the runway. I think it was my weight. I was too heavy, but he made it over the telephone wires.
Carlos: There you go, thank goodness for the physics there.
Carlos: Well, awesome. Tom, thanks so much for being on the program today.
Tom: Thanks, Carlos, for inviting me. It’s been a pleasure.
Carlos: So, my two big takeaways here are one, there will be challenges, but don’t let that stop you from moving forward. The benefits outweigh the effort. That ability to consistently be able to do those deployments, to not have to be responsible for all of those changes, the ability to get back, these are all very positive benefits that are– yeah, there are some challenges in putting this together. I think, again, Tom mentioned being able to use the Community Edition, so obviously you have to have some kind of Source Control, but the ability to get those tools very cheaply. The cost shouldn’t be the big reason that you’re not adopting this. Obviously, for some of that integration stuff, he mentioned using Octopus, so there are going to be some things that you’ll have to add on or bolt on if you wanted to do the continuous integration. But at least being able to start that process and getting in, you should be able to do that starting today. I think one of the other benefits is that it does provide some more transparency into the entire process. You don’t have to be responsible for all of the deployment process. You can now go back and look at those change sets. Hey, did you get that code in? This is what you contributed. This is what you said needed to go. I think it will provide more responsibility for all of those involved and you can kind of get that monkey off your back and if things do go a little sideways, which is going to happen from time to time. This will allow you to be better prepared for that.
Compañeros, I think that’s going to do it for today’s episode. Thanks again for tuning in. We hope you enjoyed the new SQL Family questions. One of the reasons I went with those questions is we’d like to start collecting those and putting up some stats about that. I know we’ve started collecting them on the superhero question. We need to circle back to that and maybe we’ll start publishing all of that information on a separate post. We are always interested in you connecting with us, connecting with me on LinkedIn. I am @carloslchacon. I want to know what you think we should be talking about. If there’s something you want to talk about on the program, of course, reach out to me. We’d love to have you. And we’ll see you on the SQL Trail.