Anitha Telkar

🔥 Real-Time DBA Interview Task: How to Move System Databases in SQL Server (With Examples) 🔥 As a SQL DBA, one of the critical real-time tasks is moving system databases — especially when your system drive is running out of space or storage needs to be reorganized. Here’s the step-by-step approach 👇 ⚙️ 1️⃣ Move tempdb:- ----------------------------- ✅ Easiest and safest to move ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SQLData\tempdb.mdf'); ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLLogs\templog.ldf'); 🔸 Restart SQL Service 🔸 Verify with: SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('tempdb'); ⚙️ 2️⃣ Move model and msdb:- ----------------------------------------- ✅ Need SQL restart and manual file move ALTER DATABASE model MODIFY FILE (NAME = modeldev, FILENAME = 'D:\SQLData\model.mdf'); ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = 'E:\SQLLogs\modellog.ldf'); ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData, FILENAME = 'D:\SQLData\MSDBData.mdf'); ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog, FILENAME = 'E:\SQLLogs\MSDBLog.ldf'); 🔸 Stop SQL Service 🔸 Move files manually 🔸 Start SQL again ⚙️ 3️⃣ Move master database :- ----------------------------------------- ⚠️ Critical step — SQL won’t start if incorrect 1️⃣ Stop SQL Server 2️⃣ Copy master.mdf and mastlog.ldf to new location 3️⃣ Update startup parameters in SQL Server Configuration Manager -dD:\SQLData\master.mdf -lE:\SQLLogs\mastlog.ldf -eE:\SQLLogs\ERRORLOG 4️⃣ Start SQL Server and verify Q) Do we need to change locatin of error file -e? hashtag #comment down your views 💡 Pro Tip: Always take a backup of system databases before moving and note current paths. A mistake in master DB path can prevent SQL from starting. #SQLServer #MSSQLDBA #SQLServerTips #DatabaseAdministration #SQLServerCommunity #DBAInterviewPrep #DataPlatform #SQLPerformance #RealTimeDBATasks #LearnSQLServer

13

I believe in supporting the DBA community. That’s why I am officially offering Ms SQL Server monitoring and backup toolset—developed over 10 years—for free usage. My goal is to help organizations simplify complex tasks like selective object scripting and handover management without the burden of high licensing costs. Please feel free to implement this in your organization. If you find it helpful, a like or share would mean the world to me so we can reach more DBAs and developers in need! #SQL #DataEngineering #sqlmonitor #MicrosoftSQLServer #FreeTools https://lnkd.in/gf8SXwpd https://lnkd.in/gf8SXwpd

74

6 Comments

SQL Server CDC was never built for the high-volume, low-latency demands of today’s cloud-first architectures. If your pipelines slow down as change rates rise, you’re not alone. Native CDC depends on SQL Server Agent jobs and shadow tables, which add I/O, CPU load, and real production risk at scale. Modern teams need a log-based approach that delivers real-time data without destabilizing source systems. That's where Striim comes in. Striim’s MSJET captures SQL Server CDC faster, with lower impact, and with built-in schema handling and governance. Explore the full guide from Srdan Dvanajscak: 🔗 https://lnkd.in/e3bcX7zU

10

If your team doesn’t have a dedicated DBA, you’re not alone, and you’re not out of options. Creating a culture of observability can help your team spot issues earlier, ask smarter questions, and avoid being blindsided when something breaks. In this post, I walk through five ways to build that culture in your SQL Server environment, starting today. What does observability look like in your team? https://lnkd.in/gGk6JkkW

10

2 Comments

