Tech News - Databases

In this post, we will explore how a Pod name is generated, Pod Name lifecycle, how it’s used inside a Pod to set the system hostname, and how the system hostname is used by SQL Server to set its server name metadata. Pod Naming in Deployments When deploying SQL Server in Kubernetes using a Deployment, the Pod created by the Deployment Controller will have a name with a structure of <DeploymentName>-<PodTemplateHash>-<PodID> for example, mssql-deployment-8cbdc8ddd-9n7jh. Let’s break that example Pod name down a bit more: mssql-deployment – this is the name of the Deployment specified at metatdata.name. This is stable for the lifecycle of the deployment 8cbdc8ddd – this is a hash of the Pod Template Spec in the Deployment object template.spec. Changing the Pod Template Spec changes this value and also triggers a rollout of the new Pod configuration. 9n7jh – this is a random string assigned to help identify the Pod uniquely. This changes with the lifecycle of the Pod itself. In a default Deployment configuration, the Pod’s name is used to system hostname inside the Pod. In a Deployment, when a Pod is deleted for whatever reason, Pod/Node failure, Pod administratively deleted, or an update to the Pod Template Spec triggering a rollout, the new Pod created will have a new Pod Name and a matching hostname inside the Pod. It is a new Pod after all. This can lead to an interesting scenario inside SQL Server since the Pod name can change. Let’s dig deeper… Server name metadata inside SQL Server running in a Pod To ensure SQL Server’s data has a lifecycle independent of the Pod’s lifecycle, in a basic configuration, a PersistentVolume is used for the instance directory /var/opt/mssql. The first time SQL Server starts up, it copies a set of system databases into the directory /var/opt/mssql. During the initial startup, the current hostname of the Pod is used to set SQL Server system metadata for the server name. Specifically @@SERVERNAME, SERVERPROPERTY('ServerName') and the Name column from sys.servers. In Listing 1, is an example Deployment for SQL Server. In this configuration, the hostname inside the Pod will match the current Pod Name. But what happens when the Pod name changes when a Pod is deleted, and new Pod is created with a new name? Let’s walk through that together in the next section. apiVersion: apps/v1 kind: Deployment metadata: name: mssql-deployment spec: replicas: 1 strategy: type: Recreate selector: matchLabels: app: mssql template: metadata: labels: app: mssql spec: securityContext: fsGroup: 10001 containers: - name: mssql image: 'mcr.microsoft.com/mssql/server:2019-CU8-ubuntu-18.04' ports: - containerPort: 1433 env: - name: ACCEPT_EULA value: "Y" - name: SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: SA_PASSWORD volumeMounts: - name: mssqldb mountPath: /var/opt/mssql volumes: - name: mssqldb persistentVolumeClaim: claimName: pvc-nfs-instance Listing 1 – Example SQL Server Manifest using a Deployment Controller Examining Server Name Metadata When Deploying SQL Server in a Deployment Initial Deployment When the Deployment is created, a Pod is created. In the output below, you can see the name of the Pod is mssql-deployment-bb44b7bf7-nzkmt, and the hostname set inside the Pod is the same, mssql-deployment-bb44b7bf7-nzkmt kubectl get pods NAME READY STATUS RESTARTS AGE mssql-deployment-bb44b7bf7-nzkmt 1/1 Running 0 7s kubectl exec -it mssql-deployment-bb44b7bf7-nzkmt -- /bin/hostname mssql-deployment-bb44b7bf7-nzkmt Check Server Name Metadata Since this is the initial deployment of this SQL Server instance, system databases are copied into /var/opt/mssql, and the server name metadata is set. Let’s query SQL Server for @@SERVERNAME, SERVERPROPERTY('ServerName') and the Name column from sys.servers. In the output below you can see all three values match. sqlcmd -S $SERVICEIP,$PORT -U sa -Q "SELECT @@SERVERNAME AS SERVERNAME, SERVERPROPERTY('ServerName') AS SERVERPROPERTY, name FROM sys.servers" -P $PASSWORD -W SERVERNAME SERVERPROPERTY name ---------- -------------- ---- mssql-deployment-bb44b7bf7-nzkmt mssql-deployment-bb44b7bf7-nzkmt mssql-deployment-bb44b7bf7-nzkmt Delete the Currently Running Pod Next, let’s delete a Pod and what happens to the Pod’s name, the Pod’s hostname, and the SQL Server server name metadata. kubectl delete pod mssql-deployment-bb44b7bf7-nzkmt pod "mssql-deployment-bb44b7bf7-nzkmt" deleted I’ve deleted the Pod, and since this is controller by a Deployment controller, it immediately creates a new Pod in its place. This Pod gets a new name. The existing databases and configuration are persisted in the attached PersistentVolume at /var/opt/mssql. These databases are all brought online. In this output below, you can see the new Pod name and hostname are both mssql-deployment-bb44b7bf7-6gm6v. kubectl get pods NAME READY STATUS RESTARTS AGE mssql-deployment-bb44b7bf7-6gm6v 1/1 Running 0 20s kubectl exec -it mssql-deployment-bb44b7bf7-6gm6v -- hostname mssql-deployment-bb44b7bf7-6gm6v What’s in a name? Now let’s query the server name metadata again. In the output below, you can see there are some inconsistencies. We saw above that Pod has a new name and hostname (mssql-deployment-bb44b7bf7-6gm6v), but this change isn’t updating all the server name metadata inside our Instance. The only place it is updated is SERVERPROPERTY('ServerName') the other values still have the initial Pod Name mssql-deployment-bb44b7bf7-nzkmt. sqlcmd -S $SERVICEIP,$PORT -U sa -Q "SELECT @@SERVERNAME AS SERVERNAME, SERVERPROPERTY('ServerName') AS SERVERPROPERTY, name FROM sys.servers" -P $PASSWORD -W SERVERNAME SERVERPROPERTY name ---------- -------------- ---- mssql-deployment-bb44b7bf7-nzkmt mssql-deployment-bb44b7bf7-6gm6v mssql-deployment-bb44b7bf7-nzkmt Setting a Pod’s Hostname So what do we do about this? Having instability in the server name metadata can break Replication, mess up our server monitoring systems, and even break code. To get the Pod’s hostname to a persistent value, you need to set the template.pod.spec.hostname field in the Deployment. This sets the system hostname inside the Pod to this value. In the code below you, can see I’ve set the template.pod.spec.hostname to sql01. On the initial deployment of a SQL Instance, this is the value that is stored in the Instance server name metadata. If you already have a SQL Server up and running in Kubernetes and did not set the template.pod.spec.hostname value, the server name metadata will need to be updated using standard SQL Server methods with sp_dropserver and sp_addserver. But for demonstration purposes, I’m going to start over as if this is an initial deployment. And deploy the manifest in Listing 2 into my cluster. apiVersion: apps/v1 kind: Deployment metadata: name: mssql-deployment spec: replicas: 1 strategy: type: Recreate selector: matchLabels: app: mssql template: metadata: labels: app: mssql spec: securityContext: fsGroup: 10001 hostname: sql01 containers: - name: mssql image: 'mcr.microsoft.com/mssql/server:2019-CU8-ubuntu-18.04' ports: - containerPort: 1433 env: - name: ACCEPT_EULA value: "Y" - name: SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: SA_PASSWORD volumeMounts: - name: mssqldb mountPath: /var/opt/mssql volumes: - name: mssqldb persistentVolumeClaim: claimName: pvc-nfs-instance Listing 2 – Example SQL Server Manifest using a Deployment Controller, setting the Pod’s hostname In the output, below the Pod Name is mssql-deployment-8cbdc8ddd-nv8j4, but inside the Pod, the hostname is sql01, and now all three values for our server name metadata match. If this Pod is deleted, the Pod gets a new name, the hostname inside the Pod will still be sql01, and the Pod server name metadata will still be set to sql01. kubectl get pods NAME READY STATUS RESTARTS AGE mssql-deployment-8cbdc8ddd-nv8j4 1/1 Running 0 43s kubectl exec -it mssql-deployment-8cbdc8ddd-nv8j4 -- hostname sql01 sqlcmd -S $SERVICEIP,$PORT -U sa -Q "SELECT @@SERVERNAME AS SERVERNAME, SERVERPROPERTY('ServerName') AS SERVERPROPERTY, name FROM sys.servers" -P $PASSWORD -W SERVERNAME SERVERPROPERTY name ---------- -------------- ---- sql01 sql01 sql01 Setting the hostname in the Pod Template Spec gives you the ability to persist the hostname and thus the server name metadata inside SQL Server. This is crucial for services and code that depend on a static hostname. A StatefulSet is a Controller in Kubernetes that does give you persistent, stable naming independent of the lifecycle of a Pod. I will explore those in an upcoming blog post. The post Persistent Server Name Metadata When Deploying SQL Server in Kubernetes appeared first on Centino Systems Blog. The post Persistent Server Name Metadata When Deploying SQL Server in Kubernetes appeared first on SQLServerCentral.

