What’s new in SQL Server on Linux and containers - BRK3228

Show video

All, right good afternoon welcome to, the. Lunch session at, Microsoft ignite did you miss lunch for because of us or oh. There's. No there's a lot of pressure VIN we. Better deliver so, hey. Hey thanks for coming to our session day I'm Bob Ward I'm a principal architect, at Microsoft. Quick. I'm gonna live in introduce himself because I think you're gonna just love the treat that you have today cuz then really knows this stuff very well but for me it's an interesting journey it's, my 25th year at the company and. This is the 25th and review a sequel server on Windows, NT, but. In 1993. When I joined what I thought was a little PC company my background was Unix before. There was a Linux so. My chrome was actually UNIX development C++. And Oracle and ingress ingress anybody, know what that is and Sybase, systems yeah there you go so, it's kind of coming home for me to talk about sequel, server on Linux in containers, and I started, my journey here with the team last summer and, been working ever since so then once you tell us a little about what you do at the company hi my name is VIN I am, 25 years old no I'm just, I'm. Just that would have been really bad. You. Look 25. My. Name is van I'm a manager, with the sequel server product team over the last year, two, years I've been working on, developing, sequel, server on. Linux, specifically, bringing the features that, some, of them are which available, I want announced later today and. Now. My main focus is with sequel server in containers, awesome. I don't know then you got to talk tomorrow on containers which I think you guys should take a look at it's gonna be pretty good stuff no one knows containers, better than VIN I'm. Trying to learn I go into containers talk to at, the session on Thursday so. Hey, last. Year at, this event sequel. Server 2017. Was announced, and, we announced that we announced the fact they're running on Linux and containers, and so, we're not going to do a session today to review everything, back that we did in sequel 2017. But, if you're gonna talk about you know where you're going and what's next you should talk a little bit about looking back you know what do we do and. I thought you might enjoy this slide a little bit because we get some questions and the. Frequently. Asked questions about sequel server on Linux and I and I want to address them kind of on this slide like one of them is if you look at this slide is anybody using this thing and. There's just some stats we have we don't ever publish, our exact, install base of information but, six plus million dr. Poole's that's probably updated a lot more than that now and thousands. Of different instances, and CPUs that we've observed customers, running both, sequel server on Linux in a VM in a bare-metal and also. In containers, and to. The right over here you see some company names these are actual companies that our customer, case studies so another question I get is I'd, you have any case studies have any really doing this and you, can go in the Microsoft website and look up these companies and. See their story and it varies their story and if you look kind of the next point, here this. Is the observations, we have about customers, and these, customers. On the right you know reflect that and one, is is that customers, like to standardize, on an operating system so we might have customers running sequel server on Windows and in, their organization, they're thinking about running Linux now as a standardization, and they, don't want to move everything at once so they end up in kind of a mixed environment where sequel will still run on Windows and now, sequel server running on Linux and then. Customers, kind of reevaluating. Their preferred database platform, for new applications, I may. Be using Oracle for a while may. Be using my sequel or Postgres sequel and I'm wondering you know is is sequel server now an option and that's, one of the reasons we did this right is to give customers now options, because, they want to use Linux and can, they run our database platform, then we just have customers, literally, making this migration, story from.

Another Database platform, I'll pick on dv01, it's one that we've shaping. Quite a bit and it's a great story because it's a customer that, uses. Post-course equal for many years definitely. Heavy Linux customer, that loves that operating system loves that development platform but. They wanted something different because post core sequel did not scale for the needs that they have as their business group and they. Looked at sequel server for a while but it was running on Windows so they literally reached out to our executives, and said is there any way you guys can make this thing run on Linux and, once I found out that we did they. Were one of our early adopters and a great success story of what we done now. Here's some other frequently asked questions we get this all the time and I'm happy to address them for you though is, it the same on Windows I will, defer that to the next slide as we talk about our feature. Set in the sequel server engine and things that want to call me edge around the engine and what though they're the same on Linux. Or Windows and, I also have a small architecture, discussion if you've not seen that before that helps kind of seal home. Point I got. This this morning from a customer, asking me right after my session on sequel thousand 19 and that, is the performance the same and the, answer is is that the performance is very similar and it just depends on your workload and depends on your Linux configuration. And in, some cases what we found at Microsoft, Windows. Performs, slightly, better than Linux and in some cases Linux perform slightly, better than Windows, there is no pad answer but the answer I give is that don't make your decision on, sequel. Server running. On Windows or Linux just. On performance, because, you're gonna get good performance from either system, don't make that your choice and the funny running. Joke I had I had this at a conference one time a customer, said should, I run sequel server on Windows or Linux I'm answer was yes so.

I Want you to walk away with that thing that I know I need to run sequel server no matter where it runs whether it's Windows or Linux the. Other one is about licensing, I have, a surprise how many times I get this question we're kind of technical engineer guys but we, want to make sure you know the answer that if, you have an existing sequel, server license, today it is the same license that works on sequel server on Windows or Linux you'd have to buy a special license just, to run one, or the other I will, tell you though and I'm pretty sure this offer still exists right now Red, Hat's a great partner of ours and so if you choose, to run sequel server on Red Hat Enterprise Linux you. Can get a discount from Red Hat and from Microsoft, and a subscription model to run sequel server on Linux in that environment. Here's. Another one raise, your hand this audience and it's hard for us to see with these huge bright lights if, you consider yourself a pretty good expert on Linux a. Few. Folks that have that knowledge so, what's. That what's that what's it so so, so. If you're in this audience going like I know nothing about, Linux, I know very little and, I know Windows pretty well do I have to know Linux and if you're a Linux person written they'd say of course you do my. Answer has been in my experience, going back now into this environment for running sequel and Windows for 20 plus years is that you, need to know Linux just as well as you've done things on Windows you. Need to know how IO works enough to how to configure discs properly you need to know how to network, Infinit. Working and you, got to learn the shell if you know PowerShell, very well which PowerShell does run on Linux now but. You need to learn maybe the native she'll like the bash shell so that's been my journey is learning certain commands, and syntax, from a shell but, also knowing some aspect, of the operating system in, order to configure sequel, server in a way and we've got guidance, in. Our documentation, on how to tune the Linux kernel specifically. And it's not a huge set of documentation, on how to do that for maximum performance so I would say the answer is yes, but, don't think that you have to simply turn your entire career on a dime and you're. Not going to be able to pick it up on how to use sequel, server on Linux and in fact the installation, experience is. So simple that, getting up to speed on sequel, server on Linux can be quite quickly without knowing Linux actually in a deep way. So. What's Linux are most, customers, using again, I'll have specific, numbers but it's very simple answer a lot, of customers use Ubuntu just, for development or trying sequel server but by far most. Customers are talking us about enterprise workloads and productions are it's red hat red, Enterprise Linux is the preferred, environment. That most kind of type production customers are talking to us about at least and, then. Finally is sequel server different, in a container I'm, going to defer that question to, Vin's demos because when, vim goes some of these demos you're gonna find out how very much sequel. Server runs in container and it's, just sequel server. As. Far as sequel server being the same I've.

