Product:

Microsoft Azure SQL

Issue:

How list size of database?

Solution:

Login to SSMS, go to your database and enter command:

EXEC sp_spaceused;

 

Not all commands are allowed in Azure SQL, as it is a PAAS solution. See here for diffrences:

https://www.codeguru.com/csharp/the-difference-between-sql-server-and-sql-azure/ 

https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison?view=azuresql 

Permissions comparison between on-premises SQL Server and Azure SQL Database

 

The version of Azure SQL is the latest, and is upgraded automatic, you need to manually upgrade your databases in Azure SQL. Check the version of your database with command:

SELECT name, compatibility_level FROM sys.databases;

Azure SQL Database Compatibility with SQL Server

The version of the SQL Server database engine run by Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics is always ahead of the on-premises version of SQL Server, and includes the latest security fixes. This means that the patch level is always on par with or ahead of the on-premises version of SQL Server, and that the latest features available in SQL Server are available in these services.

https://en.wikipedia.org/wiki/Microsoft_Azure_SQL_Database 

More Information:

https://database.guide/6-ways-to-check-the-size-of-a-database-in-sql-server-using-t-sql/ 

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server?view=azuresql

Product:

Microsoft Azure SQL

Issue:

Can not change default database for a SQL user in Azure SQL. User can not login to database, if it is not possible to select database in the login dialog ( like SSMS). You get this error when you try to change the default database for a SQL Azure user. If you create a SQL user that only resize in a single database, he can only login to that database. With Azure Data Studio can you select the database name during login dialog.

Solution:

Add the user to the master database too, with command:

create user pbi_reader for login pbi_reader

Before that, you may have run below command to created the login:

create login pbi_reader with password = 'Password!'

Switch to the user database, enter below code to create a user there:

create user pbi_reader for login pbi_reader

grant select on schema::DM to pbi_reader

Above command will give the SQL user pbi_reader access to the tables that have schema DM.

Azure SQL does not support the DEFAULT_DATABASE, DEFAULT_LANGUAGE, CHECK_EXPIRATION, CHECK_POLICY commands.

 

If you are db_owner in the SQL Azure database, you can add a Azure AD account to have access, by using a command like this:

CREATE USER [donald.duck@domain.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = DM;

To add a user as db_datareader to a specific database, use this command:

EXEC sp_addrolemember 'db_datareader', 'pbi_reader'

To list users in database, use this command:

SELECT * FROM SYS.DATABASE_PRINCIPALS

To be able to connect from Power BI to Azure SQL, you need to open the firewall in the database.

https://www.mssqltips.com/sqlservertip/5953/create-power-bi-connection-to-azure-sql-database/

https://hevodata.com/learn/azure-to-power-bi/

Power BI in the cloud, uses a span of ip address that change every week, if your database in the cloud or on-prem need to whitelist ip addresses in the firewall to gain access, it is best to eiter try to use a URL/DOMAIN in the firewall to allow PowerBI in the cloud to have access. You can create a support ticket with Microsoft Azure team and get a list of IP addresses that is used by Power BI.

For a on-prem database, the best solution is to install Azure gateway on-prem, then this box will keep the connection between on-prem and the azure based power bi service. And your on-prem databases will talk to the on-prem azure gateway.

The on-premises data gateway acts as a bridge. It provides quick and secure data transfer between on-premises data, which is data that isn’t in the cloud, and several Microsoft cloud services.

For Azure SQL in the cloud, it can be simpler to allow all cloud based services access, by inside https://portal.azure.com go to your SQL server page, then to network, and mark Allow Azure services and resources to access this workspace to be enabled. This could make it possible for Power BI in the cloud to access your Azure SQL database.

You may also be able to set Azure SQL firewall with command like this (in SSMS):

 exec sp_set_firewall_rule N'Allow DB Connections', '10.0.0.2', '10.0.0.2';

 

More information:

https://www.mssqltips.com/sqlservertip/5953/create-power-bi-connection-to-azure-sql-database/ 

https://www.microsoft.com/en-us/download/details.aspx?id=56519 

https://learn.microsoft.com/en-us/power-bi/admin/power-bi-allow-list-urls 

https://radacad.com/the-power-bi-gateway-all-you-need-to-know

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-gateway

https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-onprem

https://chartio.com/learn/databases/grant-sql-server-table-permissions/

https://www.guru99.com/sql-server-create-user.html

Azure SQL Limitations compared with a SQL Server Enterprise

https://learn.microsoft.com/en-us/sql/azure-data-studio/quickstart-sql-server?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/azure-data-studio/quickstart-sql-database?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16&tabs=redhat-install%2Credhat-uninstall#install-azure-data-studio

https://devblogs.microsoft.com/premier-developer/secure-access-to-azure-sql-servers-for-power-bi/

Product:

Microsoft SQL Azure

Issue:

How create a user in SQL Azure, as you do not have a GUI to use in SSMS.

Solution:

Connect to Azure SQL with SSMS as administrator.

Connect to your user database, and enter below SQL statement to create user Roger and give it datareader rights:

CREATE User Roger
WITH PASSWORD = 'Password!'

ALTER ROLE db_datareader ADD member Roger

This user only exist in the database selected.

To create a user that will need access to more than one database, select the master database and enter below SQL:

CREATE Login Roger
WITH PASSWORD = 'Password!' 

CREATE USER Roger FOR LOGIN Roger

(You need to create the user in master database to, so he can login when master is default database)

Then switch to your user database and enter this SQL statement:

CREATE USER Roger FOR LOGIN Roger

ALTER ROLE db_datareader ADD member Roger

 

Best is to use a AD group instead, and set that SQL login to have the needed rights in the database.

 

More Information:

https://stackoverflow.com/questions/55230724/set-default-database-to-user-sql-server-azure 

https://learn.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver16 

— =======================================================================================
— Create User as DBO template for Azure SQL Database and Azure Synapse Analytics Database
— =======================================================================================
— For login login_name, create a user in the database
CREATE USER <user_name, sysname, user_name>
FOR LOGIN <login_name, sysname, login_name>
WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

— =======================================================================================
— Create Azure Active Directory User for Azure SQL Database and Azure Synapse Analytics Database
— =======================================================================================
— For login <login_name, sysname, login_name>, create a user in the database
— CREATE USER <Azure_Active_Directory_Principal_User, sysname, user_name>
— [ { FOR | FROM } LOGIN <Azure_Active_Directory_Principal_Login, sysname, login_name> ]
— | FROM EXTERNAL PROVIDER
— [ WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo> ]
— GO

— Add user to the database owner role
EXEC sp_addrolemember N’db_owner’, N'<user_name, sysname, user_name>’
GO

Product:

Microsoft Windows 2019 Server

Issue:

I can not ping the server to check if the IP address or server DNS name is correct. How open ping in Windows Firewall?

Solution:

Windows Firewall
  • Search for Windows Firewall , and click to open it.
  • Click Advanced Settings on the left.
  • From the left pane of the resulting window, click Inbound Rules.
  • In the right pane, find the rules titled File and Printer Sharing (Echo Request – ICMPv4-In).
  • Right-click each rule and choose Enable Rule.

To allow TM1 architect access the TM1 server, the windows firewall need to open this ports:

5495,5498,5895,5898,12300-12400

Then you need to update the tm1s.cfg file for each TM1 instance to use above port range:

PortNumber=12345

HTTPPortNumber=12354

ClientMessagePortNumber=

By default, ClientMessagePortNumber port number is automatically and dynamically assigned when the TM1 server starts. You do not have to set ClientMessagePortNumber to a specific number unless firewalls or other network issues require the listener port to be a well-known number.

Note: Be sure to assign unique port numbers for the server and client message ports. If you have two servers running on the same machine with the same port number, the message activity may cause a system failure.

EnableTIDebugging=T

Setting the parameter to T (true) allows you to use any of the TurboIntegrator process debugging capabilities of the TM1 REST API.
More Information:

https://www.howtogeek.com/howto/windows-vista/allow-pings-icmp-echo-request-through-your-windows-vista-firewall/

https://activedirectorypro.com/allow-ping-windows-firewall/

https://optics.ansys.com/hc/en-us/articles/7144748040467-Adding-inbound-rules-to-Windows-Defender-firewall

https://www.veritas.com/support/en_US/article.100027250 

Product:
Notepad++
Microsoft Windows 2019

Issue:

How do i add compare function to Notepad++?

Solution:

Start Notepad++

Go to plugin menu and select Plugin Admin.

Enter Compare to search for the plug in. Mark Compare in the list and click on install icon.

Click Yes, and Notepad++ will restart after it have downloaded from internet the compare files and put them in folder C:\Program Files\Notepad++\plugins\ComparePlugin

If the server does not have access to internet, copy above 3 files from a working installation, and add them to the plugins folder of your Notepad++ installation.

ComparePlugin.dll
\ComparePlugin\sqlite3.dll
\ComparePlugin\git2.dll

Then you can in Notepad++ use Plugins – Compare – Compare, to get a quick listing of the different rows between the two latest (on the right) open files.

If you use TM1, you can add support for TM1 syntax with download of a tm1.zip file , and unzip it and place the tm1.xml in folder C:\temp. Then from inside Notepad++ go to Languages – User Defined Language -Define your language.
https://npp-user-manual.org/docs/user-defined-language-system/

In the dialog, click import, and select the above tm1.xml file. Then restart Notepad++.

You should now have a new selection TM1 at the bottom of the languages menu.

You can create yourself what words should highlight or be suggested in the User Defined Language dialog.

 

More information:

https://www.wimgielis.com/tm1_tm1andnotepad++_EN.htm

https://www.tm1forum.com/viewtopic.php?t=15927

https://www.tm1forum.com/viewtopic.php?f=3&t=7540

https://www.tm1forum.com/viewtopic.php?t=4248

https://exploringtm1.com/notepad-for-tm1-rules-ti-updated/

Product:
Microsoft Windows 2019

Issue:

How copy only a folder structure and not the files therein?

Solution:

Use robocopy:

robocopy "source"  "destination" /e /xf *

Above will copy folder structure from source to destination folder, without the security.

/E copy subdirectories, including Empty ones.
/XF file [file]… eXclude Files matching given names/paths/wildcards.

Add /SEC and the security for the file is also copied.

/SEC Copy files with SECurity (equivalent to /COPY:DATS). Useful if you just want to copy over security changes.

More Information:

https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/robocopy

https://social.technet.microsoft.com/wiki/contents/articles/1073.robocopy-and-a-few-examples.aspx

https://www.pdq.com/blog/hitchhikers-guide-to-robocopy/

https://plataformaremota.wordpress.com/support/use-robocopy-to-migrate-windows-shares/

Product:

Microsoft SQL server 2019

Issue:

How create a store procedure, than when run will add username and date to a row?

Suggested solution:

Paste below code in SQL management studio and edit it to your needs:

CREATE PROCEDURE [dbo].[A_AddDataToTable] 
AS
BEGIN
---- create the variables
DECLARE @TableName as nvarchar(50)
DECLARE @SqlCode as nvarchar(200)
DECLARE @TimeValue as nvarchar(50)
DECLARE @Userman as nvarchar(50)

---- enter the name of your table below instead of Donald
SET @TableName = 'Table_'+'Donald'

BEGIN
---- check if the table exist, then not create it
IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = @TableName) 