Back when Covid started I guessed that this might be yet another live-or-die challenge for PASS. PASS is heavily dependent on Summit revenue and uses it to fund all the other stuff. Looking at the most recent budget notes (see the PASS blog) there’s a forecasted shortfall of $1.5m and that’s if they hit their attendance targets for the Summit and the sign up goal for PASS Pro. Maybe they can make that up from the more or less $1m in reserves plus a bit of cash on hand plus some more cuts. Maybe there will be an insurance claim for revenue loss that might supplement that, though such claims are typically very slow to process. Maybe attendance will exceed the goal and things will work out. If they hit the goals in the budget, that provides enough money to get through the fiscal year ending June 2021 (or maybe more, I’m not a finance guy – go look at the revised budget). If they come up short, then deeper cuts will be the least of it, in the worst case PASS might have to file bankruptcy. I’m writing all of that based on some experience being on the Board quite a while ago, no insider knowledge of what’s going on now, and reading what has been posted to the PASS blog/news and in the Board minutes. If things are better than I think, well, that would be welcome news. I suspect for many it’s tempting to throw rocks. But now isn’t the time for that. From the day Covid hit I think the Board has tried hard to figure out what to do and when to do it. Deciding to go virtual was hard for a lot of reasons: They had 2020 venue contracts to think about and figure out It would mean a substantial cut to the budget Never done it before, so a huge risk You can argue about the decisions, the platform, lack of transparency, the implementation, whatever else, but that’s all water under the bridge now. We can post-mortem later. Soon after the Summit completes they should have a really good view of the budget and at that point they can make some decisions, perhaps really tough ones. I’ve written all of the above to get you to think about the next part. The Board is dealing with all of this during a time where they are all under stress at home and work. Covid has impacted everyone. More than that though, they fear failing. Wayne Snyder used to tell me something along the lines of “I don’t want PASS to fail on my watch” and I think every Board member feels that obligation. Max stress to be sure. The other part is our full time staff. Yes, they technically work for C&C, but many of them have been with us for years and all do the work we ask them to do. Imagine what it’s like to see these events, know the challenges, and wonder if you’ll have a job for much longer. Max stress, plus. Right now there isn’t much you and I can do except recognize that there are a couple of dozen people living with a lot of stress and trying to get PASS back on a solid footing. Next year, after the immediate decisions have been made, we can and should talk about if PASS is all we want it to be, but for now we have to wait and see. These are our people and they need whatever support and kind words we can muster. The post Serious Thoughts About PASS appeared first on SQLServerCentral.

