DBA Career Levels

Starting from the first day the career levels could be something like this.

Trainee

Assist on tasks and issues. Can be given trivial tasks with some training. Mentor assigned to introduce to profession.

Junior (Level I)

The Junior DBA resolves issues while developing professional skills. Handle assigned tasks. Check test results. Will mostly work individual, but will have a close colleage as mentor (Sensei).

Usually with up to two years of experience. But the period length is not defining - it is the capabilities.

Specialist (Level II)

Works on troubleshooting, review and test. Does now have solid fundamental skills.

Between two to five years of experience. Unusally depending on the organisation, capabilities and the mentor (Sensei).

Senior (Level III)

Now works as individual contributor with deep understandings. Ownership of technical components like SQL Server components. Key resource in problem analysis. Give process recommendations. Comprehensive knowledge of practices and organization structures, both formal and informal (goat paths).

Develop and present proposals, also to customers. Negotiate project and product technical terms and requirements with vendors and customers. Participate in pre-sales. Professional mentor (Sensei). Key business knowledge. Recognized for technical skills on international level with influence on wide organizational level.

Typically more than five years of experience. Often more than ten years.

Principal

Designing and presenting technical stratigies. Setting department goals and creating implementation plans. Setting and manging engineering budgets.  Develop and implement proces improvements.

Provide expert advise. People management in research and design. Professional and career mentor (Sensei) but not as primary priority. Deep business knowledge.

Now it is no longer a matter of years but more of capabilities and ambitions. Usually has at least a bachelor degree.

Chief

Deep technical understanding. Manage development through the entire program with interpretation of central demands. Take major and difficult technical decisions. No people management and no yearly budget. Only funding on activities from senior management. Direct contact on a regular basis with senior management.

Very rare title on a DBA. Take a look on a Toyota Chief Engineer to se details and examples. Usually a Chief is assigned a new product or a new edition of a product.

Master

Guiding and training professional teams and individuals. This is the primary focus. Deep knowledge and experience in both tech and processes. Can also be called Sensei in a Lean context.

Executive

Part of senior management. Set budgets. Define products and projects.

Driven by career ambitions.

Very rare title. Most people has left database administration many years before this level is relevant to pursue a more focused business career.

Inspiration

• SHIFTMAG: Engineering career levels explained.
• indeed: Principal Engineer vs. Senior Engineer.

History

2025-05-02: Chief role added.

2024-12-23: Master level added.

2024-11-16: Post created.

Reduce OS memory with BCDEdit

This is a quick description on how to reduce OS memory on virtual Windows Server 2016 from 128 GB to 48 GB without changing VM configuration. This can be done with the command BCDEdit.exe which is somewhat described by Microsoft in the documentation on bcdedit and the documents "BCDEdit Command-Line Options" and "BCDEdit Options Reference". The tool is a default tool in Microsoft Windows and is placed in the directory "C:\Windows\System32\".

The name of the tool referes to Boot Configuration Data, which is described somewhat in an old MSDN Vista document BCD.docx. Boot Configuration Data is a firmware independant data store with boot data. The store is placed in the system partition.

Reducing memory available to the OS is sometimes done, when you have an application that is not using all available memory, but is licensed by the memory amount available to the OS. And this is sometimes made worse by a computer with way too much memory that can't be reduced by ridiculous agreements.

Before

System Information (msinfo32.exe) on the guest VM. All installed memory is available to the OS:

Reduce OS Memory

Calculation: The number needed for the command is the amount of removed memory. In this case 128 GB - 48 GB = 80 GB. As the number must be in MB the corection is 80 GB * 1024 MB/GB = 81920 MB.

Start console (cmd.exe) as Administrator. PowerShell does not work. Enter the command line:

bcdedit /set {current} removememory 81920

• bcdedit is shorthand on BCDEdit.edit.
• /set is a option to BCDEdit described in BCDEdit /set.
• {current} is a reference to the current boot entry.
• removememory removes memory available to the Windows operating system.
• 81920 is the amount of memory to remove from the OS in MB. See calculation above.

Reboot VM.

After

System Information on guest VM:

Task Manager on guest VM:

It shows both 128 GB and 48 GB even only 48 GB is available to the OS. This will confuse most operators and administrators.

Roll-Back

Start console as Administrator. And enter the command line:

bcdedit /deletevalue {current} removememory

Reboot VM.

And all the memory is available to the VM as shown by System Information.

Notes on System Configuration

Windows has the standard tool System Configuration where it looks like you also can reduce OS memory. Let me point out that this does not work as bcdedit /set removememory but like bcdedit /set truncatememory which is not as effective.

Disclaimer

This is not my own findings but given to me by the good colleague Ib Thornøe (LinkedIn)

Put SQL Server PVS in own filegroup

