The power shell script Download
Please find the below mentioned sample screenshot disk space report :
Last cluster failover sql script to find out
--Last cluster failover sql script to find out
CREATE TABLE #ErrorLog(
LogDate DATETIME,
ErrorSource NVARCHAR(MAX),
ErrorMessage NVARCHAR(MAX)
)
CREATE TABLE #NumberOfLogs(
ID INT PRIMARY KEY NOT NULL,
LogDate DATETIME NOT NULL,
LogFileSize bigint
)
INSERT INTO #NumberOfLogs(ID,LogDate,LogFileSize)
EXEC master.dbo.xp_enumerrorlogs
DECLARE @ErrorLogID INT
DECLARE cNumberOfLogs CURSOR FOR
SELECT ID
FROM #NumberOfLogs
OPEN cNumberOfLogs
FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
EXEC sp_readerrorlog @ErrorLogID, 1, 'NETBIOS'
INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
EXEC sp_readerrorlog @ErrorLogID, 1, 'SQL Server is terminating'
FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID
END
CLOSE cNumberOfLogs
DEALLOCATE cNumberOfLogs
SELECT LogDate, ErrorMessage FROM #ErrorLog
DROP TABLE #ErrorLog
DROP TABLE #NumberOfLogs
LogDate DATETIME,
ErrorSource NVARCHAR(MAX),
ErrorMessage NVARCHAR(MAX)
)
CREATE TABLE #NumberOfLogs(
ID INT PRIMARY KEY NOT NULL,
LogDate DATETIME NOT NULL,
LogFileSize bigint
)
INSERT INTO #NumberOfLogs(ID,LogDate,LogFileSize)
EXEC master.dbo.xp_enumerrorlogs
DECLARE @ErrorLogID INT
DECLARE cNumberOfLogs CURSOR FOR
SELECT ID
FROM #NumberOfLogs
OPEN cNumberOfLogs
FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
EXEC sp_readerrorlog @ErrorLogID, 1, 'NETBIOS'
INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
EXEC sp_readerrorlog @ErrorLogID, 1, 'SQL Server is terminating'
FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID
END
CLOSE cNumberOfLogs
DEALLOCATE cNumberOfLogs
SELECT LogDate, ErrorMessage FROM #ErrorLog
DROP TABLE #ErrorLog
DROP TABLE #NumberOfLogs
MySQL database migration from one server to another server.
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.
Testmysql01 to Testmysql02 and updating the web application accordingly.MySQL database migration from one server to another server.
Connect to Testmysql01 using ssh
Take a dump of Test_database database
[root@Testmysql01 ~]# mysqldump -u root -p Test_database > /opt/Test_database.sql
Copy the Dump file to Testmysql02
[root@Testmysql01 ~]# scp /opt/Test_database.sql root@Testmysql02.domain.com:/opt
[root@Testmysql01 ~]# Exit
Connect to Testmysql02 using ssh
connect to MySQL
[root@Testmysql02~]# MySQL -u root -p
Create Database and grant permission for the user
MySQL> create database Test_database;
MySQL> GRANT ALL ON Test_database.* TO testuser@'%' IDENTIFIED BY 'password';
MySQL> quit
Import Dump to new database
[root@Testmysql02~]# MySQL -u root -p Test_DBA < /opt/Test_DBA.sql
[root@Testmysql02~]# Exit
Connect to webserver using RDP.
Navigate to C:\inetpub\Test_database\Test_dat
abase\config
Edit file config.php and change 'dbhost' => in the file to Testmysql02.domain.com.
verify 'dbuser' => and 'dbpassword' => is defined correctly.
restart the site from IIS manager
connect to Test_database site and verify everything is working fine.
Take a dump of Test_database database
[root@Testmysql01 ~]# mysqldump -u root -p Test_database > /opt/Test_database.sql
Copy the Dump file to Testmysql02
[root@Testmysql01 ~]# scp /opt/Test_database.sql root@Testmysql02.domain.com:/opt
[root@Testmysql01 ~]# Exit
Connect to Testmysql02 using ssh
connect to MySQL
[root@Testmysql02~]# MySQL -u root -p
Create Database and grant permission for the user
MySQL> create database Test_database;
MySQL> GRANT ALL ON Test_database.* TO testuser@'%' IDENTIFIED BY 'password';
MySQL> quit
Import Dump to new database
[root@Testmysql02~]# MySQL -u root -p Test_DBA < /opt/Test_DBA.sql
[root@Testmysql02~]# Exit
Connect to webserver using RDP.
Navigate to C:\inetpub\Test_database\Test_dat
abase\config
Edit file config.php and change 'dbhost' => in the file to Testmysql02.domain.com.
verify 'dbuser' => and 'dbpassword' => is defined correctly.
restart the site from IIS manager
connect to Test_database site and verify everything is working fine.
Upgrading SQL Server 2014 Standard to Enterprise Edition
Upgrade SQL Server 2014 Standard to Enterprise document.. Download
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.
Prerequisites
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.
Step-by-Step Upgrade Process
Step 1: Launch SQL Server Setup
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.exefrom the installation media.
Step 2: Select Edition Upgrade Option
In the SQL Server Installation Center, select Maintenance.
Click Edition Upgrade.
Step 3: Validate Installed Instance
The Edition Upgrade wizard will detect installed instances.
Select the instance of SQL Server 2014 Standard that you want to upgrade.
Click Next.
Step 4: Enter the Enterprise Edition Product Key
Enter your valid Enterprise Edition product key.
Click Next.
Step 5: Accept the License Terms
Read the License Agreement.
Accept the terms and click Next.
Step 6: Perform Rule Checks
The setup will run pre-upgrade checks.
Resolve any reported issues before proceeding.
Click Next once all checks pass.
Step 7: Upgrade the Edition
Click Upgrade to initiate the process.
Wait for the upgrade to complete.
Once completed, click Close.
Post-Upgrade Steps
Step 8: Verify Upgrade Success
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.
Step 9: Restart SQL Services
Open SQL Server Configuration Manager.
Restart the SQL Server services to apply changes.
Step 10: Validate Application and Database Performance
Test critical applications for compatibility.
Check for any performance degradation.
Monitor error logs for potential issues.
Step 11: Enable Enterprise Features (Optional)
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.
Additional Resources
Have you recently performed an SQL Server upgrade? Share your experience in the comments below!
Listing All Usernames and Roles for All Databases in SQL Server
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.
Why is This Important?
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.
SQL Script: Listing Usernames and Roles
USE MASTER
GO
BEGIN
DECLARE @SQLVerNo INT;
SET @SQLVerNo = cast(substring(CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0,charindex('.',CAST(Serverproperty('ProductVersion') AS VARCHAR(50)) ,0)) as int);
IF @SQLVerNo >= 9
IF EXISTS (SELECT TOP 1 * FROM Tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE
IF @SQLVerNo = 8
BEGIN
IF EXISTS (SELECT TOP 1 * FROM Tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%')
DROP TABLE #TUser
END
CREATE TABLE #TUser (
ServerName varchar(256),
DBName SYSNAME,
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name SYSNAME NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
[sid] VARBINARY(85))
IF @SQLVerNo = 8
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
@@SERVERNAME,
''?'' as DBName,
u.name As UserName,
CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName,
l.name AS LoginName,
NULL AS Default_db_Name,
NULL as default_Schema_name,
u.uid,
u.sid
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
JOIN [?].dbo.sysUsers r
ON m.groupuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
ORDER BY u.name
'
END
ELSE
IF @SQLVerNo >= 9
BEGIN
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
@@SERVERNAME,
''?'',
u.name,
CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM [?].sys.database_principals u
LEFT JOIN ([?].sys.database_role_members m
JOIN [?].sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN [?].sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R''
order by u.name
'
END
SELECT *
FROM #TUser
ORDER BY DBName, [name], GroupName
DROP TABLE #TUser
END
Subscribe to:
Posts (Atom)