SQL Server Database Administrator (DBA) PAID Learnings
SQL Server Performance Tuning and Query Optimization:
Trainer: Aravinth M
SQL Server 2019 AG Setup (Dev Edition) - VM Images:
Tuesday, 19 November 2024
Wednesday, 8 May 2024
Are you passionate about sharing your expertise and helping others learn ?
**Dear IT Professionals**
Are you passionate about sharing your expertise and helping others learn?
Do you have valuable knowledge and skills that you'd like to impart to a global audience?
If so, we invite you to become to become an trainer on our LMS platform: https://www.sqldbachamps.com
SQLDBAChamps is an upcoming online learning platform dedicated to providing high-quality courses in various IT disciplines.
As a trainer on our LMS platform, you'll have the opportunity to:
- Reach a Global Audience: Share your expertise with learners from around the world who are eager to enhance their IT skills and advance their careers.
- Flexible Schedule: Create and deliver courses on your own schedule, allowing you to balance teaching with your other professional commitments.
- Earn Passive Income: Generate revenue by monetizing your courses through our platform's built-in payment system.
- Build Your Brand: Establish yourself as a thought leader in your field and expand your professional network by connecting with learners and fellow trainers.
Whether you're an experienced IT professional looking to share your knowledge or a seasoned trainer seeking a new platform to reach a broader audience, SQLDBAChamps provides the tools and support you need to succeed.
To get started, please update your details using below link and to get register as an IT Trainer.
Our user-friendly interface makes it easy to create and publish courses, interact with students, and track your progress.
Join our community of educators and empower the next generation of IT professionals.
Together, we can make a difference in the world of online learning.
Thank you for considering this opportunity. We look forward to welcoming you aboard!
Best regards,
Praveen Madupu
Sr SQL Server DBA
Mb: +91 98661 30093
Sunday, 3 December 2023
Checklist for SQL Server Migration
Migrating a SQL Server instance involves moving databases and settings from one server to another. This process could be part of a hardware upgrade, moving to a new environment, or transitioning to a new SQL Server version.
Here's a checklist to guide you through the SQL Server migration process:
**1. Assessment and Planning:**
- Identify the goals and reasons for migration.
- Document the existing SQL Server environment, including configurations, databases, and settings.
- Review system requirements for the new SQL Server version.
- Assess potential challenges and risks.
**2. Backup and Disaster Recovery:**
- Perform a full backup of all user databases.
- Backup system databases (master, model, msdb).
- Document and test the disaster recovery plan.
**3. Create a Migration Plan:**
- Outline the step-by-step migration process.
- Identify downtime requirements and plan for minimal disruption.
- Define rollback procedures in case of issues during migration.
**4. Install SQL Server on the New Server:**
- Install the desired version of SQL Server on the new server.
- Apply the latest service packs and cumulative updates.
**5. Configure the New Server:**
- Set server configurations, including instance settings and security.
- Configure SQL Server Agent, linked servers, and other components.
**6. Database Migration:**
- Copy user databases from the old server to the new server.
- Consider using backup and restore, detach and attach, or database mirroring for migration.
- Verify the integrity of the migrated databases.
**7. Migrate System Databases:**
- Migrate system databases (master, model, msdb).
- Recreate logins and jobs on the new server.
**8. Update Connection Strings and Application Configurations:**
- Update connection strings in applications to point to the new SQL Server instance.
- Modify configurations in applications that reference the old server.
**9. Test Applications:**
- Conduct thorough testing of applications to ensure compatibility with the new SQL Server environment.
- Verify that stored procedures, queries, and other database interactions work as expected.
**10. Update DNS and Network Configurations:**
- Update DNS records if applicable.
- Update any network configurations or firewall rules to reflect the new server.
**11. Monitor and Optimize Performance:**
- Monitor server performance after migration.
- Optimize queries and configurations as needed.
**12. Update Documentation:**
- Update documentation to reflect the changes in the SQL Server environment.
- Document new configurations, settings, and any modifications made during the migration.
**13. Backup and Monitor Regularly:**
- Establish a new backup routine on the new server.
- Implement a monitoring system to track server performance and issues.
**14. Update Maintenance Plans and Jobs:**
- Review and update SQL Server Agent jobs, maintenance plans, and other scheduled tasks.
**15. Communicate with Stakeholders:**
- Notify relevant stakeholders about the completion of the migration.
- Provide information on any changes or actions required on their part.
**16. Perform Post-Migration Checks:**
- Conduct post-migration checks to ensure data integrity, security, and overall system stability.
**17. Decommission the Old Server:**
- Once confident in the new server's stability, decommission the old server.
**18. Documentation and Knowledge Transfer:**
- Update documentation to include any lessons learned during the migration.
- Transfer knowledge to relevant team members who will be responsible for ongoing support.
Remember that each migration scenario is unique, and you may need to adapt this checklist based on your specific requirements and constraints. Always refer to the official documentation for the version of SQL Server you are working with for any additional considerations or changes in the migration process.
Checklist for SQL Server Inplace Upgrade
An in-place upgrade of SQL Server involves upgrading an existing instance of SQL Server to a newer version without changing the instance name, server name, or databases. This process can be complex, and it's essential to follow a checklist to ensure a smooth upgrade.
Below is a checklist you can use for an in-place upgrade of SQL Server:
**1. Review System Requirements:**
- Verify that the hardware and operating system meet the requirements of the new SQL Server version.
**2. Backup:**
- Perform a full backup of all user databases.
- Take a backup of system databases (master, model, msdb).
**3. Document Configuration:**
- Document server configurations, including instance settings, linked servers, and SQL Server Agent jobs.
**4. Review Deprecated Features:**
- Check for any deprecated features in the current version that may not be supported in the new version.
**5. Check for Compatibility Issues:**
- Use the SQL Server Upgrade Advisor to identify any potential issues with the upgrade.
**6. Stop Non-Essential Services:**
- Stop any non-essential services or applications that might be accessing the SQL Server instance.
**7. Ensure Sufficient Disk Space:**
- Verify that there is enough disk space for the upgrade process.
**8. Disable Maintenance Plans and Jobs:**
- Temporarily disable any maintenance plans and SQL Server Agent jobs.
**9. Review and Update Applications:**
- Check for compatibility with the new version and update applications if necessary.
**10. Check for Blocking Issues:**
- Identify and address any long-running or blocking queries before the upgrade.
**11. Ensure Windows and SQL Server Authentication:**
- Confirm that both Windows and SQL Server authentication modes are enabled.
**12. Check Collation Settings:**
- Ensure that the collation settings for the new version match the existing settings.
**13. Temporarily Disable SQL Server Agent:**
- Stop the SQL Server Agent service during the upgrade.
**14. Verify Database Compatibility Level:**
- Ensure that the compatibility level of all databases is suitable for the new SQL Server version.
**15. Validate Linked Servers:**
- Confirm that linked servers are working correctly.
**16. Check Resource Usage:**
- Monitor resource usage on the server and address any performance concerns.
**17. Test the Upgrade in a Staging Environment:**
- Whenever possible, perform a test upgrade in a staging environment to identify and address potential issues.
**18. Review and Apply Service Packs and Cumulative Updates:**
- Ensure that the latest service packs and cumulative updates for the current version are applied before starting the upgrade.
**19. Document SQL Server Agent Jobs:**
- Document SQL Server Agent jobs, including schedules and configurations.
**20. Plan for Downtime:**
- Schedule the upgrade during a maintenance window to minimize downtime.
**21. Perform the Upgrade:**
- Run the SQL Server setup program and follow the prompts to perform the in-place upgrade.
**22. Post-Upgrade Testing:**
- After the upgrade, thoroughly test the SQL Server instance and applications to ensure everything is working as expected.
**23. Monitor Performance:**
- Monitor the performance of the upgraded instance to identify and address any issues that may arise post-upgrade.
**24. Update Statistics:**
- Update statistics for all databases.
**25. Update SQL Server Agent Jobs and Maintenance Plans:**
- Re-enable and update SQL Server Agent jobs and maintenance plans as needed.
**26. Verify Backups:**
- Confirm that backups are functioning correctly after the upgrade.
**27. Communicate with Stakeholders:**
- Notify relevant stakeholders about the completion of the upgrade.
Remember that this checklist serves as a general guideline, and you may need to customize it based on your specific environment and requirements. Always refer to the official documentation for the version of SQL Server you are upgrading to for any additional considerations or changes in the upgrade process.
Saturday, 14 December 2019
Find Status or percentage completed of any DBCC Shrink Databse files
1.First try to find the space used and empty space left in databases
SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
-- DB size.
EXEC sp_spaceused
--- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT *
FROM #t
-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #t
DROP TABLE #t
2. Find the Status or percentage completed of any DBCC Shrink Databse files with below query
SELECT
percent_complete,
start_time,
status,
command,
estimated_completion_time,
cpu_time,
total_elapsed_time
FROM
sys.dm_exec_requests
WHERE
command = 'DbccFilesCompact'
Backup databases on SQL server Express edition
please see below on how to create backups on SQL Express edition,
SQL Server express edition do not have SQL agent .
1.create a linked server from any non express edition ,make sure you have sys admin previlage on the sql login you use
2.create SP(as below) on Express edition master database.
3.Schedule a JOB to call SP from non express edition.
use master
1.create a linked server from any non express edition ,make sure you have sys admin previlage on the sql login you use
2.create SP(as below) on Express edition master database.
3.Schedule a JOB to call SP from non express edition.
use master
go
Create PROC SP_Backup
AS
BEGIN
DECLARE @path1 VARCHAR(100)
Declare @DeleteDate datetime
SET @Path1='R:\'
SET @DeleteDate= DATEADD(day, -2, GETDATE())---older than 2 days
EXEC master.sys.xp_delete_file 0,@path1,'BAK',@DeleteDate,0;
DECLARE @destination VARCHAR(300),
@now VARCHAR(50),
@now_date DATETIME,
@exec_str VARCHAR(200),
@db_name VARCHAR(30),
@path VARCHAR(100)
SET @Path='R:\'--Location of the files going to be
SELECT @now_date = GETDATE()
SELECT @Now = RIGHT(CONVERT(VARCHAR(4),DATEPART(yy,@now_date)),4) + RIGHT( '00' + CONVERT(VARCHAR(2),DATEPART(mm,@now_date)),2) +
RIGHT( '00' + CONVERT(varchar(2),DATEPART(dd,@now_date)),2) +'_'+ RIGHT( '00' + CONVERT(varchar(2),DATEPART(hh,@now_date)),2) +
RIGHT( '00' + CONVERT(varchar(2),DATEPART(mi,@now_date)),2) + RIGHT( '00' + CONVERT(varchar(2),DATEPART(ss,@now_date)),2)
SET NOCOUNT ON
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.sys.databases
where name not in ('tempdb')
ORDER BY name
OPEN db_cursor
FETCH db_cursor INTO @db_name
WHILE (@@fetch_status = 0)
BEGIN
SELECT @destination = 'R:\' + @db_name + '_Full_' + @Now +'.bak'
SELECT @exec_str = 'BACKUP DATABASE ' + @db_name + ' TO DISK = ''' + @destination + ''' WITH COMPRESSION'
--PRINT @exec_str
EXEC (@exec_str)
FETCH db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
Create PROC SP_Backup
AS
BEGIN
DECLARE @path1 VARCHAR(100)
Declare @DeleteDate datetime
SET @Path1='R:\'
SET @DeleteDate= DATEADD(day, -2, GETDATE())---older than 2 days
EXEC master.sys.xp_delete_file 0,@path1,'BAK',@DeleteDate,0;
DECLARE @destination VARCHAR(300),
@now VARCHAR(50),
@now_date DATETIME,
@exec_str VARCHAR(200),
@db_name VARCHAR(30),
@path VARCHAR(100)
SET @Path='R:\'--Location of the files going to be
SELECT @now_date = GETDATE()
SELECT @Now = RIGHT(CONVERT(VARCHAR(4),DATEPART(yy,@now_date)),4) + RIGHT( '00' + CONVERT(VARCHAR(2),DATEPART(mm,@now_date)),2) +
RIGHT( '00' + CONVERT(varchar(2),DATEPART(dd,@now_date)),2) +'_'+ RIGHT( '00' + CONVERT(varchar(2),DATEPART(hh,@now_date)),2) +
RIGHT( '00' + CONVERT(varchar(2),DATEPART(mi,@now_date)),2) + RIGHT( '00' + CONVERT(varchar(2),DATEPART(ss,@now_date)),2)
SET NOCOUNT ON
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.sys.databases
where name not in ('tempdb')
ORDER BY name
OPEN db_cursor
FETCH db_cursor INTO @db_name
WHILE (@@fetch_status = 0)
BEGIN
SELECT @destination = 'R:\' + @db_name + '_Full_' + @Now +'.bak'
SELECT @exec_str = 'BACKUP DATABASE ' + @db_name + ' TO DISK = ''' + @destination + ''' WITH COMPRESSION'
--PRINT @exec_str
EXEC (@exec_str)
FETCH db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
Notify if a database backup missing
declare @ServerName varchar(250)
declare @DatabaseList varchar(4000)
declare @CountMissingBackups int
declare @MailSubject varchar(250)
declare @MailText varchar (4000)
;WITH CTE as
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server],
msdb.dbo.backupset.database_name
FROM msdb.dbo.backupset
WHERE msdb.dbo.backupset.type = 'D'
GROUP BY msdb.dbo.backupset.database_name
HAVING (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))
UNION
--Databases without any backup history
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server],
master.dbo.sysdatabases.NAME AS database_name
FROM
master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset
ON master.dbo.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb'
--ORDER BY msdb.dbo.backupset.database_name
)
select @CountMissingBackups = count(*), @ServerName = [Server] ,@Databaselist = COALESCE(@DatabaseList + ', ', '') + CAST(database_name AS varchar(250))
from CTE
group by [Server], database_name
-- send the mail
if @CountMissingBackups > 0
begin
set @MailSubject = 'Backups on '+@ServerName
set @MailText = 'The following databases have not been backed up in the past 24 hours: '+@Databaselist
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Default',
@recipients='abc@XYZcom',
@subject=@MailSubject,
@body=@MailText
end
Subscribe to:
Posts (Atom)