AskKrishna

SQL Server: Not able to connect to SQL Server from SSMS

2023-04-20T02:30:00.004-05:00

Error Message while connecting thru SSMS

Cannot connect to <<SQL SERVER Machine Name>>.

ADDITIONAL INFORMATION:

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error

Error Message in Event Log

Login failed for user '<<User Name>>'. Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission. [CLIENT: <local machine>]

Solution:

Open the SQL Server Management Studio (SSMS) as administrator.

Optimizing transactions in Azure Data Warehouse (Synapse)

2021-03-05T00:30:00.005-06:00

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-best-practices-transactions

Minimally logged operations

The following operations are capable of being minimally logged:

CREATE TABLE AS SELECT (CTAS)
INSERT..SELECT
CREATE INDEX
ALTER INDEX REBUILD
DROP INDEX
TRUNCATE TABLE
DROP TABLE
ALTER TABLE SWITCH PARTITION

CTAS and INSERT...SELECT are both bulk load operations

Primary Index	Load Scenario	Logging Mode
Heap	Any	Minimal
Clustered Index	Empty target table	Minimal
Clustered Index	Loaded rows do not overlap with existing pages in target	Minimal
Clustered Index	Loaded rows overlap with existing pages in target	Full
Clustered Columnstore Index	Batch size >= 102,400 per partition aligned distribution	Minimal
Clustered Columnstore Index	Batch size < 102,400 per partition aligned distribution	Full

DELETE is a fully logged operation. If you need to delete a large amount of data in a table or a partition, it often makes more sense to SELECT the data you wish to keep, which can be run as a minimally logged operation. To select the data, create a new table with CTAS. Once created, use RENAME to swap out your old table with the newly created table.

UPDATE is a fully logged operation. If you need to update a large number of rows in a table or a partition, it can often be far more efficient to use a minimally logged operation such as CTAS to do so

Both UPDATE and DELETE are fully logged operations and so these undo/redo operations can take significantly longer than equivalent minimally logged operations.

Transferring data to and from Azure

2021-03-04T00:30:00.004-06:00

Capability	Azure CLI	AzCopy	PowerShell	AdlCopy	PolyBase
Compatible platforms	Linux, OS X, Windows	Linux, Windows	Windows	Linux, OS X, Windows	SQL Server, Azure Synapse
Optimized for big data	No	Yes	No	Yes 1	Yes 2
Copy to relational database	No	No	No	No	Yes
Copy from relational database	No	No	No	No	Yes
Copy to Blob storage	Yes	Yes	Yes	No	Yes
Copy from Blob storage	Yes	Yes	Yes	Yes	Yes
Copy to Data Lake Store	No	Yes	Yes	Yes	Yes
Copy from Data Lake Store	No	No	Yes	Yes	Yes

Capability	Azure Storage Explorer	Azure portal *	Azure Data Factory
Optimized for big data	No	No	Yes
Copy to relational database	No	No	Yes
Copy from relational database	No	No	Yes
Copy to Blob storage	Yes	No	Yes
Copy from Blob storage	Yes	No	Yes
Copy to Data Lake Store	No	No	Yes
Copy from Data Lake Store	No	No	Yes
Upload to Blob storage	Yes	Yes	Yes
Upload to Data Lake Store	Yes	Yes	Yes
Orchestrate data transfers	No	No	Yes
Custom data transformations	No	No	Yes
Pricing model	Free	Free	Pay per usage

Data analytics technology in Azure

2021-03-03T00:30:00.005-06:00

Capability	Power BI	Jupyter Notebooks	Zeppelin Notebooks	Microsoft Azure Notebooks
Connect to big data cluster for advanced processing	Yes	Yes	Yes	No
Managed service	Yes	Yes 1	Yes 1	Yes
Connect to 100s of data sources	Yes	No	No	No
Offline capabilities	Yes 2	No	No	No
Embedding capabilities	Yes	No	No	No
Automatic data refresh	Yes	No	No	No
Access to numerous open source packages	No	Yes 3	Yes 3	Yes 4
Data transformation/cleansing options	Power Query, R	40 languages, including Python, R, Julia, and Scala	20+ interpreters, including Python, JDBC, and R	Python, F#, R
Pricing	Free for Power BI Desktop (authoring), see pricing for hosting options	Free	Free	Free
Multiuser collaboration	Yes	Yes (through sharing or with a multiuser server like JupyterHub)	Yes	Yes (through sharing)

[1] When used as part of a managed HDInsight cluster.

[2] With the use of Power BI Desktop.

