Continuing on my journey up the Azure Automation mountain, I recently completed a simple AZ PowerShell script that takes several input parameters and scales UP or scales DOWN a given AzureSQL database instance depending on what time of day it is.
Before I go any further, if you are just getting started in Azure Automation, I wrote another article here which may help you avoid some of the headaches I ran into as part of my initial foray:
Azure Automation – Powershell – Getting it Working – Authentication the “Easy” Way -and- Ditching AzureRM
Azure has excellent auto-scaling parameters built-in for scaling App Services horizontally (i.e. “out” and “in”… adding or decreasing instance count). However for vertical scaling of AzureSQL services, you are pretty much left to your own devices.
I have an “okay” amount of experience using AZ Powershell to script and get things done in my Azure Subscriptions and with that in mind and the task set in front of me of auto-scaling our AzureSQL databases based on time of day (scale up before the busy hours, scale down when the quiet hours start) I turned to Azure Automation to get the work done.
Being an IT professional of several years, I did what we all do… I turned to Google and searched for scripts I could just steal, modify, and use (I am nodding at you right now…). However, if your want to use “AZ” Powershell instead of the deprecated “AzureRM”… I think the term is… “forgetaboutit.” The templates and examples that I found were either based on Powershell “Workflows” which I didn’t want to mess with and/or used the old AzureRM Powershell syntax. So I then turned to… Powershell ISE… and started writing.
I haven’t posted in ages due to being generally slammed with work but this little piece I threw together was too good to forget about so I wanted to put it down.
If you work with a larger owncloud deployment and have a lot of users and allow file sharing, you may be curious to occasionally take a look at how many shares there are, who owns them, who they are shared with. This isn’t easy to get from the Web GUI but via the command line and mysql it isn’t bad at all.
So, login to mysql on the command line and then use your owncloud database; ie. (if your db name is “owncloud”)
Then run the following:
select id, share_with, uid_owner, item_type, file_target from oc_share
INTO OUTFILE '/var/lib/mysql-files/shares.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Exit mysql command line and go to /var/lib/mysql-files and you will find a nicely formatted CSV file with a list of all shares and who they are shared with. The columns from left to right…
ID, Who it is shared with (one line per person/group), the person that owns the share, whether or not it is a file or folder, the name/location of the file/folder that is shared
Without going into great technical detail (which on this topic I couldn’t do anyway), it seems after much reading that it is a recommended practice to spread you SQL Server TempDB across multiple files based on how many cores (or perhaps threads) your processor has.
To keep things simple, let’s say I have a 4 core CPU and no hyper-threading (I am not sure if the rule applies to physical cores or to threads), this means I want to split my TempDB up into four different files. However there is one caveat, you should only do this if you actually have four separate physical drives. Not separate files on one drive, not even separate files on separate partitions… this is only beneficial if you actually have separate physical drives based on what I read.
I have been fiddling about with setting up a SQL Server 2012 Failover cluster using an Equallogic SAN. After a whole lot of digging about I found two different posts on two different sites which got me about 90% of the way there. However there were some key “gotcha’s” and other information that was missing in both cases and I wanted to document those here in addition to referencing the articles I followed for my setup.
BTW – Just my 2-cents, but setting up clustering is complicated… especially when you throw SQL in the mix. It isn’t bad once you have done it a few times (I tested again, and again, and again in a virtual environment) but there are honestly like 50+ considerations to take into account to ensure everything goes correctly.
I am assuming if you are here you already have a general understanding of failover clustering, know what you are wanting to do and why. This article also doesn’t really cover all aspects of high-availability. I don’t discuss how your SAN(s) should be networked for example. I do touch on a few items though that fall in this area. This isn’t meant to be comprehensive and a lot of it is just for personal reference.
So here are some tips if this is your first go around. These are in NO particular order or grouping (this is very “stream of thought”) so I would suggest reading this from start to finish at least once rather than referencing it as you are going through your setup.
SQL instances running a lot of databases can get a bit confusing as to what is kept where. Especially if said instance was setup by someone else in times prior. To that end, there is a very handy query you can run in SSMS to quickly return the on-disk file locations of all SQL data files. You can use this query exactly as-is with no modification:
SELECT name, physical_name AS current_file_location
This will output a nicely formatted table including filename and folder path, including the Master database and Temp Database files.