Episode 79: SSIS Catalog

Episode 79: SSIS Catalog

Episode 79: SSIS Catalog 560 420 Carlos L Chacon

Although the SSIS Catalog DB is created with management studio, it does not behave like other databases.  Our conversation in this episode revolves around the catalog, SSIS packages, and some of the complexities of migrating packages.  Steve and I are excited to chat with our guest Andy Leonard about his thoughts on the catalog and how this feature provides some really interesting benefits for ETL architecture.

Episode Quote

“[The SSIS Catalog] is really there to facilitate a lot of Enterprise functionality that is needed in any data integration engine you use.”

Listen to Learn

  • The benefits of project deployments
  • Some of the issues migrating packages
  • Why restoring the catalogdb can be problematic
  • The various levels or components of a package
  • How you can view reports without going into SSMS

Restoring the SSISCatalog DB
Integration Services stairway series
WorldWideImporters Sample Database
DILM Suite

About Andy Leonard

A former chicken farmer, Andy is now is a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and author of Managing Geeks – A Journey of Leading by Doing, and the Stairway to Integration Services.  He lives with his family in Farmville, Virginia.

Untranscribed introductory portion*

Carlos: So Andy, welcome to the program.

Andy: Thank you!

Carlos: We want to have you on the show ultimately one because you’re pretty close to Richmond. And of course you’re Richmond famous if nothing else for helping start the local group and we’re very appreciative of that. You’ve been doing quite a bit in SSIS, written several books on it, been kind of putting together this catalog, this DIML Catalog which we will talk about here in a minute. But ultimately, I guess our conversation today is going to revolve around the SSIS Catalog. So let’s go ahead and maybe jump into it and kind of give the 101 overview of what the SSIS Catalog is and what it’s going to do for us?

Andy: Well, first I want to thank you, Carlos, for that warm introduction. I’ll just say that there are a lot of people involved in the Richmond, Virginia SQL Server community and just all of our technical community. We have a great collection of really different communities there from JavaScript across to the .Net community, to SQL Server. And as you know there’s a lot of interaction between the groups. And I’ll just also give you props for the amount of work and the amazing work you’ve done in the past few years after stepping in. I stepped away. I’ve been an attendee and a person who’s been enjoying a lot of the advance there that Utah put on recently. So thank you for that, it’s not just us. There’s a huge team of people involved and we could name names but they know who they are. So with that said, I’ll jump into a Catalog 101. And this aptly time, Kent Bradshaw, a friend and co-worker at Enterprise Data Analytics. He and I earlier today put on a free webinar where we talked about this very topic, an introduction to SSIS Catalog. So what the catalog is, it’s an SSIS framework. And what do I mean by that? It manages SSIS package execution, logging and configuration. And before the SSIS Catalog was introduced in SSIS 2012, a lot of people wrote their own, and I was one of those people. But it’s really there to facilitate a lot of Enterprise functionality that is needed in any data integration engine you use. It doesn’t matter if it’s Informatica or SSIS or one of the others. You always need some kind of framework. You need something to manage execution, something to log, something to manage configuration, so at a very high level that’s what the catalog is.

Carlos: Now, that’s an interesting topic. I didn’t realize that you could use the Informatica packages with the SSIS Catalog.

Andy: You cannot but Informatica has its own functionality built in, its own framework, so it does a similar set of functions as the SSIS Catalog.

Steve: So then, with the SSIS Catalog, people can still run SSIS packages without it but you get a lot more functionality in the logging and capabilities there if the catalog is installed and used. Is that correct?

Andy: Yeah, absolutely can Steve. Microsoft, the way they built SSIS starting with 2012 is they realized they were offering this big shift in paradigms for execution and they made the existing way of operating. They made that backwards compatibility, a very easy to accomplish inside the new, I guess the new paradigm. So what they did was they gave the old way of executing packages a name. They called it package model and the package model deployment, and that is the old way of doing things. And then they created a new default the project deployment model. And so in order to execute packages in the catalog you need to have them in that project deployment model and that’s a property of the Visual Studio solution, the SQL Server Data Tools or SSDT solution that you built. And they really created this really very rich variable boost. And I’ve never seen it failed once, wizard for walking through. First, importing packages and two, SSIS 2012, 2014 or 2016, and they come in as this package deployment model if they are earlier than 2012. And then, once you walk through that wizard they’re now in the package deployment model, and then after that if you want you can convert that project to a project deployment model and deploy it to the catalog and run it there. But you’re right Steve you can do everything the way you’ve been doing it for 10 years if you’d like even in 2016.