I can’t remember where I saw this, but it made an interesting Question of the Day: select * from Sales where Profit !< 10000; I had never seen anything like this, in all my years of working in C, C++, Java, Lisp, APL, Pascal, Fortran, VB, C#, SQL, and more. However, there are apparently a few operators that I’ve never used: !< !> These are the not less than and not greater than. Weird, though I guess this makes sense. Personally, I think restructuring as greater than or equal to instead of not less than makes perfect sense. The post Strange T-SQL Operator Syntax appeared first on SQLServerCentral.

Taiob Ali (blog|twitter) is our host for TSQL Tuesday this month and he’d like us to talk about how our year has gone. Specifically what challenges have we had, and maybe overcome with the year (hopefully just the one) of the pandemic. I’ll be honest it hasn’t been that difficult for me. I know that’s an unpopular position. We are all having a rough time and it’s been a terrible year all around for so many reasons. But I’ll be honest, I’ve been ok. And that’s with a few extra special treats 2020 had for me (not that everyone hasn’t had their own fair of special fun). Part of that is who I am. In a lot of ways I am the very definition of phlegmatic. Definition: (of a person) having an unemotional and stolidly calm disposition. I don’t worry often or strongly. I don’t get overly rattled. Honestly unless I’m feeling rushed (a weakness I’ll admit) I’m remarkably calm. I’m also an indoor person who doesn’t particularly like crowds. While I miss my friends at the various conventions I’m able to stay at home, in my house 99% of the time and be just fine. Part of it my situation. I have a good steady job that hasn’t been strongly affected until recently by the pandemic. I work with great people and I enjoy my work. My home life is stable. I have a loving wife, two great kids, and even my in-laws (who live with us) only get to me occasionally. Now don’t get me wrong. I can tell something is wrong. I will look at something and suddenly realize just what an insane year this is. But overall I’ve been good. Which brings me to the exception and my point. The final part of it is who you are. The one time this year when I truly needed support I got it. In spades. Ever since then I’ve had people checking on me, making sure I’m ok and supporting me. Everyone needs a hand. Even those people who seem to have it the most together need to be checked up on. Even those who show no anxiety, no stress, need a hug (virtual right now please) or some moral support. With a bit of luck we can all get by with a little help from our friends. The post I get by with a little help from my friends : T-SQL Tuesday #131 appeared first on SQLServerCentral.

For the second T-SQL Tuesday ever — again, hosted by Adam Machanic — we were asked one of three options, and I elected to go with the first one: Describe a confusing situation you encountered, and explain how you debugged the problem and what the resolution was. This invitation was originally posted on 4 January-> Continue reading T-SQL Tuesday Retrospective #002: A Puzzling Situation The post T-SQL Tuesday Retrospective #002: A Puzzling Situation appeared first on Born SQL. The post T-SQL Tuesday Retrospective #002: A Puzzling Situation appeared first on SQLServerCentral.