🔧 How to Restore the Master Database in SQL Server (Real Scenario + Simple Guide) Restoring the master database is rare—but when it’s needed, it’s usually critical and time-sensitive. In a recent production case, after a change activity, I noticed server-level configurations and metadata inconsistencies. Some logins and settings were not working as expected. After evaluating options, restoring the master database from a valid backup was the quickest and safest way to stabilize the instance. 🚨 When & Why is it needed? - System database corruption - Missing/broken logins, linked servers, or configurations - Failed patching or upgrade issues - Disaster recovery scenarios ⚠️ Before You Start - Stop SQL Server services - Disable SQL Server Agent 🛠️ Steps to Restore 1️⃣ Navigate to SQL Server Binn folder cd "C:\Program Files\Microsoft SQL Server\<Instance>\MSSQL\Binn" 2️⃣ Start SQL Server in single-user mode sqlservr -c -f -s <InstanceName> -mSQLCMD 3️⃣ Open a new command prompt and connect sqlcmd -S <ServerName>\<InstanceName> -E -d master 4️⃣ Restore the master database RESTORE DATABASE master FROM DISK = '<BackupPath>\master_full.bak' WITH REPLACE; GO 🔁 After Restore - SQL Server shuts down automatically - Start SQL Server service - Enable and start SQL Server Agent 💡 Quick Tips - Use "-mSQLCMD" to restrict connections - Verify the backup path before starting - Only one connection is allowed in single-user mode 👉 Key Takeaway: Master database restore is a high-impact operation, but with the right backup and steps, it can quickly recover critical instance-level issues. If interested please follow below link. https://lnkd.in/gQjDka4e #SQLServer #DatabaseArchitecture #DBA hashtag #DataManagement #TechInsights #SQLPerformance #CloudData #TechCommunity #AlwaysOnAvailabilityGroups #DatabaseSynchronization #SynchronousCommit #TransactionManagement #LogManagement #DatabasePerformance #HighAvailability #DisasterRecovery #DatabaseAdministration #SQLServer #DatabaseConsistency #Durability #ACIDProperties #AvailabilityGroups #DatabaseReplication #SQLServer #DatabaseArchitecture #DBA #DataManagement #TechInsights #SQLPerformance #CloudData #AlwaysOnAvailabilityGroups #DatabaseSynchronization #SynchronousCommit #TransactionManagement #LogManagement #DatabasePerformance #HighAvailability #DisasterRecovery #DatabaseAdministration #SQLServer #DatabaseConsistency #Durability #ACIDProperties #AvailabilityGroups #DatabaseReplication

111

4 Comments