Steve: However, that sound like there’s a lot of benefit to using the catalog and not doing it the old way or the package deployment way.

Andy: There are some benefits to doing it that way, I’d say logging. And really the way the logging engine has been architected for the catalog that was a big step in the right direction. Microsoft decoupled a lot of things right at that logging works and just because of that you get package performance boost anywhere maybe from 20% to a couple 100%. So you can see in just a performance boost by importing the projects into, say, running in the file system to running in the SSIS Catalog, and again, it’s all around that logging.

Steve: Interesting, that’s a good point to know then, so if you’re having performance issues it might make sense just try moving them into the SSIS Catalog and see if that improves things.

Andy: Absolutely. You know, a lot of people schedule jobs in SSIS especially if you’re running again SQL Server and if you’re using SSIS you have to have SQL Server. Although the engine itself is free and you can develop and run in the debugger anything you’d like. In order to have it run outside of that development environment you need to have a SQL Server license on that machine, you know, that’s always been. But certainly, if you pull SSIS projects that are pre 2012 or even if you’re running 2012 projects, 2014 projects, and package deployment. Pull those into the Visual Studio Designer. You do the upgrade right there in Solution Explorer, you convert to a project deployment model and then you can right click the project and deploy it right over to our catalog. Yeah, you’ll see performance improvements. It’s not just the logging but the logging is probably the largest one. Probably the next largest performance boost you’ll see is they’ve got a different execution engine that they built specifically for running SSIS packages that are executing in the catalog. So it runs a little faster which isn’t surprising. And when the catalog executes packages, it executes it in that. And I don’t know if you had this experience, but I have. I’ve built a version of something, and then comeback a few years later, been asked to upgrade it, make a new version. And that second version is always better.

Steve: Oh, sure. Yeah, you all know the mistakes for the things that you would do differently the second time around. And it sounds like that’s what they’ve done, is that they’ve just done a better job that second time around.   

Andy: I think so. There’s spots in it where it could improve. And that’s part of the definition of software development, right? Anytime you build a database or piece of software you can always improve it. And I’m sure, I know for a fact talking to some of the folks involved with the project they’ve looked back and said, “Gosh, yes, we should have done that, we should have done this”, but seriously kudos to them. It is a stable engine and it does what they built it to do, and it does it well I think.

Carlos: I am getting flashbacks to the 463 Exam Prep with all these talk of converging from package to projects deployment.

Andy: Yeah, I mean it’s involved. But once you’ve done it a couple of time, Carlos, I don’t know if you had that experience but it’s really not hard. The wizards are, you know in my opinion the wizards are work of art. It’s well architected and it’s something that is really easy to, you know, the kind of fall out of a project plan. The importance of those conversion wizards or either upgrading projects or changing them, they’ll go back and forth. You can go to a project deployment and back to a package. Not only that but the deployment wizard that they built for deploying stuff into the catalog, again it’s another snappy piece of curd. Very, you know, there’s a lot of Enterprise features in there. And if nothing else, I’m really glad we’re talking about this because I don’t think people realized just how well some of that software works and how flexible, and well architected, well designed it is.

Carlos: Yeah, when we talk about the flexibility, I think particularly that’s the big benefit I’ve been able to see is the ability to change the environment parameters. And then have it execute in different places without actually having to then create all these copies of the project of those packages and then have version issues and all that kind of stuff.

Andy: It’s definitely more aware of lifecycles then in any previous version. There are still some corners there when you start thinking about it in the lifecycle management perspective especially for Enterprise software. You know, some of it is just, some of it is not Microsoft’s fault or anybody’s fault. Really, it is things like the nature of XML. So you know that just any XML engine, any document object model that is semantically managed if position, relative position in a file for instance doesn’t really count. What counts is where it is in the tree. And so when you start doing things that you would expect in an application lifecycle management like compare between two versions of a package. You may have identical versions of a package functionally. But internally the text maybe moved around because it is XML. And from a semantic perspective it is identical but coming up with a good semantic compare is not easy.