If you login to pass.org AND have previously met the criteria to vote this year, you’ll see this. Click the button and you’ll be off to the voting page where you can pick three candidates from the slate. I haven’t endorsed anyone this year and I’m not going to announce my choices either. Read the applications, view the scores from the Nominating Committee, and if you can read through the questions and answers on the Twitter AMA. The post Vote in the PASS Election appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today's tip is to plan a new activity or idea you want to try out this week. At least a few months ago, this was a coping strategy for many. With shortages, closings, and more time at home, people were baking bread at home for the first time. That lead to shortages of baking yeast, so the focus shifted to sour dough bread. The bread baking seems to have slowed down -- or at least people are not talking about it as much. So, this week try something new. It doesn't need to be anything extravagant. Maybe try out a new recipe or complete a repair that you've been avoiding for months.   The post Daily Coping 9 Nov 2020 appeared first on SQLServerCentral.

The PASS Virtual Summit 2020 conference is live this week, and I’m thrilled to have presented a preconference boot camp yesterday called “Amplify Your Virtual SQL Server Performance“. During the precon, one of the attendees in my session asked a really good question. “Do you have a list of all the scripts and links that I reference in the presentation?” The answer is not yesterday, but I do now! The following list of scripts and links are designed to help you know more about the tools and techniques in how I use them to improve the diagnostics and testing side of performance tuning. Microsoft DiskSpd DiskSpd is a synthetic disk benchmarking utility. A spiritual successor to the SQLIO utility, I use it to simulate certain SQL Server-like I/O patterns on different storage subsystems so I can see how certain storage devices respond to various disk access patterns. The scripts that is used to generate a random workload for evaluation are as follows. 80/20 Read/Write test, 100GB workload file, 4KB disk access, 4 worker threads and 8 operations per thread – to simulate activity but not trying to overwhelm the storage 80/20 Read/Write test, 100GB workload file, 64KB disk access, 8 worker threads and 8 operations per thread – to simulate a high demand I/O workload pattern DVDStore Database Benchmarking Utility HammerDB is not the only database benchmarking utility in town. The DVDStore benchmarking suite is useful for repetitive database benchmarking tests where I can script it out and run it via a PowerShell/Batch file to make for very quick database activity. I have a walk-through for how to use this tool out at my company site here. The command that I used to simulate the moderate SQL Server load is as follows. Tuning Cost Threshold of Parallelism Jonathan Kehayias has a great query out at SQLskills that I use to help identify any and all commands in the execution plan cache that have either gone parallel or stay single threaded. With derivatives of this query, I’m able to find key queries that have a query cost to replay and validate where the Cost Threshold of Parallelism setting should be dialed in at for a given SQL Server workload. DBATools PowerShell Module It goes without saying how important the DBATools PowerShell module is to all DBAs around the world. I used this to help deploy and manage all of the SQL Servers that I demoed during my precon. If you’ve not yet explored it, I implore you to dig in and learn how you can use this tool to make your life better.  Perfmon Setup for 24×7 Collection Windows Perfmon is a fantastic means to collect and record ongoing performance metrics from your key SQL Servers. I’ve got a PDF and video set here to show you how to set up Perfmon for ongoing 24×7 collection. Import Perfmon Data into a SQL Server Database In addition to collecting it, we now need to load it into a database so that we can start to mine the data and extract the meaning from the data. We’ve released a PowerShell module at GitHub that will help you extract the BLG file and load the data into a SQL Server database, as well as sample mining queries to start to access key metrics within the data. We also have a video here that will show you how to use it and extract meaning from the data. CPU-Z CPU-Z is a wonderful free tool to help you identify all the key components and specifications of your processors underneath your most critical servers.  Geekbench Geekbench is my go-to choice for performing relative CPU performance comparisons. It’s per-core benchmarking metrics help me understand the performance differences that you can expect when moving to new hardware, either on-premises or in the cloud. The best part is that the database of results is searchable so you can mine the info to see the performance of other CPUs that you might be interested in moving to. iperf Iperf is a great free utility to help you performance network throughput testing. You download a copy, deploy it to multiple servers, and then set up one as a server and one as the test client. More details on how to use this utility is found here. CoreInfo (Microsoft Sysinternals) The CoreInfo utility from the Microsoft Sysinternals suite is a great utility to help you determine the in-guest CPU topology, as seen and used by your SQL Server workloads. NUMA, hyperthreading, and alignment tasks can be validated through the use of this free utility. SQL Server Disk Stall Collector SQL Server keeps track of the ‘stall’ rates of how quickly it can access the database data and log files on the file system. These are different metrics than what your underlying operating system tracks, as the disk stall rates can show indications of metadata contention inside SQL Server, even if the underlying operating system doesn’t see it. DMVs inside SQL Server do track this information, and while aggregate information is usually what is stored, the law of averages might mean that you lose the importance of the data. This script, documented on my other site, mines this information and helps you track this information over time so you can understand if you have hot-spots during the day or night that are causing your databases to run slow. I really hope that these scripts and utilities helps you test, validate, and stress your infrastructure underneath your critical SQL Servers so that you can help to have the best possible platform for your data! Enjoy the rest of the PASS Summit conference, and do not hesitate to reach out if you have any additional questions on the content in my precon!  The post PASS Summit 2020 Precon - Scripts and Links first appeared on Convergence of Data, Cloud, and Infrastructure. The post PASS Summit 2020 Precon – Scripts and Links appeared first on SQLServerCentral.

