 One quick note is I noticed outside. It says trails. We're not talking about trails today. So So my talk today is on a Microsoft SQL server to Postgres the company I work with we're currently in this process now We're near the ending stages of it. We should be done by the end of the year My name is Lewis Royster. Just a quick background about me. I guess Spent some time in the army after I left high school After some time there decided it was time to move on. So I went to college. You got a degree in computer science I'm currently with the company. I've been with now for two years. So I actually have two years of professional development experience Of those two years, I've been working primarily with Postgres for about 18 months a Little bit about the company I work for Small company in Ohio. We only have 60 employees Of those 60 employees. We have 14 developers We have five people in our QA department and we have three people in our tea department Our code base all of our code is written in visual studio. Most of it is VB.net We do have some C sharp. We do have some JavaScript, but the big chunk of it is VB.net the decision to go from SQL server to Postgres was primarily a business decision because it We are going to save about a million dollars a year in licensing and fees So once you're talking about numbers that big it was really a no-brainer to make that switch Throughout this whole process. I'll be talking about our Postgres progress team our Postgres team It's been an average of three developers. We've had more at times. We've had less than times But on average, we've had three developers throughout the whole process And we started full-time conversion process in October of 2015 So right now we're about 18 months into our conversion process We're hoping to be completely done all of our clients and databases on Postgres by the end of the year That's a little bit of what we're talking about today We'll go over our planning phase things to help us get ready to go over our prep the things that we did to get ready to Convert to Postgres. We'll go over our conversion process our testing and then last how we plan to roll out Alright first is planning We decided to switch the Postgres We knew we wanted to go with an open-source database It came down really the two came down to my SQL and Postgres after doing research in both of them We just decided that Postgres was better fit for our needs. So that's what we want with Next that we started researching IDEs Again, this decision really came down to two as PG admin or data grip We decided to go with data grip There are a couple people in Our company that still use PG admin for the most part myself included. I use data grip Next is research hosting on AWS this morning they had a keynote talk in AWS and Mark had said something about the AWS schema conversion tool That was not out yet whenever we started our conversion process and we heard rumors of it We actually talked to AWS when they were going to release it But when we were ready to start it wasn't out yet So we just went ahead with our conversion process without that And lastly in our planning phase is time and resource estimates How long was it going to take us to do all this? We had no idea. This was I don't even know why we're estimating at this point because we had no idea Alright next we got in our preparation stage Again, we formed our Postgres team At the time we formed those three developers. It was myself and two others Next we did time estimates and meeting with the business We spent a lot of time doing time estimates at this point Probably too much time After we were done doing that we did more time estimates No, really there were a lot of time spent trying to do time estimates at this phase There was a lot of you know, if we have X amount of developers on Postgres full time Then it'll be all converted in X amount of time The problem with this at this point in stage we had no statistical data at all There was just nothing to tell us how long it was going to take So we had nothing to go off and really we spent a lot of time doing that with no point Which brings me to one of my points today is half stator's law Being in the software development community, I'm sure a lot of you have heard this But it states that it'll always take longer than you expect even when you take into account half stator's law I'll put this up here because if there's one piece of advice I can give to anybody who is going to Convert from SQL server to Postgres is it's going to take way longer than you think Take your time estimates you can double them and triple them and it'll still probably take longer than that All right Then we came to the question of How are we going to deal with active development and conversion at the same time? This is a big deal for us We had a lot of projects going on when we decided to do this So the first thing that we did is we thought that we could Shrink this how many projects that we have You know, we may had four or five six seven projects at that time if we take it down to one or two Two things would happen one. We would free up resources to work on Postgres essentially converting Postgres faster And Sorry also Not knowing a whole lot at this stage. We just thought we'll stay out of active development You know, whatever modules or whatever part of the software that was currently being actively developed on the Postgres team Would just stay out of there neither one of these works very well actually never one of them worked at all We'll talk more about that a little bit later Next in our preparation stage we built our post pro postgres progress tool This was probably one of the more important things that we did This is it. Well, this is the the interface for it. This is written in C sharp Basically a couple features on here and I'll get into this more later, but if you look at the top There's a timer again. We were real big on time estimates But if you look at the search the search box There's a little you can search for a procedure any any procedure name You could also search by database you can search by the type. You know could be a view sort procedure trigger Once you got whatever script that you wanted you would hit start task That would then bring it up to the top that top View box is all of the things that you would be working on you could have one procedure in there You could have 25 The function out of this really is once you hit start On that procedure it would then do a get pull from the server It would bring it down to your local and open it up in any IDE that you were using in my case It would be data grip You would convert it once you're done converting it you would have five it and Then once you click finish it would then do a push back to the server This is how we kept track so that way the three developers didn't end up working on the same script at the same time or Either someone it was in the process of working at it and somebody else did another poll against it that Was really it for that we'll get more in the depth of this tool later. It does some better things All right, so now we are ready to start converting we did all the planning that we could and we did all the prep that We could and we did a million and ten time estimates So we're where to start We didn't know you know the We tried to look into other companies that had switched from Microsoft SQL server to Postgres We didn't find a whole lot. There wasn't a lot of documentation on it So this point in time. We didn't really know where to start First things we did is we use the Amazon web services the database migration service along with a tool was written in a go To migrate all our databases from SQL server to Postgres What this did is it made a copy of our databases put it on our Postgres server It also picked up all the tables. So essentially all our tables now were on Postgres What it didn't pick up is any type of Script and no stored procedures views triggers functions anything like that Next we created another tool This is also written in go to convert our scripts to Postgres And I wish just was as easy as it sounds it just ran it through and converted all our scripts to Postgres That was not the case All this really did is it pulled all of our SQL scripts It would run them through this tool and it would spin them out as Postgres scripts a couple things that it would add it would add like to replace the I can't think of the name right now the Anyways, it would add it would add the script name it would add the parameters It looks for a couple little things like in SQL is null is coalesce and Postgres So it would change all the is nulls to coalesce Yeah, yes, yes But that's about all I did it all of those scripts still needed to be gone through and really wrote to use in Postgres So we had 10,000 SQL server scripts and We just started converting them as fast as we could we really had no direction at this point in time And this is probably one of the bigger mistakes that we made along the way All right, so this was a dashboard that we had created on That postgres progress tool Just real quick going over if you look it'll say how many scripts we had how many scripts we had left to convert the overall processor process the Different type of scripts that we had and then at the bottom would have the developers names and how many scripts that they converted that day Really the big part of this is that overall bar What happened was is we had Those 10,000 SQL scripts and we were only focused with converting them as fast as we could that's all that we cared about So naturally all of the easy scripts got picked up first to convert So we were converting all the views the very easy stored procedures things like that So over time through you know the first couple weeks that we were doing this that Progress the overall progress bar went up drastically. I mean there was one week I think we did 13% of all our stored procedures in a week And that worked fine until we hit about 80% and at 80% We had all of our difficult stored procedures left and it just came to a screeching haul So we were doing 10 12 5% a week and then we were doing 1% a week one and a half percent a week So it's not realistic tracker at all. This is very far from realistic All right Dealing with development while converting I said that we come back to this and now we're here Again, I said that the business was going to shrink how many active development projects that we had going on That did not happen actually the exact opposite that happened. We got more projects And then our second was to just stay out of areas that were actively being developed against that did not happen as well What we found is that we may have Development going on in module a of our software, but that module also touches module B and module C So it was really impossible just to stay out of places that were being actively developed That's where our pros postgres progress tool really came to help us This is probably the the most important part of this tool First thing what we did is we set up triggers on our Microsoft in Microsoft Anytime that somebody Got a stored procedure a view anything like that changed it and then committed it that trigger would run and It would trigger the activated by column in here So what happened is we would know if we were in an area or going to an area that we were going to try to Convert those scripts to postgres We knew that they were actually being developed against and not to touch those The second thing that I triggered is it sent an email if that script had already been converted So if I had converted a script already and another developer had to go in that script in SQL server and change it It would send an email to me it would send an email to the developer that was currently working on in SQL And we would then get together talk figure out how we were going to implement those changes Most of the times it came down to hey, I saw that you change this and they were like yeah I'm done I would then pull up the sequel and add any changes that were done in postgres It worked out great though this that was probably one of the best things that we did as far as being able to organize the active development with the postgres All right, so We kind of reached like a plateau or stopping point at this point in time What happened as I said before With that progress tracker We were going from 10 12 percent a week down to one or two percent a week that came down in as that progressed The percentage of weekly we were getting done was shrinking and also we had a lot of active development going on It was no longer viable just to let's just convert scripts as fast as we can it just wasn't it wasn't working for us So we switched focus I'll call this conversion 2.0 First thing is we changed our focus we went from Hey, let's take these 10,000 stored procedures convert them as fast as we can to let's start going through our software module by module and Let's try and to convert it module by module instead of looking at a big picture. We broke it down into smaller bits This is another very important thing that we did is we set up is using postgres in our web config It's using postgres as a bit if it was in a postgres web config It was a one if it was in the sequel web config. It was a zero What this would do is just let our code know what database basically we were working with an example of kind of how this would work or Yeah, how this would work is there is spots in our code. We build out dynamic sequel Obviously building out dynamic sequel as sequel is not going to run in postgres So at that point you would put an if statement in it was if sequel helper is using postgres Then we would write out that dynamic sequel in postgres If it was not using that if that came back as false then it would then execute the regular dynamic sequel That comes into play later on down the line, too But that was a very important thing that we did was being able to have our code talk to postgres our sequel at the same time Again kind of touched on this, but yeah, this is where we so we like I said We really shrunk our focus and we started going through Mods our software module by module instead of instead of having that broad We you know, let's let's all these developers all the postgres developers Focus on module a because there's nobody developing a module a right now There's obviously little problem with that. I'm gonna get into those a little bit later All right, another thing that happened when we shrunk our focus is We started to find those little nuances the things that Sequel and postgres are just a little bit different on As I said before as we were going through and we were converting as fast as we could We would convert a stored procedure and then we would hit f5 And if that stored procedure executed we considered that converted That really wasn't converted. We had no way we didn't test it at all yet There was no way of knowing if that thing actually worked so once we Started limiting our focus and doing a module by module really started to find that Although these scripts are executing. They're not really working an example that is and Postgres when you have parameters if you set your parameters to a value if you want to set a parameter to null That parameter has to come last in your parameter list And sequel server doesn't matter so that could be first it could be third it could be wherever in your parameter list So we had to go in and change the way that our parameters were getting passed from our code to our postgres We had no way of knowing that before I am going very quick All right next we're going to go into testing Some things that we did for testing First is we didn't take into account how big of a task testing was going to be I think that when we got into this we were planning and even into our conversion process We started we thought that converting all those scripts would be the biggest hurdle that we had to overcome Really it was testing Testing was long. It was probably Longer it just as long if not longer than converting all those scripts Next and this was another Thing that we did that was outstanding is we got the whole company involved again. We're a small company We only have 60 employees total And when we were looking at testing We decided that we have trainers We have a sales team. We have documentation. We have all these people that use this software every day They know it like the back of their hand. So we're getting in testing. Why not get them involved too? Why not to have them click through it? Why not have them go into it and use it like they would any other day and find bugs with it? So that's what we did. We got we got everybody involved So the first thing that we would do in our press in our testing process is as developers We would click through whatever module that we were sending in a testing We looked through click clack click crashes high-level things Really just little mistakes things like that once the dev click through it. We knew everything was converted Once they click through it. It would then go to those other employees The trainers the documentation all those other employees our role of thumb for them were They needed to have three people click through whatever module we were working on So if we were working on module a if that was the one we sent to them Then trainer one would click through the whole entire thing once and they would they would add data They would do a little bit deeper dives and what the developers did The trainer two would do it then trainer three would do it. They found bugs. They would send them to us We would then fix them and send them back We did find a lot of bugs this way. We found a ton of bugs this way I'm like I said when developers went through it, we were it was mostly click crashes that we were looking through We didn't we didn't get deep in it at all so Developer click through it send of the trainers the trainers click through it three times We fixed all the bugs. They said it was good and then I went to our QA team Our QA team still found a lot of bugs your QA team really Drove deep into the data Making sure like for instance that tables were coming back with the same data in the same order Things like that. They really got deep into the testing And they found a decent amount of bugs again. It was the same process. They would Send the devs the bugs we fix them send them back One of the things that we did in this process though is Going back to that is using postgres Our code was now set up to use postgres and sequel and we were making changes in our code So they would go through and test postgres and it would clear and then they would go through and test sequel So we tested for both after a clear postgres we would test again for sequel We didn't get a lot of sequel bugs actually there wasn't I I mean there was less than five probably per module But what we did is we found a whole ton of bugs that were in production We probably found close to a hundred. There are little things that just our clients hadn't found so Once QA then cleared it on Postgres and sequel we would release that module we sent it into production So we the product that module was then in production It could work with both sequel or postgres, but our clients are currently still on sequel Um Once that this happened and this is kind of going back to how we tried to keep the active development and postgres away from each other Once this happened this module would go and go we call it a lockdown state Once it was it wasn't really a lockdown state what's happened is once it will take I'll say module a for example And module a has been converted to postgres. It's been tested for postgres It's in production So if another team has gets a project and they need to go into module a They are now responsible for Converting that both postgres and Microsoft so they have any changes of store procedures. They need to do they have to do it To both Tables same thing any changes of tables they have to do they have to do it to both Once they are done doing their development it then goes to qa and qa again will test both They'll test for the postgres. They'll test for the sequel trying to think All right deploying This is where this is using postgres is going to really help us We plan to roll out Database by database even kind of client by client we're planning to do a slow and very long roll out So What'll happen is we'll start rolling out small clients first We'll take clients that are databases aren't very big and we'll put them on postgres We the plan is to wait Could be a couple weeks two months To see if they find any bugs see if they anything that happened to make sure everything happens well Any bugs that we get will fix them and then we'll roll out roll out more clients The plan is is that we're all small clients will wait a decent amount of time We're out more more clients will wait a decent amount of time until we shrink that time Eventually getting to a point where we just flip a switch And roll out all our clients Okay, so this is just some Mistakes that we made some things that I think we did well that we could probably take you could take away from here One of the biggest mistakes was time estimates again. We spent a lot of time doing time estimates It was really a lot of a wasted time of doing time estimates Another thing is that The business is always pushing to do more work, but with less resources Like I said before we were planning on shrinking down the amount of active development that we had To focus on postgres when actually the exact opposite happened we we had more projects and then having too wide of an area of focus are Just going in and converting the scripts as fast as we could without having a good testing plan in place It didn't really help us at all what what we found is that I would During that process I would convert a script Then when I went in in the module to make sure that it was working to test it I basically just had to rewrite that script again so going through those and Just converting as fast as we could was was not great Some of the good things we did was getting the whole company involved that really helped us a lot It was just it was any any time you get more people involved. I think it's great The tools that we built internally to help us those were also a big help that postgres progress tool Not so much for the getting to push that is whatever But the way that we use that to track and the way that we put those triggers on there to track That was a big help that really helped us AWS does have that tool now. I haven't personally used it. I've but I heard it's great For us. It just wasn't out when we started this conversion And then when we narrowed our focus instead of looking at it as a big picture We just broke it down and went module by module and that was really one of the best things that we did It helped us get through the conversion process faster, but also helped us test along the way That is I went I talked really fast Anyways, so we have a lot of time for questions Yeah, and I'm gonna put my email up here if any of you have any questions outside of here That you would like You're more welcome to email me. Also, there is a whole Postgres team. So if I can't answer it somebody in my company probably can be that this is Dave Who is also a postgres developer with my company? So I'm sure we can answer any questions you have We were basically almost done with the conversion process We started like I said we started October 2015 really That's really full time. We started and we had mostly everything converted in about a year It wasn't working, but it was converted so I personally have not used that AWS Yeah, actually Dave was Dave can probably talk more about this and I can Yeah, there were a couple performances, but a lot of it was more with the day that was being returned One thing in particular is the case and sensitive searching versus the case sensitive searching sequel That was that was a big Big big concern for us. We started noticing it at that time. I also started using The tool in notepad plus plus once we found a situation We use that because it better searching in the inside the scripts to find all the occurrences of things that would do that for example the like like for his case sensitive and a lot of times we want to do a I like Compare in postgres, so we use no pad plus plus to find all the occurrences of like the word like and Some of it we out was comments and we had to sort through it But you know it found all you know a couple hundred different cases of that that we could go in easily and make the change No, these are the scripts we have to change easily I think Yeah, I wanted I mean we've used other comparison tools before And they work, but like you said, it's it's once you get in the real complex store procedures It's it's not really going to do you that much good. Yeah I Think Yeah, it also also the cost savings will allow us to add Extra as more clients come on or for backup to Yeah, we kind of had the just briefly we kind of had the Postgres this whole conversion broken out of three parts where IT developers and QA The developers really they hand are none developer for the IT really handled all the database stuff We didn't touch that as developers. We were mainly focused on just primarily converting those scripts and QA ever Susan testing So we know a little bit about the database stuff, but probably not a time So the company being so small Our development area is really kind of flat like we don't we don't have a developer man like development manager We our development our AT everybody we report straight to the owner of the company So when these time estimates and everything it would just be whatever, you know We would have like a team lead on Postgres That would go in talk to him about these time estimates And it got really They got goofy some points, but you know, it is what it is. So once we started explaining though You know, it was a lot of communication saying hey, you know, this is why it is is because we got to this Everything was yeah, you saw this big jump because all the easy stuff was done first now. We're getting the more complex And we're doing more testing We tried to tell him as much and he he understood that we try to tell him like this is a major This is a major project like if this fails This would cost us losing clients which cost us losing money, you know, I'm not enough clients. We lose enough clients We're gonna eventually be wonder Yeah, yeah Yeah I'm sorry. Yeah, I think he was next. I'm sorry. I know that we were using 2005 Yeah, and as far as I don't I don't know the data size It was cheaper, yeah, yeah, exactly And like I said in the long run, they estimated it was going to save a million dollars a year on server costs licenses Yeah, yeah, yeah, I mean what really would the money was a motivating factor to switch That as a whole So it's not just the database. It's also the front-end that's being worked on also So yeah, it's a plan to be a little more flexible a little more Reduce our costs Yes What As far as the progress tool or the The one that took it in how long did it take Yes, yeah What So as far Yeah Not not a lot, I mean probably like 2% we it would take that sequel script It would then start to build out a postgres script. One of the things it would do is like they create a replace function Whatever that script name was if there's parameters in there it would add those And it would add, you know, the body of the script, but really Yeah, exactly, that's that's all it was that's yeah Yeah Along the way Yeah, that's all it was You had your hand up correct? Yeah I Don't know tables off top of my head, but I can tell you it is it is a lot I mean, I know I personally know tables have 35,000 rows. So I mean it is big Yeah, I don't know how many tables off top of my head Yeah Yeah, um so kind of What happened We're in the medical software business Basically, and we're it's actually educators. So we look at like resident doctors when they're in the residency And there's an agency that oversees that That agency like right around the time that we started postgres just mandated all these new roles We had to implement them in our software Yeah, there wasn't like You know in a perfect world, we would have shrunk that down, but it was because of that happened I mean we had to have projects go on so Anyone else I I think you've had your hand up for a while. I'm sorry. I'm sorry I'm trying to get everybody Yeah No, they were on this slide. Let me go back a couple Yeah, there it is right there. So yeah, we had it's like a little over 7,000 stored procedures 1,300 functions 900 views and 100 triggers that was all that's all of our sequel It yeah, absolutely. You're 100% right a lot of our logic it was Just the way it was I've been at the company for two years Dave. I think he's only been there a year So all this logic was definitely written before we got there. Well, yes Our sequel does perform a lot of logic for our software We I don't think so one of the big Problems that we met like functionality wise was pivot tables actually pivot tables in sequel There's not a really good fix for them in in postgres We ended up using cross tab Which probably wasn't the best Honestly, those scripts probably should be Rerote to take the pivot out of them But other than that not really I think most of the logic most of the functionality of sequel We found a way to do it in postgres Come I think we got a couple more minutes We are we are currently yeah Yeah Yeah, we are change we are So we're making changes to our code as well. I said everything everything right now is visual studio We the current projects that are underway are actually getting developed with Angular 2 And we're hoping to make a switch more towards Angular 2 and hopefully maybe open source Unfortunately ours Databases are going to go to AWS. They're on AWS now our Microsoft are on AWS and then they'll say AWS Go ahead No, that you know actually the that tool really and that database migration service that Amazon has really helped we can actually Convert all that stuff in less than an hour. So our whole every one of our databases can be ran through that tool in less than an hour the one thing that we I don't know it's not an issue but With tables if somebody went into a table and added Column to that table. They didn't get picked up automatically and it didn't get picked up by that tool We then basically have to do a database refresh and run it through that database migration service and our tool again to pick up those table changes Go ahead. I was actually going to put that in here and I did it So we use sequel server with sequel prompt I mean as far as the ID itself. I like data grip. It's fine But they have nothing that compares a sequel prompt and I just I mean I use I've still developed and sequel You know still and I use sequel prompt a lot They just yeah, they don't have anything that really compared to sequel prompt. It's not bad. I really like I really like data grip. They're just not as good. And they They're adding the latest versions and they're adding feature slowly. Yeah Listening to community there. They have a new they have a new build coming out. I think fairly soon. So Yeah Say that again, okay A lot of vendors Yeah I Have to talk with the owner and go from there Like I said, you know Beyond compare as a tool that we use also To compare the script it gives us kind of the basic of the script so we can see it, you know, we drop it If block or something like that That's the one tool like I said no pet plus plus was a huge help in searching for all the scripts I think it probably would have saved some time like I said what happened is, you know, such a big scope that we went and converted everything. Yeah, and I Think it probably would have saved some time like I said what happened is you know, such a big scope that we went in and converted everything. Yeah, and We had no idea if that really worked or not and then we were finding out as we were going through it It doesn't work So it kind of you know, there's some things Yeah, I think you would have to go wide for a little bit in the beginning But now you're focused real quick Yeah Yes, thank you guys for coming