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.

Read More

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.

Read More

SQL Server DBA (Bundle)

Dear Aspiring SQL DBA's,

We are thrilled to share some exciting news with you! At SQL DBA Champs, we believe in the power of continuous learning and growth. That's why we're introducing our exclusive prelaunch bundle of SQL DBA Topics designed to empower you on your educational journey on SQL Server Administration.

https://www.sqldbachamps.com/courses/SQL-Server-DBA-Bundle-655f902de4b00124bdac18ea-655f902de4b00124bdac18ea

**NOTE: All videos of all modules recording is in progress and will be uploaded completely by 31st January 2024**

**🌟 What's in the Prelaunch Bundle?**

Our carefully curated bundle of DBA Topics tailored to meet your needs and elevate your skillset in Microsoft SQL Server Database Administration. 

These Topics have been crafted by industry experts to ensure you gain practical knowledge and insights that make a real impact.

- **60-Day Full Refund Guarantee:** 

We are so confident in the value our courses provide that we are offering a full refund within the first 60 days if you're not completely satisfied. 

Your success is our priority.

**✨ How to Enroll:**

1. Visit our website https://www.sqldbachamps.com and explore the details of the prelaunch bundle.

2. Select the courses that align with your goals and interests.

https://www.sqldbachamps.com/courses/SQL-Server-DBA-Bundle-655f902de4b00124bdac18ea-655f902de4b00124bdac18ea

**NOTE: All videos of all modules recording is in progress and will be uploaded completely by 31st January 2024**

**🤝 Our Commitment:**

At sqldbachamps, we are committed to your success. We've designed these SQL DBA Topics with your journey in mind, and we're confident that you'll find immense value in each module. However, if you don't feel the impact within the first 60 days, we'll gladly issue a full refund—no questions asked.

**📆 Limited Availability:**

This exclusive offer is available until [31st December 2023]. 

Don't miss out on this chance to invest in yourself and take the first step toward achieving your goals.

Thank you for being part of the SQL DBA Champs community. We're excited to embark on this learning journey with you!

Enroll now and unlock your full potential! 

https://www.sqldbachamps.com/courses/SQL-Server-DBA-Bundle-655f902de4b00124bdac18ea-655f902de4b00124bdac18ea

**NOTE: All videos of all modules recording is in progress and will be uploaded completely by 31st January 2024**

Best regards,

Praveen Madupu

Mb: +91-986 613 0093 (Whatsapp)

Mail: praveensqldba12@gmail.com

Read More

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'

Read More

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

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

Read More

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

   

Read More

send the result of stored proceedure via email

declare @subject varchar(1000)

set @subject = 'Space Alerts on FPVI-Xweb1'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'abc@xyz.com',
@query = 'EXEC [FPVI-XWEB1].Master.dbo.XP_FixedDrives',
@subject = @subject
GO

Read More