T-SQL Tuesday is a monthly blog party in the SQL Community and this month is hosted by Taiob Ali (b | t) and it’s about a topic I could write a lot about because I’ve had to do a lot to be able to cope with the pandemic on three realms mentioned: mental health, physical health, and professional growth.  Given that this darn pandemic doesn’t seem be ending anytime soon this is an excellent topic to tackle to everyone can learn and glean from others and put things into practice that may help them. Mental Health As many in the community know I suffer from mental health issues, I was diagnosed with bipolar II, PTSD, and generalized anxiety disorder a long time ago, over 15 years ago.  Just last December I started presenting on how mental health issues are prevalent in IT in general due to stress, burnout, harassment, and bullying in our work environments.  Shameless plus you can catch the newest form of this session at Summit on Thursday at 10:30 AM EST (unless they change the schedule).  It also has a recorded version on my presentation page that is older.  I recommended attending one virtually so you can ask questions and initiate discussion.  It’s when we discuss these mental health openingly that others benefit and take the stigma behind it and make disappear and make as common to take about as diabetes or cancer. Some things I’ve done to help my mental health (not that it’s been the greatest during the pandemic) is first remember this too shall pass.  You can back and read a blog post where I was all over the place worrying about everything in the world.  Yes, one day this darn pandemic will gone and the world will return to normal (I hate the term the new norm and refuse to accept it).  I’ve also developed closer relationships with several people in the SQL Community that I can talk to when I’m struggling so they can keep me grounded and they can in turn tell me when they are struggling (hopefully, it’s been a good two way street).  Also, I think presenting my mental health session has help remind me of all the things I can do to help myself and the fact that others have said it has help them (except the one rude person in Austin) has given me boost to know that other people are struggling and I’m not alone.  I’ve learned to put my fears about the future in perspective.  I’ve been through a lot in life and came out on the other side, this is just one more thing to go through and persevere (one of my core values).  I’ve also continue to do my volunteer work with foster kids, the mental health council, and the SQL Community which goes with my other core value of making a difference. Due to having existing mental health issues I’ve also had to get adjustment in medications and lean on my therapist for sanity because besides the pandemic, personal issues are never lacking in my life. I truly encourage to catch my session as it will explain better all the things I incorporate to help with my mental health and other things you may be able to do to help yourself.  If you are attending Summit find me if you don’t attend the session and feel free to just talk, I’m open via DMs on Twitter as well. Physical Health My physical health hasn’t neither improved or gotten worse.  Which is good in away.  I’ve gotten lazier (which is worse), I was already working from home before the pandemic, but now I just don’t care about my routines to take care of myself or about what I eat.  So I’ve recently started seeing a nutritionist to start getting back on track with healthy eating habits.  I’ve started trying to get 10,000 steps in a day, with just taking a break every hour from a my computer and traversing the hallways and stairwells in 4 story apartment building and maybe a 30 minute walk outside.  By January, I want to start a program to get ready to run a 5K, only if I can consistently get into walking outdoors 4x a week.  Then build back up to a half marathon and back to my original goal of a full marathon for a 45 birthday which may get pushed out due to the pandemic but getting my nutrition back to where it needs to be will help with all these goals. Professional Growth I’m really a nerd when it comes to goals.  I update a Life Plan** each year, set 10 goals for the year, and use a Full Focus Planner each quarter to help me reach my goals.  But the quarter the pandemic hit man the goals just wanted to run and hide and some just could not be completed.  So after a few weeks of being like this year is going to suck and be a waste of time, I regrouped and decided on new goals for the ones I could not complete.  First up was to write a new tech presentation, which I’ll do another one over Christmas week (yes I know not the way most people would spend a Christmas week off from work).  Next up is what did I want to learn for the year, well I needed Kusto for work, so Pluralsight to the rescue, and setting aside an hour a week to learn it which is still something I need to finish.  I’ve also added getting Azure certified and learning containers better.  So I developed a learning plan for those that I’m slowly going through so I don’t overwhelm myself.  But I do have other areas of my life I’m trying to develop as part of this time where I’m spending more time at home. Summary In summary, stick to your core values*.  Mine are to persevere and to make a difference and hopefully and I can look back at this year and see that I have done that like other years. *I used Brene Brown’s book Dare to Lead to identify my core values. **Living Forward is the book I used to make my Life Plan.  I even got Andy Leonard a few other people in the community to check it out. The post T-SQL Tuesday #132: How Are You Coping with Pandemic? first appeared on Tracy Boggiano's SQL Server Blog. The post T-SQL Tuesday #132: How Are You Coping with Pandemic? appeared first on SQLServerCentral.