Been Showing a second a platform. Diagram, that we've used before to, tell. You and convince you that the sequel, server core engine is the same running. On Windows or Linux and, I literally mean that from a binary perspective, the same core bits, run. On Windows, and Linux we just have one code base for them but. What this diagram looked like before if you look at it closely in the right-hand side and look at the blue dots that say new and planned these. Are features, that, are part of the sequel server product, that. I consider to be on the edge of the engine things, that rely on the engine but are not necessarily built into the engine that, people use things like replication, or poly base or. Distributive transactions, or machine learning services, and so. In sequel 17 these were blank, and. With sequel server 19, preview, that we just announced you see where it says that word new these. Are features now that will work on sequel server on Linux by, the way quick know hear the word planned right, here for, poly base is. Not because it doesn't work it, does we, just weren't quite ready in CTB 200 to. Have that working for you but trust me that's coming along very quickly so I expect this slide to look like black across the board the, only noticeable, difference is up at the top where it says bi services, so, our. Team for analysis services and reporting services they have not made any decisions, yet about, their plans to run on sequel serve on Linux but remember, those. Services on Windows can connect to, a sequel server on Linux unchanged, because the core engine is actually the same and. What do I mean the core engine is the same it's, because of this platform diagram, instead. Of actually recompiling, the sequel server code on Linux and changing. All our calls for api's to the Linux kernel we, use a very clever technique and you may have seen this before, through. A project, called drawbridge for Microsoft Research and. This concept called the sequel Powell you're gonna hear us use that term today a little bit our session where, the sequel server code runs. Unchanged, from windows in this pal layer quite. Frankly in a way that doesn't even know it's running on Linux but in a very lightweight way so it's still very very performant, so, the sequel Powell is an interesting innovative technology, that, gives us ability, to run various, processes, that were compiled and built for Windows on, a Linux, kernel without recompiling, that code. So. If you think about the themes that we're gonna talk about today one of them is platform of choice with, compatibility, and then, is the one that actually convinced, me to change this messaging we were talking about a session, we did this summer an internal session, it's, like Bob I don't think people get enough. That there's compatibility. Built in with this system, with using sequel on Linux and, what we mean by that is that you can take a database, for example and back it up on sequel on Windows and just, restore it to Linux or vice versa again. Because the core database engine is the same so. First of all this is our messaging if you've not seen us on sequel server 2019. And seen, and heard about the news about our release with, our preview we just made the news on yesterday, out, of session this morning about a road map and we went over this slide and I'll just apologize, how, about doing too much of a repetitive talk on this let, me just kind of nail it home for you number one on the left is.

Really The, significant, breakthrough in this release and the, interesting thing about our, ability, now to integrate, with the world of patchy spark and Big Data is Linux. So. In this model right here these big data clusters are all Linux, containers, running on a kubernetes cluster and, that is one of the powers of platform we've achieved to be able to make that story happen really. Good session today at 4:00 p.m. by our colleague Travis right where he's gonna go in-depth show, you some pretty cool demos and how that technology works and then. Of course I mentioned about choice of platform, and language an interesting. Story here it's not just about the fact that you can program with, these programming, languages against sequel server whether. It be on docker on Windows. Or Linux but. You're gonna see in this session we're going to talk about we. Now provide extended, T sequel capabilities, with, things like Java so. Now you're gonna have the ability to run T sequel code that's. Gonna be able to call a Java program within. The database engine itself, that's actually pretty groundbreaking stuff for developers that I think you're gonna like that we've now announced for 2019, we're. All about performance, in fact the number one benchmark, today 40-piece, EHR analytic, work load is on linux so, we're a believer in making sure that the performance of sequel server can run well on these platforms. We. Won't get into the depths of it today because it's kind of our thuggin I'll just to the sequel legs conversation. But. It's not just about running fast it's about keeping you fast so intelligent query processing, is built-in. Capabilities, to the query processor engine of sequel server to. Adapt, or make, smart decisions on. Your queries, when you compile so. That we can you, know make decisions, based on your workload to help fix problems for you automatically, or advise you on problems, you have to spend so much time diagnosing, query plans that's kind of breakthrough technology, that's pretty interesting and. Then security is very important, and for sequel. Server we are the most secure platform of the industry and the great news for you there from a security perspective on features, is that just about every feature you have in, sequel, server on Windows works. On Linux the differences you run to its configuration because, some of these things are based on dependencies, on the Linux kernel just. As we depend on Windows and so, some of that information some some of those types of scenarios like maybe Active Directory authentication. Requires. Some slightly different, configurations. But we do have a new feature in nineteen. On Linux to make that easier for you and then, of course power bi and reporting services not, running native and Linux but still run on Windows Server can connect to. A sequel Linux with no issues now, look at the bottom this diagram this is an interesting part if you, think about something called in-memory, technologies. Quickly for us that's, columnstore, indexes, that's. In-memory OLTP, these. Are unique technologies, to us that help your workloads, and we also use, this technology within, our own innovation, like for example columnstore, helps. Power some of the analytics, for the big data cluster, but. I'll just tell you I've done some evaluations, of this technology with our competition and yes. A company like Oracle has some of this technology. But it's like an acid and. You look at Postgres equal they do not have a columnstore, technology built, in their engines nor do they have in memory LTP, and we do have those and then. Finally consistency, is important to you so, when you think about sequel, server on windows sequel, server on linux or even our public cloud offerings, the. T-- sequel language is critical because it's a common surface area of skills.

