Database Solution - SQL Server Cloud Migration Strategy
The below mentioned table provides a side-by-side comparison of SQL Server features supported on Amazon RDS and Amazon EC2. Use this information to understand the differences between the two services and to choose the best approach for your use case.
SQL Server 2022 new features
The new features in SQL Server 2022 include:
Parameter Sensitive Plan Optimization
Query Store enhancements
Link to Azure SQL Managed Instance
Contained Availability Groups
Azure Synapse Link for SQL
Multi-Write Replication
Azure Active Directory authentication
Azure Purview integration
SQL Server Ledger
AWS S3 storage integration
Page Life Expectancy (PLE) in SQL Server:
Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references.
If your page stays for more time in the buffer pool your PLE is high which leads to high performance. Every time request comes there are more chances that it may find its data in the cache itself, instead of going to the hard drive to read the data.
PLE measures in seconds.
PLE is one of the performance counters of SQL Server.
Higher the PLE, performance of SQL Server is good.
As per recommendation, the value of PLE counter is around 300 seconds.
You can check this value for your SQL server using below DMV.
SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manger%' AND [counter_name] = 'page life expectancy'Buffer Cache Hit Ratio
Buffer Cache Hit Ratio indicates the percentage of pages found in the buffer cache without having to read from the disk.
The ration is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses.
Buffer Cache Hit Ratio is one of the performance counters of SQL server.
You can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server or by using the buffer pool extension feature.
You can check this value for your SQL server using below DMV.
SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manger%' AND [counter_name] = 'Buffer cache hit ratio'A Database Administrator (DBA) plays a crucial role in managing, configuring, and securing databases within an organization. They handle a range of responsibilities from installation to maintenance, ensuring that the database systems are efficient, secure, and reliable.
Installing and Upgrading Database Tools: Setting up and upgrading database server tools to ensure the latest features and security patches are applied.
Planning Storage Requirements: Assessing and planning for future storage needs to accommodate database growth.
Modifying Database Structure: Making necessary changes to the database structure based on requirements provided by application developers.
User Management and Security: Enrolling users, managing system security, and ensuring compliance with database vendor license agreements.
Access Control: Monitoring and controlling user access to the databases to maintain data security.
Performance Monitoring and Optimization: Regularly monitoring database performance and making optimizations to enhance efficiency.
Backup and Recovery Planning: Developing strategies for backup and recovery to ensure data integrity and availability.
Data Archiving: Implementing data archiving strategies to manage historical data efficiently.
Database Backup and Restoration: Performing regular backups and restoring databases as needed.
Vendor Support: Contacting database vendors for technical support and troubleshooting issues.
Report Generation: Generating reports by querying the database as required.
Disaster Recovery Testing: Participating in disaster recovery tests to prepare for potential database failures.
Collaboration: Working closely with other teams such as Network Operations and Monitoring Teams to ensure seamless database operations.
Communication Skills: Excellent communication skills are essential for interacting with various teams and stakeholders.
Database Theory: Strong understanding of database theory and principles.
Database Design: Knowledge of both logical and physical database design.
RDBMS Knowledge: Proficiency in Relational Database Management Systems (RDBMS).
SQL Expertise: Skills in SQL, including Transact-SQL, for querying and managing databases.
Distributed Computing: Understanding of distributed computing architectures.
Operating Systems: Familiarity with the underlying operating system that supports the database.
Storage Technologies: Knowledge of storage technologies, including memory management, disk arrays, NAS/SAN, and networking.
Maintenance and Recovery: Expertise in routine maintenance, recovery procedures, and handling database failovers.
Production Support DBAs: Focused on the physical aspects of database management, including installation, configuration, patching, backups, restores, and disaster recovery.
Development DBAs: Specialize in the logical and development aspects, such as data model design, SQL writing, performance tuning, and pre-production activities.
Application DBAs: Typically work with third-party application software like ERP and CRM systems to manage and optimize databases.
Hybrid DBAs: Versatile professionals who perform a wide range of tasks and may evolve into architectural roles within the enterprise.
As the field of database administration continues to evolve, DBAs are expected to adapt and specialize in various aspects of database management, contributing to their growth and career advancement.
Now the DBA can have peace of mind by giving access to these roles if any user requested for performance or monitoring tools access .
Now no need of giving SA access to such requests.
Below is 2019 SQL SERVER roles.
| Fixed server-level role | Description |
|---|---|
| ##MS_DatabaseConnector## | Members of the ##MS_DatabaseConnector## fixed server role can connect to any database without requiring a User-account in the database to connect to. To deny the CONNECT permission to a specific database, users can create a matching user account for this login in the database and then DENY the CONNECT permission to the database-user. This DENY permission will overrule the GRANT CONNECT permission coming from this role. |
| ##MS_DatabaseManager## | Members of the ##MS_DatabaseManager## fixed server role can create and delete databases. A member of the ##MS_DatabaseManager## role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database. Members of the ##MS_DatabaseManager## role don't necessarily have permission to access databases that they don't own. |
| ##MS_PerformanceDefinitionReader## | Members of the ##MS_PerformanceDefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY PERFORMANCE DEFINITION, and respectively has VIEW PERFORMANCE DEFINITION permission on any database on which the member of this role has a user account. This is a subset of what the ##MS_DefinitionReader## server role has access to. |
| ##MS_SecurityDefinitionReader## | Members of the ##MS_SecurityDefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY SECURITY DEFINITION, and respectively has VIEW SECURITY DEFINITION permission on any database on which the member of this role has a user account. This is a small subset of what the ##MS_DefinitionReader## server role has access to. |
| ##MS_DefinitionReader## | Members of the ##MS_DefinitionReader## fixed server role can read all catalog views that are covered by VIEW ANY DEFINITION, and respectively has VIEW DEFINITION permission on any database on which the member of this role has a user account. |
| ##MS_LoginManager## | Members of the ##MS_LoginManager## fixed server role can create and delete logins. |
| ##MS_ServerPerformanceStateReader## | Members of the ##MS_ServerPerformanceStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER PERFORMANCE STATE, and respectively has VIEW DATABASE PERFORMANCE STATE permission on any database on which the member of this role has a user account. This is a subset of what the ##MS_ServerStateReader## server role has access to. |
| ##MS_ServerSecurityStateReader## | Members of the ##MS_ServerSecurityStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER SECURITY STATE, and respectively has VIEW DATABASE SECURITY STATE permission on any database on which the member of this role has a user account. This is a small subset of what the ##MS_ServerStateReader## server role has access to. |
| ##MS_ServerStateReader## | Members of the ##MS_ServerStateReader## fixed server role can read all dynamic management views (DMVs) and functions that are covered by VIEW SERVER STATE, and respectively has VIEW DATABASE STATE permission on any database on which the member of this role has a user account. |
| ##MS_ServerStateManager## | Members of the ##MS_ServerStateManager## fixed server role have the same permissions as the ##MS_ServerStateReader## role. Also, it holds the ALTER SERVER STATE permission, which allows access to several management operations, such as: DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE ('ALL'), DBCC SQLPERF() |