Who Am I Talking To You use bash or python. PowerShell seems wordy, extra verbose, and annoying. It’s a windows thing, you say… why would I even look at it. Pry bash out of my fingers if yuo dare (probably not for you ??) What PowerShell Is The best language for automating Windows… period. A great language for development tooling and productivity scripts. One of the best languages for automation with interactivity. Python is fantastic. The REPL isn’t meant for the same interactivity you get with PowerShell. PowerShell prompt is sorta like mixing Python & fish/bash in a happy marriage. A rich language (not just scripting) for interacting with AWS using AWS.Tools. A rich object-oriented pipeline that can handle very complex actions in one-liners based on object-oriented pipelines. Intuitive and consistent mostly for command discovery. a common complaint from bash pros. The point of the verbosity Verb-Noun is discoverability. tar for example is a bit harder to figure out than Expand-Archive -Path foo -DestinationPath foo A language with a robust testing framework for unit, integration, infrastructure, or any other kinda testing you want! (Pester is awesome) What PowerShell Isn’t Python ?? Good at datascience. Succinct Meant for high-concurrency Good at GUI’s… but come-on we’re devs… guis make us weak ?? A good webserver Lots more. The Right Tool for the Job I’m not trying to tell you never to use bash. It’s what you know, great! However, I’d try to say if you haven’t explored it, once you get past some of the paradigm differences, there is a rich robust set of modules and features that can improve most folks workflow. Why Even Consider PowerShell As I’ve interacted more and more with folks coming from a mostly Linux background, I can appreciate that considering PowerShell seems odd. It’s only recently that it’s cross platform in the lifecycle of things, so it’s still a new thing to most. Having been immersed in the .NET world and now working on macOS and using Docker containers running Debian and Ubuntu (sometimes Alpine Linux), I completely get that’s not even in most folks purview. Yet, I think it’s worth considering for developer workflows that there is a lot of gain to be had with PowerShell for improving the more complex build and development workflows because of the access to .NET. No, it’s not “superior”. It’s different. Simple cli bash scripting is great for many things (thus prior article about Improving development workflow Task which uses shell syntax). The fundemental difference in bash vs PowerShell is really text vs object, in my opinion. This actually is where much of the value comes in for considering what to use. Go For CLI Tools Go provides a robust cross-platform single binary with autocomplete features and more. I’d say that for things such as exporting pipelines to Excel, and other “automation” actions it’s far more work in Go. Focus Go on tooling that makes the extra plumbing and stronger typing give benefit rather than just overhead. AWS SDK operations, serverless/lambda, apis, complex tools like Terraform, and more fit the bill perfectly and are a great use case. Scenario: Working with AWS If you are working with the AWS SDK, you are working with objects. This is where the benefit comes in over cli usage. Instead of parsing json results and using tools like jq to choose arrays, instead, you can interact with the object by named properties very easily. $Filters = @([Amazon.EC2.Model.Filter]::new('tag:is_managed_by','muppets') $InstanceCollection = (Get-EC2Instance -Filter $Filters)).Instances | Select-PSFObject InstanceId, PublicIpAddress,PrivateIpAddress,Tags,'State.Code as StateCode', 'State.Name as StateName' -ScriptProperty @{ Name = @{ get = { $this.Tags.GetEnumerator().Where{$_.Key -eq 'Name'}.Value } } } With this $InstanceCollection variable, we now have access to an easily used object that can be used with named properties. Give me all the names of the EC2 instances: $InstanceCollection.Name Sort those: $InstanceCollection.Name | Sort-Object (or use alias shorthand such as sort) For each of this results start the instances: $InstanceCollection | Start-EC2Instance Beyond that, we can do many things with the rich eco-system of prebuilt modules. Here are some example of some rich one-liners using the power of the object based pipeline. Export To Json: $InstanceCollection | ConvertTo-Json -Depth 10 | Out-File ./instance-collection.json Toast notification on results: Send-OSNotification -Title 'Instance Collection Results' -Body "Total results returned: $($InstanceCollection.Count)" Export To Excel with Table: $InstanceCollection | Export-Excel -Path ./instance-collection.json -TableStyle Light8 -TableName 'FooBar' Send a rich pagerduty event to flag an issue: Send-PagerDutyEvent -Trigger -ServiceKey foo -Description 'Issues with instance status list' -IncidentKey 'foo' -Details $HashObjectFromCollection Use a cli tool to flip to yaml (you can use native tooling often without much issue!): $InstanceCollection | ConvertTo-Json -Depth 10 | cfn-flip | Out-File ./instance-collection.yml Now build a test (mock syntax), that passes or fails based on the status of the instances Describe "Instance Status Check" { Context "Instances That Should Be Running" { foreach($Instance in $InstanceCollection) { It "should be running" { $Instance.StatusName | Should -Be 'Running' } } } } Now you have a test framework that you could validate operational issues across hundreds of instances, or just unit test the output of a function. Exploring the Object I did this comparison once for a coworker, maybe you’ll find it useful too! "Test Content" | Out-File ./foo.txt $Item = Get-Item ./foo.txt ## Examine all the properties and methods available. It's an object $Item | Get-Member This gives you an example of the objects behind the scene. Even though your console will only return a small set of properties back, the actual object is a .NET object with all the associated methods and properties. This means that Get-Item has access to properties such as the base name, full path, directory name and more. You can access the actual datetime type of the CreationTime, allowing you to do something like: ($item.LastAccessTime - $Item.CreationTime).TotalDays This would use two date objects, and allow you to use the relevant Duration methods due to performing math on these. The methods available could be anything such as $Item.Encrypt(); $Item.Delete; $Item.MoveTo and more all provided by the .NET namespace System.IO.FileInfo. I know many of these things you can do in bash as well, but the object pipeline here I’d wager provides a very solid experience for more complex operations based on the .NET framework types available. Wrap Up This was meant to give a fresh perspective on why some folks have benefited from PowerShell over using shell scripting. It’s a robust language that for automation/build/cloud automation can give a rich reward if you invest some time to investigate. For me the basic “right tool for the job” would like like this: data: python serverless: go & python (powershell can do it too, but prefer the others) web: go & python basic cli stuff: shell (using Task which uses shell syntax) complex cli project tasks: powershell & go automation/transformation: powershell & python high concurrency, systems programming: go Maybe this provided a fresh perspective for why PowerShell might benefit even those diehard shell scripters of you out there and maybe help convince you to take the plunge and give it a shot. #development #cool-tools #golang #automation The post Consider the Benefits of Powershell for Developer Workflows appeared first on SQLServerCentral.