Steve: Yeah, and I think that’s one of the interesting challenges that I’ve come across and trying to do source control around SSIS packages is that you committed to source control and then it tries to do a text based gif and you end up with a lot more gifs than what you might have expected because things shifted out in the XML.

Andy: It is very challenging. Yeah, and even the act of deploying an SSIS package to the Catalog alters some of the value that are stored in there. Some of those are just insignificant things like there’s a last deployed field that maybe updated. But there are some other things that actually happened to the package XML itself for instance the package protection level is set to a value that in 2012, 2014, 2016 does not appear in the list of protection levels. So in the old days there were six and they were, don’t save sensitive. It’s all around sensitive information so there was don’t save sensitive, encrypt sensitive with user key, encrypt sensitive with a password, encrypt all with a user key, encrypt all with a password, and then the sixth one which isn’t in the new stuff. In the old days there was one called the server storage. And that primarily existed so that you could set your packages to be encrypted on server storage and that was only used when you deploy to the MSDB database which you can still do that’s part of package deployment. But what they did when they create a project deployment is they made the SSIS Catalog and the SSISDB database. It uses encryption and in order to create it you have to supply a password that will solve the encryption. In order to restore it you also need that password and that we’re going to talk about that in a minute. But what happens is in the act of deploying it changes whatever protection level you set for the development environment into the equivalent of server storage. But again that’s not in the list. You can’t pick that as one of your options on your side but the catalog will switch whatever of those five you pick. It will change it to server storage and it’s using SQL Server native encryption, transparent data encryption. And, you know, that’s pretty rock solid. I know a lot of people have moved to that and I was absolutely thrilled when SP1 came out. At the time of this recording we’re doing, we’re doing this at the end of December 2016 back in November at the MSConnect Conference when they announced SQL Server 2016 SP1. They made encryption available across all of these additions and so much more. It was really nice to see that.

Steve: Prior to that announcement the TDE option or transparent data encryption that was only available with Enterprise. Did that you mean that you had to have Enterprise edition of SQL Server to use the SSIS Catalog in this encryption mode?

Andy: Yeah, that’s a great question, Steve, you did not. They had encryption, I don’t know how they did that but you had it in Standard. What happens when you create an SSIS Catalog is there’s a backup of the SSISDB database that shifts with SQL Server. It’s actually still there if you go look. Any place that you’ve checked the box for integration services in your installation if you go dig through program files into DTS directory. You’re going to find an SSISDB.bak file. And what you’re doing with the GUI that they supply in SSMS when you create a catalog you have to enable SQL CLR. It has to have that if it’s not already enabled because all of the execution and a lot of validation and other functionalities that occur in the catalog is actually happening down the .Net framework so there’s a lot of SQL CLR stored prox in there. So that part has to happen and you have to supply a password because you’re going to be using that encryption.

Steve: Ok, so that’s a good transition I think for us then into the topic of restoring an SSIS Catalog database. And I think that’s one of the things as a DBA a lot of people might be really familiar with normal backup and restore but they may have never done it with SSIS Catalog. What kind of thing do you need to know or you do differently when you’re doing that?

