Skills SQL Server DBA Database Administration: Difference between revisions
| (38 intermediate revisions by the same user not shown) | |||
| Line 16: | Line 16: | ||
''[[Role_Autodata|Autodata]]'' | ''[[Role_Autodata|Autodata]]'' | ||
''[[Role_Bluecrest_Capital|Bluecrest Capital]]'' | ''[[Role_Bluecrest_Capital|Bluecrest Capital]]'' | ||
''[[Role_ING_Investment_Management|ING Investment Management]]'' | |||
</div> | </div> | ||
</div> | </div> | ||
/indexing/statistics | /indexing/statistics | ||
Query hints | |||
SQL Profiler / server-side traces | |||
Query plans / Actual execution plans | |||
SQL 2008 Best Practices Analyzer | SQL 2008 Best Practices Analyzer | ||
Performance Dashboard | |||
Locks, blocking, and deadlocks | |||
== Capacity planning == | == Capacity planning == | ||
| Line 42: | Line 51: | ||
''Used at: '' | ''Used at: '' | ||
''[[Role_Autodata|Autodata]]'' | ''[[Role_Autodata|Autodata]]'' | ||
''[[Role_Bluecrest_Capital|Bluecrest Capital]]'' | |||
''[[Role_ING_Investment_Management|ING Investment Management]]'' | |||
''[[Role_Autotrader|Autotrader]]'' | |||
</div> | </div> | ||
</div> | </div> | ||
| Line 47: | Line 59: | ||
orphaned users | orphaned users | ||
Failed logins | |||
Standard vs integrated security | |||
== SQL Agent == | == SQL Agent == | ||
| Line 54: | Line 70: | ||
''Used at: '' | ''Used at: '' | ||
''[[Role_Allen_Overy|Allen & Overy]]'' | ''[[Role_Allen_Overy|Allen & Overy]]'' | ||
''[[Role_ING Investment_Management|ING Investment Management]]'' | |||
</div> | </div> | ||
</div> | </div> | ||
SQL Agent | SQL Agent | ||
SQL Scheduled jobs | |||
SQL Maintenance tasks / cleanups | |||
Alerts | |||
== Backups and restores == | == Backups and restores == | ||
| Line 68: | Line 91: | ||
</div> | </div> | ||
</div> | </div> | ||
SQL Server database backups, differential, incremental backups, transaction log backups | SQL Server database backups, differential, incremental backups, transaction log backups, simple vs full recovery models. | ||
SQL Server database restores | SQL Server database restores | ||
Moving databases by detaching, moving, and reattaching their .mdf and .ldf files. | |||
Disaster recovery | |||
Business Continuity | |||
== Data Migration == | == Data Migration == | ||
Source directories for source files | |||
Staging tables | |||
ETL | |||
ELT | |||
Fact, dimension tables | |||
Trigger files | |||
Header rows (in files). | |||
Duplicate rows | |||
File and data concatenation (joining of the content of one file with the next) | |||
== SQL Server upgrades == | == SQL Server upgrades == | ||
| Line 80: | Line 127: | ||
''Used at: '' | ''Used at: '' | ||
''[[Role_Autodata|Autodata]]'' | ''[[Role_Autodata|Autodata]]'' | ||
''[[Role_ING_Investment_Management|ING Investment Management]]'' | |||
</div> | </div> | ||
</div> | </div> | ||
SQL Server version upgrades & downgrades | SQL Server version upgrades & downgrades | ||
== SQL Server versions == | |||
SQL Server versions: | |||
* 2000 | |||
* 2005 | |||
* 2008 | |||
* 2012 | |||
* 2016 | |||
* 2017 | |||
SQL Server named instances | |||
== Database mirroring and log shipping == | == Database mirroring and log shipping == | ||
| Line 95: | Line 154: | ||
== Transactional Replication == | == Transactional Replication == | ||
Transactional Replication | |||
Articles (tables being published) | |||
Log Reader | |||
Distribution database | |||
Merge replication | Merge replication | ||
Subscription database | |||
Snapshots | |||
SkipErrors 20598 flag | |||
distribution.dbo.MSrepl_errors table | |||
Suspect state (database) | |||
== Synonyms == | == Synonyms == | ||
| Line 114: | Line 191: | ||
Testing | Testing | ||
TEMPDB database | |||
Windows clustering. Microsoft Cluster Server. | |||
Latest revision as of 18:45, 11 October 2024
Used at: Allen & Overy Five Guys Bluecrest Capital
Performance tuning
/indexing/statistics
Query hints
SQL Profiler / server-side traces
Query plans / Actual execution plans
SQL 2008 Best Practices Analyzer
Performance Dashboard
Locks, blocking, and deadlocks
Capacity planning
Used at: Autodata
Capacity planning
SQL Licencing
Security
user accounts
orphaned users
Failed logins
Standard vs integrated security
SQL Agent
Used at: Allen & Overy ING Investment Management
SQL Agent
SQL Scheduled jobs
SQL Maintenance tasks / cleanups
Alerts
Backups and restores
Used at: Autodata Bluecrest Capital
SQL Server database backups, differential, incremental backups, transaction log backups, simple vs full recovery models.
SQL Server database restores
Moving databases by detaching, moving, and reattaching their .mdf and .ldf files.
Disaster recovery
Business Continuity
Data Migration
Source directories for source files
Staging tables
ETL
ELT
Fact, dimension tables
Trigger files
Header rows (in files).
Duplicate rows
File and data concatenation (joining of the content of one file with the next)
SQL Server upgrades
Used at: Autodata ING Investment Management
SQL Server version upgrades & downgrades
SQL Server versions
SQL Server versions:
- 2000
- 2005
- 2008
- 2012
- 2016
- 2017
SQL Server named instances
Database mirroring and log shipping
Used at: Autodata
Database mirroring and log shipping
Transactional Replication
Transactional Replication
Articles (tables being published)
Log Reader
Distribution database
Merge replication
Subscription database
Snapshots
SkipErrors 20598 flag
distribution.dbo.MSrepl_errors table
Suspect state (database)
Synonyms
Used at: MUFG Securities Autodata
Synonyms
Other
Out of hours support
Logging
Testing
TEMPDB database
Windows clustering. Microsoft Cluster Server.