I'll be presenting at 7AM Central Time in the first timeslot of the main three-day PASS Virtual Summit 2020 conference. It's long been a goal of mine to present at the best of all international SQL conferences, and this is the first year it happened for me, so I'm thrilled to be a part of it. It's not too late to register for the all-online event, with the same great quality content as always, at a fraction of the usual cost of going to Seattle. Like many (but not all) presentations at PASS Virtual Summit, my 75-minute presentation will feature roughly 60 minutes of pre-recorded (and painstakingly edited) content, with the rest of the time available for live Q&A with the speaker.  My presentation will cover a lot of important foundational material about security, accounts, authentication.  For folks new to SQL Server security design and administration, this will be a great foundation for your learning.  For those experienced in SQL admin, this will be a thorough evaluation of what you know, or thought you know, and maybe some gaps in what you know.  I think there is content in here to interest everyone in the SQL career lifecycle, and I’m not just guessing at that. I got my first DBA job in 2006. I’ve been giving a presentation on Security at User Groups and SQLSaturdays basics for years, it was one of the first topics I started speaking technically on a decade ago. As my own experience has deepened and broadened throughout my career, so has the content I build into this presentation.  So I’m going to start basic, and build quickly from there, focusing my content around common hurdles and tasks that database administrators face, in the hopes of deepening or broadening your experience, as well.  I'm setting the stage for a good conversation around security at PASS Virtual Summit 2020, especially around how permissions behave inside each database, how you can design database security, the relationships between logins, users, and databases. My session one of a four part Learning Pathway on security. We worked together over the past four months to make sure we're presenting a thorough conversation on security.  In subsequent presentations over the next three days: John Morehouse is presenting on Understanding Modern Data Encryption Offerings for SQL Server, including a lot more information on all the various sorts of encryption, plus some important security features of SQL Server like Transparent Data Encryption and Always Encrypted. Jeff Renz is presenting on Securing Your Data In Azure: Tips and Tricks, which covers Advanced Threat Detection, the Azure Key Vault, cloud secure connection strings, and certified data sets for security in PowerBI. Ed Leighton-Dick will cap it off with a presentation on Building a Security Dashboard for SQL Server, talking about when certs expire and what does that actually mean, more on Azure Advanced Threat Detection, SQL Audit and monitoring. The post PASS Virtual Summit 2020: I'm (Virtually) Presenting appeared first on SQLServerCentral.

