Administering SQL Server 2012 Running On Windows Server Core

Are you afraid of deploying (or supporting) an instance of Windows Server Core?

Are you telling others to “stay away” because of your own fears of doing something new?

Then I am here to tell you that you’re doing it wrong.

Last month I decided that it was time to get down and dirty with Windows Server Core, SQL 2012, and AlwaysOn Availability groups. [Side note about AlwaysOn: it is ‘off’ by default…I’m just sayin’ is all.] I’ve seen other bloggers raise their objections to their readers regarding the deployment of Server Core so I wanted to see how difficult it would be to administer an instance of SQL Server running on Server Core.

As a Microsoft SQL Server MVP I am afforded the opportunity to take part in promotional programs from other companies that like to offer complimentary licenses to MVPs. One of those companies is VMWare. As part of the VM Guru program I have been allowed to use VMWare Workstation in order to create guests locally here on my laptop for just over a year now.

The plan now was to create three guests inside of VMWare Workstation. One would be my domain controller, and the other two would be my SQL 2012 instances on Server Core. I won’t go into the dirty details of getting the guests built in VMWare Workstation except to say that it was incredibly easy using VMWare Workstation to point and click at the .iso files and have everything installed. Configuration of the servers was a bit more involved but not too difficult and I had all three servers up and running in a short amount of time.

Joining the Server Core instances to the domain required me to use the command line. Luckily I know Joey D’Antoni (blog | @jdanton), one of the experts in the field of virtualization and server administration today. He shared with me a template for how he scripts out his Server Core installs and installation of SQL Server which saved me a bit of Googling to find similar scripts.

After I got everything built, named my domain (DALEK.local), and the instances (DOCTOR and ROSE) installed, etc. I wanted to then take a quick tour of how I would administer these Server Core instances. Since I was hearing about how difficult it would be for someone without any proper training I was curious to know what was the learning curve for this new platform.

First up for me was figuring out how to administer the instances of SQL Server. How would I ever administer that instance without the SQL Server tools being installed?

I simply used SSMS and connected to the instance. It’s the same experience. Nothing is different. SSMS doesn’t care if the engine is running on Server Core or not. Check out what it looks like:

ssms_doctor

Everything you know in SSMS carries over to Server Core instances. No need to panic. If you are used to using SSMS to administer SQL Server then you can continue to use SSMS for SQL Server running on Server Core.

What about other administration tasks, such as computer management? Turns out you can do that remotely with a right-click:

comp_mgt1

After I connect to the DOCTOR I see that the SQL Server Configuration Manager is available from within the Computer Management window:

sscm

That covers roughly 98% of any administrative tasks I would ever need to do against my Server Core instance, both inside and outside of SQL Server. The remaining 2% can be done through Powershell. However, with the reduced surface area of Server Core (i.e., less software running as part of the O/S) you aren’t likely to need to do as much administration against those servers anyway.

I can’t imagine why anyone would be afraid to use Server Core, or to support Server Core starting right now. I’d recommend you keep a few servers available with a full O/S, just in case you need to connect remotely to the instance of Server Core for something in that 2% of tasks. If you are concerned about working with Server Core then do what I do: create some VMs and have a try yourself. When it comes to the installation, configuration, and support for Server Core I find it all to be quite easy.

Your mileage may vary, of course.