Andy: Well, there’s a great article out in the MSDN, and I think there’s a companion article over in TechNet. Both say basically the same things. So they walk you through this, this process, there’s in a lot of ways it’s like restoring any database that uses encryption. So whenever you’re using encryption you have certificates, and you have passwords and all of that sort of stuff. The catalog has all that as well. In addition to that, there are some users, some logins that you need to create to fully restore to operational status in SSIS Catalog. One of the things that I get calls, it’s a tragic call, is someone will have lost the database and they will have been backing it up for years, and SSISDB is out there and it’s all backed up. And they say, “I’m having trouble, I restored it.” You can actually restore it without the password you can do a standard restore. And it will come in there and a lot of your metadata will be press and you can get to it and read it. But when you start executing packages from that, or when you try to export a package for instance, or deploy a package to that catalog all of a sudden you’re getting this errors you never saw before. And what’s happened is the encryption is broken. And in order to make that work and work correctly you have to follow those instructions out there at the MSDN and TechNet sites. I wrote a blog post about it, and I know you guys sometimes include blog posts. I blog at SQLblog.com, and If you go to SQLblog.com and click on my name and then you can search for “restore SSIS Catalog”. I took it from the perspective of what it’s like when you try to deploy an SSIS package or project to a catalog that’s been restored incorrectly. So I started with those error messages and kind of work through, “This is the problem you have now and here’s how you can solve it.” I included my template script that I use all the time. I gave it away but it creates the users and the login you need. I put dummy passwords in for a bunch of stuff and you just go in and everywhere I mark places where you need to update it because I don’t you using the same passwords. But if you do that it restores at about 5 seconds. And you’ve got a, well, an empty one restores about 5 seconds let me say it that way. Your mileage will vary. The more stuff is in the catalog your mileage will vary but the key here is really understanding the importance like so much in our field. Like you see, like the password for the encryption. You’ll think I never need that again, right? Export the keys, export the encryption keys and you see these warnings all the time for years with Reporting Services. You know, all of these products that are great but you can really leave your Enterprise in a lurch if you haven’t done these best practices.

Steve: I think it shows there is the difference between, I won’t say a normal database, but maybe application database one that’s kind of being dig in to the SQL Server Management Studio that is so tightly integrated with several of those components that it kind of require its own special handling.    

Andy: It really does, and you know, for the longest time and I’ll still say this. In fact, I said it today on the webinar, it’s just a database. The SSISDB database but I would hang in Astrosoft that and say that, you know, with encryption and then you kind of need to hang another Astrosoft that says that with the special set of users that are happened, you know, the logins happened to be hard coded into some of the interactions that happened between the database and the applications especially those .Net framework classes. And they’re specific to each version of the catalog by the way so you can’t mix and match two much with it. Yeah, you can get yourself into a world of hurt there just by skipping a step and it’s a very easy step to skip. And like I said, I’ve had the tragic calls from clients saying, “How do I hack this password out of here?” And I’m like, “I don’t know.” I sent up someone like you guys, and say it’s now a database issue. These guys can help, if anyone can help, they can, that’s over my head.

Carlos: The links that you mentioned earlier will be available on our website for the show notes today at sqldatapartners.com/ssiscatalog. We’ll make sure to put all of those components there.

Andy: Awesome!

Steve: Now, one more thing that you mentioned when we were talking before we start recording the show was your Data Integration Lifecycle Management or DILM Suite work that you’ve been doing. What exactly is that and who would want to use it?

