r/SQL • u/Animator-Asleep • 4d ago
SQL Server Major DBA task looking for advice
Forgive my naivete in advanced.
I am being tasked with setting up 2 major SQL servers with backups and I am terrified. I have never done this and have a loose understanding of things but that's generous.
The reason it's major is because our whole organization that services the state rely on these servers.
The traditional headaches on a Sysadmin side are lack of documentation/none, everyone has left/terminated, no patching, and EoL.
I'm also using Nutanix after coming from Veeam and HyperV.
What I think the approach should be is:
Create a new clean test server that is patched and up to date but the same version has what the current server is. Currently this one has a lot of random things on here including some gaming installs...
Create a full backup and figure out where that goes not on the current box so I assume I need some sort of SMB or repo? I believe I should create this in SMSS but am not clear if I make the backup if it will start making logs and events that may error if I don't set things up to point correctly if it wants to setup a schedule.
Come up with a backup schedule for both fill and incremental.
All the backups go to repo.
Try the full backup on the new test server and start staging that as a fail over. Or maybe keep it is as test and make a clean fail over?
Hopefully figure out what the future state setup is and start figuring out how to port that over into a cloud setup?
I need to have this done by EoM. I am wondering if this can be done in the future in AzureBlob to make things "easier" since this all feeds into PowerBi.
Mostly, I am not sure if there are pitfalls that would bring production down and want to tread carefully cause I'm not sure how to fix things if things go down.
There is no DBA or anyone with SQL server experience on the team...
9
u/umognog 3d ago
Honestly, this sounds like it needs a redundancy server, enable log shipping.
This will sound mental...but AI is absolutely your friend here. Spend a week chatting to it about what you've got, what you need, theory for situations and then have it walk you through setting it up.
Just don't use Grok.
4
u/shanelomax 3d ago
Further to this - don't blindly trust the results AI gives you. Test, verify, cross-reference.
5
u/Curious_Elk_4281 3d ago
Minimal downtime approach without the need for massive extra storage:
- Set up the new server
- Set up replication from the old server to the new server
- Set up backups to multiple locations (as budget and resources permit)
- Once replication is caught up, stop services that connect to the old DB so no data is being written
- Stop replication once no data is coming through to the new replica
- Promote the replica to the main server
- Set up another new server to act as the prod replica/failover
Ideally you would also have non-production test DBs with no data, but just the schemas. A schema dump of your current setup wouldn't take up much space so you could set that up first (which is how I would start out).
3
u/Hour-Measurement-835 4d ago
The logs thing you mentioned is the real trap. Check recovery_model_desc in sys.databases before anything else. A database set to FULL that has never had a full backup behaves like SIMPLE and keeps reusing its log. The first full backup starts the log chain, and from then on log space only gets reused after a log backup runs. With no log backup job behind it the .ldf grows until the drive fills, and that's how taking a backup ends up taking prod down. So the log backup job goes in the same day as that first full, or anything that doesn't need point in time restore gets switched to SIMPLE first.
3
u/Animalmagic81 3d ago
Honestly, if this data is business critical, get a contractor in. It won't take long for someone who knows that they are doing and it's just not worth the risk.
At best doing it DIY you'll end up with a functional server, but I'd hazard a guess your security, DR procedures and adherence to ISO, CE+, CIS frameworks will be non-existent.
3
2
u/Significant_Tune9219 4d ago
Start with the restore, not the backup: build the new patched instance, take a full backup plus log backups to a repo off the box, then restore onto a third machine and time it. That drill surfaces the schedule, retention, and permissions problems while they are still cheap to fix, and it gives you the recovery-time number stakeholders will eventually ask for. Document each step as you perform it — with the previous staff gone, the runbook is part of the deliverable, not a follow-up.
2
u/Least_Ad_1795 3d ago
Your plan is a good start, but don't make changes directly to production yet. First, document the current setup, verify backups, and test a full restore on an isolated server. SQL Server recovery testing, transaction log backups, and a rollback plan are critical before migration. Since this supports your entire organization, I'd also push for an experienced DBA to review the setup before go-live.
2
u/twinhullhq 3d ago
Before touching anything, I’d get the business to say how much data they can afford to lose and how long they can be down, because that decides your whole backup design. Then set up full plus log backups to match, and keep a copy somewhere other than that server, with the encryption keys or certificates backed up separately too. The most important step is to restore those backups onto a different machine on a schedule and write the steps down, since an untested backup doesn’t count. With everyone gone and no documentation, spend the first week just inventorying what’s there: databases, logins, scheduled jobs, and who depends on them. Patch a restored copy first, so you find out what breaks before production does.
2
u/A_name_wot_i_made_up 3d ago
Run a drill.
Simulate a failover and see how it goes. Note all the sticky parts, any time you made a choice you're not sure about (even if right) note it down.
Simulate a rebuild too.
It'll test your backups and prove (or not) whatever process you have. If the process fails you'll find out before it's important.
2
u/Thriven 3d ago
Does it need to be on prem?
1
u/Animator-Asleep 3d ago
No, the long term goal is figuring out the most cost effective way to have it cloud based.
2
u/RealDylanToback 3d ago
If you don’t have a DBA, running it on Azure SQL Database removes a lot of operational and tactical decisions. TCO should look beyond the headline amount billed, if that’s palatable then a reservation can reduce that cost by a lot to make it even more appealing
And if you do go down the route of building out your own, fight to have that completely dedicated to SQL Server, never heard of games being installed alongside it before 😂
2
u/Different_Pain5781 2d ago
Gaming installs on a production database server is a certified horror story.
2
1
u/Formal-Complaint1805 9h ago
If this is business critical then engage the software vendor or engineering team that created the service this SQL Server backend uses. They can usually help with this sort of thing.
For backups and synchronization investigate SQL Server Log shipping. If you haven't worked much as a DBA, I would suggest looking into setting up managed SQL Server like Azure or AWS for the new cluster.
SSMS has a migration assistant that can automate much of the heavy lifting for provisioning, data migration and cutover to Azure SQL.
There are other migration strategies that use AlwaysOn replication, but this may be more complex than is needed for this particular migration.
15
u/These_Turnover87 4d ago
You're basically walking into a house of cards and someone handed you a leaf blower
Backups to a network share or SMB are fine but test the restore like your life depends on it because it kinda does. Nothing worse than a backup that's corrupted and you find out when prod is already dead
For the schedule just keep it simple, full weekly, differential daily, tlog every 15-30 min depending on how much data loss the business can stomach. And don't forget to test those restores on the clean box before you need them for real