Product:

Microsoft SQL Azure database

Issue:

Can not delete a user in the database from SSMS.

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Solution:

Go to inside SSMS, check what schema is with this command (replace donald with the login you want to check):

select * from information_schema.schemata
where schema_owner = 'donald'

Change the schema stage to use DBO instead with command:

 ALTER AUTHORIZATION ON SCHEMA::[Stage] TO [dbo]

After the schema is changed to have a different owner, then the user can be dropped in SSMS.

More Information:

https://www.mssqltips.com/sqlservertip/2620/steps-to-drop-an-orphan-sql-server-user-when-it-owns-a-schema-or-role/

To list all user in database enter:

 SELECT DB_NAME(DB_ID()) as DatabaseName, * FROM sys.sysusers

Product:
Microsoft Azure Data Factory
Issue:
How schedule the ADF main pipeline to run every hour?

Solution:

Inside ADF  ( https://adf.azure.com/en/authoring/pipeline/Main ) under Factory Resources select you pipeline named “main”.
Click on the Trigger(1) icon to get the dropdown that show “New/Edit”, select that. (The number show how many triggers you got running)

Click the arrow at choose trigger to get the New icon to select.

Enter the name for your trigger.
Edit the start date to sometime next day – easy to only write the numbers in this field. It will start at the time specified, so set 1:00:00 AM to make it run at every whole hour. If you set it to 1:30:00 will make it run at 1:30 and then 2:30 etc.
Select a time zone that is the one you are in – otherwise it will run at for you unexpected time.

Set the Recurrence to 1 hour, if you want it to run each hour.
Note: that it is marked to Start.
And click OK.

You need to click OK one more time.

You need to click on the Publish All button in ADF, to make the change be uploaded and activated.

To check your trigger the next day, go to Monitor icon and Trigger runs. Here will be a list of the last 24 hours trigger runs and if they where successful.

To edit a existing Trigger – go to the pipeline and select again the Trigger drop-down and New/Edit link.

Click on your trigger name to get it up in Edit dialog. Here you can change the name and time it will run – select a start date and time in the future.

Ensure it is started, before you press OK.
Then you have to Publish your changes again to ADF, to make it happen.

As Azure change the layout all the time, the dialogs may look different when you read this.

More Information:

https://www.mssqltips.com/sqlservertip/6062/create-schedule-trigger-in-azure-data-factory-adf/ 

https://www.mssqltips.com/sqlservertutorial/9402/azure-data-factory-scheduling-and-monitoring/ 

https://www.sqlshack.com/how-to-schedule-azure-data-factory-pipeline-executions-using-triggers/ 

https://www.serverlessnotes.com/docs/schedule-azure-data-factory-pipeline-executions 

https://learn.microsoft.com/en-us/azure/data-factory/how-to-create-schedule-trigger?tabs=data-factory 

Product:
Azure Data Factory (ADF)

Issue:
Can not connect to SQL server from ADF in same subscription.

Cannot connect to SQL Database: ‘databaseservername.database.windows.net‘, Database: ‘databasename‘, Reason: Connection was denied since Deny Public Network Access is set to Yes. To connect to this server,
1. If you persist public network access disabled, please use Managed Virtual Network IR and create private endpoint. https://docs.microsoft.com/en-us/azure/data-factory/managed-virtual-network-private-endpoint; https://docs.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal-private;
2. Otherwise you can enable public network access, set “Public network access” option to “Selected networks” on Auzre SQL Networking setting.

Solution:

Prompt yourself to Owner in the subscription and ADF.
Inside ADF you need first ensure that Integration Run-times are using a Managed Virtual Network. Create a new Integration runtime setup,
select Azure. self-hosted.
select Azure
set region to your needs, and click create.

When this use Managed Virtual Network, you can go and create the Linked Service.
Select Azure SQL database.
In connect drop-down select the above created integrationruntime2 that have managed virtual network.
Select your Azure Subscription.
Select your database server name from the drop-down.
Select the database name.
Select the Authentication type to be “System Assigned Managed Identity”
Click Test, and if there is OK, click Create.

Then you may inside SSMS add the ADF managed user to the database with command similar to this:

In master database:

CREATE login [adf_user] FROM EXTERNAL PROVIDER

CREATE USER [adf_user] FROM LOGIN [adf_user] WITH DEFAULT_SCHEMA=[dbo]

In user database:

CREATE USER [adf_user] FROM LOGIN [adf_user]

ALTER ROLE [db_owner] ADD MEMBER [adf_user]

More Information:

https://learn.microsoft.com/en-us/azure/data-factory/managed-virtual-network-private-endpoint

https://lazyadmin.nl/office-365/how-to-use-azure-managed-identity/ 

https://www.inthecloud247.com/configure-a-user-assigned-managed-identity-the-basics/ 

Product:

Microsoft SQL 2019 server standard
Microsoft Windows 2022 server datacenter

Issue:

How take a backup that does not affect the night differentiation backups etc ?

Solution:

Take a full backup and select copy-only backup in general.

In this example, a copy-only backup of the test database is backed up to disk at the default backup location.

1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2. Expand Databases, right-click test, point to Tasks, and then select Back Up….
3. On the General page in the Source section, check the Copy-only backup checkbox.
4. Select OK.

More Information:

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-ver16

https://www.mssqltips.com/sqlservertip/1772/copy-only-backup-for-sql-server/ 

https://www.ninjaone.com/blog/copy-only-backup-in-sql-servers-explained/ 

Product:

Microsoft SQL server Azure

Issue:

How to sum the amount in one column?

Solution:

Inside SSMS enter this, to sum column [amount] all rows that have active=”y’ and date = 20221201.

SELECT SUM(amount)
FROM [DM].[table1]
where [Active] = 'Y'
and [sDate] = '20221201'

To list all rows from two identical tables, including duplicates enter:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2

List number of rows from a table, from a selection:

select count(*)
FROM [DM].[table1] WHERE [Active] = 'Y'

How copy a number of rows into a new table:

SELECT *
INTO [DM].[table1]
FROM [DM].[table2]
WHERE [active] = 'N';

More information:

https://www.w3schools.com/sql/sql_sum.asp

https://www.w3schools.com/sql/sql_union.asp

https://www.w3schools.com/sql/sql_update.asp

Product:

Microsoft SQL Azure

Issue:

Want a new column on a table, that is needed only for the SP.

Want two column to be a new one, so we do not need to join on the two columns later.

Suggested Solution:

In a separate query , that you run before your SP,  check if the column exist and if not create it with SQL like this:

IF COL_LENGTH('[Stage].[tablename]','cDate') IS NOT NULL
PRINT 'Column Exists';
ELSE
BEGIN;
ALTER TABLE [Stage].[tablename] ADD cDate AS ( Year + '-' + Month + '-01') PERSISTED;
END;

Or even this will work:

IF COL_LENGTH('[Stage].[tablename]','cDate') IS NULL
BEGIN;
ALTER TABLE [Stage].[tablename] ADD cDate AS ( Year + '-' + Month + '-01') PERSISTED;
END;

You can find out better SQL to this problem.

Should give a new column with data:  2023-09-01, if the Year contain 2023 and Month contain 09.

Then you can compare a date formatted column with your cDate column.

PERSISTED will store the value of the calculation in the table, so it does not need to be calculated when asked for later.

More Information:

https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver16 

https://www.sqlshack.com/an-overview-of-computed-columns-in-sql-server/ 

SQL Server Computed Columns

https://database.guide/add-a-computed-column-to-an-existing-table-in-sql-server/ 

https://www.sqlservercentral.com/articles/using-computed-columns 

https://www.tsql.info/ex/sql-check-if-column-exists-in-a-table.php 

https://www.geeksforgeeks.org/how-to-check-if-a-column-exists-in-a-sql-server-table/ 

https://blog.sqlauthority.com/2017/07/29/sql-server-check-column-exists-sql-server-table/ 

https://www.sqlshack.com/sql-if-statement-introduction-and-overview/ 

Product:
Microsoft SQL Azure

Issue:

The process need more CPU, can i change the DTU on the AZURE SQL server from inside the SQL store procedure?

Solution:

Yes, use command:

ALTER DATABASE [databasename] MODIFY (EDITION = 'Standard', MAXSIZE = 250 GB, SERVICE_OBJECTIVE = 'S4');

You have to test what level is needed for your SQL query’s.

DTU Model is a preconfigured blend of compute, storage, and IO resources at a fixed price. This is the simpler option that is great for paying a fixed amount each month for a preconfigured bundles of resources.

DTU stands for Database Transaction Unit. DTUs give you a way to compare database performance across the service tiers offered by Azure. DTUs roughly measure performance as a combination of CPU, Memory, Reads, and Writes.

100 DTU S3 is less than one CPU core in a on-prem database.

To see current Azure level in a database:

SELECT Edition = DATABASEPROPERTYEX('databasename', 'EDITION'),
ServiceObjective = DATABASEPROPERTYEX('databasename', 'ServiceObjective'),
MaxSizeInBytes = DATABASEPROPERTYEX('databasename', 'MaxSizeInBytes');

More Information:

https://legacysupport.timextender.com/hc/en-us/articles/360026418592-What-Azure-SQL-Database-Service-Tier-and-Performance-Level-Should-I-Use- 

Azure SQL Database DTU Versus vCore

https://sqlperformance.com/2017/03/azure/what-the-heck-is-a-dtu 

https://www.fmsinc.com/microsoft-azure/sql-server/monitoring.htm

https://techcommunity.microsoft.com/t5/azure-sql-blog/real-world-azure-sql-db-unexpected-database-maximum-size-limit/ba-p/305363 

Product:

Microsoft SQL Azure

Issue:

What processes are running on the SQL database server?

Solution:

In SSMS enter this:

select 
r.session_id, 
s.login_name, 
c.client_net_address, 
s.host_name, 
s.program_name, 
st.text, s.status 
from sys.dm_exec_requests r 
inner join sys.dm_exec_sessions s 
on r.session_id = s.session_id 
left join sys.dm_exec_connections c 
on r.session_id = c.session_id 
outer apply sys.dm_exec_sql_text(r.sql_handle) st 
where client_net_address is not null and text is not null and s.status = 'running'

Copy the content of the text column out to notepad to see the query running in detail.

To see if it is CPU or I/O that is used most the last hour, enter in SSMS this:

SELECT
database_name = DB_NAME()
, UTC_time = end_time
, 'CPU Utilization In % of Limit' = rs.avg_cpu_percent
, 'Data IO In % of Limit' = rs.avg_data_io_percent
, 'Log Write Utilization In % of Limit' = rs.avg_log_write_percent
, 'Memory Usage In % of Limit' = rs.avg_memory_usage_percent 
, 'In-Memory OLTP Storage in % of Limit' = rs.xtp_storage_percent
, 'Concurrent Worker Threads in % of Limit' = rs.max_worker_percent
, 'Concurrent Sessions in % of Limit' = rs.max_session_percent
FROM sys.dm_db_resource_stats AS rs --past hour only
ORDER BY rs.end_time DESC;

Data IO show 100%, here is the bottleneck, maybe the SQL QUERY PLAN is using more TEMPDB than it should.

To see what SQL plan is in use, install, and use:

sp_blitzwho @GetLiveQueryPlan=1

Click on live-query plan, and you will get the SQL plan in used shown inside SSMS.

More Information:

https://learn.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use?view=azuresql 

https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs?view=azuresql

https://www.brentozar.com/archive/2018/07/cxconsumer-is-harmless-not-so-fast-tiger/ 

https://github.com/amachanic/sp_whoisactive/releases 

https://www.brentozar.com/first-aid/sp_blitzwho/ 

https://www.mssqltips.com/sqlservertip/4132/correct-sql-server-tempdb-spills-in-query-plans-caused-by-outdated-statistics/

Product:
Microsoft SQL 2016 server

Issue:

How create a new database from a BACPAK file in SSMS?

Solution:

Start SSMS and connect to the target SQL server.

On the database select “Import Data-Tier Application”

Click Next

Select the BACPAC file to import and click Next

Enter the name of the new database and click Next

Click Finish

Wait during the import.

When Operation Complete click Close.

You have your new database in the new server, including data.

More Information:

https://4sysops.com/archives/dacpac-and-bacpac-in-sql-server/ 

https://blogs.msmvps.com/deborahk/deploying-a-dacpac-with-sql-server-management-studio/ 

https://www.sqlshack.com/importing-a-bacpac-file-for-a-sql-database-using-ssms/ 

A DAC is a logical database management entity that defines all of the SQL Server objects which associates with a user’s database. A BACPAC includes the database schema as well as the data stored in the database.

Product:
Microsoft SQL server 2016 standard
Microsoft Windows 2016 server

Problem:
How load data from csv file into SQL server, where the table contains more columns than the text file?

The BULK INSERT command will fill the not used target columns with the next row of data, and you get a inconsistent table.

Solution:

Create a view of the table, where you have less columns shown, and then bulk insert to the view.

From inside SSMS script out a select of the table, and add create view to first line, remove the columns you do not want.

CREATE VIEW StorageView AS
SELECT [Organisation]
,[Version]
,[Belopp]
FROM StorageTable

Then in your BULK INSERT us the view instead, so the csv files number of columns match the (view) target tables number of columns.

BULK INSERT [StorageView]
FROM 'C:\temp\storagefile.csv'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\r',
FIRSTROW = 2
);

FIELDTERMINATOR = set the separator for the columns in the csv file
ROWTERMINATOR = set the character to skip to next row/record
FIRSTROW = tell that first line in the csv file is headers and should not be read

If you get a error like this:

Msg 4832, Level 16, State 1, Line 16
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 16
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 16
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.

Then the csv file have a blank line as the last line, edit your csv file to not contain any empty lines.

More Information:

https://www.sqlservertutorial.net/sql-server-administration/sql-server-bulk-insert/ 

https://www.w3schools.com/sql/sql_view.asp

https://www.w3schools.com/sql/sql_update.asp

https://www.mssqltips.com/sqlservertip/6109/bulk-insert-data-into-sql-server/