[2] You can search the Maven repository for community-contributed packages.

[3] Python packages can be installed using either pip or conda. R packages can be installed from CRAN or GitHub. Packages in F# can be installed via nuget.org using the Paket dependency manager.

Data pipeline orchestration technology in Azure

2021-03-02T00:30:00.006-06:00

Capability matrix

The following tables summarize the key differences in capabilities.

General capabilities

GENERAL CAPABILITIES
Capability	Azure Data Factory	SQL Server Integration Services (SSIS)	Oozie on HDInsight
Managed	Yes	No	Yes
Cloud-based	Yes	No (local)	Yes
Prerequisite	Azure Subscription	SQL Server	Azure Subscription, HDInsight cluster
Management tools	Azure Portal, PowerShell, CLI, .NET SDK	SSMS, PowerShell	Bash shell, Oozie REST API, Oozie web UI
Pricing	Pay per usage	Licensing / pay for features	No additional charge on top of running the HDInsight cluster

Pipeline capabilities

PIPELINE CAPABILITIES
Capability	Azure Data Factory	SQL Server Integration Services (SSIS)	Oozie on HDInsight
Copy data	Yes	Yes	Yes
Custom transformations	Yes	Yes	Yes (MapReduce, Pig, and Hive jobs)
Azure Machine Learning scoring	Yes	Yes (with scripting)	No
HDInsight On-Demand	Yes	No	No
Azure Batch	Yes	No	No
Pig, Hive, MapReduce	Yes	No	Yes
Spark	Yes	No	No
Execute SSIS Package	Yes	Yes	No
Control flow	Yes	Yes	Yes
Access on-premises data	Yes	Yes	No

Scalability capabilities

SCALABILITY CAPABILITIES
Capability	Azure Data Factory	SQL Server Integration Services (SSIS)	Oozie on HDInsight
Scale up	Yes	No	No
Scale out	Yes	No	Yes (by adding worker nodes to cluster)
Optimized for big data	Yes	No	Yes

Azure Data Lake Store vs Azure Blob Storage containers

2021-03-01T00:30:00.006-06:00

Capability	Azure Data Lake Store	Azure Blob Storage containers
Purpose	Optimized storage for big data analytics workloads	General purpose object store for a wide variety of storage scenarios
Use cases	Batch, streaming analytics, and machine learning data such as log files, IoT data, click streams, large datasets	Any type of text or binary data, such as application back end, backup data, media storage for streaming, and general purpose data
Structure	Hierarchical file system	Object store with flat namespace
Authentication	Based on Azure Active Directory Identities	Based on shared secrets Account Access Keys and Shared Access Signature Keys, and Azure role-based access control (Azure RBAC)
Authentication protocol	OAuth 2.0. Calls must contain a valid JWT (JSON web token) issued by Azure Active Directory	Hash-based message authentication code (HMAC). Calls must contain a Base64-encoded SHA-256 hash over a part of the HTTP request.
Authorization	POSIX access control lists (ACLs). ACLs based on Azure Active Directory identities can be set file and folder level.	For account-level authorization use Account Access Keys. For account, container, or blob authorization use Shared Access Signature Keys.
Auditing	Available.	Available
Encryption at rest	Transparent, server side	Transparent, server side; Client-side encryption
Developer SDKs	.NET, Java, Python, Node.js	.NET, Java, Python, Node.js, C++, Ruby
Analytics workload performance	Optimized performance for parallel analytics workloads, High Throughput and IOPS	Not optimized for analytics workloads
Size limits	No limits on account sizes, file sizes or number of files	Specific limits documented here
Geo-redundancy	Locally-redundant (LRS), globally redundant (GRS), read-access globally redundant (RA-GRS), zone-redundant (ZRS).	Locally redundant (LRS), globally redundant (GRS), read-access globally redundant (RA-GRS), zone-redundant (ZRS). See here for more information

Azure Data Factory - Execute Pipeline Activity

2019-09-10T00:30:00.000-05:00

Execute Pipeline activity is used to call another Azure Data Factory Pipeline. This will help to develop reusable pipelines and use it in other pipelines

For example, you can create a separate pipeline to archive the files after processing. You can create one pipeline for archiving files (move the blobs or files to different folders) and call that pipeline in multiple pipelines. This will help you to reduce development time and increase code reusability.

Similarly, you can create a separate pipeline for sending emails in Azure data factory and re-use in multiple pipelines.

In the following screen-shot, we are calling two different pipelines (Archiving Pipeline, Send_Error_Message)

