Whilst preparing a simple dataset to with a stacked bar chart in PowerBI l included both the month number and name. The dataset is shown below
The data set was created in PowerBI desktop, the next step was to plot the data on a clustered column chart. The month name was the value l wanted to appear on the axis, there was a little challenge with the way data was being displayed. The month names where being displayed in alphabetical order (see Figure 2)
Figure 2 - Power BI chart after placing data on the canvas
What l wanted was the month names to be sorted according to month numbers so l knew l had missed something. First step was to go to the data tab to have a look at the data. So I licked on the the dataset and selected the 'Modeling' tab (see Figure 3).
Figure 3 - Data view 'Modeling' tab selected the 'Month_Name' column selected
Everything looked the way l expected then with the 'Month_Name' column selected l clicked on the 'Sort By Column'. This was sorted by 'Month_Name' so it would be sorted alphabetically, so l changed it to sort by the 'Month_Number' see Figure 4.
Figure 4 - The first figure shows the default setting for the 'Month_Name' column, the second figure shows the amended selection to sort the 'Month_Name'Column by the 'Month_Number' column
Then l returned to the report view to review the change see Figure 5
Figure 5 - The clustered bar chart report showing the Month Names sorted in the order of the 'Month_Number' column
Success the months where ordering as l wanted them, next I'm off to resolve my next challenge, of which there is never a shortage.
For various reasons which l have now forgotten, l set up transactional replication for some clients. The result of this is l am the caretaker of transactional replication for two of our clients, what l lucky person l am !
T-SQL code used to check Transaction replication
Once l got the process up and running (a very, very long and stressful story). At that point l realised that I would have to monitor these processes. Following some goggling this article was found with some TSQL written by SQLSoldier here. This worked for me and l used this to monitor replication by running the script and checking the results manually.
Our SysAdmin uses a tool called nagios to monitor out IT estate. So they suggested that a script could be written to monitor the replication process and send an alert if anything needed to be looked at. This sounded like an excellent idea, how to do it? The approach that was arrived at, involved using a PowerShell script which would run a SQL query examine the results then respond to Nagios, with the following values
Next we decided to use PowerShell to return the results. Following some goggling we found this page http://www.madeiradata.com/cross-server-replication-health-check-using-powershell/ which runs a SQL script and returned a data set. First challenge was the TSQL script from SQLSoldier was rather long for my first powershell script, l wanted some thing smaller. So l created a stored procedure based on the script, and placed it in my Distribution database on the replicated database server. Doing this had a two reasons, first less TSQL in the PowerShell script, second changing one of the parameters meant it returns different amounts of data. The stored procedure takes the following parameters ;
When considering this error message it was primarily to give some guidance as to what might be happening with the replication process. It is not intended to give any guidance on the underlying reason that is causing the issue. All that is required is that the Nagios process shows that there is something wrong. What ever the reason it requires some form of human intervention. Once an error condition has been detected then the issue will be handed to me to resolve. At least now l do not have to check the process periodically, now l just have to wait for a message from our sysadmin.
Since this one caught me out and a few other people it seemed like a good idea to blog about this.
Sometime ago the Azure portal got a make over, which l have say make the portal look very pretty. Whilst setting up a proof of concept for PowerBI l thought it was also a good time to look at setting up an Azure SQL database. The last time l did this was in the previous portal. So l duly set up the database, one of the last steps was to allow the IP address from my work organisation access to the database. So l goggled and found this page - https://azure.microsoft.com/en-gb/documentation/articles/sql-database-configure-firewall-settings/
The Wrong Way
Open the new style Azure Portal
Click on browse (1)
From the list of blades click once on SQL databases (Figure 1)
Figure 1 - Opening the SQL databases blade
Click on the 'SQL databases' blade this will open a list of the all the SQL databases that are in your account (Figure 2)
Figure 2 - List of SQL databases showing area where to click to show settings
Clicking on the ellipsis will cause a new menu window to appear (Figure 3)
Figure 3 - Clicking on the ellipsis to get the settings link
Click once on the settings link this will then open the settings window (Figure 4)
Open the new style Azure Portal
Click on browse (1)
From the list of blades click once on SQL servers (Figure 5)
Figure 5 - Opening the SQL servers blade
Click on the SQL Servers blade and this will open a list of all the SQL servers in your account (Figure 6)
Figure 6 - List of SQL servers
Move the mouse over the row which is the SQL server that require to see the settings for. Click once with left hand mouse button the selected SQL server in Figure 6 this is SQL server 'kjsgbci03i'. This will then open Settings window on the far right hand side (see Figure 7)
Figure 7 - Settings page for SQL server
Click once on the firewall row and this will open the 'Firewall settings' window (see Figure 8)
Figure 8 - Firewall settings window
Splitting the settings for SQL servers and SQL databases, does make sense, the same logic is used within SSMS. That said in the old style portal, the link to the page for setting allowed IP Addresses is under 'SQL databases' (see Figure 9). Hence why l must have looked at SQL databases blade first. At least that's my excuse and l am sticking to it ;->
Figure 9 - SQL server page with link to Manage allowed IP addresses (Firewall rules) in the old style Auzure portal.