Tech News

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 aside regular time to pursue an activity you love. For me, I don’t know I love a lot of things, but I do enjoy guitar and I’ve worked on it a lot this year. The last month, I’ve let it go a bit, but I’m getting back to it. I try to remember to bring it downstairs to my office, and I’ll take some 5-10 minute breaks and play. I’ve also started to put together a little time on Sat am to work through a course, and build some new skills. The post Daily Coping 1 Dec 2020 appeared first on SQLServerCentral.

Introduction In my last blog post, I have addressed an issue with DAX Median function consuming a lot of memory. To refresh the result, below is the performance summary of those implementations. More on that topic you can find in this previous article. Performance for hour attribute (24 values) Duration (s) Memory Consumed (GB) Native median function 71.00 8.00 Custom implementation 1 6.30 0.20 Many2Many median implementation 2.20 0.02 Performance for location attribute (422 values) Duration (s) Memory Consumed (GB) Native median function 81.00 8.00 Custom implementation 1 107.00 2.50 Many2Many median implementation 41.10 0.08 It seems we have solved the issue with memory but still, the duration of this query when used with locations is not user-friendly. Today we will focus on the performance part. Tuning 3 – Improve User Experience I did not find how to improve the performance with some significant change of DAX or model. As such, I was thinking if we can somehow use aggregations for the median. MEASURESenzors[Calc_MedTempMap] =VAR _mep = [MedianPositionEven]VAR _mepOdd = [MedianPositionOdd]VAR _TempMedianTable = ADDCOLUMNS( values(TemperatureMapping[temperature]), “MMIN”,  [RowCount]– [TemperatureMappingRowCount] +1 , “MMAX”, [RowCount])        VAR _T_MedianVals =FILTER( _TempMedianTable ,( _mep >= [MMIN]&& _mep <= [MMAX])||( _mepOdd >= [MMIN]&& _mepOdd <= [MMAX]) )RETURNAVERAGEX( _T_MedianVals, [temperature]) The part highlighted is still the critical one having the biggest impact on performance because formula engine needs to do the following: – Iterate through all values we have on visual (for example location) – For each item take a list of temperatures – For each temperature get a cumulative count (sum of all counts of lower temperatures) Although we made faster a less expensive cumulative count, we are doing too many loops in the formula engine evaluating similar values again and again. What about to pre-calculate “_TempMedianTable” table so we don’t have to change the algorithm but just pick up cumulative counts as a materialized column? This is how the new model would look like: We can do the aggregation in the source system or we can do it even in Power BI, because we have less memory consumption. There are two helper tables: LocMedAgg – for analyses through a location. HourMedianAgg – for analyses by hour. Now we need to create an hour and location-specific measures and then one combined measure which will switch among them according to the current selection of attributes made by the user. This is DAX expression for LocMedAgg table: MEASURESenzors[LocMedAgg] =FILTER(SUMMARIZECOLUMNS(Senzors[location],TemperatureMapping[temperature],“TcountEndCount”, [RowCount],“TCountStart”,[RowCount] – [TemperatureMappingRowCount] + 1,“Cnt”, [TemperatureMappingRowCount]),— due to m2n relation we would have empty members we do not need and therefore let’s filter themNOT(ISBLANK([TemperatureMappingRowCount]))) New definition for hour Median measure is: —————————————————————— MEASURESenzors[HM_MedianPositionEven] =ROUNDUP(([HM_CountRows] / 2), 0) —————————————————————— MEASURESenzors[HM_MedianPositionOdd] =VAR _cnt = [HM_CountRows]RETURNROUNDUP(( _cnt / 2), 0) + ISEVEN( _cnt ) —————————————————————— MEASURESenzors[HM_Med] =VAR _mpe = [HM_MedianPositionEven]VAR _mpeOdd = [HM_MedianPositionOdd]VAR _T_MedianVals =FILTER( HourMedianAgg,VAR _max = HourMedianAgg[TcountEndCount]VAR _min = HourMedianAgg[TCountStart]RETURN( _mpe >= _min&& _mpe <= _max )||( _mpeOdd >= _min&& _mpeOdd <= _max ))RETURNAVERAGEX( _T_MedianVals, [temperature]) However, when we bring it into the visualization, we see the following issue: We are missing the total value. But that actually is no issue for us as we need to bring a context into the final calculation anyway, so we will compute the total value in a different branch of the final switch. We create the aggregated median measures for location the same way as for hour, and then we put it all together in the final median calculation that switches among different median helpers. For simplification, I wrapped the logic for each branch into a new measure, so the final calculation is simple: MEASURESenzors[CombinedMedian] =SWITCH(1 = 1,[UseHourMedian], [HM_Med_NoAgg],[UseLocationMedian], [LM_Med_NoAgg],[IsDateFiltered], [Orig_Med],[Calc_MedTempMap]) The switch above do this: If an hour and nothing else is selected use hour aggregation median calculation If location and nothing else is selected use location aggregation median If date attribute is selected use native median In all other cases use M2M median calculation Below is one of the switching measures: MEASURESenzors[IsDateFiltered] =— as I let engine to generate hierarchy for me I need to have this filter a bit complex to identify if any level of data is filteredISFILTERED(Senzors[Date].[Date])||ISFILTERED(Senzors[Date].[Day])||ISFILTERED(Senzors[Date].[Month])||ISFILTERED(Senzors[Date].[MonthNo])||ISFILTERED(Senzors[Date].[Quarter])||ISFILTERED(Senzors[Date].[QuarterNo])||ISFILTERED(Senzors[Date].[Year]) MEASURESenzors[UseHourMedian] = ISFILTERED(‘Hour'[Hour])&&NOT(ISFILTERED(Location[Location])) &&NOT([IsDateFiltered]) And that’s it! Now we have solution where you get median under one second for major dimensions. You can download sample pbx from here. The post Power BI – Hungry Median – Aggregations appeared first on SQLServerCentral.

All the changes for this release can be found in the Github Project page Mainly bug fixes this time around , but we have also added new functionality: Improvements #263 If you centralize your Servers’ collections into a single database, you may be interested in the latest addition, we added the ability to override most of the global settings for thresholds found in the Settings table on a server by server basis so you are no longer locked to a threshold for all the servers information contained within the database. Check out the Github issue for more details regarding the change or check out the Inspector user guide. Bug Fixes #257 Fixed a bug where the Inspector Auto update Powershell function was incorrectly parsing non uk date formats, download the latest InspectorAutoUpdate.psm1 to get the update. #261 We noticed that ModuleConfig with ReportWarningsOnly = 3 still sent a report even if there were no Warnings/Advisories present so we fixed that. #256 If you use the powershell collection and one of your servers had a blank settings table that servers’ collection was being skipped, shame on us! we fixed this so that the settings table is re-synced and collection continues. #259 The BlitzWaits custom module was highlighting wait types from your watched wait types table even when the threshold was not breached , A silly oversight but we got it fixed. #265 The Backup space module was failing if access was denied on the backup path , we handle this gracefully now so you will see a warning on your report if this occurs. The post Inspector 2.4 now available appeared first on SQLServerCentral.

Kristin Adderson December 1, 2020 - 1:12am December 1, 2020 Virtual training is something I’m very passionate about because I’ve experienced firsthand how powerful it can be. But it recently occurred to me that if you’ve never taken any virtual training, it’s likely you don’t fully understand what it is. Is it eLearning? Pre-recorded webinars? It isn’t very clear. In our rapidly changing digital world, many learning offers are ‘on-demand,’ and virtual training refers to any learning that can be done online. I’d like to give you a behind-the-scenes look at what you can expect in a Tableau virtual training. When you attend a Tableau virtual training, you get the same in-depth content found in our in-person classrooms, delivered by an exceptional instructor. But the similarities end there. Don’t get me wrong; I love our in-person classes, but the last time I attended an 8-hour, all-day training, it wiped me out. Learning new content for 8 hours a day is exciting but exhausting. In contrast, our virtual training is delivered via Webex and scheduled over a week, typically for 2 ½ hours a day (this varies per class). As an instructor, it’s always so rewarding to see my students progress during the week. I love building rapport with them and seeing them connect with the product and with each other. Students still make connections in a virtual classroom—with classmates across the globe, instead of across the room. A question from someone in Tokyo can inspire someone who is from Denmark. Virtual training averages about ten attendees per session, providing students with the classroom feel and benefits of learning from each other. Activities are scattered throughout the training sessions, providing hands-on practice opportunities to apply what you’ve learned. By having an instructor present, you get your questions answered before you can get stuck. Additional practices are assigned as homework to encourage further exploration. So how are questions handled during virtual training? You simply ask your questions through your audio connection on your computer. We recommend using a headset with a microphone so your questions can be heard clearly. There's a chat option for those who don’t want to speak up in class so you can address your question directly to the instructor or the entire class. Get stuck on an issue while doing homework? Bring your questions to class the next day to discuss during the homework review period. Or, email the instructor who will be happy to guide you in the right direction. Questions aren’t a one-way street in Tableau virtual training. The instructor will use a variety of methods to engage with each attendee. Classes kick off with the instructor and students introducing themselves. This helps build a community of learning and makes it easier to interact with the class when you know a little about everyone. The instructor encourages a high level of engagement and will ask the class questions to track their understanding of the material. Responses can be given through audio, chat, or icons. Polls are sometimes used to validate understanding. All Tableau classroom training is available in a live virtual format. Our most popular classes (Desktop I and Desktop II) are offered in English, Spanish, Portuguese, French, German, and Japanese. Get the virtual advantage Virtual training holds a special place in my heart. Speaking as someone with many years of personal experience with virtual training, I still appreciate what it offers. To recap, here are five reasons why you should consider virtual training: Same content, only more digestible. Virtual training contains the same content as our in-person classes but broken into smaller segments. Learn a little at a time, absorb and apply the concepts, and come back the next day for more. Available anytime, anywhere. Virtual training provides the flexibility to attend classes in multiple time zones and six different languages. As long as you have a strong internet connection, you are good to go. Less disruptive to your daily schedule. Virtual training makes it easy to get that valuable interaction with a live instructor without even having to leave your office (or your house, for that matter). Real-time feedback. No need to struggle on your own. Ask the instructor and other attendees questions, understand different use cases, and get the guidance you need while doing hands-on activities. More practice makes perfect. There’s plenty of hands-on practice time both during class and with extra homework. “Time flies when you’re having fun with data” is an excellent way to describe the Tableau virtual training experience. If you’re looking for a flexible and fun way to gain valuable Tableau knowledge, go virtual. Take it from me, a virtual training veteran. You’ll never look at learning the same way again. Find a virtual class today!

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Recently I saw Jason Horner do a presentation on JSON at a user group meeting. I’ve lightly looked at JSON in some detail, and I decided to experiment with this. Basic Querying of a Document A JSON document is text that contains key-value pairs, with colons used to separate them, and grouped with curly braces. Arrays are supported with brackets, values separated by commas, and everything that is text is quoted with double quotes. There are a few other rules, but that’s the basic structure. Things can next, and in SQL Server, we store the data a character data. So let’s create a document: DECLARE @json NVARCHAR(1000) = N'{  "player": {             "name" : "Sarah",             "position" : "setter"            }  "team" : "varsity"}' This is a basic document, with two key values (player and team) and one set of additional keys (name and position) inside the first key. I can query this with the code: SELECT JSON_VALUE(@json, '$.player.name') AS PlayerName; This returns the scalar value from the document. In this case, I get “Sarah”, as shown here: I need to get the path correct here for the value. Note that I start with a dot (.) as the root and then traverse the tree. A few other examples are shown in the image. These show the paths to get to data in the document. In a future post, I’ll look in more detail how this works. SQLNewBlogger After watching the presentation, I decided to do a little research and experiment. I spent about 10 minutes playing with JSON and querying it, and then another 10 writing this post. This is a great example of picking up the beginnings of a new skill, and the start of a blog series that shows how I can work with this data. The post Basic JSON Queries–#SQLNewBlogger 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 make a meal using a recipe or ingredient you’ve not tried before. While I enjoy cooking, I haven’t experimented a lot. Some, but not a lot. I made a few things this year that I’ve never made before, as an experiment. For example, I put together homemade ramen early in the pandemic, which was a hit. For me, I had never made donuts. We’ve enjoyed (too many) donuts during the pandemic, but most aren’t gluten free. I have a cookbook that includes and one for donuts. It’s involved, like bread, with letting the dough rise twice and then frying in oil. I told my daughter I’d make them and she got very excited. I didn’t quite realize what I’d gotten myself into, and it was hours after my girl expected something, but they came out well. It felt good to make these. My Mom had made something similar when I was a kid, but I’d never done them until now. The post Daily Coping 30 Nov 2020 appeared first on SQLServerCentral.

A project I’ve been meaning to work on for a while has been to build my own Kubernetes cluster running on Raspberry Pis. I’ve been playing around with Kubernetes for a while now and things like Azure Kubernetes Service are great tools to learn but I wanted something that I’d built from the ground up. Something that I could tear down, fiddle with, and rebuild to my heart’s content. So earlier this year I finally got around to doing just that and with Azure SQL Edge going GA with a disconnected mode I wanted to blog about my setup. Here’s what I bought: – 1 x Raspberry Pi 4 Model B – 8BG RAM 3 x Raspberry Pi 4 Model B – 4GB RAM 4 x SanDisk Ultra 32 GB microSDHC Memory Card 1 x Pi Rack Case for Raspberry Pi 4 Model B 1 x Aukey USB Wall Charger Adapter 6 Ports 1 x NETGEAR GS308 8-Port Gigabit Ethernet Network Switch 1 x Bunch of ethernet cables 1 x Bunch of (short) USB cables OK, I’ve gone a little overboard with the Pis and the SD cards. You won’t need an 8GB Raspberry Pi for the control node, the 4GB model will work fine. The 2GB model will also probably work but that would be really hitting the limit. For the SD cards, 16GB will be more than enough. In fact, you could just buy one Raspberry Pi and do everything I’m going to run through here on it. I went with a 4 node cluster (1 control node and 3 worker nodes) just because I wanted to tinker. What follows in this blog is the complete build, from setting up the cluster, configuring the OS, to deploying Azure SQL Edge. So let’s get to it! Yay, delivery day! Flashing the SD Cards The first thing to do is flash the SD cards. I used Rufus but Etcher would work as well. Grab the Ubuntu 20.04 ARM image from the website and flash all the cards: – Once that’s done, it’s assembly time! Building the cluster So…many…little…screws… But done! Now it’s time to plug it all in. Plug all the SD cards into the Pis. Connect the USB hub to the mains and then plug the switch into your router. It’s plug and play so no need to mess around. Once they’re connected, plug the Pis into the switch and then power them up (plug them into the USB Hub): – (Ignore the zero in the background, it’s running pi-hole which I also recommend you check out!) Setting a static IP address for each Raspberry Pi We’re going to set a static IP address for each Pi on the network. Not doing anything fancy here with subnets, we’re just going to assign the Pis IP addresses that are currently not in use. To find the Pis on the network with their current IP address we can run: – nmap -sP 192.168.1.0/24 Tbh – nmap works but I usually use a Network Analyser app on my phone…it’s just easier (the output of nmap can be confusing). Pick one Pi that’s going to be the control node and let’s ssh into it: – ssh ubuntu@192.168.1.xx When we first try to ssh we’ll have to change the ubuntu user password: – The default password is ubuntu. Change the password to anything you want, we’re going to be disabling the ubuntu user later anyway. Once that’s done ssh back into the Pi. Ok, now that we’re back on the Pi run: – sudo nano /etc/netplan/50-cloud-init.yaml And update the file to look similar to this: – network: ethernets: eth0: addresses: [192.168.1.53/24] gateway4: 192.168.1.254 nameservers: addresses: [192.168.1.5] version: 2 192.168.1.53 is the address I’m setting for the Pi, but it can be pretty much anything on your network that’s not already in use. 192.168.1.254 is the gateway on my network, and 192.168.1.5 is my DNS server (the pi-hole), you can use 8.8.8.8 if you want to. There’ll also be a load of text at the top of the file saying something along the lines of “changes here won’t persist“. Ignore it, I’ve found the changes do persist. DISCLAIMER – There’s probably another (better?) way of setting a static IP address on Ubuntu 20.04, this is just what I’ve done and works for me. Ok, once the file is updated we run: – sudo netplan apply This will freeze your existing ssh session. So close that and open another terminal…wait for the Pi to come back up on your network on the new IP address. Creating a custom user on all nodes Let’s not use the default ubuntu user anymore (just because). We’re going to create a new user, dbafromthecold (you can call your user anything you want ): – sudo adduser dbafromthecold Run through the prompts and then add the new user to the sudo group: – sudo usermod -aG sudo dbafromthecold Cool, once that’s done, exit out of the Pi and ssh back in with the new user and run: – sudo usermod --expiredate 1 ubuntu This way no-one can ssh into the Pi using the default user: – Setting up key based authentication for all nodes Let’s now set up key based authentication (as I cannot be bothered typing out a password every time I want to ssh to a Pi). I’m working in WSL2 here locally (I just prefer it) but a powershell session should work for everything we’re going to be running. Anyway in WSL2 locally run: – ssh-keygen Follow the prompt to create the key. You can add a passphrase if you wish (I didn’t). Ok, now let’s copy that to the pi: – cat ./raspberrypi_k8s.pub | ssh dbafromthecold@192.168.1.53 "mkdir -p ~/.ssh && touch ~/.ssh/authorized_keys && chmod -R go= ~/.ssh && cat >> ~/.ssh/authorized_keys" What this is going to do is copy the public key (raspberrypi_k8s.pub) up to the pi and store it as /home/dbafromthecold/.ssh/authorized_keys This will allow us to specify the private key when connecting to the pi and use that to authenticate. We’ll have to log in with the password one more time to get this working, so ssh with the password…and then immediately log out. Now try to log in with the key: – ssh -i raspberrypi_k8s dbafromthecold@192.168.1.53 If that doesn’t ask for a password and logs you in, it’s working! As the Pi has a static IP address we can setup a ssh config file. So run: – echo "Host k8s-control-1 HostName 192.168.1.53 User dbafromthecold IdentityFile ~/raspberrypi_k8s" > ~/.ssh/config I’m going to call this Pi k8s-control-1, and once this file is created, I can ssh it to by: – ssh k8s-control-1 Awesome stuff! We have setup key based authentication to our Pi! Configuring the OS on all nodes Next thing to do is rename the pi (to match the name we’ve given in our ssh config file): – sudo hostnamectl set-hostname k8s-control-1 sudo reboot That’ll rename the Pi to k8s-control-1 and then restart it. Wait for it to come back up and ssh in. And we can see by the prompt and the hostname command…our Pi has been renamed! Ok, now update the Pi: – sudo apt-get update sudo apt-get upgrade N.B. – This could take a while. After that completes…we need to enable memory cgroups on the Pi. This is required for the Kubernetes installation to complete successfully so run:- sudo nano /boot/firmware/cmdline.txt and add cgroup_enable=memory to the end, so it looks like this: – and then reboot again: – sudo reboot Installing Docker on all nodes Getting there! Ok, let’s now install our container runtime…Docker. sudo apt-get install -y docker.io Then set docker to start on server startup: – sudo systemctl enable docker And then, so that we don’t have to use sudo each time we want to run a docker command: – sudo usermod -aG docker dbafromthecold Log out and then log back into the Pi for that to take effect. To confirm it’s working run: – docker version And now…let’s go ahead and install the components for kubernetes! Installing Kubernetes components on all nodes So we’re going to use kubeadm to install kubernetes but we also need kubectl (to admin the cluster) and the kubelet (which is an agent that runs on each Kubernetes node and isn’t installed via kubeadm). So make sure the following are installed: – sudo apt-get install -y apt-transport-https curl Then add the Kubernetes GPG key: – curl -s https://packages.cloud.google.com/apt/doc/apt-key.gpg | sudo apt-key add - Add Kubernetes to the sources list: – cat <<EOF | sudo tee /etc/apt/sources.list.d/kubernetes.list deb https://apt.kubernetes.io/ kubernetes-xenial main EOF Ok, I know that the 20.04 code name isn’t xenial, it’s focal but if you use kubernetes-focal you’ll get this when running apt-get update: – E: The repository ‘https://apt.kubernetes.io kubernetes-focal Release’ does not have a Release file. So to avoid that error, we’re using xenial. Anyway, now update sources on the box: – sudo apt-get update And we’re good to go and install the Kubernetes components: – sudo apt-get install -y kubelet=1.19.2-00 kubeadm=1.19.2-00 kubectl=1.19.2-00 Going for version 1.19.2 for this install….absolutely no reason for it other than to show you that you can install specific versions! Once the install has finished run the following: – sudo apt-mark hold kubelet kubeadm kubectl That’ll prevent the applications from being accidentally updated. Building the Control Node Right, we are good to go and create our control node! Kubeadm makes this simple! Simply run: – sudo kubeadm init | tee kubeadm-init.out What’s happening here is we’re creating our control node and saving the output to kubeadm-init.out. This’ll take a few minutes to complete but once it does, we have a one node Kubernetes cluster! Ok, so that we can use kubectl to admin the cluster: – mkdir -p $HOME/.kube sudo cp -i /etc/kubernetes/admin.conf $HOME/.kube/config sudo chown $(id -u):$(id -g) $HOME/.kube/config And now…we can run: – kubectl get nodes Don’t worry about the node being in a status of NotReady…it’ll come online after we deploy a pod network. So let’s setup that pod network to allow the pods to communicate with each other. We’re going to use Weave for this: – kubectl apply -f "https://cloud.weave.works/k8s/net?k8s-version=$(kubectl version | base64 | tr -d 'n')" A couple of minutes after that’s deployed, we’ll see the node becoming Ready: – And we can check all the control plane components are running in the cluster: – kubectl get pods -n kube-system Now we have a one node Kubernetes cluster up and running! Deploying a test application on the control node Now that we have our one node cluster, let’s deploy a test nginx application to make sure all is working. The first thing we need to do is remove the taint from the control node that prevents user applications (pods) from being deployed to it. So run: – kubectl taint nodes $(hostname) node-role.kubernetes.io/master:NoSchedule- And now we can deploy nginx: – kubectl run nginx --image=nginx Give that a few seconds and then confirm that the pod is up and running: – kubectl get pods -o wide Cool, the pod is up and running with an IP address of 10.32.0.4. We can run curl against it to confirm the application is working as expected: – curl 10.32.0.4 Boom! We have the correct response so we know we can deploy applications into our Kubernetes cluster! Leave the pod running as we’re going to need it in the next section. Don’t do this now but if you want to add the taint back to the control node, run: – kubectl taint nodes $(hostname) node-role.kubernetes.io/master:NoSchedule Deploying MetalLb on the control node There are no SQL client tools that’ll run on ARM infrastructure (at present) so we’ll need to connect to Azure SQL Edge from outside of the cluster. The way we’ll do that is with an external IP provided by a load balanced service. In order for us to get those IP addresses we’ll need to deploy MetalLb to our cluster. MetalLb provides us with external IP addresses from a range we specify for any load balanced services we deploy. To deploy MetalLb, run: – kubectl apply -f https://raw.githubusercontent.com/google/metallb/v0.8.1/manifests/metallb.yaml And now we need to create a config map specifying the range of IP addresses that MetalLb can use: – apiVersion: v1 kind: ConfigMap metadata:   namespace: metallb-system   name: config data:   config: |     address-pools:     - name: default       protocol: layer2       addresses:       - 192.168.1.100-192.168.1.110 What we’re doing here is specifying the IP range that MetalLb can assign to load balanced services as 192.168.1.100 to 192.168.1.110 You can use any range you want, just make sure that the IPs are not in use on your network. Create the file as metallb-config.yaml and then deploy into the cluster: – kubectl apply -f metallb-config.yaml OK, to make sure everything is working…check the pods in the metallb-system namespace: – kubectl get pods -n metallb-system If they’re up and running we’re good to go and expose our nginx pod with a load balanced service:- kubectl expose pod nginx --type=LoadBalancer --port=80 --target-port=80 Then confirm that the service created has an external IP: – kubectl get services Awesome! Ok, to really confirm everything is working…try to curl against that IP address from outside of the cluster (from our local machine): – curl 192.168.1.100 Woo hoo! All working, we can access applications running in our cluster externally! Ok, quick tidy up…remove the pod and the service: – kubectl delete pod nginx kubectl delete service nginx And now we can add the taint back to the control node: – kubectl taint nodes $(hostname) node-role.kubernetes.io/master:NoSchedule Joining the other nodes to the cluster Now that we have the control node up and running, and the worker nodes ready to go…let’s add them into the cluster! First thing to do (on all the nodes) is add entries for each node in the /etc/hosts file. For example on my control node I have the following: – 192.168.1.54 k8s-node-1 192.168.1.55 k8s-node-2 192.168.1.56 k8s-node-3 Make sure each node has entries for all the other nodes in the cluster in the file…and then we’re ready to go! Remember when we ran kubeadm init on the control node to create the cluster? At the end of the output there was something similar to: – sudo kubeadm join k8s-control-1:6443 --token f5e0m6.u6hx5k9rekrt1ktk --discovery-token-ca-cert-hash sha256:fd3bed4669636d1f2bbba0fd58bcddffe6dd29bde82e0e80daf985a77d96c37b Don’t worry if you didn’t save it, it’s in the kubeadm-init.out file we created. Or you can run this on the control node to regenerate the command: – kubeadm token create --print-join-command So let’s run that join command on each of the nodes: – Once that’s done, we can confirm that all the nodes have joined and are ready to go by running: – kubectl get nodes Fantastic stuff, we have a Kubernetes cluster all built! External kubectl access to cluster Ok, we don’t want to be ssh’ing into the cluster each time we want to work with it, so let’s setup kubectl access from our local machine. What we’re going to do is grab the config file from the control node and pull it down locally. Kubectl can be installed locally from here Now on our local machine run: – mkdir $HOME/.kube And then pull down the config file: – scp k8s-control-1:/home/dbafromthecold/.kube/config $HOME/.kube/ And to confirm that we can use kubectl locally to administer the cluster: – kubectl get nodes Wooo! Ok, phew…still with me? Right, it’s now time to (finally) deploy Azure SQL Edge to our cluster. Running Azure SQL Edge Alrighty, we’ve done a lot of config to get to this point but now we can deploy Azure SQL Edge. Here’s the yaml file to deploy: – apiVersion: apps/v1 kind: Deployment metadata: name: sqledge-deployment spec: replicas: 1 selector: matchLabels: app: sqledge template: metadata: labels: app: sqledge spec: containers: - name: azuresqledge image: mcr.microsoft.com/azure-sql-edge:latest ports: - containerPort: 1433 env: - name: MSSQL_PID value: "Developer" - name: ACCEPT_EULA value: "Y" - name: SA_PASSWORD value: "Testing1122" - name: MSSQL_AGENT_ENABLED value: "TRUE" - name: MSSQL_COLLATION value: "SQL_Latin1_General_CP1_CI_AS" - name: MSSQL_LCID value: "1033" terminationGracePeriodSeconds: 30 securityContext: fsGroup: 10001 --- apiVersion: v1 kind: Service metadata: creationTimestamp: null name: sqledge-deployment spec: ports: - port: 1433 protocol: TCP targetPort: 1433 selector: app: sqledge type: LoadBalancer What this is going to do is create a deployment called sqledge-deployment with one pod running Azure SQL Edge and expose it with a load balanced service. We can either create a deployment.yaml file or deploy it from a Gist like this: – kubectl apply -f https://gist.githubusercontent.com/dbafromthecold/1a78438bc408406f341be4ac0774c2aa/raw/9f4984ead9032d6117a80ee16409485650258221/azure-sql-edge.yaml Give it a few minutes for the Azure SQL Edge deployment to be pulled down from the MCR and then run: – kubectl get all If all has gone well, the pod will have a status of Running and we’ll have an external IP address for our service. Which means we can connect to it and run a SQL command: – mssql-cli -S 192.168.1.101 -U sa -P Testing1122 -Q "SELECT @@VERSION as [Version];" N.B. – I’m using the mssql-cli here but you can use SSMS or ADS. And that’s it! We have Azure SQL Edge up and running in our Raspberry Pi Kubernetes cluster and we can connect to it externally! Thanks for reading! The post Building a Raspberry Pi cluster to run Azure SQL Edge on Kubernetes appeared first on SQLServerCentral.

This is one of them little options that I see which quite often gets little consideration or gets set to a user database without consideration of what the consequences may be if that DB becomes unavailable. There are going to be situations where setting a default other than master is essential and there are going to be situations where leaving as master suits best and this comes down to the individual requirements of each login, Recently I had to fix an issue with user connectivity for a single login, the user was getting failed connections when trying to connect to the SQL server when trying to access one of their legacy databases , everything appeared fine – User account was enabled the password hadn’t been changed and was therefore correct, the database they were trying to access was up and accessible but the SQL error log highlighted the real issue. Login failed for user ‘MyLogin’. Reason: Failed to open the database ‘TheDefaultdatabase’ Ahhh makes sense now because at the time that database (the default database for the login) was in the middle of a restore as part of some planned work, problem is this was not the database the user was trying to connect to at the time, the expected behavior for the login was to be able to access all of their databases regardless of any of the other databases not being available. Easy fix for this situation was to set the default database to master but could have been avoided if set correctly in the beginning, however when this login was created only one user database existed so the admin who configured the login didn’t think twice about setting the login to have a default database of their single user database, unfortunately this setting was forgotten as more databases were added to the instance. In most cases leaving it as master will be the reliable option as the master database in terms of user connectivity because if SQL is up so is the master database unless there is some other issue going on! however you may have valid reasons to want to assign a login a specific default database and that’s cool provided you consider what will happen to these logins when the database becomes unavailable. I checked BOL , unfortunately this only provides the following: DEFAULT_DATABASE =database Specifies the default database to be assigned to the login. If this option is not included, the default database is set to master Unfortunately there is no real warning there to allow you to give this setting good consideration, but it is pretty important to ask yourself the following question when creating a new user login. Does it matter if the user/login cannot access the SQL server if the default database is inaccessible when making new connections? If the answer is no then you can set to whichever database makes the most sense or leave as the default. If the answer is yes then you might want to consider master as the default database if the login is granted permission to more than one database on the instance because when the default database becomes inaccessible i.e Recovery pending suspect offline restoring possible even single user The login/user will lose access to SQL server when they try and make a new connection Thanks for reading! The post SQL Server login – default database and failed logins 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 broaden your perspective: read a different paper, magazine, or site. Many of us tend to get caught in bubbles of information. Google/Bing do this, bringing us certain results. We have a set group of people on social media, and often we read the same sites for news, information, fun, etc. The US election took place recently, and it was a contentious one. Like many, I was entranced with the process, the outcome, and the way the event unfolded. I have some sites for news, and a subscription, but I decided to try something different since I was hoping for other views. In particular, I gave CNN a try at CNN.com. I haven’t been thrilled with their television program for years, as I think they struggle to find new and interesting things to day and still fill 24 hours. However, I saw some ratings about how people around the world view the site, and it’s fairly neutral. I also grabbed a new book, Dirt, after hearing someone talk about it on NPR. It’s a book by a writer that moves to France to learn French cooking. A little out of my area, and I struggled to get into this book, but eventually it turned a corner, and I enjoyed hearing about how the writer blends cooking, chefs, ingredients, and life in a foreign country. The post Daily Coping 27 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 tell someone how you’re grateful for them in your life. It’s Thanksgiving, a day traditionally in the US where we give some thanks. Lots of people do this all month, picking something each day. My family does this at dinner, each of us giving a few things we’ve been thankful for this year. However, this is something you ought to do regularly with those in your life for whom you are grateful. Partners, kids, parents, boss, employee, etc. Remember to appreciate things, and people, in your life. I do try to let some people know that I an grateful, but not enough. I’d hope I remember to do this monthly, if not more often. There are a lot of people I am grateful for. In this case, for today, I’m remembering to tell me wife how grateful I am to have her in my life, for all the help, support, love, and joy she brings. The post Daily Coping 26 Nov 2020 appeared first on SQLServerCentral.

Being in the cloud does have many benefits, from lower administration to fast scaling but another “side effect” of operating in Azure SQL Database is the cloud first nature of changes. By this I basically mean new features always get … Continue reading ? The post Azure SQL Database – Greatest and Least TSQL appeared first on SQLServerCentral.

A few weeks ago, I began answering every single T-SQL Tuesday from the beginning. This week it’s the fifth entry, and on April 5th, 2010, Aaron Nelson invited us to write about reporting. You can visit the previous entries here: T-SQL Tuesday #001 – Date/Time Tricks T-SQL Tuesday #002 – A Puzzling Situation T-SQL Tuesday-> Continue reading T-SQL Tuesday Retrospective #005: Reporting The post T-SQL Tuesday Retrospective #005: Reporting appeared first on Born SQL. The post T-SQL Tuesday Retrospective #005: Reporting 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 connect with someone from a different generation. I used to always view a different generation as an older one. Now as I age, with more life behind me than in front, I tend to think about younger generations more. We did some speed networking recently at work, as a way to connect and meet other people. I ended up with people I know in two of the three sessions, but in the third, I ran into a former intern that was hired back during the pandemic. It was a neat 10 minute conversation, hearing some of his experiences on how things are different, his experience and impressions of working with his team inside the company. I enjoyed it, and it brought me back to my first job after college, and how different the experience was, both in life and at work. I don’t know if he enjoyed it as I did, but I really appreciated the opportunity and perspective. I hope I get to see him again, and also get to do more speed networking. The post Daily Coping 25 Nov 2020 appeared first on SQLServerCentral.

You can register now, but on Dec 3, I have a webinar with Microsoft on database devops and Azure. This is a joint effort with Redgate, so I’ll be showing some of our tools. In the webinar, which I recorded last week, I walk through the idea of Database DevOps in general, showing how we evolve our database development to incorporate automation, use Azure DevOps, and deploy code to an Azure database. Register today, and I’ll be ready for questions on Dec 3. The post Database DevOps Deployment to Azure–Webinar appeared first on SQLServerCentral.

This week we are going to launch a blog series geared towards folks that are new to PowerShell. The growing popularity of automation is seeing people getting started with the PowerShell scripting language. The Start-Transcript is a built-in command that allows you to quickly build a log of actions being taken by your script. There are other ways to build logs, but for beginners using the commands that are available to you is pretty easy. Getting Started Using the command is easy to get started and incorporate into your process. The example below will show you how to use the command and see the output that is captured. Start-Transcript -Path "C:LoggingDemo_Logging.log" Get-Process Stop-Transcript   Here you can see in the log that was created, it captures run time information and then starts tracking commands that are executed. With this simple example, you can see how beneficial this option can be for tracking and troubleshooting execution issues. Additional Features There are a few parameters that come along with this command that allow you to make your logging more scalable. NoClobber = This parameter allows the command to not overwrite the log file if you are using the same name Append = This parameter will add anything new to the log file that is executed each time your script runs References Start-Transcript on Microsoft Docs     The post [PowerShell Newbie] Using Start-Transcript appeared first on GarryBargsley.com. The post [PowerShell Newbie] Using Start-Transcript appeared first on SQLServerCentral.