For more information, please check the following
https://docs.microsoft.com/en-us/azure/data-factory/control-flow-execute-pipeline-activity

Azure Data Warehouse - Materialized View

2019-09-09T19:02:00.002-05:00

Materialized View

Materialized Views are similar to Indexed Views in SQL Server. These are used to increase query performance and hide complex query logic from the users.

To enable disabled materialized view, you need to use the "Alter" statement.

ALTER MATERIALIZED VIEW My_Indexed_View REBUILD;

Materialized views are visible within SQL Server Management Studio under the views folder of the Azure SQL Data Warehouse instance

Limitations

Materialized views are disabled during an update or delete operations on the base tables.
Only Clustered Cloumnstore Index is supported.
Only Hash and Round Robin distributions are supported.

Sample Code

CREATE MATERIALIZED VIEW mv_test2
WITH (distribution = hash(i_category_id), FOR_APPEND)
AS
SELECT MAX(i.i_rec_start_date) as max_i_rec_start_date, MIN(i.i_rec_end_date) as min_i_rec_end_date, i.i_item_sk, i.i_item_id, i.i_category_id
FROM syntheticworkload.item i
GROUP BY i.i_item_sk, i.i_item_id, i.i_category_id

For more information, check the following links

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-materialized-view-as-select-transact-sql?view=azure-sqldw-latest

Azure Data Factory - If Condition activity

2019-09-08T00:30:00.000-05:00

If activity is used to perform different activities based on the input expressions. In the following, we are checking if the file is present or not using "GetMetaData" activity. If file presents perform the activities in if True activities. If file does not present then Edit the False activities.

Passing input values for If condition activity

Set validation expression in settings for If condition activity.

Adding activity for True or False conditions.

For more information, please check https://docs.microsoft.com/en-us/azure/data-factory/control-flow-if-condition-activity

Azure Data Factory - For Each Activity

2019-09-07T17:20:00.000-05:00

"ForEach" activity in Azure Data Factory is used to loop thru each row of the input and perform the actions based on the data.

Following screenshot shows two activities one is GetMetaData and second one is ForEach loop. The output of the GetMetaData is passed to the ForEach activity in the Settings. If the checkbox for "Sequential" is selected all the activities are performed in sequence for all the child items. If parallel execution is needed we need to un-check "Sequential".

"ForEach" activity can be used to an output of Lookup activity as well.

You can perform multiple activities for each row of data received for the input of ForEach activity. You can add all the activities by going to Activities and Edit Activities.

Limitations and workarounds

Here are some limitations of the ForEach activity and suggested workarounds.

Limitation	Workaround
You can't nest a ForEach loop inside another ForEach loop (or an Until loop).	Design a two-level pipeline where the outer pipeline with the outer ForEach loop iterates over an inner pipeline with the nested loop.
The ForEach activity has a maximum `batchCount` of 50 for parallel processing, and a maximum of 100,000 items.	Design a two-level pipeline where the outer pipeline with the ForEach activity iterates over an inner pipeline.

For more information, please check the documentation at https://docs.microsoft.com/en-us/azure/data-factory/control-flow-for-each-activity

Azure Analysis Services

2019-09-06T00:30:00.000-05:00

Supported Features

AAS is available in 3 tiers (Developer for development, Basic and Standard)
We can pause and resume the server when not used to reduce the cost.
Supports only the tabular model.
Scale-out up to 8 nodes when the load on the model is increased.
Partitions are supported
Row-level permissions are supported.
Perspectives are supported.
Bi-directional relations are supported.
Metadata is stored in Azure Blob storage.

Limitations

A multidimensional model is not supported
Power-pivot for SharePoint is not supported.

For more details pleas visit https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-overview

Azure SQL Server VS Azure Data Warehouse

2019-09-05T00:30:00.000-05:00

Azure SQL Server

Database size limit is 4TB
Optimized for OLTP loads
Cross-database queries are supported
Automatic performance tuning of a database is supported

Azure Data Warehouse

Supports up to 1 Peta Byte (1024 TB) size.
Optimized for OLAP loads
MPP (Massively Parallel Processing) system (data is processed on multiple parallel nodes)
Polybase support to load data from multiple systems.
Cross-database queries are not supported.
Manual performance tuning.
You can pause and resume the database to reduce cost.

Azure Data Factory Stored Procedure Activity

2019-09-04T00:30:00.000-05:00

Stored Procedure activity is used to execute Stored Procedure in Azure Data Factory. You can use this to maintain audit logs, update metadata information, to track files in Azure blobs to MetaData activity output. To create dynamic external tables in Azure Data Warehouse.