And Interfaces, you need to interact across all of those things and then, you're going to see some demos from us we're going to use a new tool set called Azure data studio which, we just announced yesterday used, to be called sequel operation studio, and. Talked about consistency it's. The same look and feel of a tool that runs both on Mac Windows and Linux with the same interface, same look and feel and you, can even run that against your cloud properties, on your your private cloud or I guess our public cloud offerings as well and also. About programming interfaces we talked about platform of choice when you're building and developing applications, we, want those things to run very, very well and make them compatible so. You'd have to make decisions like am i running on Windows or Linux what, do I do, that's, our goal you just build the app and point it to either one and it just works and that's about choice, with compatibility. So. I kind of mentioned that a little bit here I want, to make sure it's very clear if you've not seen this story before about our official, support, story whether it be Windows or, Linux distributions. So today we, officially support Red Hat Linux Enterprise. Sousei. And a boon to it. Works on other Linux distributions, we've done it ourselves we've tested it but we wanted to give you an enterprise support store enterprise class support story so for example with, Red Hat we actually have a joint partnership together with that company where. If you have a support issue we actually work together we don't pawn you to either company we, work it together we have people in Redmond that actually work with our support team we're, going to give that type of feel for you when you go into enterprise workload with sequel server out of the gate and that's why we choose these, specific distributions. For official, support from Microsoft, and then. You're going to see a lot or in nineteen. And further about our use of kubernetes, you're gonna hear about that some more today you may have heard a little bit about it already but, kubernetes is going to be a driving force for us in orchestration. And in, clustering technologies to support high availability going, forward. These. Were some of the features that I talked about this morning in our roadmap session, and the, point is is that when you're thinking about sequel, server on Linux don't forget, again it's the same engine so, don't think you don't get clustered, columnstore online rebuild from sequel just because you saw it on a webpage from us you, may be thinking oh it's only for Windows all, of this technology works, the same in sequel. Server on Linux because again it's the same quarter based core, database engine so, if you're a customer, worried about gdpr and you. Need to look at data classification and you see now we have that built in with metadata, and auditing, built in you, get that and sequel server on linux don't think that you're just not gonna get these features because.

We Announced it because we've been more of a windows centric product from this go. You know from particular, point in time now we've been so Windows centric do, you still get this stuff. So. I think it's no better person to talk you about feature, parity and what we're doing this product because then spent a lot of time in the space of then once you start walking through your your view, I think you told me something earlier how, you felt about feature parity right sounds good Thanks about I think that was a really good summary of you know from 2017. To where we are now and some. Of the things that I'm gonna talk about now is some, of the improvements to, specific, to secret server on Linux. Specific. Tutorial 2019, CDP -, so. This slide encompasses. Some of the improvement areas that, we have for sequel server on Linux this is not everything that we have been working on but these. Are the areas that since 2017, a lot of our customers have come to us asking for okay. So on the top left you see we. Have been working on replication this was on the, list of improved on. The diagram comparing. Secret. Server on Windows to secrets or on Linux with, 2019, we have replication, we've, also introduced, ability to deploy availability. Groups to kubernetes in. The center we, you see that we have a open. LDAP support for third party providers what this means is if, you, could if you, have a third party ad. Providers, such as Centrify, or Vendela managing, your ad environment they. Can also now manage sequel server on Linux, as well right. We also have some hardware, improvement a sequel server plays well on Linux plays well with hardware improvements such. As a persistent memory so, pmm and Bob will talk about that in a bit with. Secret survival index we, also have machine, learning services, with arm Python, this was pretty, previously. Available while, secret server on Windows we, are now bringing it to secret server on Linux, another. Feature. That a lot of our customers have been asking us for is distributed, transactions, we. Also have that 20:19 and there, are a few improvements around. Secret. Server and containers, namely. Official. Support that was announced yesterday for, sequel, server and Red Hat containers, event. Before you go forward I gotta just laugh before, the session Yui told me he said Bob you know what I think our goal is to make these features, Paradiso a good story that we don't come back next year is that what you're yeah, I, was saying that you know by the time we get done with 2019, and we, ship, this thing you. There won't be a session on secret server on linux just be it. Would just be Seco Sarah I think it's pretty good story that's a good story yeah, so. On that though talk a little bit about secret. Server application, on Linux and embalm, mentioned that, a lot of the features the, way they work is identical, but the configuration, is slightly different and this is what the slide shows. So. The goal to when we thought about bringing soukous or replication and it has many, of those agents like. Log reader agent, snapshot. Agent we want to make it work identical, to the way it does on Windows, so, you could so you can see this very first bullet point is our goals to achieve parity so, we want to make replications. Such as transactional. Snapshot. Merge those, technologies, work, the same. They. Do on Linux as they do on Windows so, there is no additional. Installation. Steps, that you need to install all. You have to do is enable. Sequel. Server agent which is required, to required. For replication and I have those two lines, that's all it takes to. Startup, sequel server agent all. Right at this point your sequel server, replication. Is ready. For, for configuration and this, can be merged with SSMS, using those wizards just. Like you do with, sequel server would know and this. Can be set, up across Linux and windows so you can imagine if you have an existing, replication. Set up on. Windows, and you want to add a subscriber, that's sequel, server on Linux, that is very possible and we can imagine we, do have some folks looking, to.

