To manage and maintain SQL Server information databases.
General database configuration
- Database servers should all be running the same build of SQL Server.
- Memory should be configured appropriately: ensure enough RAM is provided for memory cache so that as much data as possible is read from physical memory rather than from disk. The defaults for SQL Server are too low for most enterprise applications.
- Database capacity management.
- Databases should be configured in accordance with Mastercard Group standards, with special attention to collation and storage engine selection.
- Ensure that all replicas and scheduled jobs have failure notifications configured.
Performance tuning and troubleshooting
- Analyze database performance using SQL Server Enterprise Monitor.
- Databases are accessible and provide good response times
- 100% up time for databases
- Write queries against the INFORMATION_SCHEMA database and decipher the metadata obtained
- Display, decipher and edit server configuration variables to improve performance of applications
- Display and analyze status variables to ensure that applications are utilizing the settings in the most effective manner
- Write queries that take advantage of the SQL Server performance enhancements dealing with queries and indexing
- Evaluate the application architecture for efficient design, structure, caching, number of connections and other factors affecting performance
- Evaluate hardware and OS for effects on performance
- Evaluate techniques for loading data into the database and effects on performance
- Make changes to database to improve performance.
Manage transfer of data between databases
- Develop and support SQL Server database replication
- Data accurately and securely transferred between databases
- Use SQL Server Enterprise monitor to perform replication health checks.
Manage database security
- Create security policies to define database access for database users. Ensure that data is secure.
- Monitor access to databases by users
- Develop controls to monitor access to data
- Maintain integrity of a SQL Server installation utilizing security protocols
- Securely backup and restore databases as required by users
- Limited downtime when database fails
- Archiving of data
- Perform daily health checks
- Customize thresholds and alerts
- Table/index Fragmentation should be monitored and indexes periodically rebuilt.
- Index statistics should be kept as up to date as possible.
- Database capacity should be reviewed monthly and future growth planned.
- Redundant data should be archived as often as is necessary.
- Bug notifications from SQL Server should be tracked and reviewed regularly.
Assist with the analysis and design database structures
- Develop and optimize physical design of SQL Server database systems
- Database structures allow for effective storing and retrieval of data
- Analyze database requirements.
- Do benchmark test to determine resource requirements.
- Design database structures.
- Create database, tables, views, users, indexes etc.
Develop stored procedures
- Interact with users to determine stored procedure requirements
- Functional and correct stored procedures provided to users
- Analyze requirements
- Design stored procedures to meet requirements
- Develop code for stored procedures
- Test stored procedures
Experience / Knowledge Required
- 12 years formal schooling.
- A degree or diploma with Computer Science as major.
- Minimum 3 years DB Administration Experience
- Well versed with core SQL Server administrative functionality including all standard levers to monitor and manage performance, redundancy, backup and restoration of databases
- Has actively administered high volume production SQL Server servers based on a 24x7 demand
- Extensive use of Standard Query Language.
- SQL Server Certification