Andy: Well, it’s great timing, Steve, to bring that up at this point in the conversation because an option to remembering all of your old passwords and stuff that you may or may not have available is to export this metadata. And the SSIS Catalog has a slew of metadata associated with it. Carlos mentioned earlier about environment variables and parameter values. You can store all of these configurations, metadata in the catalog itself. And as Carlos said, if you setup a lifecycle where say, you’re moving from dev, to test, to hopefully QA, and then production, something like that. I know years ago when I worked at we ended up setting up six of these tiers all the way through. And, you know, the more the merrier. It’s actually a good way to do it. I imagine you can get crazy with the dozen or something but. I always say, “You need a minimum of three tiers in your environment.” And you want to run as close to production in each one those as it makes sense to. And different people are going to define what makes sense differently. Here’s what 10 years of experience with SSIS has shown me makes sense. I want three environments at a minimum because I want to be able to build it in one environment. And I’m actually going to deploy it to the catalog in that environment and I’m going to run local unit test and integration test there. I’m going to look for functionality. I want to make sure that the rows are moving and any kind of transformation is happening as I want. Then I want to deploy that to something other than production and here’s why. When I build it I’m going to design it with all of these defaults configured. I’m going to have default connection strings pointing to my either to my local laptop, or a virtual machine, or some development server that I’m login to a client. I’m going to have everything pointed natively to that. If I deploy it straight from there to production and I have forgotten to externalize something, maybe a connection string or variable value, a path. Then I’m going to figure that out when I try to run it in production and that’s called testing. And we don’t want to test in production. Certainly, any application but definitely not databases. So want to send it to some third location that’s not production, that’s not where you developed. Anyone who tests it there makes sure that it does what you wanted to do. So when you start making that transition there are really five things you need to think about in an SSIS Catalog.  There are the folders because there’s a logical separation of objects in there. All projects have to be in a folder. All environments have to be in a folder. We just talk about environment variables. There are projects and packages of course and those are really managed well by IS pack files. Wrap up the deployment in a nice little container. You double click it, it starts that wizard. I was going on about it earlier, I like that wizard. Those are managed well, that’s number two. So we’ve got folders and projects. And projects and packages are mostly together. The third thing is those environments that Carlos mentioned earlier. They have a collection of environment variables inside of them, that’s number three. Number four is really some catalog specific overrides. In Catalog they are called literals. And the fifth thing is really a reference, and this is how you get environments and projects and parameters to talking to each other. So think about projects and packages, they both can have parameters in them. And then over here we’ve got environments. And you can’t see me but I’m doing hand signs for this. Maybe I should turn my camera on. Like a reference is really a bridge between this project and this environment. And then beneath that you can like you have environments and environment variables you have these little attributes that I call reference mappings. And what they do is they connect the environment variable to the project parameter or the package parameter. So if you could think about the hierarchies kind of going on there you can, if you’re talking all the way down to say a package you can have something that’s kind of linked through about six or seven layers.

The project and the package Microsoft covers well, migrating the folders not so much. And as you get down to environments and the references, the environment variables and the reference mappings, it really the story from a lifecycle management perspective just sort of falls apart. But to get to the environments you have to double click on an environment or right click any properties. And you may have a couple of dozen environment variables in there, data types configured and values configured. Some of them maybe encrypted. There’s a sensitive bit you can set for every value individually. So you could choose what’s encrypted and what’s not. And you open that up after you’ve configured it. It’s different when you first configured it. Well the DILM Suite, when I started working on that, I started working on something else called frameworks. And I’ve got built frameworks forever but I was working on an interface for my framework, so GUI. And I realized about after three weeks, you can’t move this metadata in the catalog. There is no clean way to do this. And I needed that working before I could do anything with frameworks so I wrote a product called “SSIS Catalog Compare”. If you go to dilmsuite.com, there’s like six projects on the page. That’s the only one that I sell. The rest are either open source or are just flat out free. And one of them is called “Catalog Browser” where it’s a tree view. It will show you everything that is associated with those SSIS projects deployed to the catalog. It will show you the reference mappings, it will show you the references, it will show you environment variables and their values and their data types, it will show you environments. And if you’ve ever looked at what is in the node in SQL Server Management Studio. You can get to everything I showed there but you have to open different windows to see everything. And this just presents it in one view. And that’s one of the free tools out here called Catalog Browser.

Steve: Ok, so the Catalog Browser and Catalog Compare those are then applications that are built using the DILM framework. Is that correct?  

Andy: Yeah, that’s a fair way to say it.

Steve: Ok, so if somebody wants to do something that was maybe different but wanted to access those same pieces they could download and use the DILM Suite. And that’s your community edition, right, so people could write code to do that. But if they wanted to jump in and use the Framework Browser or the Catalog Compare they could just download those or purchase those from you and use them as well.

