SQL Server & MySQL Database Administration

MySQL vs. SQL Server: Choosing the Right Engine

MySQL / MariaDB

Installation

# Ubuntu/Debian
apt install mysql-server -y
# or MariaDB (drop-in replacement)
apt install mariadb-server -y

# CentOS/AlmaLinux
dnf install mysql-server -y
systemctl enable mysqld
systemctl start mysqld

# Secure the installation (set root password, remove test db)
mysql_secure_installation

User Management

# Log in as root
mysql -u root -p

-- Create a database
CREATE DATABASE myapp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create a user with a strong password
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'Str0ng!P@ssw0rd';

-- Grant privileges (least privilege principle)
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_production.* TO 'myapp_user'@'localhost';

-- For admin tasks (migrations, schema changes)
GRANT ALL PRIVILEGES ON myapp_production.* TO 'myapp_admin'@'localhost' IDENTIFIED BY 'Adm1n!P@ss';

-- Never do this in production:
-- GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';

-- Apply changes
FLUSH PRIVILEGES;

-- View all users
SELECT User, Host FROM mysql.user;

-- View grants for a user
SHOW GRANTS FOR 'myapp_user'@'localhost';

-- Remove a user
DROP USER 'old_user'@'localhost';

Essential Queries & Operations

-- Show all databases
SHOW DATABASES;

-- Show tables in a database
USE myapp_production;
SHOW TABLES;

-- Describe table structure
DESCRIBE users;

-- Basic CRUD operations
-- Create
INSERT INTO users (name, email, created_at)
VALUES ('Brandon', 'brandon@example.com', NOW());

-- Read
SELECT id, name, email FROM users WHERE email LIKE '%@example.com' ORDER BY created_at DESC LIMIT 10;

-- Update
UPDATE users SET name = 'Brandon Boyd' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