70 thoughts on “Administering SQL Server 2012 Running On Windows Server Core”

  1. Tom–thanks for the shoutout–Just curious, what tasks have you not been able to perform remotely? Particularly with Windows 8, and the new Server Manager there really isn’t a need to be on the server at all (after config).

    Reply
    • Joey,

      Thanks again!

      I can’t think of anything. I hedged my bets by saying 98%. I assume someone will come along and give me some obscure example of something that can’t be done remotely.

      From where I sit, any DBA is going to be just fine with all the remote tools, and it wouldn’t hurt to learn a little Powershell along the way either.

      After the initial config of the servers (using your scripts), I haven’t had the need to interact with them at all. TO me they are just…engines, really.

      Tom

      Reply
  2. I just finished writing up a blog on creating a Virtual SQL 2012 Lab with Hyper-V. I really wanted to add a section for deploying an extra node to the cluster on Windows Core. I already put the AD and iSCSI servers on Windows Core. It’s easy to manage through Server Manager!! I think it is a great way to have a lightweight environment and when I have time, I will add a SQL server node on a Windows Core VM. This is a great post, I think DBAs should be less intimidated with Windows Core, and dare I say PowerShell?!!?

    Reply
      • I created the entire lab with four VMs and 5GB of RAM. Could not have done it without Windows Core! I did end up using the GUI from my non-core installations but still, saved tons of space and 3GB of RAM on a system that has 9GB. DBAs get out there and play with Windows Core and PowerShell!!

        Reply
  3. It worked in a VM once so it’s ready for the world, eh? 😉 Interesting take.

    Had a customer a few weeks ago who had SQL 2012 AGs running across multiple datacenters on Windows Core. During network switch failures, they needed to check which replica had the most current data, force it online, and clean up the aftermath. They could still connect locally using HP iLOs on a separate management subnet, but they couldn’t connect to SQL Server using SSMS from other machines until the network troubleshooting was done. They lost hours of troubleshooting time.

    But I’m sure it’s ready because it worked on your VMware workstation. 😉

    Reply
    • Wow… Brent you can’t really be serious in thinking that because your customer hasn’t done their homework that it doesn’t and cannot work? That sounds a little short-sighted, don’t you think? I have been working with Hyper-V since v1 (and am also VMware Certified) and would posit that nearly anything that can be done in the physical server world can be done in a virtual server. The only thing is sometimes you need an expert to architect your solution and virtual environment to make sure the glitches are out. If I could get it to work in my Hyper-V datacenter (instead of his VMware workstation) would you be satisfied?
      Hours lost in troubleshooting are usually better invested in learning.

      Reply
      • I think the real issue is that Tom was using VMs to demonstrate functionality, not as a reference architecture for a client with chronic connectivity issues. I use VMs all the time and don’t think that it invalidates my demonstrations. I use a MBA when I travel. I don’t think anyone thinks that I’m saying that companies should run their enterprise systems on a 4GB ultra machine.

        This is the first time I’ve ever heard that using a VM to show how something works is a bad practice and I’m sad that it’s being used as a reason to say that Server Core should never be used in a SQL Server deployment.

        I think using a VM to demonstrate how to administer a server using commonly available tools is an acceptable practice. I fact, I’d never do a demonstration on a client enterprise environment like Brent is saying must be done. I don’t think my clients would like that, either.

        Reply
        • It’s OK, I’m used to having people attack me for no reason. I’m sure Brent didn’t mean to come across so poorly in his response, and that he has used VMs in the past to show that functionality should work before urging his clients to move forward.

          Reply
          • Yeah, but I do that attacking in a formal debate. And I don’t attack you, just your crazy ideas about NULLs and Normalization. In front of people who know we are there to help them uncover their own positions in the debate and why they have them. To understand that designs and architectural decisions need to be made in context. Cost, benefit and risk. And sorry for all the bruises I’ve left on your positions.

          • “Do whatever you want, and if it’s something you’re going to regret in the morning, sleep late.”

        • “Tom was using VMs to demonstrate functionality” – except he wasn’t. He said he created three VMs, a DC and two SQL Server instances, and then he connected to them via SSMS. From where? Where’s this other magical machine? You have to have something with a GUI, or else this whole administration thing doesn’t work, and that’s where it falls apart.

          Reply
          • It seems clear to me that you have had an issue with having deployed Server Core in an environment where no one knows how to manage enterprise production database except through wizards. I agree that in theses cases, where no DBAs are available, using Server Core might not make the CBR for the architecture. But that’s hardly a reason to say that the demonstration here is invalid.

            Your tone makes it clear that you came to troll and tear down, as the others have said. Your agenda has been noted. Why not actually contribute to the technical discussion here instead of continuing along with the bash-the-community path? It’s why the rest of us are here.

            I stand by my position that demonstrating functionality via VMs is perfectly fine. I also know that deploying solutions in the enterprise via VMs is fine, too. But it all comes down to cost, benefit, and risk. You know now that the cost and risks of Server Core for your projects is too high due to lack of non-GUI skills. Architect or hire accordingly. That’s how architecture works.

          • Wow – when someone disagrees with you or your friends, you resort to personal attacks, calling me a troll. It’s a shame you have to resort to such bullying tactics.

          • Actually, I called out your behaviour and your tone. I didn’t call you anything. I haven’t attacked you. I invited you to have a technical, architectural discussion. As the others here are. I’m not sure what’s up with your community-bashing of late. But it doesn’t help readers or the community. If we can continue to talk about trade-offs instead of black and white answers, everyone benefits.

      • Thanks Mitch, I doubt Brent meant to come across so poorly, and I also doubt he feels that “it worked on my machine” is a valid reply I would give my clients.

        Reply
      • Mitch – yes, I am definitely serious because most folks don’t do their homework. Visit any forum and take a look at the questions people ask. In a land of experts and bloggers, sure, Windows Core works. For the rest of us…not so much, and the rest of us are doing real business work with people who aren’t qualified to manage Core installations. Throwing them into Core just isn’t right.

        Reply
        • And I’m going to disagree with you, again. At this point we’re not on Windows Core for SQL Server. It’s not a skill issue, but other components that require more than what Core provides that we use on our systems. Otherwise, we’d be on Core for our SQL Servers.

          We do use Core for key infrastructure servers. Why? Because there have been fewer patches/reboots required. That means less downtime. That’s something we’re striving for, right? Why else? Because when you do have the “auditors/pen testers” (when really they are just FUD evangelists) come in who you eventually have to give them greater and greater access, they stop cold when they hit Core. They don’t know how to proceed with their “We raped your system and this is why you suck even though we forced you to give us domain admin access.” Why else? Because it does remove some of the performance overhead. Why else? To keep folks who don’t know what they’re doing off of the system. They don’t have the GUI and they back away. That’s a good thing.Especially when it’s a key server.

          We’re not alone in this approach, nor are we alone in our reasons. I feel empathy for you that you’ve had a bad experience with customers who weren’t properly prepared for Core. But don’t take it out on Core, as Core isn’t the root cause. Also, don’t take it on folks who recommend Core, as you know good and well they wouldn’t do so if they were dealing with a customer who couldn’t handle it. Not every organization is equipped to handle the various NoSQL solutions or Amazon cloud offerings. However, I don’t see you attacking folks who recommend such solutions even though there are clients out there who can handle neither.

          Reply
          • No no, we agree – Windows Core is *great* for key infrastructure servers like AD controllers. Totally agreed.

          • Brent,

            Your comments haven’t really helped here at all. I have no idea why you have decided to stop by and resort to trolling tactics. (Note I didn’t call you a troll, I just said you are using those tactics.)

            My post doesn’t “fall apart” because I didn’t explain that I connected from SSMS on my laptop to the VM network. I also stated that it would be a good idea to keep some servers around with GUIs, just in case you felt you needed them. In my case, I only have the DC, but I could install the tools there if desired. (Kids, don’t try this at home.)

            Instead of continuing to cite edge cases that use odd architectural design patterns and questionable hiring practices, why not write your own blog post that describes the problem you are trying (poorly, I might add) to describe here?

            I think it would be great to see a post that helps explain the situation, how things got to that point, the issue that arose, the solutions that came about, and the steps taken in order to avoid the issue from happening again.

            That would make for a wonderful post. It would be both informative and helpful. I’d be happy to help.

          • I didn’t see anything there about the project you mentioned. Perhaps you used the wrong link?

          • I’m sure you’re aware that people have been doing all those things on Unix systems for years, and that Microsoft created Power Shell and server core in response to deficiencies in Windows.

          • Well, to be fair, MS introduced Server Core in W2K8 (no R2). It didn’t support things like SQL Server since it was very “pure” and had zero .NET in it. Of course, it only lended itself to limited scenarios, so they put in minimal .NET support, and now SQL Server 2012 supports it on W2K8 R2 SP1 and greater. So you can argue to a degree MS diluted SC to get long term adoption.
            Let’s look at the other side of the coin – I remember when Oracle added a GUI in I believe 8i to make it easier. Command line isn’t a panacea, either. Windows didn’t have deficiencies per se – pretty much everything had a command line (maybe not specific apps, but for OS level tasks? Sure. WMI has also been around forever, too.). PS just made it easier/more unified. You see the work really in what shipped with Windows Server 2012 and all 2400+ cmdlets. With few exceptions, all of the old DOS commands have a PS equivalent which is impressive.

    • Brent,

      The point of my post was to “not fear trying something new”. I never used the words “it worked on my VM Workstation” or “ready for the world”. Those are your words, not mine. If that was your takeaway from my post I’m sorry you didn’t understand the real message.

      The situation you described is an edge case. I would think that SQLCMD would have worked to find which AG had the latest data, but I’m sure they tried that.

      It could very well be that Server Core, and this post, is just not right for you or your customer at this time.

      Next time you need help troubleshooting network or SQL Server issues feel free to reach out to others in the SQL Community, we’re always happy to help one another.

      Reply
    • Brent, this one looks like you’re reaching to find something wrong with the post. The problem wasn’t with Core. The problem was two fold: (1) the network was hosed and (2) the folks doing the investigating weren’t comfortable with SQLCMD. I’m sorry, but if they were running Core they should have prepped to use SQLCMD. That’s a training/personnel issue, not an OS issue.

      Reply
      • KBK – no, using a real world scenario isn’t reaching, but if you’d like to talk through additional scenarios, here’s two that quickly come to mind.

        First, I bet you’ve tried hiring DBAs recently. How do the interviews go? Do you find yourself awash in a sea of great, qualified candidates who are well-skilled in SQL Server? Do you find that people who know Core administration via PowerShell are easy to find? I don’t. I struggle finding people who know the basics. Because of that, I’m hesitant to make my servers even *harder* to administer.

        Second, got SQL Servers alone in the DMZ? I’ve got clients who have no other machines from which to do administration.

        I get paid to solve real business problems, not create them. Taking away troubleshooting tools is the opposite of solving a business problem.

        Reply
        • SQL Servers in the DMZ? No. I believe in security. I’d rather have a bastion host/app firewall in front of the SQL Server, which is what sits in the DMZ. SQL Server sits in the protected network. BTW, this is the same approach as you’d take with Microsoft Exchange, so it’s a tried and true architecture pattern.

          And I didn’t say PowerShell. I said SQLCMD. In other words, they need to know the T-SQL to manage their SQL Server instances. I would expect this of any DBA, SSMS or not. If they can’t do it in T-SQL (when T-SQL covers it), then that’s the problem.

          EDIT: fixed typo of “app server” to be “app firewall”

          Reply
          • Agreed with KBK, I’d never architect with a SQL Server in the DMZ (unless I absolutely had to, and then I’d want to use Core just to reduce attack footprint), and SQLCMD should be in any DBAs tool kit. Oracle DBAs all over the place manage to do their jobs daily without a gui, there is no reason why SQL DBAs can’t do the same thing.

          • Carrying over the convo from Twitter: I think that people have their preferences for tools versus command line work. But when it comes to supporting enterprise databases, we have to have people who can do both. We also need to ensure that they have training and experience in using those tools. That’s why corps do DR exercises. And why the DBAs I work with do their own exercises. Practicing. Using checklists. Having alternatives when stuff doesn’t work. Just like pilots and, well, astronauts. I think we’ve all blogged something about those methods at some point.

            Maybe it’s time to do it again.

          • That’s a nice post–I haven’t been a full time Oracle DBA for close to 4 yrs, and I still remember most of the commands he talked about. The command line is a good thing.

        • The scenarios you’re describing should be exceptions, not the rule. You are trading security for convenience. We don’t install Windows Media Player on our database servers and we don’t run web browsers on them either.

          Reply
  4. Tom, I read this post as a call to embrace something unfamiliar as did Amy when she read it. Sometimes when you build bridges you develop a troll problem.

    Reply
    • I’m not sure that is a viable solution just to get around the need to run a query for the edge case that Brent is mentioning but yes, the ability to easily install and un-install the GUI when needed is a nice feature to have as well.

      Reply
    • However, it’s a one way trip for SQL Server (only way it’s supported). And you have very speciic things that can’t be installed if going from GUI–>SC or GUI–>minshell. I’ve blogged about switching. It works, but it’s not going to solve issues and since it’s only supported one way, you really need to think about it. Plus, switching requires downtime. Not something you’d do in the middle of the day for sure.

      Reply
      • Good point about the downtime. It’s not necessarily something I’d want to do when fighting a fire. I didn’t realize it was one way, either. Thanks for sharing that.

        Reply
        • It’s not a Windows thing – you can switch all day long. SQL decided (and not sure how public they’ve made it) to only make this a one way trip. I understand – it’s a test matrix thing. There are specific rules how you can install SQL on Core anyway, so you have to think ahead to begin with if you’re starting on GUI and then removing it. There are lots of issues with Server Core you need to work through depending on how you start. I’ve had a head start, so to speak 🙂

          Reply
          • Ah, I see it now, thanks again. I think I should spin up a new core VM and test this functionality, that way I have a better feel for it.

          • For example, if you start on full GUI and want to switch to Core and you installed SSMS, you need to uninstall before removing the GUI otherwise you have a technically illegal install. So you’d be best NOT installing the GUI tools at all to begin with if you think you’re going to switch to Core or MinShell.

  5. Seems like the fiery debate has cooled down so I can ask my questions now.
    1. To manage the disk remotely in Computer Management I had to change 3 firewall rules. It was a lot of googling to be honest, but is it the only way to manage disk remotely that are on a Windows Core Machine?
    2. Regarding SSMS connecting to SQL Server on Windows core, that is a no brainier. However, what about connecting to SQL Server Configuration Manager? I mean you can add the server to your list of servers in SSMS, but sometimes that doesn’t work; especially with older servers. There are lots of configuration options in there that many of us (me included) do not know how to configure without going through RDP and using Configuration manager (aliases, ports, etc etc).
    Thanks

    Reply
    • Thanks for the questions.

      I’m not sure that all firewall rules are going to be the same for each shop. I would suspect that for physical servers you are likely going to need that, yes. However, for SAN attached or VM servers you would be able to manage this disk in a different manner.

      I’m able to connect to the SQL Server Configuration Manager through the COmputer Management MMC, I have a screenshot of that in the post.

      Reply
      • I didn’t even notice! I kept trying to use Registered Servers in SSMS with lots and lots of trouble! I’m going to give this a try tomorrow at work. Thanks for the heads up. I also get your point on firewall rules with different storage arrangements. In my lab I used iSCSI which was hosted on a Windows Core machine, so obviously to manage the storage remotely I needed to open firewall rules.
        Thanks

        Reply
        • Since most people have external firewalls, I find that most customers comletely disable all three in Windows. You still need to obviously poke holes somewhere, but better to not have to manage it per server unless you script it all out.

          Reply
  6. SQL Rockstar is known to be a reputed consulting firm for SQL SERVER related Courses with top talented database professionals and software engineers coming from some of the most established and most successful software development and technology consulting firms around the globe for providing a tailored-made solutions to their clients at the very best of their knowledge and liable time.

    Reply
  7. Nice post, I needed to set fixed ports for SQL Server on Server Core because the dynamic ports wasn’t working even after I made some firewall rules for it. I was able to access the Config Manager using the Computer Management to change the fixed port settings for a named instance. -Cheers.

    Reply
    • Tom,

      You are most welcome, and thanks for reading! The SSCM was one of those “must haves” for me, as I still do a lot of tasks through the GUI.

      Reply
  8. As a very early adopter of Server Core and SQL Server 2012 (I first presented on it at PASS in 2011), it’s nice to see people start to pick it up and start using it even if in non-production environments. I’m usually the ultimate early adopter when it comes to these things and fully embrace people doing new stuff and even failing at it. The Server Manager trick is a nice one – I’ve shown that since 2011 as well. It’s a very handy tip to know.

    That said, I can tell you as someone who has talked about it since 2011 with multiple customers, nearly each and every one identified command line as a skillset deficiency and was the #1 reason they will not implement Server Core. Even with Windows Server 2012 and 2400+ cmdlets (I’ve got scripts for both W2K8 R2 SP1 and W2012, both radically different) to configure a server, it doesn’t matter.
    I proved at TechEd 2012 in my presentation on SQL Server and Server Core that patching is much less (video available on Channel 9 i believe), but even that can’t sway people. There is definite fear of the unknown, but it’s more than that: as KBK points out, everything you do has to be able to work on Server COre. You need a whole new build and/or build process. This stuff is not a slam dunk and takes time.

    So yes, you can do pretty much everything remotely against the box but I do not see Server Core being adopted any time soon by my customers – or any for that matter. I wish some would. I even have a Server Core option in my HA class’ labs. A few have done it and loved it, but I bet none will do it in production.
    I still strongly encourage people to play with Server Core. Maybe in 2 – 3 years this conversation will be different. I’m not sure it will be.

    Reply
    • Also, I remember sending Joey my Server Core script to configure W2K8 back in 2011, so I’m glad he’s passing stuff on.

      Reply
      • Allan,

        Thanks for the comments, and thanks for giving Joey the scripts that he passed along to me. They were quite useful. Now I have my own set of scripts for Core!

        I don’t see a lot of adoption either. My post was an effort to help people get over the hump. I’d rather encourage people to try something before making a decision as to whether or not it is the right thing for them.

        Server Core, and other new technologies, just are not going to be the right thing for everyone, in every situation.

        Reply
        • I’m sure Joey modified them since he got them, but then again, that’s the point, isn’t it?
          Server Core is definitely an uphill battle for adoption in the traditional server (i.e. stuff like SQL) space. I think over time the space will mature. If nothing else, the Modern UI in Windows Server 2012 certainly is pushing some to consider it more for better or worse.
          We’ll see what time brings. Ironically, I find it’s not the DBAs burying their head in the sand but the non-SQL admins. Funny how that works.

          Reply
          • I didn’t ask if he modified them. I should probably send them to you in an effort to play “telephone” with command line scripts.

            Thanks again for reading, the comments, and for your feedback when I was doing the research for this post.

  9. Hi there, trying to figure out how to join the SQL server on my windows server 2012 domain. I’ve installed SQL 2012 while running windows server 2012. Any idea how?

    Reply
  10. Hey Thomas, I’ve been going this route with our new cluster. I hit a small issue though, and was wondering if you’d seen it. I can connect SCCM remotely and manage the network settings (ports, etc), but no services show under “SQL Server Services”. Any ideas?

    Reply
    • What version of SQL Server, and what version of the tools? I’ve had issues recently with SCCM as a result of switching between versions of SSMS 17, mostly because I like to install CTP versions of the tools.

      Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.