Mostly Friday notes here. This morning I was the moderator for Continuous Integration with Local Agents and Azure DevOps by Steve Jones. The way the Summit worked this year is that the speaker, moderator, and support staff would all join a Zoom call and then the Zoom call was streamed back to Cadmium and out to users. I also had a private link to the streamed session so that I could monitor the questions and discussions. I started out by joining Zoom and the public stream, wanted to see the delay first hand. It seemed to be around 30 seconds, but I understand that can vary up to 90 seconds. I only checked a couple times. In that mode I could see the questions but not answer them. It was also – as expected – a confusing experience, because on Zoom Steve was 30+ seconds ahead of what was playing in the public stream. So, I closed that, joined back with the private link and muted the audio. After that it was watching the question tab while listening to the presentation on Zoom. The technical experience questions were handled by the support team, so I only had to watch for the technical ones. We ended up with two batches, one about half way and toward the end. The first question was a good one, but I had not seen the deck ahead of time and decided to hold it for a slide to see if it would get answered in the flow. Almost, so then just a matter of waiting for a place to interrupt, without interrupting! I tried to do the following, with mixed success: Pose the question as “Andy asked…” if I thought I could get the name right. Mute Try to capture a bit of the verbal answer and type that in to the question answer. In one case I was able to find a link to the page he was looking at. Mark as answered Go back and mark any technical question as a favorite (if there had been a 100 questions, maybe this wouldn’t have been doable) Repeat, keeping an eye on the time He ended up with a couple mins to spare at the end after answering all of the questions, so I asked a couple of my own to clarify a couple points and that carried it just a bit over the end time (which is ok). I checked the discussion tab a few times, but short of a big problem assumed that was attendees chatting and shouldn’t need moderation. The UI for this was just OK. One tab for questions with an option to see all or unanswered, and then the discussion tab. You can go back and forth or set them up side by side. When you answer a question there is not an option to mark as favorite then, you have to go through the all questions list to mark it. Annoying, not huge. Tried Discord, didn’t seem to land in the right place, then got interrupted and didn’t get back to it. Someone out there, write something about that experience! A friend mentioned that she wished she had thought to write and publish notes from the beginning and I’m noting that here because we should encourage that. Finding topics to write about is hard and the Summit offers a lot of topics. More than that though, it’s a way to think about and share what works, what’s fun, what you noticed or appreciated that maybe no one else did (and it’s a way to share with those not able to attend). I’d love to see some kind of contest next year related to blogging. I finished up the day in the community zone, pleased to see old friends and have a chance to talk about this and that. A few miscellaneous notes based on some stuff we talked about: Classroom layouts in general are not great for networking Background music and getting everyone a drink (even if it’s soda) tends to encourage the walk and talk networking (like the opening night reception) Holiday events are good (if hard to do this year) Lots of value in having events that are just Q&A, or just a meal together That finishes up the week. This weekend I’m going to read back over my notes and think about the week before writing a final wrap up post for Monday. The post Notes on the 2020 PASS Virtual Summit – Part 6 appeared first on SQLServerCentral.

Quick video showing you how to failover your Azure SQL Database between your primary and secondary location. The post [Video] Azure SQL Database – Failing Over (Failover Groups) appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to think of three things that give you hope for the future. Today especially, election day in the US, it likely feels hope is elusive for many, but I don’t feel that way. I do have some anxiety, but it’s mostly for the immediate changes in my country, not the future beyond the next year. I do have hope and here are three things that I do cling to. Individuals are good Despite the disagreements I might have with many people about how specific issues, for the most part, I find that people I encounter in life, even in this crazy pandemic world, are good. They have compassion, empathy, and kindness towards others that stand in front of them. It’s important to remember that most of our world is still our live connections, not the digital ones. The Next Generations Have Values Beyond Themselves I don’t mean to imply that my generation doesn’t think this way, but I find that so many people overall think about themselves more so than anything else. They look at their situation, or their family’s, as much more important than anything else. They have short term thinking, in general. Despite the vapid, immediacy of how so many young people view the world, I also find them to more often be thinking about the wider world, other cultures, the planet, and more abstract items, with less concern about money. A vague generalization, but one I think is hopeful for the future of many aspects of the world. We Locked Down As much as a pain as it was, and as much disruption as it caused, regardless of effectiveness, I was amazed the world pulled together to shut down most air travel, most borders, and many businesses. It was truly amazing to me that we were able to do this without too much resistance. I know that this has been contentious since then, but in March and April, the world amazed me. Much like I was impressed by efforts like this one. The post Daily Coping 3 Nov 2020 appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. Today’s tip is to set a goal that links to your sense of purpose in life. My purpose in life is to help others. I do this in a few ways, but one of them that I’ve been wanting to tackle is to find a new way to volunteer locally. Today, I’m setting a reminder to take my volunteer day from Redgate this year and spend it with a local group. The post Daily Coping 5 Nov 2020 appeared first on SQLServerCentral.