To pass the parameter values to Stored Procedure, you can use pipeline parameters. Use dynamic content to pass dynamic values. You can import all the stored procedure parameters using import parameters

For more information on Stored Procedure activity, Please check the following https://docs.microsoft.com/en-us/azure/data-factory/transform-data-using-stored-procedure

Azure Data Factory Execute Pipeline Activity

2019-09-03T00:30:00.000-05:00

Execute Pipeline Activity is used to call a different pipeline in Azure Data Factory. It is similar to calling Execute Package task in SSIS.

The main purpose of Execute Pipeline is to develop common pipelines which can be used in multiple pipelines and call them when needed. This helps to reduce development time and reduce maintenance of code.

If the calling pipeline needs to wait till the called pipeline is completed, set the "waitOnCompletation" to True.

Check the following for more details https://docs.microsoft.com/en-us/azure/data-factory/control-flow-execute-pipeline-activity

Azure Data Factory Lookup activity

2019-09-02T00:30:00.000-05:00

Lookup activity in Azure Data Factory is different from the lookup activity present in SSIS. Lookup activity in Azure Data Factory is used to execute Stored Procedure with output.

It can return the first row or multiple rows of data from the stored procedure. The output can be used to input for each loop.

The lookup activity supports a max of 5000 rows as output.

For more details, please check the following https://docs.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity documentation

Azure Data Analytics: U-SQL Skipping headers and outputting headers

2019-09-01T02:30:00.000-05:00

To skip the headers while reading the input files. Use the skipFirstNRows parameter

USING Extractors.Csv(skipFirstNRows:1)

For complete parameter details check the following

https://docs.microsoft.com/en-us/u-sql/functions/operators/extractors/extractor-parameters

Output the Header row while writting the output file

USING Outputters.Csv(outputHeader:true);

For complete parameter details check the following
https://docs.microsoft.com/en-us/u-sql/functions/operators/outputters/outputter-parameters

Microsoft Azure: Free Microsoft Azure learning videos for different roles at Pluralsight

2019-08-31T23:42:00.000-05:00

Go to the following link and register with your email. Three roles are for free of cost. There is a sequence of videos. 

https://www.pluralsight.com/partners/microsoft/azure 

In Azure Environment can be used for different things
Web-based platforms
Big Data Platform
AI platform

Choose the technologies based on the platform you are interested in.

Microsoft Azure: End to End technologies used in Azure for data storage and processing

2019-08-30T02:30:00.000-05:00

Azure Storage (Blob storage) -- Used to store data files. Landing or storage area for getting data from multiple systems. Create multiple storage containers for landing, staging, processing and archiving. Virtual folders. Permissions are at the container level. Less data cost. Cost and data availability is different in Hot, Cold & Archive levels.

Azure Data Lake (Gen-1) -- Optimized for Big Data storage and processing.  Data can be stored in a hierarchy format. Security can be enabled at the folder level. Parallel writes and reads are enabled. Storage cost is more compared to Azure Storage.

Azure Data Lake (Gen-2) -- Optimized for Big Data storage and processing. Supports both Object-based storage and Hierarchy level storage. Advantages of Azure Storage and Azure Data Lake (Gen-1).

Azure Data Analytics -- Serverless and cluster level processing of big data.

Azure DataBricks -- Cluster-based processing of big data using Spark.

Azure Data Factory -- Similar to SSIS and SQL Server Agent. Used to develop control flows and different tasks for processing data. Jobs can be executed one time or scheduled basis. Only UTC time is supported for job schedules. You can use SSIS, U-SQL, Data Bricks Spark and Azure Data Factory tasks to process the data.

Azure SQL Server -- Supports up to 4 TB of data size. Mainly used for OLTP systems. When needed SQL Server instance, you need to use Managed Azure SQL Server. You can also create a single database.

Azure SQL Data Warehouse -- Used for OLAP data marts and if the data size is more than 4 TB. MPP processing architecture. Data is processed on multiple nodes. Data is stored in Azure Storage. You can create External tables to access HDFS and other data sources. Not all the SQL features are supported currently.

Azure Analysis Services -- Tabular model for Analysis services.

Power BI --  For Reporting purpose.

Azure DevOps -- For source code control and auto-deploy the code.

Azure Key Vault -- To store sensitive data

2019-08-29T23:21:00.000-05:00

Azure Key Vault --  To store sensitive data 