Set Up and that type of replication, topology and. We've also gotten a lot of work to ensure that 80 works Active, Directory works, in mixed. Environments, as well as Linux only as, well it's nice. So. With that let's get to a demo of this. So. I'm going to configure I'm going to setup sequel. Server on Linux and get it running and the way I'm gonna do this is actually with containers and I'm. Going to be using a tool called docker, compose for. Those who are not familiar docker, compose is a, tool that, spins. Up multiple, containers that interact, with each other in order to make the service complete. Typically. You would use this with a web app with a database because they were the, web app requires the database to get it up but. In this case I'm going to spend up to databases. DB. 1 DB 2 and I. Want to have them I want to have a snapshot, replication. Push. Replication, from DB 1 to db2 so. If you look at my environment I have my password I have, my accepted, the EULA and I, have the, enable. My agent and this is why I mentioned this is the only step, that is required to actually start configuring. Replication, and. With db2 this, is my subscriber, here, and again. It has the password I, said. The EULA and I'm setting the. The. Agent so, one of them is listening, on my local machine on, 2500, the others listening on 2600. Right. Now if I were to look at what this does at, startup, time they, actually so my DB one actually, runs a. Actually. Runs a sequel script at, startup time so. First the first thing I'm going to do is I'm going to create a you. Know a very basic database. I want to create a table and answer some data into it alright and, then I want to proceed to set, up the distributor, I'm going. To set up the, subscriber, we. Won't have time to go through each and every single line, here with. All the replication, store products and then. I want to set, up a publication. Add some article, and the last thing I'm going to do is I'm, going to add a subscription, the subscriber and, I. Want to start the jobs with. SP. Star job so then real quick this will be the same set of scripts that you might write like we use the Wizards and windows and SSMS, it's, pretty much gonna do the same thing you're just doing from a script perspective, yeah actually that's, good point Bob why don't you generate to actually prep for this dududu with us if I did was actually use SSMS and I hit the generate script button nice okay it's so great. Learning tool.

You. Know I'm much less error-prone. In. Case you miss a field so in my second, database, instance, you can see that the only T sequel I'm running is create. Database. Sales so here I shouldn't, have any tables and I, shouldn't have any, data in it if I were to just run this container on its own so. With that said you know let's go ahead and, start. This and, with the, way to start this is I will use docker come compose. Up. Right. And, at this point you can see that I created the network which. Is allows that containers, to communicate, as well as both the instances, and, then here I didn't. Run, this in the background process, because I want to show you what's actually going on here, you can see that the instances. Are. Starting, up and some of this is if you would look at a star blog that's what it looks like now. It's gonna get to the point where I'm going to create some of those databases. You meant you see here starting. Up database sales and then. I'm going to go ahead and start. Create. A start configuring replication at, this point and you can see here I was a scroll slightly a little bit up I set, up the distribution database on my my first. Instance and then, I want, to configure some, of the the, publication, and the articles and. You. Can see that near the bottom I have started the. Snapshot job so I needed the date I need a snapshot to be available before I apply it. And. Then. You. Could and at this point it just finished completed, running the actual. Snapshot. Job of actually pushing other of the push itself so. If I go to my Azure data studio and I, connect to my. Ii, database and. I'm gonna log in here. Oops I. Can. Connect and. I look at the database so this sales at this point before. Replication. It wouldn't have had any data or tables, and if I look at the tables I could see that the customer table exists, which means that, the staff the replication, snapshot, was, applied from, my first instance to my second instance and. If I were to go here just, to look at the data itself I could. See that the data is there right. So. They let me ask a question so in. A way like. Containers, give us this new opportunity, you can build replication. Containers, is what you're literally done effectively right like, you could use docker, to deploy, a series of replications. Sequel servers with publishers and subscribers. Automated. In an automated fashion right now one one really good I think one use case off the top of my head of using this is you, can imagine seeding, a test, database data. From, you. Know obviously, after you cleanse it from a production environment you, know a mock, data okay. Very. Cool. Where. Is. Neat. Another, feature that we spent a lot of time with last year designing, and bringing over to sequel. Server on Linux is distributed transactions, and in the blue boxes those are some of the capabilities that work with distributed. Transactions, so, distribute. Transactions and Linux works, with. Secret. Servant link servers across, Windows, and Linux environments. It, also works with all a transactions. Using. DTC, so what that means is if you have a c-sharp, application. That, depends on distributed, transactions, this. Will work with. If, you connect to a secret server on Linux, instance that will work in, that environment in addition. And this is the last last point here in the blue this is actually a net new and is. That we support native, XA transactions, what we found for. Distributed, transactions, on a linux, environment is that XA is the way a lot, of applications. The. Way they do it and the way we did this is with by introducing two sub processes, in the sequel Powell on the top left, outside, the top right you, can see that there's. A MS, DTC process this is very, similar to the one you see on Windows and we, an endpoint mapper, now. If there was, an MST TC client say a JT an app that's supported, by JDBC, or ODBC, driver and, that, wants to start a. Distributor. Transaction with sequel server so let's say the T sequel was begin, trend it. Would first talk to the endpoint mapper and say, and ask, it what port is the DCC service the.