-- Table size / row count
SELECT table_name, table_rows, ROUND(data_length/1024/1024, 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'myapp_production'
ORDER BY data_length DESC;

-- Find slow queries
SHOW PROCESSLIST;

-- Check which queries are running
SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time DESC;

Indexes & Performance

-- Analyze a slow query
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

-- Add an index to speed up lookups
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Show existing indexes
SHOW INDEX FROM orders;

-- Check if a table needs optimization (after many deletes/updates)
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;

MySQL Backups

# Dump a single database
mysqldump -u root -p myapp_production > backup_$(date +%Y%m%d).sql

# Dump with compression
mysqldump -u root -p myapp_production | gzip > backup_$(date +%Y%m%d).sql.gz

# Dump all databases
mysqldump -u root -p --all-databases > all_dbs_$(date +%Y%m%d).sql

# Dump structure only (no data)
mysqldump -u root -p --no-data myapp_production > schema.sql

# Dump specific tables
mysqldump -u root -p myapp_production users orders > partial_backup.sql

# Restore a backup
mysql -u root -p myapp_production < backup_20260320.sql

# Restore from gzip
gunzip < backup_20260320.sql.gz | mysql -u root -p myapp_production

# Automated daily backup (add to crontab)
# 0 2 * * * mysqldump -u root -pYOUR_PASS --all-databases | gzip > /backups/mysql/$(date +\%Y\%m\%d).sql.gz

MySQL Configuration Tuning

# Key settings in /etc/mysql/mysql.conf.d/mysqld.cnf (or my.cnf)

[mysqld]
# InnoDB buffer pool — set to 50-70% of available RAM
innodb_buffer_pool_size = 1G

# Log slow queries (> 2 seconds)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Max connections (match your PHP-FPM pool size)
max_connections = 100

# Temp table sizes
tmp_table_size = 64M
max_heap_table_size = 64M

# Query cache (MariaDB — MySQL 8.0 removed this)
query_cache_type = 1
query_cache_size = 64M

# Restart to apply
systemctl restart mysql

Remote MySQL Access

# Allow remote connections (edit mysqld.cnf)
bind-address = 0.0.0.0    # Listen on all interfaces
# Or restrict: bind-address = 10.0.0.5

# Create user for remote access (restrict to specific IP)
CREATE USER 'remote_user'@'10.0.0.10' IDENTIFIED BY 'Str0ng!P@ss';
GRANT SELECT, INSERT, UPDATE ON myapp_production.* TO 'remote_user'@'10.0.0.10';
FLUSH PRIVILEGES;

# Open firewall port
ufw allow from 10.0.0.10 to any port 3306

# Require SSL for remote connections
ALTER USER 'remote_user'@'10.0.0.10' REQUIRE SSL;

Microsoft SQL Server

Installation on Windows

# Download SQL Server from Microsoft:
# - Express (free, 10 GB limit, good for small apps)
# - Standard (licensed, full features)
# - Developer (free for dev/test, full features)

# After installation, install SQL Server Management Studio (SSMS)
# SSMS is the GUI tool for managing SQL Server

# Enable TCP/IP connections:
# 1. Open SQL Server Configuration Manager
# 2. SQL Server Network Configuration > Protocols
# 3. Enable TCP/IP, set port 1433
# 4. Restart SQL Server service

Installation on Linux

# SQL Server on Ubuntu 22.04
curl https://packages.microsoft.com/keys/microsoft.asc | \
  sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
sudo add-apt-repository \
  "$(wget -qO- https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list)"
sudo apt update
sudo apt install mssql-server -y

# Run setup
sudo /opt/mssql/bin/mssql-conf setup
# Choose edition, set SA password, accept license

# Install command-line tools
sudo apt install mssql-tools18 unixodbc-dev -y
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc

# Connect via command line
sqlcmd -S localhost -U SA -P 'YourPassword!'

SQL Server: Database & User Management

-- Connect via SSMS or sqlcmd

-- Create a database
CREATE DATABASE MyAppDB;
GO

-- Create a login (server-level)
CREATE LOGIN app_login WITH PASSWORD = 'Str0ng!P@ssw0rd';
GO

-- Create a user mapped to the login (database-level)
USE MyAppDB;
GO
CREATE USER app_user FOR LOGIN app_login;
GO

-- Grant permissions
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;
GO

-- For full access (dev environments only)
ALTER ROLE db_owner ADD MEMBER app_user;
GO

-- View all databases
SELECT name, state_desc, recovery_model_desc FROM sys.databases;

-- View all logins
SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE type IN ('S','U');

-- View database sizes
EXEC sp_helpdb;

SQL Server: Essential Queries

-- Basic CRUD (T-SQL syntax)
-- Create
INSERT INTO Users (Name, Email, CreatedAt)
VALUES ('Brandon', 'brandon@example.com', GETDATE());

-- Read with pagination
SELECT Id, Name, Email
FROM Users
ORDER BY CreatedAt DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- Update
UPDATE Users SET Name = 'Brandon Boyd' WHERE Id = 1;

-- Delete
DELETE FROM Users WHERE Id = 1;

-- Check table sizes
SELECT t.NAME AS TableName,
    p.rows AS RowCount,
    CAST(ROUND(SUM(a.total_pages) * 8.0 / 1024, 2) AS DECIMAL(18,2)) AS TotalSpaceMB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY t.Name, p.Rows
ORDER BY TotalSpaceMB DESC;

-- Find currently running queries
SELECT r.session_id, r.status, r.command, t.text AS query_text,
    r.cpu_time, r.total_elapsed_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.status = 'running';

SQL Server: Stored Procedures

-- Create a stored procedure
CREATE PROCEDURE GetUsersByStatus
    @Status NVARCHAR(20),
    @Limit INT = 50
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Id, Name, Email, Status, CreatedAt
    FROM Users
    WHERE Status = @Status
    ORDER BY CreatedAt DESC
    OFFSET 0 ROWS FETCH NEXT @Limit ROWS ONLY;
END;
GO

-- Execute it
EXEC GetUsersByStatus @Status = 'active', @Limit = 10;

-- View all stored procedures
SELECT name, create_date, modify_date
FROM sys.procedures
ORDER BY modify_date DESC;

SQL Server: Backups

-- Full backup
BACKUP DATABASE MyAppDB
TO DISK = 'C:\Backups\MyAppDB_Full.bak'
WITH FORMAT, COMPRESSION, STATS = 10;

-- Differential backup (changes since last full)
BACKUP DATABASE MyAppDB
TO DISK = 'C:\Backups\MyAppDB_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION, STATS = 10;

-- Transaction log backup
BACKUP LOG MyAppDB
TO DISK = 'C:\Backups\MyAppDB_Log.trn'
WITH COMPRESSION, STATS = 10;

-- Restore a database
RESTORE DATABASE MyAppDB
FROM DISK = 'C:\Backups\MyAppDB_Full.bak'
WITH REPLACE, RECOVERY;

-- Restore with move (different paths)
RESTORE DATABASE MyAppDB
FROM DISK = 'C:\Backups\MyAppDB_Full.bak'
WITH MOVE 'MyAppDB' TO 'D:\Data\MyAppDB.mdf',
     MOVE 'MyAppDB_log' TO 'D:\Logs\MyAppDB_log.ldf',
     REPLACE, RECOVERY;

SQL Server: Performance Tuning

-- Find missing indexes (SQL Server suggests these)
SELECT d.statement AS TableName,
    d.equality_columns, d.inequality_columns, d.included_columns,
    s.user_seeks, s.avg_total_user_cost * s.avg_user_impact AS ImprovementScore
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY ImprovementScore DESC;

-- Find the most expensive queries
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_ms,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset/2 + 1,
        (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE qs.statement_end_offset END - qs.statement_start_offset)/2 + 1) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed_ms DESC;