Putting the Persisted Version Store (PVS) in its own file group is to improve the performance of SQL Server Accelerated Database Recovery (ADR). ADR was introduced with SQL Server 2019 and had some improvements with SQL Server 2022. This is described in the Microsoft documentation "Accelerated database recovery". Putting the PVS in its own filegroup is recommended and described in the Microsoft document "Manage accelerated database recovery".

This example is on the AdventureWorks database. The script i in the SQLAdmin Github repository.

Please notice that the Microsoft documentation does not create the filegroup before using it for PVS. This is required.

DBA Questions

These questions are relevant your first day on the job as Database Administrator. Also the questions are usefull to look at on a regular basis like a couple of times every year.

Q1: Where are the backups?

This is all types of backup files; full, differential and log backup files. And on all databases.

Also the backup files on the Service Master Key (SMK) and the Database Master Keys (DMK) on the system database [msdb] and the user databases with encrypted columns. This might require some investigations on encrypted columns.

This is most likely the most important question the first day.

Q2: How are the backups created?

Get concrete quickly and ask about tools and schedules. Get to know all the tools all the way to the last storage area. If you want to be a little more confrontative you could ask for the definitions of the processes to handle SQL Server recovery. I would wait a couple of weeke at least and prepare the formulation.

If the backups are created by SQL Server Maintenance Plans you should consider that as an anti-pattern and be cautious.

Q3: How are the backups used?

At first you should focus on database restore. Ask for a recovery plan and restore guides. The restore guides are the most important.

Be aware of the tools used for restore. Are they general available or are you to install them by your self? In what context are the tools to be used? Is a special user or other access required? Is a priviledged access device required?

Then you can open up and ask for a report from the last recovery test. Also you can ask about your new collegues personal experiences with recovery. Both in general and in the actual organisation.

Please remember that it is not you but the SQL Server service accounts that require access to the backup files.

Q4: How do I get administrative access to the SQL Server installations?

Do I have to apply for AD groups? Or apply for an administrative account? Or a combination? Is the access permanent or only by request? This is also you first peek into the security structure build around the SQL Server installations. The approach to SQL Server security is usually unique to organizations and people. A lot of people say that there is a strick role-based (RBAC) security model, but usually the reality is way different and with several variations.

Q5: What are the three most important systems using SQL Server?

Limit the question to three system. This you can look into the first day. Do the rest later.

Ask about a inventory on these systems. What databases, servers, contact persons etcetera.

Q6: What is your phone number?

Ask this question to your new collegues, Incident Management, line manager and the IT manager. Enter the numbers in your personal contact list. Decide by yourself who will have your private phone number. Personally I always have two phones, one job and one private. Also I am rather picky with who have my private number. Especially if I am not paid to take calls outside office hours.

SQL Server Workstation

Some notes from a SQL Server 2022 Developer Edition installation on a Windows 11 Pro workstation. The infrastructure is VMware Workstation Pro.

General Preperations

If you clone an exiting VM then you should rename the new SQL Server workstation to something different. And do it before anything else.

As I usually has a sandbox for different versions of SQL Server the name could something like "MSSQL 2022 Workstation". Consider if you should differ between sandbox, workstation, devbox or anything else.

Make sure the Windows installation is up to date.

PowerShell

Install PowerShell and then Windows Terminal from Microsoft Store.

Open Windows Terminal and pin it to the task bar. Then close Windows Terminal.

PS > Install-Module -Name SqlServer
PS > Install-Module -Name dbatools

Storage

Move CD-ROM to Z–drive.

Drive	Name	Size	NTFS	Path	Comments
C	System	64 GB	default		Windows and tools (SSMS)
P	Program	64 GB	default		SQL Server
D	Data	64 GB	64 KB		User database (mdf & ndf)
L	Translog	32 GB	64 KB		User db & tempdb (ldf)
T	tempdb	32 GB	64 KB		tempdb (mdf & ndf)
X	Backup	128 GB	64 KB		Full, diff & log backup

1. Create one disk at a time: One-Piece Flow (Lean)- Must be done manually in VMware Workstation. Can be scripted in vCenter with PowerCLI.
2. In VMware Workstation NVMe can only be added offline.
3. Split virtual disk into multiple files.
4. Do not allocate all space now.
5. „MSSQL Workstation_<Drive>“
6. All partitions are GPT.
7. Only use Simple Volume.
8. Quick format.

SQL Server Installation