Andy: They certainly could. So what Catalog Compare can do is it’s really built to manage that metadata. It’s built to facilitate and support Devops. Anybody practicing any kind of lifecycle where they are promoting code, you know, stage control is a piece of this. Source control is a piece of this although I’m not touching source control. I’m just looking what’s in catalog 1 and catalog 2. And I’m not so much focused on the piece that Microsoft really does a great job on which is the projects and the packages. I’m focused on the rest of that stuff, the folders, the environments, environment variables, the references, the reference mappings because like I said, it’s look like they got to about that spot and stop for whatever reason. And without using some of these very clever and cool solutions that other people have developed out there. You have to either write your own clever and cool solution or you’re doing a lot of copy and then paste. But the new version is coming out in about, I’m hoping some time in the next few weeks. I’m testing it now. It’s going to include a command line interface. And everything in the Data Integration Lifecycle Management Suite is all built around this idea of achieving a piece of the puzzle so that we could do continuous integration with SSIS. That’s really what I’m after. It’s not going to have all of the pieces. I’m never going to be able to touch testing because frankly Pragmatic Works led you test does a fantastic job of testing SSIS. And they’re hooked right into the testing harness that comes with Visual Studio, John Welch and Brian Knight and the crew down there have done a bang up job. I don’t need to build that, that’s already built. But what I’m hoping to do with this command line interface, one of the commands is DCD for Deploy Catalog Differences. So Carlos, you may be developing SSIS packages on your laptop, and Steve you may be developing on an Integration Server in the office, and what I want to have is the ability for you guys to run this script every night that says, compare this to the catalog in this integration server set over here, and then just move whatever is different there. So whatever I have that I built today that since the last time that the script ran go ahead and deploy those services to this integration server. Now we’ve got that piece of the continuous integration going, right? We’ve got a target server where everything syncs up. So imagine that piece and then imagine running, having some way to automatically execute SSIS packages and then have tests to go look at the results of those executions and provide feedback right into TFS or whatever the testing framework can talk to in Visual Studio. And you’ve got a big piece of the picture now, right, for continuous integration.

Steve: Oh yeah. I can see how that would really save a lot of time in getting that continuous integration environment setup there for your SSIS work.

Andy: That’s what we are hoping for. I kind of mentioned in passing you need to execute the packages. Well, if you’re looking at the dilmsuite.com website, that SSIS framework community edition is a free framework that will actually do that. You have to do a little more work than if you pay for the commercial edition of the framework or the enterprise edition but that’s what it’s there for.

Steve: Wow, good stuff.

Carlos: Very cool!

Andy: Thank you! Yeah, thank you, trying to solve that problem.

Carlos: You know, that’s right. So thanks for the conversation, Andy. Should we go ahead and do SQL Family?

Andy: Absolutely.

Steve: So Andy, how did you get first started with using SQL Server?

Andy: So it was in the 1990s, and I was doing manufacturing automation at the time. On one of my projects we were storing the outputs of tags as they’re called in Human Machine Interface software in an Access database. I believe it was Access too. And to stress test it, I turned on all of the tags, it was over a thousand. And I set the software to record the value every second, and I left for a holiday weekend. I came back and there was a 3GB MDB file on the drive and when I tried to open it wouldn’t open. I went to altavista.digital.com and I typed in Microsoft database and searched and I saw this thing called SQL Server. This was about 20 years ago, maybe a little more. Carlos, I don’t know how long you’ve been around Richmond area but back then they used to have this computer and software sales and shows over at the showplace in Mechanicsville. I went over there, I’m going to say this and then, because it’s been 20 years. I think the stature of limitation is over. But I purchased an NFR copy of SQL Server 6.5 for like $20. It was like a week after that and I took it down to this plant and we installed it and I did the same test and it succeeded. I was able to open up Enterprise Manager, I believe it was Enterprise Manager, maybe it was Query Analyzer, I was able to open up stuffs and still run queries against the tables and get data out of it, even though it was 3GB of data. When they saw that that worked, that the promises were true, they bought a licensed copy. Actually they bought 7.0 because it was out. It just come out I believe and we upgraded, and of course tested it again, it run. That’s what got my toe into water with SQL Server.

Steve: Interesting. I’m amazed on how often we hear that, started with Access it didn’t do what we wanted to do so we did something different from there and that’s how we got to SQL Server. It’s been a pretty common theme along the way with other guests as well.

Carlos: If you could change one thing about SQL Server what would it be?

Andy: It would iterate better. So all software has corners right? Everything has something that it does really really well and something it doesn’t do so well. I do training, Tim Mitchell and I actually deliver the SSIS immersion events these days for SQL skills, and one of the questions I’ll ask to students is, are cursors bad? What do you think? Are cursors bad?

Carlos: depends on the usage, right, your mileage may vary.

Steve: I know, in my experience I’ve seen some cursors used very badly. And I’ve seen some cursors that have been used more appropriately and they’ve worked well. However, they’re not as efficient as I’d like to see them.