This technology is used to hide all the sensitive information like SQL Connection strings, SQL User Name, and passwords. Advantage of this technology is you define the key-value pairs like give the connection string a name and the entire connection string is hidden from all the applications. 

This will help not to store the connection strings in source control or applications. In all the applications and source control we refer only with the secret name

For more information, check the following

https://docs.microsoft.com/en-us/azure/key-vault/key-vault-whatis

Azure Data Factory - GetMetaData activity

2019-08-28T02:30:00.000-05:00

GetMetaData activity is used to get file information which is present in Azure storage. This will get file size, row count, lastModifiedDate, file exists and other information. 

Following screenshot shows how to get all files information present in a particular folder. The folder name is passed as pipeline parameter

The output of this activity can be used as input to Stored Procedure activity. It can be used to store the metadata information in Azure SQL Database

For more information check the following

https://docs.microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activity

Azure SQL Data Warehouse unsupported features while creating tables

2019-08-27T02:30:00.000-05:00

 https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-overview 

The following list shows some of the table features that are not supported in SQL Data Warehouse.

Primary key, Foreign keys, Unique, Check Table Constraints
Computed Columns
Indexed Views
Sequence
Sparse Columns
Surrogate Keys. Implement with Identity.
Synonyms
Triggers
Unique Indexes
User-Defined Types

Azure Storage: Azure Storage VS Azure Data Lake Storage (Gen-1 & Gen-II)

2019-08-26T02:30:00.000-05:00

Azure Storage VS Azure Data Lake Storage (Gen-1 & Gen-II)

Azure Storage

· Object Storage

· Virtual folders -no real folders.

· No folder level security 

· No folder specific performance optimizations

· Data is stored in Containers as blobs

· Storage cost is based on hot, cold and archive tiers

· Available in all regions globally

· Data replication and redundancy options

Azure Data Lake Storage (Gen-1)

· Hierarchical file system

o Supports nesting of files within folders.

· Folder level security can be implemented

o Fine-grained security visa access control lists

· Performance optimization at the folder level

· Parallel reads and writes

· Scaled out over multiple nodes

· Hadoop and big data optimizations

· Optimized for analytics workloads.

Azure Data Lake Storage (Gen-2)

· Multi-modal storage combining features from both Azure Storage and ADLS (Gen-1)

· Enable the Hierarchical Namespace to use for the file system.

· Data can be accessed using object storage endpoint or file system storage endpoint.

· Object Storage Endpoint – wasb://containername@accountname.blob.core.windows.net/

· File System Endpoint – abfs://filesystemname@accountname.dfs.core.windows.net/

· Optimized for analytics workloads.

Leverage partition scans & partition pruning to improve query performance. 

Azure Data Analytics: How to access Azure Storage Blob data from Azure Data Lakes

2019-08-25T02:30:00.000-05:00

1. Register Azure Storage Blob account in Azure Data Lakes

Ensure that your Windows Azure Blob Storage account is registered with your Azure Data Lake Analytics account. I have copied the steps below from Registering Your Windows Azure Blob Storage account.

Navigate to the Azure Portal and log in.
Navigate to your Azure Data Lake Analytics Account.
Select Data Sources under Settings.
Verify whether your WABS account is listed. If yes, stop here. If no, continue to next step.
Click Add Data Source.
Select Azure Storage from the Storage Type drop-down list.
Select Select Account from the Selection Method drop-down list.
Select your WABS account from the Azure Storage drop-down list.
Click Add

PowerShell: How to rename multiple files in a folder at the same time

2019-01-09T02:30:00.000-06:00

To rename single file you can manually edit the file names. If there are multiple files you want to change the name of the file or part of the file name, it is easy to rename the file in PowerShell.

You can use rename-item with -Replace option like below. To replace ".StoredProcedure" in all the files present in the current directory with "_", you can use the following syntax

dir | rename-item -NewName {$_.name -replace ".StoredProcedure","_"}

For complete syntax and other examples visit the site at rename-item

SQL SERVER: How to refresh local cache in SQL Server Management Studio

2019-01-04T02:30:00.000-06:00

When working on SQL Server Management Studio, when any new tables are created or columns are added, the local cache is not refreshed immediately and display as error for the new objects or columns in the Query Window. To avoid this we need to update local cache of SSMS.

If you need to refresh the local cache of SSMS you can use two methods

First Method:
Press the following combination of keys on your keyboard by selecting SSMS

CTRL + Shift + R

Second Method:
Select the Query window in SSMS
In the Edit Menu
Go to IntelliSense
Press on Refresh Local Cache