The Sub process listing and sequel server and, then. It will provide the port which effectively, acts as a port redirect, the, client can, now talk to the MSD DC sub process, in. The, sequel server process, and then from there we could continue with, our distribute, transaction. They've. Been real quick on that that's slide I think what are the key parts that architecture, if you look here is it's a sequel pal he in the Box the sequel pal and, I mentioned to earlier that innovative, technology, this, allows us to run DTC, services, from Windows, in. Linux so. We don't think a recompile, DTC, from Windows we didn't go to that team and say hey can you guys rebuild this for Linux for us right so it allows us to come to market very great, example of us to come to market quickly with the feature functionality, that you need and. Especially going from Windows to Linux and that's that's, one of the innovations of sequel pal that's really amazing this situation, so yep. So. Now I want to pass back to Bob to talk a little bit more about some of the other features in the, modern native platform thanks Ben appreciate it yeah. You can see where Vince headed here right I mean again, we want to put ourselves out of a out of a talk next year we don't have to do this again so, replication. DTC we don't want to have an excuse anymore and I would say also between the two of us we. Can talk to us afterwards if this is a scenario if you have a blocking, situation, where you found something a linux sequel linux is not working to, your like you need to go and tell us because we want to make sure again that there's just no reason for you to make that leap if you want to go move to the linux platform, so. We talked about choice with compatibility, then. Talked about feature parity and now let's talk a little bit about more what's some modernization things, we've done specific. To sequel server that also run in Linux it's a little bit unique and different so. First of all persistent, memory optimization. Many. Many, of you have heard maybe and now in the industry technology called, like env dim or persistent. Memory modules, that run in hardware platforms, they. Have unique characteristic, it's like a drive it, it's battery-backed right it's like pout there's power to it so if you start using this technology as a drive you can kind of affect that your data is persisted and, sequel. Server already, supports, in fact I demoed this last year at the, past summit that. We have sequel server supports, these type of devices in what we call block mode like just a normal Drive and you get better performance just by using that technology, but. What would be possible if your database engine, could understand. That you are using one of these drives an API. Support, was available so you can literally just copy memory, blocks memory. Pages, between the devices like a mem copy between processes in your and your in your on your system what do you do that would, that be faster and it turns out heck yeah so. In sequel, server 19, for Linux we now offer persistent, memory support we've actually what we called enlightened mode if, we, recognize we're, on a linux server with one of these devices we. Will literally bypass the i/o stack of the Linux kernel and just memory, copied data like, your data from, one of these drives into, sequel server buffer pool providing. An amazing, performance scenario, and our. Genis Fernandez our colleague tomorrow is speaking, very on this technology doing a demonstration of, it and give you more details about how this actually works and it's, not the firt it's just the first step in the journey we'd like to do other possible, scenarios, if we, can recognize the device is like a memory module how, could you something very innovative to. Actually avoid IO bottlenecks, for your applications, so that's. One thing that's a performance optimization, I think you really like but. What about if your developer as, I mentioned earlier the. Fact that we're gonna support the concept of doing Java and, you may ask yourself okay, wait a minute I've, been down the path of using extended stored procedures of a sequel server before, sequel. CLR, other, types of technology, and. My experience was not all that great I still, think sequel CLR is a good technology for to use but there's some there's some problems with it one.

Of It is is that it must be dotnet, second. Of all if you want to go off the dotnet path like to say a piece that call a piece of Java code it's. Called an unsafe, assembly, at that point and we don't support that on sequel server on Linux nor, they really recommend you do that the. Other functionality. That I found and I was in support for a long time so I ran into these type of problems is that both, extended procedures, and CLR all run, the memory space of the sequel server process, so. If there is any issues that occur with some of this code it could cause some issues for your overall sequel server stability, that's that's bad, so. Along comes in sequel 16 the ability to run R and Python together and we. Built a very innovative technology. You see in the screen here what, would I call a launchpad, satellite. Process. Infrastructure. And the, way that works is is that if we recognize that you're gonna try to invoke an R script or a Python script C, what we call a special, system procedure, which I'll show you in a second when. We recognize that we, will actually take this launchpad, service, ok. Launchpad, service will, invoke a satellite, process, to go run your. R a Python, script and, do. That on the same computer a sequel server but provide a direct pass so the satellite process have a direct communication path to, the sequel server on this box so. Provides really interesting, scenarios, for you to go run our Python scripts in an isolated, way so they don't interfere with the reliability equal server but, in a performant, way because I caught talk directly to the database engine, and they. Can exchange data so for example you can now run queries, with. Your RM Python script sequel, will transmit, the data to your arm Python code running you, can interact with it and send the results back pretty cool so. We've had that in sequel 16 and 17. We announced Python, so. Along comes sequel 19, and the ability now to run java we just kind of thought Java was a natural, path. For us to go after and so. Java has the same type of properties, where this launchpad, interacts. And spawns off another process, to run your java code and you have the same type of interaction, where. We're really headed here this, is kind of our thinking doesn't mean we're actually going to do this but this is our planning and our thinking is that, this type of model could be extensible.

For You, imagine. If we built an API set where, you could use this same infrastructure, to launch whatever you wanted to launch because again these, processes, run the same computer, but. They're, isolated, from the sequel server process, now, here's the other aspect, when I start talking about this diagram that people ask me is like how do I control, that stuff how. Do I prevent, a programmer, who's a Java programmer, from, like running something that just consumes, the CPU and overtakes sequel server well. This system is titled resource governor resource. Governor being a function and sequel server again, in the core database engine so it works on Linux and Windows and, resource. Governor provides, the capabilities, to control, how, much CPU memory, or even affinity, that these processes, run at now, out of gate in CTP zero that functionality, doesn't exist on Linux but that's what we're going to make sure happens before we ship on Windows it actually works we, would use something on Windows called Windows jobs Linux. Provides a function that called C groups which we can take advantage of to provide the same type of functionality so. Pretty cool technology and, I, want you to just for. A second soak this in you're, a developer you know T sequel, but. T sequel doesn't provide some functionality, for you and you, know that you can run a piece of Python script or Java Script to supplant that well, how do you integrate these worlds, well, now they're providing this T sequel interface for you you can embed this inside your stored procedure for example call, off your Java code your Python script again you're going to interact with data doing it return, data back and now, what have you've done you've extended, the T sequel language of that box right there for our Python, and Java is effectively, extending. The, T sequel language for your needs but, again in a very isolated way for your processing, for reliability and a secure way in a performant, way and, it's something that you can control, so. Let's take a look at a demonstration of that this.