In SQL Server, there are undocumented objects, an internal stored procedures and functions that Microsoft doesn’t officially support. They can change or disappear after an update, so while they’re handy for quick scripts, you shouldn’t use them in production code. One of my favorites is sp_msforeachdb. It runs in a loop, and the number of iterations equals the number of databases on your server. In each loop, the ? placeholder is replaced with the database name (The question mark is the default placeholder. If you want to change it, you can use the procedure's second parameter and specify your own placeholder). For example, the query below checks every database and returns the ones that contain a procedure called MyProc: exec sp_msforeachdb 'use [?]; if object_id(''MyProc'') is not null select ''?'''; This comes handy when developers ask me to take a look at a table or a stored procedure, but forget to send me the database's name. It is faster for me to use sp_msforeachdb in order to find where the object exists than sending them email and wait for an answer.  The procedure actually have 6 parameters (3 commands that will run in the loop, one place holder, one command that will run before the loop and one after the loop), but I have to admit that I've never used the other parameters.

24

6 Comments

That’s what makes you a stronger leader ................. For years, I've been leading groups of database professionals. Over time, I’ve worked with all sorts of people—sometimes as a peer, sometimes as a leader. I’ve held roles where I was basically the software or IT deputy in large companies, dealing with a wide variety of people and situations. What I’ve learned over these years is that many folks think of themselves as honest or upright, but from others’ perspectives, they might not be seen that way. Someone might believe they’re responsible, but if they’re not actually showing fairness or empathy, others won’t see them that way. As a leader, the biggest lesson is that integrity and honesty truly are the best policy. When you’re transparent and fair, people notice. And when you move from being just a colleague to being someone’s leader, you suddenly have the responsibility to ensure fairness, to recognize people’s efforts, to be patient and empathetic. They no longer just see you as a peer—they expect you to uphold those values and to handle their needs with a sense of justice and gratitude. In other words, as a leader, you have to be not just patient and resilient, but also deeply empathetic and fair. You need to control your emotions, show a high level of emotional intelligence, and understand that your role is to create an environment where all these values—integrity, respect, responsibility—are actually lived out. That’s what makes you a stronger leader.

21

The Myth of “Secure by Default” 🛡️ SQL Server might feel safe out of the box — but it isn’t. I once walked into an environment where the DBA swore, “We haven’t touched the defaults, so it must be secure.” Guess what? 🚨 sa account active (with a weak password) 🚨 No encryption on backups 🚨 TempDB wide open NB: Defaults are designed for ease of setup, not for production security. Here’s what you should never assume: ❌ “If it installed, it’s secure” ❌ “Defaults are best practice” ❌ “Cloud-hosted means pre-secured” Instead: ✅ Harden from OS to object level (layered defense) ✅ Rename/disable risky accounts ✅ Encrypt everything that matters ✅ Audit regularly Lesson: Security is not a checkbox, it’s a discipline. 💬 Have you ever found a shocking default setting in a production database? Drop it below 👇🏾 #SQLServer #DataSecurity #DatabaseAdministration #InfoSec #Cybersecurity #SQLTips #IsidoreFonyuy

Expanding Into Data Engineering and AI the Right Way My foundation has been built over many years in SQL Server, database architecture, operational reliability, and solving hard enterprise data problems. What has become increasingly important to me is how that foundation connects to the future: data engineering, automation, analytics, and AI enablement. I am especially interested in the practical side of this work. Not hype. Not buzzwords. Real solutions. That means better data pipelines, stronger data foundations, improved automation, more useful analytics, and responsible AI adoption that is built on structure, governance, and technical discipline. I believe organizations get the most value from AI when the underlying data and platform work are taken seriously. That is one reason this next chapter is so interesting to me. It is not a departure from my past experience. It is an extension of it. The tools are evolving, but the need for sound engineering judgment is not going away. #DataEngineering #AI #Automation #SQLServer #DataPlatforms #Analytics #AIGovernance

8

Chief Architect Network While the week has gone excited with #chatgpt5.0, looking at an equally hot topic #DSPM( Data Security & Posture Management). This is an area where #EnterpriseArchitecture #CIO #CTO #CDO need to seriously invest in this topic and can truly talk #DatalifecycleManagement and #DataGovernance at #Enterprise level.

112

17 Comments

Database upgrades don't have to be a nightmare. Learn how small businesses navigated SQL Server compatibility challenges in the past, and why having the right technology partner makes all the difference. https://zurl.co/Mu2tG

1

Your SQL Server process should almost never spawn a command shell. If "sqlservr.exe" launches "cmd.exe" or "powershell.exe", it is a high-fidelity indicator that the database has been compromised. This is typically achieved via SQL Injection and enabling the "xp_cmdshell" stored procedure. This KQL query monitors the process tree for this specific parent-child relationship. #SQLInjection #BlueTeam #DatabaseSecurity #KQL #MicrosoftSentinel

5

🚀 One SQL Server feature that can turn hours of downtime into seconds: Accelerated Database Recovery (ADR) Every DBA has faced this situation: A long transaction gets cancelled… and SQL Server spends hours rolling it back. With Accelerated Database Recovery (ADR) in Microsoft SQL Server 2019 and later, rollback no longer depends on how long the transaction ran. Instead of physically undoing millions of row changes, ADR uses logical revert with a Persisted Version Store, allowing SQL Server to recover almost instantly. 💡 What this means in practice: • Faster database restart after crashes • Near-instant rollback of large transactions • Predictable recovery times • Better availability for mission-critical systems For large workloads, ADR can literally change “hours of recovery” → “seconds to minutes.” ⚠️ If you're running SQL Server on Kubernetes, ADR becomes even more important than on physical servers. On traditional physical or VM-based deployments, database servers are long-lived and rarely restarted. But in Kubernetes, restarts are part of the normal operating model: • Pods can restart during node maintenance • Containers may be rescheduled to different nodes • Auto-scaling and upgrades can trigger restarts • Infrastructure failures can recreate pods automatically Without ADR, a pod restart during a large transaction can leave the database stuck in long crash recovery and rollback. With ADR enabled, the database comes online quickly, while rollback happens logically in the background. In cloud-native environments where restarts are expected, fast recovery becomes critical for availability. That’s why for SQL Server running on Kubernetes, ADR is often not just a performance feature — it’s a reliability requirement. #SQLServer #Kubernetes #DatabaseAdministration #DBA #DataEngineering #ADR

32

2 Comments