SQL Administration Woes
-
So in the near 5 years I've been at my current company, they've had a dedicated server for their accounting department and the software they use (Sage AccPac). They've been in need of an upgrade for years and they finally pulled the trigger on it a couple weeks ago, and the upgrade was completed today.
Prior to the upgrade, I built a new Server 2012 R2 server (as a VMware guest in our ESXi environment). One of the guys who built our network (prior to me coming on-board) told me to build it with four extra drives, configured as follows:
"Backups" (Disk 1 / 500GB)
"Logs" (Disk 2 / 100GB)
"Database" (Disk 3 / 100GB)
"Temp" (Disk 4 / 50GB)Building that was all fine and dandy, but as far as installing and configuring SQL Server, and then migrating the data from the old server to the new server, I was/am completely out of my league.
Our vendor (ADSS Global) ended up doing the upgrade for us today (which included the SQL installation and configuration), but the lady who did all the work was not a SQL Admin; she is just someone who's been trained to do the basics that specifically relate to their software. She even said at one point during the upgrade "I'm not a SQL administrator, so when I run into problems, I call our SQL engineer". Needless to say, when I told her about the drives I had setup in advance for the SQL data, she said "I don't know anything about that. I'm just going to install it like we're trained to do (everything on the
drive), and you'll have to pay $165/hour to talk to one of our SQL engineers if you need more info".OK, so after that long-winded preface, here's what I'm wondering:
- At this point, should I even bother with all those other disks that my previous SQL Admin told me to build? Even if I do keep them, at this point I don't know how to properly utilize them (at least according to the original intended purpose).
- We don't have any SQL backup software at the moment, so I'm wondering how I should go about doing the backups... And a word of warning on this particular subject: My company would be more inclined to buy a dedicated application for backing up SQL (especially since we already own Altaro for all of our VM backups). They would also be thrilled if I could find an app that's "free" to accomplish the SQL backups.
I've heard legendary tales of SysAdmins who have done SQL backups all for free, but I'm still way out of my league on this topic, so I'd like some advice if possible...
-
-
No. Splitting the disks like that is a historical oddity once you're virtualized. OBR (One Big RAID) is the way to go on the host, and then everything else is transparent to the hosted machines.
-
If you're happy with Altaro, just license their database backup client.
2A. Database backups are not at all hard to setup. The free management client from Microsoft can handle setting it up. Generally this is done by running the built in database backup to the local drive, and then backing up that flat file(s) with your normal backup utility for archiving.
-
-
@travisdh1 said in SQL Administration Woes:
- No. Splitting the disks like that is a historical oddity once you're virtualized. OBR (One Big RAID) is the way to go on the host, and then everything else is transparent to the hosted machines.
Da fuq?
TempDB grows too fast, fills up the C:\ drive, boom, you f***ed. Logs fill up the drive, need to perform a clean up by running a backup, can't because C:\ is full. Everything is on C:\ but you need to extend the drive, oops you fucked that up now you are toast. Then of course we forget about the absolute stupidity of putting all the binaries on the C:\ drive. Hope you never need to cluster.
Breaking them down per drive is what I've done for a long time and what is considered best practice even now. And given that I would never put backups on any of my SSD tiers, why the hell wouldn't I split off that? Letting TempDB grow on their own volume would prevent it from taking down the OS.
Unless of course you don't care about tiering, better disk management, and so forth.
-
@PSX_Defector said in SQL Administration Woes:
@travisdh1 said in SQL Administration Woes:
- No. Splitting the disks like that is a historical oddity once you're virtualized. OBR (One Big RAID) is the way to go on the host, and then everything else is transparent to the hosted machines.
Da fuq?
TempDB grows too fast, fills up the C:\ drive, boom, you f***ed. Logs fill up the drive, need to perform a clean up by running a backup, can't because C:\ is full. Everything is on C:\ but you need to extend the drive, oops you fucked that up now you are toast. Then of course we forget about the absolute stupidity of putting all the binaries on the C:\ drive. Hope you never need to cluster.
Point taken on that one, I don't deal with anything but tiny DBs.
Breaking them down per drive is what I've done for a long time and what is considered best practice even now. And given that I would never put backups on any of my SSD tiers, why the hell wouldn't I split off that?
Because the backup isn't the local file created, it's what goes to the normal backup client. The local file is only because most backup software can't handle creating backups of active databases.
Letting TempDB grow on their own volume would prevent it from taking down the OS.
If you're paying so little attention to your backups that you let TempDB grow out of control, I'd say the failure is not with the database in that case.
Unless of course you don't care about tiering, better disk management, and so forth.
This is all handled at the hypervisor level. If you really need tiering, just setup a different virtual disks for the VM with different priority levels.
-
@travisdh1 said in SQL Administration Woes:
@PSX_Defector said in SQL Administration Woes:
@travisdh1 said in SQL Administration Woes:
- No. Splitting the disks like that is a historical oddity once you're virtualized. OBR (One Big RAID) is the way to go on the host, and then everything else is transparent to the hosted machines.
Da fuq?
TempDB grows too fast, fills up the C:\ drive, boom, you fed. Logs fill up the drive, need to perform a clean up by running a backup, can't because C:\ is full. Everything is on C:\ but you need to extend the drive, oops you fed that up now you are toast. Then of course we forget about the absolute stupidity of putting all the binaries on the C:\ drive. Hope you never need to cluster.
Point taken on that one, I don't deal with anything but tiny DBs.
Breaking them down per drive is what I've done for a long time and what is considered best practice even now. And given that I would never put backups on any of my SSD tiers, why the hell wouldn't I split off that?
Because the backup isn't the local file created, it's what goes to the normal backup client. The local file is only because most backup software can't handle creating backups of active databases.
Letting TempDB grow on their own volume would prevent it from taking down the OS.
If you're paying so little attention to your backups that you let TempDB grow out of control, I'd say the failure is not with the database in that case.
Unless of course you don't care about tiering, better disk management, and so forth.
This is all handled at the hypervisor level. If you really need tiering, just setup a different virtual disks for the VM with different priority levels.
You obviously know very little about large database administration.
You are not even talking about the right things.
@PSX_Defector is completely correct here.
Yeah, you have hypervisor level backups, but those are not application level recovery. SQL backups to SSD tier is a waste.
-
@Shuey said in SQL Administration Woes:
So in the near 5 years I've been at my current company, they've had a dedicated server for their accounting department and the software they use (Sage AccPac). They've been in need of an upgrade for years and they finally pulled the trigger on it a couple weeks ago, and the upgrade was completed today.
Prior to the upgrade, I built a new Server 2012 R2 server (as a VMware guest in our ESXi environment). One of the guys who built our network (prior to me coming on-board) told me to build it with four extra drives, configured as follows:
"Backups" (Disk 1 / 500GB)
"Logs" (Disk 2 / 100GB)
"Database" (Disk 3 / 100GB)
"Temp" (Disk 4 / 50GB)Building that was all fine and dandy, but as far as installing and configuring SQL Server, and then migrating the data from the old server to the new server, I was/am completely out of my league.
Our vendor (ADSS Global) ended up doing the upgrade for us today (which included the SQL installation and configuration), but the lady who did all the work was not a SQL Admin; she is just someone who's been trained to do the basics that specifically relate to their software. She even said at one point during the upgrade "I'm not a SQL administrator, so when I run into problems, I call our SQL engineer". Needless to say, when I told her about the drives I had setup in advance for the SQL data, she said "I don't know anything about that. I'm just going to install it like we're trained to do (everything on the
drive), and you'll have to pay $165/hour to talk to one of our SQL engineers if you need more info".Your vendor is shit, but what you are probably stuck with.
OK, so after that long-winded preface, here's what I'm wondering:
- At this point, should I even bother with all those other disks that my previous SQL Admin told me to build? Even if I do keep them, at this point I don't know how to properly utilize them (at least according to the original intended purpose).
Yes, you absolutely want them and you want SQL fixed to use them. Pay the price. You can easily get away without it for years and never have a fail. But when it fails, you will be screwed hard.
- We don't have any SQL backup software at the moment, so I'm wondering how I should go about doing the backups... And a word of warning on this particular subject: My company would be more inclined to buy a dedicated application for backing up SQL (especially since we already own Altaro for all of our VM backups). They would also be thrilled if I could find an app that's "free" to accomplish the SQL backups.
You do not specifically needs something to do SQL backups. SQL Server can do it all natively if you know what you are doing. See previous answer about paying someone. With the partitioned drive layout, the SQL server would be making whatever schedule backup to the backup drive. these are the SQL .bak files that you can restore to get your shit.
You VM backup process would then have these backed up.
I've heard legendary tales of SysAdmins who have done SQL backups all for free, but I'm still way out of my league on this topic, so I'd like some advice if possible...
ANy good SQL DBA can setup maintenance plans to handle indexing, cleanup, backups, etc. all from within SQL server.
-
From what I can tell so far, it looks like all of the accounting data (which at this point I can only assume contains the database(s) as well) is only occupying 3.5GB (and that's nearly 10 years worth (and possibly more) of data).
As far as licensing Altaro's "database backup client", they don't have one as far as I know :-S... Some of that functionality may be included with their existing version, but last I talked to the guys from Altaro, they said they don't have a full-fledged SQL backup feature in their software.
I spoke with our previous SQL admin (Bill), as well as Sam and Jean from Altaro, and here was how the conversation went:
Me: One of our contracted IT Pros told us that we should consider going with Veeam. The major reason he said this is because he said that their software allows âinstant recoveryâ for SQL server backups (whereas Altaro does not).
Altaro: Veeam does allow granular restores of single items from an SQL database, while we donât currently have the functionality for that as of yet. It might be something that weâll introduce later on, but no ETA on that John.
Me: Bill's reply: âI would not follow the recommendation of your backup vendor as it will allow you only limited recovery of the SQL database (ie point of time based recovery). I would really recommend a backup software that uses an SQL agent along with VSS to back up the server.â
Altaro: This isnât really an issue in my opinion here John. As in, youâre going to have the restore points available for whenever you took backups. Regarding running an SQL agent along with VSS â this isnât required as VSS would be taking care of truncating the logs and ensuring that the DB is in a fully application consistent state.
Me: Bill's reply: âThat still does not answer the question if they recommend simple recovery model or not. I would confirm with them the recommend model, if they say simple recovery, then you need to look at something else.â
Altaro: Simple recovery model is definitely the way to go if youâre not backing up SQL using another software inside the VM itself for example. In order to truncate the logs, it must be configured to use a simple recovery model. The logs are there in order for you to be able to rollback in case anything happens. Whenever Altaro runs a backup it triggers a VSS job and thereby using a simple recovery model, that means that you will have a restorable backup via Altaro and the logs can be safely truncated in order to clear space being taken up by the transaction logs.
Altaro: Since you just took a backup, thereâs no need for the logs in such cases. AgainâŚthis is assuming youâre not using some other product, or SQL backup itself in order to backup SQL from within the VM. In which case you can utilise a Full Backup Recovery Model.
Maybe I'm worry about nothing on both fronts (the disk provisioning AND the backups)??...
@JaredBusch said in SQL Administration Woes:
Your vendor is shit, but what you are probably stuck with.
OK, so after that long-winded preface, here's what I'm wondering:
- At this point, should I even bother with all those other disks that my previous SQL Admin told me to build? Even if I do keep them, at this point I don't know how to properly utilize them (at least according to the original intended purpose).
Yes, you absolutely want them and you want SQL fixed to use them. Pay the price. You can easily get away without it for years and never have a fail. But when it fails, you will be screwed hard.
- We don't have any SQL backup software at the moment, so I'm wondering how I should go about doing the backups... And a word of warning on this particular subject: My company would be more inclined to buy a dedicated application for backing up SQL (especially since we already own Altaro for all of our VM backups). They would also be thrilled if I could find an app that's "free" to accomplish the SQL backups.
You do not specifically need something to do SQL backups. SQL Server can do it all natively if you know what you are doing. See previous answer about paying someone. With the partitioned drive layout, the SQL server would be making whatever schedule backup to the backup drive. these are the SQL .bak files that you can restore to get your shit.
You VM backup process would then have these backed up.
I've heard legendary tales of SysAdmins who have done SQL backups all for free, but I'm still way out of my league on this topic, so I'd like some advice if possible...
ANy good SQL DBA can setup maintenance plans to handle indexing, cleanup, backups, etc. all from within SQL server.
Any good SQL DBA CAN do all that, but I'm not a SQL DBA, lol. So I either gotta pay somebody to take care of it, or I gotta hope I can learn and implement it while I'm in the interim (and hope it doesn't all go south during that time) :-S.
-
It's still a best practice to set up those extra drives, especially if you have it virtualized.
For example, on your hypervisor, you might have an SSD RAID and an HDD RAID. You would put the "Database" and "Logs" virtual disks on the SSD RAID, and the "Backups" and "Temp" virtual disks on the HDD RAID.
There's no reason to put backups on the more expensive storage tier, or temp files. What matters is the database and logging. Generally, you should follow vendor best practices no matter what you see on forums... If Microsoft and your Sage software vendors say to set it up a certain way so they will properly support you, than that's what you do. (generally)
-
@Tim_G said in SQL Administration Woes:
It's still a best practice to set up those extra drives, especially if you have it virtualized.
For example, on your hypervisor, you might have an SSD RAID and an HDD RAID. You would put the "Database" and "Logs" virtual disks on the SSD RAID, and the "Backups" and "Temp" virtual disks on the HDD RAID.
There's no reason to put backups on the more expensive storage tier, or temp files. What matters is the database and logging. Generally, you should follow vendor best practices no matter what you see on forums... If Microsoft and your Sage software vendors say to set it up a certain way so they will properly support you, than that's what you do. (generally)
Unfortunately, the "support" person who setup SQL for us was just following a script. When I told her about the drives that I had provisioned, she said "I don't know anything about that" and refused to install to one of them. She also ran into an issue when installing and said she was going to uninstall the software and re-install it! During the re-install, she came across an error and had no idea what to do. I ended up Googling the error and found a solution and implemented it myself while she watched O_o... So as far as best-practice goes, our vendor seems to have no clue what that is.
As far as the types of storage we have, I work for a company who still lives in the dark ages in many regards. So on that note, our "storage" in our ESXi hosts is just some old 10K rpm SAS drives, and our storage arrays where we keep our backups are all running SATA drives! (all our storage arrays are old MSA 60s).
-
@travisdh1 said in SQL Administration Woes:
Breaking them down per drive is what I've done for a long time and what is considered best practice even now. And given that I would never put backups on any of my SSD tiers, why the hell wouldn't I split off that?
Because the backup isn't the local file created, it's what goes to the normal backup client. The local file is only because most backup software can't handle creating backups of active databases.
Da fuq?
Even NT Backup can handle SQL backups. Any modern VSS aware backup product can handle SQL backups.
Which doesn't even touch the fact that you are filling up the drive with junk that can easily be put off the machine, and really should be because WTF?
Unless of course you don't care about tiering, better disk management, and so forth.
This is all handled at the hypervisor level. If you really need tiering, just setup a different virtual disks for the VM with different priority levels.
But you just put everything onto one VHD. There is no management, you have to put it in the fastest tier because of SQL binaries and the MDF/LDF files.
-
@Shuey said in SQL Administration Woes:
I've heard legendary tales of SysAdmins who have done SQL backups all for free, but I'm still way out of my league on this topic, so I'd like some advice if possible...
ANy good SQL DBA can setup maintenance plans to handle indexing, cleanup, backups, etc. all from within SQL server.
Any good SQL DBA CAN do all that, but I'm not a SQL DBA, lol. So I either gotta pay somebody to take care of it, or I gotta hope I can learn and implement it while I'm in the interim (and hope it doesn't all go south during that time) :-S.
https://msdn.microsoft.com/en-us/library/ms187510.aspx
USE <<DATABASE>>;
GO
BACKUP DATABASE <<DATABASE>>
TO DISK = '<<PATH.BAK>>'
WITH FORMAT,
MEDIANAME = 'Full_<<DATABASE>>',
NAME = 'Full Backup';
GORun as SQL job, done.
That will be $500 for DBA work.
-
@PSX_Defector said in SQL Administration Woes:
@travisdh1 said in SQL Administration Woes:
- No. Splitting the disks like that is a historical oddity once you're virtualized. OBR (One Big RAID) is the way to go on the host, and then everything else is transparent to the hosted machines.
Da fuq?
TempDB grows too fast, fills up the C:\ drive, boom, you fucked. Logs fill up the drive, need to perform a clean up by running a backup, can't because C:\ is full. Everything is on C:\ but you need to extend the drive, oops you f***ed that up now you are toast. Then of course we forget about the absolute stupidity of putting all the binaries on the C:\ drive. Hope you never need to cluster.
Breaking them down per drive is what I've done for a long time and what is considered best practice even now. And given that I would never put backups on any of my SSD tiers, why the hell wouldn't I split off that? Letting TempDB grow on their own volume would prevent it from taking down the OS.
Unless of course you don't care about tiering, better disk management, and so forth.
The confusion, I think, comes from calling partitions disks. It's not four disks, four partitions. Which mostly makes sense. But four totally separate disks would be crazy.
-
@Tim_G said in SQL Administration Woes:
There's no reason to put backups on the more expensive storage tier, or temp files. What matters is the database and logging.
Actually temp matters a lot for performance. It's where query scratch work is done and can have insanely high I/O demands that the rest of the system has to wait on. It's actually the portion that some people consider moving to RAM disk, it is so I/O sensitive.
-
@PSX_Defector Thanks for the link and info!
-
@scottalanmiller said in SQL Administration Woes:
The confusion, I think, comes from calling partitions disks. It's not four disks, four partitions. Which mostly makes sense. But four totally separate disks would be crazy.
I don't have it setup as four partitions - I created four separate vmdks for this server (besides the fifth disk which is disk 0; the OS vmdk).
-
@Shuey said in SQL Administration Woes:
@scottalanmiller said in SQL Administration Woes:
The confusion, I think, comes from calling partitions disks. It's not four disks, four partitions. Which mostly makes sense. But four totally separate disks would be crazy.
I don't have it setup as four partitions - I created four separate vmdks for this server (besides the fifth disk which is disk 0; the OS vmdk).
VMDKs are "volumes" in the modern sense. That's an LVM layer. A partition, volume, LUN, array... they are all "disks" but we don't call them that as it is confusing like we mean separate physical devices. Making a separate VMDK for each is identical to making an LVM volume for each... just managed at the VMware layer instead of inside of the OS.
-
@Shuey said in SQL Administration Woes:
@scottalanmiller said in SQL Administration Woes:
The confusion, I think, comes from calling partitions disks. It's not four disks, four partitions. Which mostly makes sense. But four totally separate disks would be crazy.
I don't have it setup as four partitions - I created four separate vmdks for this server (besides the fifth disk which is disk 0; the OS vmdk).
This article can help with what I was talking about...
http://www.smbitjournal.com/2016/06/what-is-drive-appearance/
-
@scottalanmiller said in SQL Administration Woes:
VMDKs are "volumes" in the modern sense. That's an LVM layer. A partition, volume, LUN, array... they are all "disks" but we don't call them that as it is confusing like we mean separate physical devices. Making a separate VMDK for each is identical to making an LVM volume for each... just managed at the VMware layer instead of inside of the OS.
Ah ha! moment Thanks Scott! That's at least the third time you've taught me something cool that should've been more obvious but I totally missed it until you shed new light on it for me! : )
-
How many users use the software and connect to the database? What's the load going to be like?
-
@Tim_G said in SQL Administration Woes:
How many users use the software and connect to the database? What's the load going to be like?
We're licensed for five total, but only four people will be connected to it every day (until/unless they end up hiring a 5th member to join the accounting department). I'm not sure what kind of a load the server will have, but I can say that the previous server (also a guest VM in our ESXi environment) only had 4 vCPUs and 8GB of RAM dedicated to it, and a 130GB vmdk.