Morning If then, you get hit 6 for me right there. This. Morning in the. Roadmap. Session, I did a demonstration of this on Windows and so. You're like ok well I wasn't there I didn't get to see that right so what did you really do or maybe you were there and you're like well how you can do us on Linux so. Let me first show you right here here. Is Visual, Studio code. With. My java program, so, our, great, colleague, and friend nelly built this for me and i. Am not a word classification. Natural, language processing an. Engram, is. A functionality, you need to do that you to do word classifications. And language natural language processing you, need this Ngram functionality. And so. That, is an existent T sequel there is no T sequel Ngram function, so how do you do that well you build a piece of java code that does this and in. This java code are actually, specific. Protocols, for. How you're going to in you're going to actually accept, rows of data from sequel server and then. Send rows back so there gonna be kind of a map. We're gonna help show, you how you would build your Java code how to do that and then, in the middle this is going to be the heart of the processing, itself for actually doing the Engram, processing, of this text data and so, you build a a main, class called engrams, which we're gonna show you how we call that mean. You scroll down where is that right. Here this. Is the class and if. You go down right here this get engrams, it's gonna be like a method in my Java code that I'm gonna call and then, we also show you this is all in our example of our documentation, we. Show you how. To do an input row and an output row this is how to structure a row from, sequel, server coming in from a table you would query right and, here's how to structure a Java class that models, the, output because. Again my goal here is what I'm gonna do is I'm gonna call, this, Java method passing it in a select. Statement I know that sounds crazy an, axle select state and with rows of data my tables that, this is gonna process these engrams on and then, I'm gonna spit, back out a set of rows so. I'm not passing as parameters. Into this Java method like an array of data that, I queried from sequel server I actually, have a clever way of just giving it a query and then, sequel interacts with together to feed it the rows it needs and spit back the actual, output so, how does this work well on Windows. What I did today is I compiled. A set of Java this. Java code in the set of classes and I put it on my folder here on my Windows machine and then, I ran it with, a script well, how do you do this on Linux so, I took these Java classes that I'd already compiled. Here's. A terminal. Window SSH. Session into, my Red Hat Enterprise Linux VM, running sequel, server 2019. And in. Here I have a directory called Java it's like I already had it up here so. I just copied, these classes, in there I didn't recompile anything just copy the classes in to this directory and, it was a sure data studio I ran, this script only in kind of break down how this works so, first. Of all this class path is where are the Java classes I'm gonna run with this method and you see in this particular case it's a Linux file. Path. Syntax. Whereas, before with windows it was C colon, backslash on, that folder I just showed you so I just copied them into that folder put, it here now, the other thing I had to do is I had install, the. Packages, for what, we call extensibility, for sequel server so. Let me just pause for a second and tell you why that's significant. In. Sequel, server on Linux different. Than Windows the, way we actually distribute, the software is in form of packages, we don't put everything in one big thing so when, you install a sequel, server you get what Vin showed you get sequel server agent and the engine together which includes replication, and dtc that he showed you but. If you want to do this you would install a separate, package what we call extensibility, to. Do machine learning services, and Java and then. All you need to do is actually set some permissions, properly. On this folder so the sequel server Launchpad, member I mentioned that before can access these and then, you run a script like this and let's just kind of look at that if you've not seen this before this. Is this special stored procedure I mentioned called execute, external. Script it's, the same way you would, run R or Python and notice I'm actually not a specified, language called Java, whereas. Before it could be R in Python if you're doing like machine learning type programming the. Script, is in the script case this parameter, is actually. The name of the Java method I'm going to call and the, code is found in this class path and then, here's the input data so that's a query that's.

Gonna Get run against my local sequel, server sequel. Server is gonna take these rows and it's gonna feed it that Java program, based on those classes, I showed you right it's, gonna feed it row by row we can even streamline that to make that even better not, just a row by row thing and then. That code. Is gonna spit back actually, some. Output parameters but also here this result set so it's gonna give me back some. Sort of identifier, plus the Engram, result, this. Is really amazing stuff I'm not passing parameters, a bunch of data around I'm actually just giving it a query and telling give me results back in, a very efficient, way and that's. The way you run any of these things anything an r and python, and java is gonna be the same type of methodology so. When i kick this off and run it, it. Just comes back with these result of these engrams, and we're not here to study Engram, philosophy. And how. Engram and the grounds work but just to see comes back and results that so, what did it look like on the Linux system well. If you go over here I've, got these commands up I think here notice. Here I'm running a command sudo systemctl it's. A command, on Linux to check the status of a system D service unit service, we, have a new one called ms sequel launchpad d that's. Equivalent to that launchpad icon i showed you and in Windows that would just be called the launchpad service, so. If I run this and I put in my password. You're. Gonna see it's a separate service from sequel server running to handle this and if I also just looked at different processes, here. I'll. Show this here's. My sequel server processes, running the engine and then here are my launchpad, processes, and the way this works in. Order to support proper permissions, is that we have this launchpad, D process which spawns an, actual launchpad program, which, will then run our Java executables, one. Difference in the sequel server 2019. Architecture, for Java then. We did on 16, for Python an R is that we have something called the ext, host process, the. Ext host process, is one that, actually is going to support using. The Java. Runtimes, where you yourself can host a Java runtime with a DLL so. In terms, of Linux will host it that way the same way you did in Linux not with a DLL but, so the the launchpad will actually be hosting, the java code with this ext, ext host, process, whereas, on our, own Python it would be a separate process for like our term or Python or something of that nature the. UC ext, host process, now. Gives us this capability now to be extensible, in the future if we'd like to add other type of language support, that's. About as simple as it gets so. This is all built into sequel server so, you just get this with the box you don't buy add-ons the only difference for Linux is you're gonna be installing separate, packages, because. We kind of have that philosophy to make the overall sequel. Experience, more lightweight. And, more. Manageable, so that's, as easy as it gets we have documentation, already built on this how to run these examples, and. I want you to stop and think about what's possible for you now, you're. Out there building, a database application and, you're thinking about building Java code and your middleware, app well. What you may like to do though instead of said you know what I'd rather build it on the server side I want to do server-side programming on, the sequel server itself but. I don't know how to do that I don't interact with that now what if you're gonna build that credit card processing. Or something of that nature now. Imagine taking a T sequel stored procedure wrapping, it around that Java code. And then embedding that and another set of procedures, or something that call your overall application, and since. This Java code can interact with sequel from a perspective of taking in rows and spinning back rows it's, very efficient from a performance perspective so, I'm. Really excited about this I was talking to Nell even about this yesterday, is I would love to go build github examples, for every, job I think we can think of in the world Reggie.

