Migrating a MySQL database from one server to another is a common DBA task, whether for hardware upgrades, disaster recovery, or server consolidation. In this guide, we will walk through the process of migrating a database from Testmysql01 to Testmysql02 and updating the web application accordingly.
Upgrading SQL Server 2014 Standard Edition to Enterprise Edition can unlock advanced features such as online indexing, advanced high availability, and enhanced performance. This guide provides a structured approach to ensure a smooth and successful upgrade.
Before proceeding with the upgrade, ensure the following:
Backup: Take full database and system backups.
Check Compatibility: Verify application compatibility with Enterprise Edition features.
Disk Space: Ensure sufficient storage for the upgrade.
License Key: Have a valid Enterprise Edition license key.
System Requirements: Confirm that the system meets Enterprise Edition hardware requirements.
User Notifications: Inform stakeholders about potential downtime.
Log in to the SQL Server host with administrative privileges.
Insert the SQL Server 2014 Enterprise installation media or mount the ISO file.
Run setup.exe from the installation media.
In the SQL Server Installation Center, select Maintenance.
Click Edition Upgrade.
The Edition Upgrade wizard will detect installed instances.
Select the instance of SQL Server 2014 Standard that you want to upgrade.
Click Next.
Enter your valid Enterprise Edition product key.
Click Next.
Read the License Agreement.
Accept the terms and click Next.
The setup will run pre-upgrade checks.
Resolve any reported issues before proceeding.
Click Next once all checks pass.
Click Upgrade to initiate the process.
Wait for the upgrade to complete.
Once completed, click Close.
Open SQL Server Management Studio (SSMS).
Run the following query to check the new edition:
SELECT SERVERPROPERTY('Edition') AS Edition;The output should display Enterprise Edition.
Open SQL Server Configuration Manager.
Restart the SQL Server services to apply changes.
Test critical applications for compatibility.
Check for any performance degradation.
Monitor error logs for potential issues.
Online Indexing
Partitioning
Data Compression
Advanced High Availability (AlwaysOn)
Upgrading SQL Server 2014 Standard to Enterprise Edition provides enhanced performance, scalability, and availability. Following this step-by-step guide ensures a smooth transition while minimizing risks. Always test in a non-production environment before upgrading a live system.
Have you recently performed an SQL Server upgrade? Share your experience in the comments below!
As a SQL Server DBA, managing and auditing user roles and permissions across multiple databases is a critical task. Ensuring that each user has the appropriate access rights is essential for security and compliance. In this post, we’ll share a script that allows you to list all usernames and their associated roles across all databases in a SQL Server instance.
Security & Compliance: Regular audits help prevent unauthorized access.
Troubleshooting Access Issues: Quickly identify missing or incorrect permissions.
User Management: Helps maintain a structured approach to assigning roles and logins.
This script retrieves user information, including their associated roles, from all databases on a SQL Server instance. It works for different SQL Server versions and dynamically queries all databases using sp_MSForEachdb.
In SQL Server, SQL Agent Jobs play a crucial role in automating database maintenance tasks, backups, and other scheduled activities. However, sometimes jobs may be disabled due to administrative decisions, troubleshooting needs, or unexpected issues. As a DBA, it’s essential to track and manage these disabled jobs to ensure critical processes are not inadvertently left inactive.
In this blog post, we will look at a simple yet effective SQL query to retrieve a list of all disabled jobs in SQL Server.
Query to Fetch Disabled Jobs Below is a straightforward SQL query to identify all jobs that are currently disabled:
SELECT name
FROM msdb.dbo.sysjobs
WHERE enabled = 0
ORDER BY name;Notes:
msdb.dbo.sysjobs: This system table stores metadata for all SQL Server Agent jobs.
enabled = 0: The enabled column indicates whether a job is active (1) or disabled (0). By filtering on 0, we retrieve only disabled jobs.
Automating the Monitoring Process To proactively monitor disabled jobs, consider setting up an automated alert or report. Here’s an approach using SQL Server Agent:
Create a SQL Server Agent JobUsing Database Mail for Notifications
DECLARE @body NVARCHAR(MAX);
SET @body = (SELECT STRING_AGG(name, ', ') FROM msdb.dbo.sysjobs WHERE enabled = 0);
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourMailProfile',
@recipients = 'dba_team@example.com',
@subject = 'Disabled SQL Server Jobs Alert',
@body = 'The following jobs are currently disabled: ' + @body;Managing database backups and restores is a critical aspect of SQL Server administration. When dealing with large databases, it becomes essential to track the progress of ongoing backup and restore operations to estimate completion time and monitor performance. This blog post provides a structured SQL script to monitor these operations effectively.
Why Monitor Backup and Restore Progress?
Ensures visibility into long-running operations
Helps estimate completion time for better planning
Identifies potential performance bottlenecks
Prevents unnecessary disruptions by avoiding premature interventions
SQL Script to Monitor Backup and Restore Progress Use the following SQL script to track the percentage completion, elapsed time, and estimated completion time of backup and restore operations in SQL Server:
SELECT
r.session_id AS [Session_Id],
r.command AS [Command],
CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete],
GETDATE() AS [Current Time],
CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time],
CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min],
CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min],
CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours],
CONVERT(VARCHAR(1000), (
SELECT SUBSTRING(TEXT, r.statement_start_offset / 2,
CASE WHEN r.statement_end_offset = -1 THEN 1000
ELSE (r.statement_end_offset - r.statement_start_offset) / 2 END)
FROM sys.dm_exec_sql_text(sql_handle)
)) AS [Statement Text]
FROM sys.dm_exec_requests r
WHERE r.command LIKE 'RESTORE%'
OR r.command LIKE 'BACKUP%'
OR r.command LIKE 'DbccFilesCompact'
OR r.command LIKE 'DbccSpaceReclaim';
Ensuring database backups are completed successfully is a critical task for every SQL Server DBA. In this post, I’ll share a SQL script that quickly checks the latest full backup status of all databases on a SQL Server instance.
Problem:
A friend encountered an issue while installing SQL Server in a clustered environment. The installation stopped midway, and he was unable to remove SQL Server using Add/Remove Programs or the SQL Server Setup. Even after manually deleting registry entries and folders, SQL Server services were still visible in Services.msc.
Solution:
If you face a similar issue, follow these steps to completely remove SQL Server services:
Open Command Prompt as Administrator and run the following commands:
This will remove the SQL Server Agent and SQL Server (MSSQLSERVER) services. If you are using a named instance, replace mssqlserver with mssql$<InstanceName>.
Since the services might still be registered in the Windows registry, follow these steps to clean up:
Win + R, type regedit, and hit Enter.After performing these steps, the SQL Server services should be completely removed from the system.
Managing
permissions effectively in SQL Server is crucial to maintaining security and
access control. One common requirement is to grant users or groups the ability
to view the definitions of stored procedures or execute them without giving
broader privileges.
In this post, we'll share SQL scripts that automate granting VIEW DEFINITION and EXECUTE permissions on all stored procedures in a given database.
Granting
VIEW DEFINITION Access
The VIEW
DEFINITION permission allows users to see the stored procedure’s
definition without modifying it. Use the following script to generate and
execute GRANT VIEW DEFINITION statements for all stored
procedures:
SELECT
'GRANT VIEW DEFINITION ON ' + QUOTENAME(specific_schema) + '.' +
QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu]'
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
routine_type = 'PROCEDURE'
Note: Replace [Domain\\Ajeyudu] with
the actual user or group to whom you want to grant permissions.
After running the above query, copy the generated GRANT VIEW DEFINITION statements and execute them.
Granting
EXECUTE Access
To allow
users to execute all stored procedures without requiring other permissions, use
the script below:
SELECT
'GRANT EXECUTE ON ' + QUOTENAME(specific_schema) + '.' +
QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu]'
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
routine_type = 'PROCEDURE'
Run the generated commands to apply EXECUTE permissions to the specified user or group.
Automating
the Permission Granting
For larger
databases, automating permission assignments via a dynamic SQL approach can be
beneficial. Use the following script to execute the generated GRANT statements
dynamically:
DECLARE @sql NVARCHAR(MAX) = ''
SELECT
@sql = @sql + 'GRANT VIEW DEFINITION ON ' + QUOTENAME(specific_schema) + '.' +
QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu];' + CHAR(13)
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE routine_type = 'PROCEDURE'
EXEC
sp_executesql @sql
Similarly,
modify the script for EXECUTE permissions:
DECLARE @sql NVARCHAR(MAX) = ''
SELECT
@sql = @sql + 'GRANT EXECUTE ON ' + QUOTENAME(specific_schema) + '.' +
QUOTENAME(specific_name) + ' TO [Domain\\Ajeyudu];' + CHAR(13)
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE routine_type = 'PROCEDURE'
EXEC
sp_executesql @sql
By using
dynamic SQL, these scripts can directly apply permissions without requiring
manual execution of individual statements.