-- Check index fragmentation
SELECT OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Rebuild fragmented indexes
ALTER INDEX ALL ON Users REBUILD;

Connecting from Applications

PHP to MySQL (PDO)

$pdo = new PDO(
    'mysql:host=localhost;dbname=myapp_production;charset=utf8mb4',
    'myapp_user',
    'Str0ng!P@ssw0rd',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

// Always use prepared statements
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();

See our PDO Configuration guide for full details.

C# / ASP.NET to SQL Server

// Connection string (appsettings.json)
"ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyAppDB;User Id=app_login;Password=Str0ng!P@ssw0rd;TrustServerCertificate=True;"
}

// Using Dapper (lightweight ORM)
using var connection = new SqlConnection(connectionString);
var users = await connection.QueryAsync<User>(
    "SELECT Id, Name, Email FROM Users WHERE Status = @Status",
    new { Status = "active" }
);

Node.js to MySQL

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'myapp_user',
    password: 'Str0ng!P@ssw0rd',
    database: 'myapp_production',
    waitForConnections: true,
    connectionLimit: 10,
});

// Parameterized query (prevents SQL injection)
const [rows] = await pool.execute(
    'SELECT * FROM users WHERE email = ?',
    [email]
);

Common Troubleshooting

• Access denied for user — wrong password, user doesn't exist for that host, or missing FLUSH PRIVILEGES after grant changes
• Too many connections — increase max_connections in MySQL config, or investigate connection leaks in your application (connections not being closed)
• Table is full — disk space exhausted, or InnoDB tablespace limit reached. Check with df -h and consider innodb_file_per_table = ON
• Lock wait timeout exceeded — long-running transaction blocking others. Find it with SHOW PROCESSLIST and kill with KILL <id>
• SQL Server: Cannot open database requested by the login — the login exists but has no user mapped in that database. Create the user with CREATE USER ... FOR LOGIN
• SQL Server: Named Pipes Provider error — TCP/IP not enabled in SQL Server Configuration Manager, or firewall blocking port 1433