How About reg reg X expression, type handling things of that nature so so, one thing I like to just add on to please what Bob mentioning is you, know you're executing. The code in the sequel engine which, means you don't have typically, when you have an application and, you have the database separately. Separate. What happens is you actually end up transferring, that data back and forth right and this ends up actually, so you can imagine if your data set is extremely. Large right right and you're doing this every query right you, can imagine that actually having, the toll and the performance, absolutely, right so this is why you. Know initially, we had this for machine learning type. Scenarios, where to, do machine learning you, need very large data sets to train your models right so instead of actually taking, all your data out onto a separate machine take, consuming, the memory and the, i/o. And, the, network you're. Just bringing, the model to the data itself, right, and that's and that's like one of the very key points of you, know in fact we're actually kind of recording. This machine learning and extensibility, because, think. About Python is a language and Java is a language you can do machine learning great, with those right but, think of all the stuff with Python Amish and Java, for example you can do that have nothing to do with machine learning so I kind, of feel it now it's a new extensibility. Of T sequel, machine. Learning built-in plus extensibility, of other types and areas like I mentioned we were talking yesterday about regular expression type parsing things, that things that we don't feel are necessary to put in the T sequel language itself we, think this can be a new extensibility, platform, for to use so so pretty amazing stuff go back to the slide, deck there. Ok, so again, I told you then. Those containers I should like to label that bin those containers I'm just learning for VIN I'm certain learning myself but I'd like VIN you to kind of walk over because you've worked specifically, in this area right extensively, what, is our plan and and what have we done new 4019, in containers, and especially, he's going to dive into this whole really cool concept on.

Unavailable Group for kubernetes yep, thanks. Bob so. These are some, of the improvements we have for CDP - oh that's announced. - now we are actually still working on many. Improvements that we will, announce at a later date. So. But at, the moment you know you can see here that you, know we're actually slowly transitioning. Off, serving. Our containers from docker hub over to Microsoft, container registry, we. Also guess they announced. Official. Sequel, server Red, Hat certified container. Image, okay so we've had a lot of customers actually asked for that and we. Partnered, up with the right I had to make that possible and the last thing I want to bring up and the slides that come is to mention this always-on, availability, groups for kubernetes, and. That means that we're gonna be able you now have the ability to deploy. And. Always on AG group in kubernetes and Will's go in that commit now. With 20. 2017. With. Sequel, server being available inside, containers, you could have deployed that into. A kubernetes, environment, secret server into. A pod however. So and this is the way it would work sequel, server we deployed in a pod and. Which we placed in the node you can think of a node as a machine and there's. A load balancer service, that the user could connect to and this is a service that has a static. IP all, right for. The user to connect to so. If there's something were to go wrong with, the pod say the service dies kubernetes. Is smart enough to actually spin up a new pod and put sequel server that initially. You will try to put it on the, same node hey, may not always and. Then. The load balancer is smart enough to reconnect. To that new pod now. If the node dies kubernetes. Are smart enough to put. That process, into, a a new node, you. Can see that here right, but there's a few problems with this a few, problems with. Just. With, what kubernetes, provides, out-of-the-box now the first thing you can imagine is sequel server could it's. A fairly complicated process, the process itself could be running but, it could be in a problem state now, kubernetes, won't spin up a new pod because it doesn't know anything about sequel server itself right, but the user will be stuck they will try to connect to sequel, server and it may not be taking any connections, then, you'd be stuck in a, a troubled, situation, right. The second is that you notice that any given point there's only one active pod right. So if, you were to spin up a new sequel, server instance, inside. A new node or, within. A within a new pod that, could take a little longer right, so and from my own experience from, wife tested they could take up to four, to five minutes to pencil in depending. On the location of the node if you have your nose geograph in, different geo locations, then, it could definitely take even longer right. So. This is why in 2019. We're actually. Building. The build the availability. To, deploy availability, groups in, kubernetes, so, let, me explain to this this icons are a lot going on but let me explain a bit so you slip the very top left there's an operator, right. So what the user ends up doing is all they deploy is the secret server operator and the operator will. Then deploy resources. On the kubernetes clusters on behalf, of the user so the user would never actually deploy, a primary, the. User would never deploy three, separate, instances, of sequel server hooked. Them up and ensure that they are in a G group instead. We. Wrote the code so that you can deploy, an operator the operator will then automatically, deploy three, instances of sequel server and create an AG group out of them so you can kind of think of it as a G's.

In A single click of a button right. We, also included. An AG, agent and what this is gonna do is it's gonna check the health status of each. Sequel, server to ensure that there's nothing wrong with it and you could connect to it so. You can imagine that, so. You could see that the load balancer, that's. Actually the service that's connected that the user could connect to from the main app if. Something were to go wrong with the sequel server instance whether the services can no longer be connected to or, the process self has died, the. Operator, will, automatically, tell the load balancer, hey will. Automatically, do a failover and say hey this, is the second instance over here now is is now the primary and this, switch over is as little, as seconds like. It's very quick because all these there's three active instances at any given time, and. Also. It. Wasn't the very top right because. Have secondary's, available, you can also do what we've always been pitching, is using your secondaries, for Reed workloads, and. This is very good for reporting, type, services, here so you can see that if. You look at the previous slide you can see that the. Load balancer, there has automatically. Connected, to a secondary, and, you could see that a swap between a secondary, and primary in this situation, as well Haven I kind of feel like you. Describe this and my, apologies I kind of built some of this diagram we made a pretty complex we're, trying to show their full capabilities it's, a little bit like you showed in a replication I mean we're talking about like containers. Being this way - like you just said one click we. Have this operator concept, and you've seen the complexities, of setting availability, groups of four for sequel server that, would this operator we make this very very simple the, other thing that people have told us a little bit about with, Linux with high availability both, for shared storage is, some of the complexities, of using pacemaker and setting it up now you're talking about like I don't even need that and I can use this environment, now to have like hae. And a clique or something like that so it's very very powerful yeah. And. We'll actually be doing a demo. Of these there. If you look at your sessions, at the end we'll probably have this listed we have a theater session of this, in live-action it. Takes a little longer to you. Know to do do do, do justice, to this, instead. Of this having just set up in this actually right, in to wrap things up I'm, going to have one more demo here. On. My screen so I want. To have I want to spin up now a application. So. Again, using docker compose I'm going to spin up a application. That connects to a sequel server the. Application, is going to be listen to 5,000 and the database, time 1500 and all just once I started up then we could really dig dive deep into the demo itself. Just. While, you're doing it take the picture you have an ASP app that you want to talk to sequel server and you'd, like to just deploy it as a together, is like a whole set. Of applications, which is a little bit the promise what containers gives you right so. I'm gonna do docker compose up in my asp.net, core. Application, and, what's it gonna talk through this a little bit so, now I'm spinning up, the.