1. Connect VM CD-drive to SQL Server ISO-file
2. Run SETUP.EXE
3. Select „SQL Server Developer Edition“
4. Select "Database Engine Services" and "Full-Text…"
5. Place "Instance Root Directory" on another physical drive than Windows, e.g. "P:\Program_Files\Microsoft_SQL_Server\". This is to spread preassure on (local) storage units.
6. Name the (Database Engine) instance, e.g. „SSDB00“. This will allow side-by-side installation of different SQL Server versions. On the other hand I usually does not recommend instanse name with version information. The instance name in shared installations should be generic – like a dumb database key.
7. Change Service Startup Type to "Manual". When running several instances this will help you control local resources on your workstation. With virtual workstations I usually create one VM to each instance.
8. Set „Grant Perform Volume Maintenance Task priviledge to SQL Server Database Engine Service“. This is to speed up database file (auto-)growths which might be when expanding the physical data model. This is really relevant on a workstation where you experiment. On the other hand I would never use Auto Shrink (AUTO_SHRINK) even in a sandbox.
9. Collation tab: Use collation "Latin_General_100 AS KS WS" maybe with UTF-8.
10. Add Current User as SQL Server administrator. Adding (Local) Administrators is not enough.
11. Set Directories. Define seperate paths for
• Data
• TransactionLog
• Backup
• tempdb
  where [tempdb] transaction log is with the other user database transaction logs.
12. Set tempdb data and log
13. Set MAXDOP to 4
14. Set memory to Recommended
15. Run installation
16. Close SQL Server Installation
17. Disconnect CD-drive
18. Restart VM

SQL Server Update

1. Copy latest CU to VM. I use „C:\temp\“
2. Start PowerShell as Administrator. That is Microsoft PowerShell (v7) not Windows PowerShell (v5)
3. Change directory to „C:\temp\“
4. Run the SQL Server CU exe-file
5. Complete SQL Server CU installation
6. Delete SQL Server CU exe-file from „C:\temp\“
7. Restart VM

SSMS Installation

1. Copy latest SSMS installation (SSMS-Setup-ENU.exe) to „C:\temp\“
2. Start PowerShell as Administrator
3. Change directory to „C:\temp\“
4. Run SSMS Setup exe-file
5. Complete SSMS installation
6. Delete SSMS Setup exe-file from „C:\temp\“
7. Start SSMS
8. Cancel suggested connection
9. Pin SSMS to taskbar
10. View Registered Servers (Ctrl + Alt + G)
11. Select properties on SSDB00
• Server name: (local)\SSDB00
• Trust server certificate
• Registered server name: SSDB00
• Connection Properties tab…
• Connect to database: tempdb
• Connection time-out: 5 seconds
• Use custom color: green
• Save
12. View Object Explorer Details (F7)

ODBC

If SQL Server ODBC 17 is already installed, it will most likely generate a error like "The feature you are trying to use is on a network resource that is unavailable.".
The solution is to uninstall SQL Server ODBC 17. This can be done by Control Panel > Remove Program.

SSDB Configuration

Query: ALTER DATABASE [model] SET RECOVERY SIMPLE WITH NO_WAIT;

Query:

EXECUTE master.sys.sp_configure
	@configname = N'remote access',
	@configvalue = N'0';
GO
RECONFIGURE WITH OVERRIDE;
GO

Query:

EXECUTE master.dbo.xp_create_subdir N'X:\MSSQL\Backup';

Query:

EXECUTE master.sys.sp_configure
	@configname = N'backup compression default',
	@configvalue = N'1';
GO
EXECUTE master.sys.sp_configure
	@configname = N'backup checksum default',
	@configvalue = N'1';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE master.dbo.xp_instance_regwrite
	@rootkey = N'HKEY_LOCAL_MACHINE',
	@key = N'Software\Microsoft\MSSQLServer\MSSQLServer',
	@value_name = N'BackupDirectory',
	@type = N'REG_SZ',
	@value = N'X:\MSSQL\Backup';
GO

Query:

EXECUTE master.sys.sp_configure
	@configname = N'show advanced options',
	@configvalue = N'1';
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE master.sys.sp_configure
	@configname = N'cost threshold for parallelism',
	@configvalue = N'50';
EXECUTE master.sys.sp_configure
	@configname = N'backup compression default',
	@configvalue = N'1';
EXECUTE master.sys.sp_configure
	@configname = N'backup checksum default',
	@configvalue = N'1';
RECONFIGURE WITH OVERRIDE;
GO
EXECUTE master.sys.sp_configure
	@configname = N'show advanced options',
	@configvalue = N'0';
RECONFIGURE WITH OVERRIDE;
GO

AdventureWorks Installation

See SQLAdmin post on Restore AdventureWorks on SQL Server 2019.

;

Plan Explorer Installation

1. Download Plan Explorer from www.solarwinds.com/free-tools/plan-explorer. This requires registration with e-mail and phone#.
2. Start Plan Explorer
3. Pin to Start

History

2024-10-23 – Details added.
2024-09-22 – Element on administrators added. And a few typhographic corrections.
2024-09-09 – Post created.