---- fill variable with the SQL code to create the table
set @SqlCode=''
set @SqlCode= @SqlCode + 'CREATE TABLE ' + @TableName + ' '
set @SqlCode= @SqlCode + ' (Rowid int IDENTITY(1000, 1) PRIMARY KEY, '
set @SqlCode= @SqlCode + ' ProUser nvarchar(50) , '
set @SqlCode= @SqlCode + ' ProDate nvarchar(50) ) ;'


---- execute the code in the string
EXEC sp_executesql @SqlCode

END
---- every time the processes is run, do below
---- add a row with date and username

---- get the values for date
SELECT @TimeValue = convert(varchar, getdate(), 0)
---- get the name of the user who run the process
SELECT @Userman = CURRENT_USER

---- build the string of values
set @SqlCode=''
set @SqlCode= @SqlCode + 'INSERT INTO '+ QUOTENAME(@TableName) +' (ProUser,ProDate) ' 
set @SqlCode= @SqlCode + 'VALUES ( ''' + (@UserMan) + ''' , ''' + (@TimeValue) + ''' ) '

-- execute the code in the string
EXEC sp_executesql @SqlCode

END


The result in the table will be like this, after a few run of the SP.

 

More Information:

https://www.sqlshack.com/introduction-to-sp_executesql-stored-procedure-with-examples/

https://codingsight.com/10-sp_executesql-gotchas-to-avoid-for-better-dynamic-sql/

Product:

Microsoft SQL server 2016

Problem:

How is my Index used?

Solution:

List the index in the database:

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
   ,IX.name AS Index_Name
   ,IX.type_desc Index_Type
   ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
   ,IXUS.user_seeks AS NumOfSeeks
   ,IXUS.user_scans AS NumOfScans
   ,IXUS.user_lookups AS NumOfLookups
   ,IXUS.user_updates AS NumOfUpdates
   ,IXUS.last_user_seek AS LastSeek
   ,IXUS.last_user_scan AS LastScan
   ,IXUS.last_user_lookup AS LastLookup
   ,IXUS.last_user_update AS LastUpdate

 

Show if index are fragmented:

SELECT  OBJECT_NAME(IDX.OBJECT_ID) AS Table_Name,
IDX.name AS Index_Name,
IDXPS.index_type_desc AS Index_Type,
IDXPS.avg_fragmentation_in_percent  Fragmentation_Percentage
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXPS
INNER JOIN sys.indexes IDX  ON IDX.object_id = IDXPS.object_id
AND IDX.index_id = IDXPS.index_id
ORDER BY Fragmentation_Percentage DESC

 

List index usage:

SELECT *
FROM
sys.dm_db_index_usage_stats
INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
indexes.is_primary_key = 0 --This line excludes primary key constarint
AND
indexes. is_unique = 0 --This line excludes unique key constarint
AND 
dm_db_index_usage_stats.user_updates <> 0 -- This line excludes indexes SQL Server hasn’t done any work with
AND
dm_db_index_usage_stats. user_lookups = 0
AND
dm_db_index_usage_stats.user_seeks = 0
AND
dm_db_index_usage_stats.user_scans = 0
ORDER BY
dm_db_index_usage_stats.user_updates DESC

 

 

More Information:

Gathering SQL Server indexes statistics and usage information

https://codefibershq.com/blog/useful-sqlserver-queries-commands-and-snippets

Product:

Microsoft SQL server 2016

Issue:

What happens on the SQL server now?

Solution:

Run this query:

SELECT r.start_time [Start Time],session_ID [SPID],
DB_NAME(database_id) [Database],
SUBSTRING(t.text,(r.statement_start_offset/2)+1,
CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
END) [Executing SQL],
Status,command,wait_type,wait_time,wait_resource,
last_wait_type
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID -- don't show this query
AND session_id > 50 -- don't show system queries
ORDER BY r.start_time

or install and run sp_WhoIsActive from Adam Machanic (https://github.com/amachanic/sp_whoisactive/):

 

More Information:

https://www.sqlmatters.com/Articles/See%20what%20queries%20are%20currently%20running.aspx

https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

Product:
Microsoft SQL Server 2019
Issue:
How active encryption on SQL servers databases?

Solution:

You need a folder on the SQL server to store the certificate, create a folder like e:\key and only give local administrators and the SQL service account access there.

You can use the same certificate for a group of SQL servers. Then it is possible to restore a database backup to one of the others server in that group – that use the same certificate.

One the first SQL server:

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='InsertStrongPasswordHere12!';
GO

CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO

BACKUP CERTIFICATE TDE_Cert
TO FILE = 'e:\key\TDE_Cert.cer'
WITH PRIVATE KEY (file='e:\key\TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='InsertStrongPasswordHere12!')

 

Then on every other SQL server in the group , copy above files to the e:\key folder on the next server, and do this to activate TDE:

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='InsertStrongPasswordHere12!';
GO

USE MASTER
GO
CREATE CERTIFICATE TDE_Cert
FROM FILE = 'e:\key\TDE_Cert.cer'
WITH PRIVATE KEY (FILE = 'e:\key\TDE_CertKey.pvk',
DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere12!' );

 

Then to enable the encryption, you need to run this on every database:

USE <DB>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO

ALTER DATABASE <DB>
SET ENCRYPTION ON;
GO

Replace <DB> with your database name.

 

Then the database and its coming backup files are encrypted. The Backup can only be restored on a server with the same certificate.

 

If you get an error like this:

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

Try by change the path from file=‘e:\key\TDE_CertKey.pvk’ to file=’e:/key/TDE_CertKey.pvk’

 

To see what databases are encrypted:

SELECT name,is_encrypted,* FROM sys.databases WHERE is_encrypted = 1

To check if the certificate is installed:

SELECT * FROM sys.certificates WHERE name = 'TDE_Cert'

 

Important: Keep your password and backup of the certificates files in a secure location. In case you need to restore a database to a new SQL server, this keys need to be restored first.

To remove encryption from a database:

ALTER DATABASE [RecoveryWithTDE]
  SET ENCRYPTION OFF;
GO
USE [RecoveryWithTDE]
GO 
DROP DATABASE ENCRYPTION KEY;

To backup the master key:

USE Master ;
Open Master Key Decryption by password = 'InsertStrongPasswordHere12!'
Backup master key to file = 'e:\key\MasterKeyName.key'
        ENCRYPTION BY PASSWORD = 'InsertStrongPasswordHere12!';
    GO

To restore the master key to the database server:

Use master 
    restore master key
    FROM FILE = 'e:\key\MasterKeyName.key'
    DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere12!'
    ENCRYPTION BY PASSWORD = 'InsertStrongPasswordHere12!'

 

The TEMPDB database will be encrypted when you start using TDE, then it is always encrypted.

Cannot encrypt a system database. Database encryption operations cannot be performed for ‘master’, ‘model’, ‘tempdb’, ‘msdb’, or ‘resource’ databases.

More Information:

https://www.databasejournal.com/ms-sql/suspending-and-resuming-transparent-data-encryption-tde/

https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/

https://www.sqlservertutorial.net/sql-server-administration/sql-server-tde/

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16