Application, Itself and I'm, spinning up the I'm just the, day basis starting up as well. Then. I'm going to go to. Here a. Sec. Hope the schema probably, has not been applied yet, let. Me just let me take a look. Let's. Take a look. There. You go cool I had a bit delay so in this application so, one of the things that we talked about from the very beginning, to end is that this is really, the same sequel, server and capabilities. So, in this application is. Demo application, I'm using features that are. Specific. For example temporal. Tables and by, sliding the slider I'm actually querying, the data at that point in time right, I'm using yeah so that's one feature no I don't because temporal tables just come with the engine right yes it works yeah I'm also using features, built into the engine that could be configured through T sequel such as dynamic data masking Here I am masking, out the email and the phone number of my data, as. Well as this, whole application is actually backed by single table and there's, a feature called row-level security where, if, I switch the user the current user they, only see that they see different subsets, of the data itself all, right and this, hole and the reason why I'm showing this demo is because I'm, really just hammering, down the point of this is exactly this is the same sequel server whether, you choose to run it on Linux whether you choose it run it in a container or you choose to. Run it on Windows nice. Yep. Then. Just real quick on that app so to. Make sure I just have put my head around this you, took, dr. you have one set of docker compose definitions. That say here's how to build my ASP app right you had your code all supported, that on your machine and then the other is just a sequel server container that's it yes, that's, amazing so docker compose up I and, so this is all declared, see that's um one thing about this that I love it's all declarative, so, I declared, my intentions, for these applications, and their ports and so forth and then. I just say doctor compose up and I get execution. Out of that so that's one thing I enjoy about the docker system, is, to be able to declare things based, on the images I've actually built so if you wanna learn more tomorrow I do have a session that. Dives deeper, into app, development, with sequel. Server and containers. Okay. I'm just gonna put you in a spot here so I want, to know more about that so is is your intention tomorrow, to kind of show things like oh you just showed me or, what what what we're going to talk about just, having using. Sequel server in development, environments, for testing for CI, CD as well as some as, well as having this in, CI. CD pipelines like a DevOps type of. Op site scenarios, so. If you're a dev if you want to know more about DevOps you're thinking how do a sequel, serve in a DevOps hub environment, this is the session definitely, come to use well its development, as well awesome cool oh.

Yeah. Here you go, so tomorrow. So, first of all a couple couple, plugs for you one VIN. And I actually run a workshop, just. An interesting, experience yesterday to run our workshop we're making a few adjustments to it to make it easier for folks, some. Some great feedback from our audience but, effectively what we've done is and we've got a. URL. For your the next slide here we, built an internal training session and, you. Know one thing I've learned from folks like VIN and Travis is like Bob's put on github man and just publish your stuff up there let people use it so we built, a serie a series of free self-paced labs on sequels from our Linux on github we've taken that and modified it for a workshop so tomorrow morning at nine o'clock this, workshop you can come and meet us personally, and we'll walk you through the process of deploying, sequel, server on Linux interacting. With it deploying docker containers, in fact that same demo I believe, in is available, as part of that demonstration and then here, is a Vince session on containers. With DevOps and, sequel, server I think you'd really enjoy that session if, you're interested in that how would I use sequel serve in that environment and then. Here's our session, on hardware optimization, our Jennifer nanus is going to talk about sequel server and it's not just a Linux session it's, about sequel server and all sorts of different types of optimizations, both with memory. Compute, and storage but, he will talk about the, persistent, memory feature we mentioned earlier and then. Shameless plug for myself I've. Kind of known at Microsoft, and, sequel server doing what I called inside, talks so. I really want you to come to Vince talk to learn about how you're gonna apply this technology, what. I've done in my talk Thursday is take the sequel server container, concept, and kind of under, the covers show you how we're doing it how does it work you know if you how does our docker file look like and how do we actually build a sequel server container image and, then some interesting scenarios, you can maybe consider doing it that you hadn't thought of before so. Those are our sessions that we have specifically. Ordered towards sequel server on Linux I think, you've mentioned, van we also a theatre session coming up on kubernetes. With high availabilities, and our colleague Sarabhai forgot to list a session on here yeah he has a session as well on high availability with, sequel server and he, is the lead p.m. for the kubernetes high availability available.

To The group scenario. So please come take a look at that if you're interested just you. Know as you mentioned just swing by our booth we have all our sessions listed there as well right okay. So quick, shameless plug time. So. About. February March I started on a journey, because. I wanted to go write a book I'd never done it before. And so I chose sequel, server on Linux because of my background in UNIX from years ago plus, I'd not really seen out of there in the industry a really, good solid book on how this really works so, with the help of the team people like then people like Slava Oakes and many people I was, able to author this book it's now available, for pre-order and, I, expect the release of the actual book to be in mid November of, this year I

2018-10-04

Show video