Andy: And that’s why I’m going with whole thing about iteration. It’s not just about cursors. Anytime you’re doing any kind of looping inside of the engine. Again, you know, Microsoft love it when I say this way, cursors aren’t bad, SQL Servers are bad at cursors. I feel that’s accurate and I get why the advice is out there, you know don’t use cursors. I had this experience about 10 years ago where or maybe it was more than that, but I was converting some Oracle code over from PL/SQL to SQL Server. And there were some cursors in it that were like three layers deep and they were nasty. And at that time, I knew as much as I do and I promise I’m not picking up on anybody because I’m still learning. But I looked at that and went, “This is just wrong. How could this ever perform?” And we had a test server set in there with it and I ran it and darn men it flew. You know, I rewrote it obviously. I think that may have been my first experience with, and the names of the tables flew out of my head just now, but it was Jeff Moden wrote about it. Back about that time I was looking for it. Some kind of tables, but I’m getting old I can’t remember what things are called. But basically you create a little lookup tables and you just get what you wanted they’re lightning fast.

Carlos: Tally tables.

Andy: Tally tables thank you. I can never remember what they are called. Yeah.

Steve: There we go.

Andy: Yeah, Tally tables, so I have my first experience with those but we had a solution that worked and I checked that box and move forward. Yeah, that would be my answer to that question. I’d like to see it do that. As long as they can do that without breaking any of the other cool stuff because SQL Server doesn’t awfully a lot really well.

Carlos: That’s right.

Steve: So what is the best piece of career advice that you’ve ever received?

Andy: Oh gosh! I’ve received a lot of really good career advice. I’ll share what probably impressed me the most recently. Do what you are, and you know, I’ve been a big believer of doing what you’re passionate about. I modified that belief since listening to Grant Cardone and other speakers talk about this advice where they say, “Become passionate about what you’re doing.” And I agree with that more. I think I was very fortunate in that I’m able to make money doing what I’m passionate about, technology. And, you know, it kind of just fell into place for me. But there are a lot of folks out there who want to get into technology, they’re not particularly passionate about it and they come into the field kind of hesitant and resistant and afraid. And I’ve been tailoring most of my career advice these days to those people and saying, “You know, find a way to get passionate about this.” And I’ve been sharing some of the things that I like and I don’t like.

Carlos: Andy, our last question for you today is if you could have one superhero power what would it be and why do you want it?

Andy: Oh wow! That’s a toughy. I’m a Batman fan, ok, I’m 53 years old. My wife got a picture of me in a kiddie pool when I was about 18 months old with a Bat mobile toy, ok. I am a half century into Batman. I have always been a Batman fan, but the truth is Batman isn’t so much of a superhero. Well, I’ll say it this way, he doesn’t have like those powers like I think you’re asking about here. He just train really hard and he has that discipline to do all that and that drive him to his tragic, it did drive him to that, been a huge Batman fan. But I don’t think he qualifies as having a superpower so I’m going to say that first because first and foremost I’m a Batman fan.

Carlos: There you go.

Andy: I would want to fly. I think Superman’s powers are cool but I would want to fly. You know, I like the idea, I like what Dr. Strange does, right, with folding time and being able to manipulate stuff. But as I’m saying that I’m thinking about what I just said about failing and I will probably blow up the universe. Probably not a good choice after all. Maybe, you know, my third choice, I’ll go to speed. I’m not a fast person at all in any aspects, mentally or physically. And I’ve always been a really slow runner and a poor athlete. It would be cool to run as fast as Flash. I want to stay with that one.

Steve: Nice I like that.

Carlos: Well, Andy, thanks so much for joining us in the program today.

Andy: Thank you guys! It’s been a great conversation. I hope it turns into a fun show for people listening. I really appreciate the opportunity. I think what you guys are doing is great. I’ve listened to several of your podcasts. I haven’t listened to all of them but it’s great to listen to people and learn more about what they’re thinking. And I love the SQL Family questions, those are awesome.

Steve: Alright, well thanks, Andy.

Andy: Thank you guys!

1 Comment

Leave a Reply

Back to top