Video Resources for HA/DR (Always On)

Posted: April 6, 2022 | Author: | Filed under: Uncategorized | Leave a comment

I have followed Russ Thomas courses on Pluralsight on SQL Admin topics.
The check list he suggested is a good starting point to follow along.
You may not need to memeorize the details for the topic but you can have a place to follow along to get the project done.
From an early video: Practical SQL Server High Availability and Disaster Recovery
https://app.pluralsight.com/library/courses/sql-server-high-availability-disaster-recovery/table-of-contents
A quick list to get the always On implemented along with details of other HA/DR solutions:
Mirrors, Log Shipping, FailOver Clustering.

You can find the Check list from course material:

https://app.pluralsight.com/library/courses/sql-server-microsoft-administering-availability-groups/table-of-contents

In the course, he touches many aspects of administrations for SQL Availability Groups. It is a good source to check back when I need more information
or use a a pointer to find other documents for further reading.

Use .bacpac file to export your database in script

Posted: April 5, 2022 | Author: | Filed under: Uncategorized | Leave a comment

You can export your database as a .bacpac(data and schema) file from SSMS.

Right click on your database name>>Tasks>>Export Data-Tier Application (.bacpac file) This file has all your data and schema.

You can import this file as new database if you want to.

“Using the Import Data-tier Application Wizard
To launch the wizard, use the following steps:

Connect to the instance of SQL Server, whether on-premises or in SQL Database.

In Object Explorer, right-click on Databases, and then select the Import Data-tier Application menu item to launch the wizard.
….
“

Datetime or Date and Time in SQL Server

Posted: April 4, 2022 | Author: | Filed under: Uncategorized | Leave a comment
Drop table if exists DateTimeTable
Drop table if exists DateTableTime
Create table DateTimeTable (id int identity(1,1) primary key, mydatetime datetime)
Create table DateTableTime (id int identity(1,1) primary key, mydate date, mytime time)
–===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1
Where n between 1 and 100),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
Insert into DateTableTime(mydate,mytime)
select Cast(dateadd(minute,n, getdate()) as date) dt,
Cast(dateadd(minute,n, getdate()) as time) tm from Nums
Insert into DateTableTime(mydate,mytime)
select mydate,mytime from DateTableTime
Insert into DateTableTime(mydate,mytime)
select mydate,mytime from DateTableTime
Insert into DateTableTime(mydate,mytime)
select mydate,mytime from DateTableTime
Truncate table [dbo].[DateTimeTable]
insert [dbo].[DateTimeTable]
select try_Cast(concat(mydate ,'T' , mytime) as datetime2 ) as MyDateTime from DateTableTime
–Check queries
Set statistics io,time on
SELECT
SUM(CASE WHEN MyTime >= '09:00:00' AND MyTime < '17:00:00' THEN 1 ELSE 0 END) AS Shift1_Count
,SUM(CASE WHEN MyTime >= '17:00:00' OR MyTime < '01:00:00' THEN 1 ELSE 0 END) AS Shift2_Count
,SUM(CASE WHEN MyTime >= '01:00:00' AND MyTime < '09:00:00' THEN 1 ELSE 0 END) AS Shift3_Count
FROM DateTableTime
WHERE MyDate >= '4/5/2022' AND MyDate < '4/6/2022';
SELECT
SUM(CASE WHEN Cast(MyDateTime as time) >= '09:00:00' AND Cast(MyDateTime as time) < '17:00:00' THEN 1 ELSE 0 END) AS Shift1_Count
,SUM(CASE WHEN Cast(MyDateTime as time) >= '17:00:00' OR Cast(MyDateTime as time) < '01:00:00' THEN 1 ELSE 0 END) AS Shift2_Count
,SUM(CASE WHEN Cast(MyDateTime as time) >= '01:00:00' AND Cast(MyDateTime as time) < '09:00:00' THEN 1 ELSE 0 END) AS Shift3_Count
FROM [dbo].[DateTimeTable]
WHERE MyDateTime >= '4/5/2022' AND MyDateTime < '4/6/2022';
Set statistics io,time off
view raw sql hosted with ❤ by GitHub

A markup test for a blog from

Shift calculations (or same time every day)

Import Excel file(.xlsx) to SQL Server 2019

Posted: April 1, 2022 | Author: | Filed under: Uncategorized | Leave a comment

When you try to import Excel file from SSMS, you may run into this error:

The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine.

You need to make some changes on your machine to import Excel data.

Here are a few things you need to do:

  1. Download 64-bit Microsoft Access Database Engine 2016 Redistributable
    https://www.microsoft.com/en-us/download/details.aspx?id=54920

accessdatabaseengine_X64.exe
and install it on your 64-bit machine.

  1. Use SQL Server 2019 Import and Export Data (64-bit) directly from Startup menu:
    SQL Server 2019

SQL Server 2019 Import and Export Data (64-bit)
(you can pin the Import and Export Wizard to Start for easy access)

Click to launch the app
Import your Excel file with Excel version “Microsoft Excel 2016” option.

Another option is to add an entry from Tools>>External tools to point to this 64-bit version:

C:\Program Files\Microsoft SQL Server\150\DTS\Binn\DTSWizard.exe

For example: I have added one entry ImportExcel64 to point to this 64 bit executable.

You can use this entry to start import Excel from SSMS to launch your import process.

https://learn.microsoft.com/en-us/answers/questions/512631/cannot-import-excel-files-to-ssms-but-possible-usi.html