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/

Product:

Microsoft SQL Azure server

Issue:

Need to change a column to have nvarchar(20) instead of nvarchar(200). How change nvarchar in table?

Solution:

Check the data length in the column (nvarchar is unicode, that uses 2 bytes for a single character):

SELECT DATALENGTH(Column_Name) AS FIELDSIZE, Column_Name 
FROM Table_Name
ORDER by FIELDSIZE DESC

Change the columns value with:

Update Table_Name set Column_Name = left(coalesce(Column_Name ,''),20);
Alter table Table_Name alter column Column_Name nvarchar(20) not null;

If you have constrains, you need to drop them first.  DROP INDEX index_name  ON table_name  ;

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

Otherwise, rename the table;

sp_rename 'old_table_name', 'new_table_name'

Creata a new table with the old name, in SSMS you select the table and from menu select “Script table as” – create to – new query editor window. Adjust the code to have the new nvarchar value, example below:

CREATE TABLE [dbo].[table2](
[index] [int] NOT NULL,
[Name] [nvarchar](20) NULL,
[Country] [nvarchar](50) NULL,
[Employess] [int] NULL
) ON [PRIMARY]

Copy the data over to the new table

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition; 

For above table the example is as below, the left(coalesce([Name] ,”),20) make that we only copy the 20 first characters.

 insert into table2 ( [index],[Name]
,[Country]
,[Employess])
select [index] ,left(coalesce([Name] ,''),20)
,[Country]
,[Employess] from table1

If you not use the LEFT function you may get a error like “String or binary data would be truncated”.

More Information:

https://javarevisited.blogspot.com/2016/03/how-to-increase-length-of-existing-VARCHAR-column-in-SQL-Server.html 

https://www.sqlservertutorial.net/sql-server-basics/sql-server-nvarchar/

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver16 

How to rename tables in SQL Server with the sp_rename command

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

https://www.tutorialrepublic.com/sql-tutorial/sql-cloning-tables.php

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

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

As index is a reserved command in SQL, you need to have it inside [ ] for SQL to understand it is a table name.

Product:
Microsoft SQL Azure

Issue:
System is slow, and it looks like TEMPDB is working hard. What can we do?

Solution:

Check what is going on in SQL TEMPDB with this query:

SELECT [Source] = 'database_transactions',
[session_id] = ST.session_id,
[transaction_id] = ST.transaction_id,
[login_name] = S.login_name,
[database_id] = S.database_id,
[program_name] = S.program_name,
[host_name] = S.host_name,
[database_id] = DT.database_id,
[database_name] = CASE
WHEN D.name IS NULL AND DT.database_id = 2 THEN 'TEMPDB'
ELSE D.name
END,

[log_reuse_wait_desc] = D.log_reuse_wait_desc,
[database_transaction_log_used_Kb] = CONVERT(numeric(18,2), DT.database_transaction_log_bytes_used / 1024.0 ),
[database_transaction_begin_time] = DT.database_transaction_begin_time,
[transaction_type_desc] = CASE DT.database_transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END,

[transaction_state_desc] = CASE DT.database_transaction_state
WHEN 1 THEN 'The transaction has not been initialized.'
WHEN 2 THEN 'The transaction is active'
WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
WHEN 4 THEN 'The transaction has generated log records.'
WHEN 5 THEN 'The transaction has been prepared.'
WHEN 10 THEN 'The transaction has been committed.'
WHEN 11 THEN 'The transaction has been rolled back.'
WHEN 12 THEN 'The transaction is being committed. (The log record is being generated, but has not been materialized or persisted.)'
END,

[active_transaction_type_desc] = CASE AT.transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction'
END,

[active_transaction_state_desc] = CASE AT.transaction_state
WHEN 0 THEN 'The transaction has not been completely initialized yet.'
WHEN 1 THEN 'The transaction has been initialized but has not started.'
WHEN 2 THEN 'The transaction is active'
WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
WHEN 6 THEN 'The transaction has been committed.'
WHEN 7 THEN 'The transaction is being rolled back.'
WHEN 8 THEN 'The transaction has been rolled back.'
END

FROM sys.dm_tran_database_transactions DT
INNER JOIN sys.dm_tran_session_transactions ST ON DT.transaction_id = ST.transaction_id
INNER JOIN sys.dm_tran_active_transactions AT ON DT.transaction_id = AT.transaction_id
INNER JOIN sys.dm_exec_sessions S ON ST.session_id = S.session_id
LEFT JOIN sys.databases D ON DT.database_id = D.database_id
WHERE DT.database_id = 2 -- tempdb
ORDER BY ST.session_id, DT.database_id;

More Information:

https://techcommunity.microsoft.com/t5/azure-database-support-blog/azure-sql-db-and-tempdb-usage-tracking/ba-p/1573220 

https://techcommunity.microsoft.com/t5/azure-database-support-blog/resolve-tempdb-related-errors-in-azure-sql-database/ba-p/3597944