Buying a car or truck?
This article was originally posted by the FTC. See the original article here.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Buying a car or truck?
This article was originally posted by the FTC. See the original article here.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Lesson Learned #225: Has an unresolved reference to object using Linked Server or External Table
This article is contributed. See the original author and article here.
We used to have cases when our customers are trying to export their database using SQLPackage or Export service in the Portal having, for example, the following error message: Error SQL71562: Error validating element Synonym: [dbo].[MyView] has an unresolved reference to object [linkedserver].[databasename].[schemaname].[tablename]. External references are not supported when creating a package from this platform. In this post I would like to suggest an alternative to export this data.
The first thing that we need to know that SQLPackage validates that we don’t have any of the following points in our database that we want to export:
- Reference using 4 parts, for example, databasename.schemaname.tablename
- Using an external table.
- Calling a table using a linked server, for example, select * from mylinkedserver.databasename.schema.table
- Calling a synonyms that uses a linked server, for example, CREATE SYNONYM [dbo].[ExternalTable1] FOR [linkedserver].[databasename].[schemaname].[tablename]
If we have any of these scenarios we are going to have the following error message exporting the data:
- One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71562: Error validating element Synonym: [dbo].[MyView] has an unresolved reference to object [linkedserver].[databasename].[schemaname].[tablename]. External references are not supported when creating a package from this platform.
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71562: Error validating element [dbo].[ExternalTable1]: Synonym: [dbo].[ExternalTable1] has an unresolved reference to object [linkedservername].[databasename].[schemaname].[tablename]. External references are not supported when creating a package from this platform.
In order to be able to export the data, I developed the following stored procedure that basically, runs the following code. The goal is to create dynamically the external table and/or view or synonym that is calling external tables,
- Parameters:
- @TableName = will be the name of the synonym or external table to create.
- @Action = will be the operation to perform, CREATE or DROP the synonym
CREATE PROCEDURE dbo.DeployMyExternalTable(@TableName varchar(400),@Action VARCHAR(10))
AS
IF @TableName = 'ExternalTable1'
BEGIN
IF @Action = 'CREATE'
BEGIN
IF NOT EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable1' )
EXEC SP_EXECUTESQL N'CREATE SYNONYM [dbo].[ExternalTable1] FOR [LinkedServerName].[DatabaseName].[SchemaName].[ExternalData1]'
END
IF @Action = 'DROP'
BEGIN
IF EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable1' )
EXEC SP_EXECUTESQL N'DROP SYNONYM [dbo].[ExternalTable1]'
END
END
IF @TableName = 'ExternalTable2'
BEGIN
IF @Action = 'CREATE'
BEGIN
IF NOT EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable2' )
EXEC SP_EXECUTESQL N'CREATE SYNONYM [dbo].[ExternalTable2] CREATE SYNONYM [dbo].[ExternalTable2] FOR [LinkedServerName].[DatabaseName].[SchemaName].[ExternalData2]'
END
IF @Action = 'DROP'
BEGIN
IF EXISTS( SELECT TOP 1 NAME FROM SYS.synonyms WHERE Name='ExternalTable2' )
EXEC SP_EXECUTESQL N'DROP SYNONYM [dbo].[ExternalTable2]'
END
END
The idea is if you need to run any query against this synonym or external table is to run the following code:
exec dbo.DeployMyExternalTable 'ExternalTable1', 'CREATE'
SELECT * FROM [dbo].[ExternalTable1]
Or if you need to delete it
exec dbo.DeployMyExternalTable 'ExternalTable1', 'DROP'
Basically, if you need to export the data, you could call these stored procedure for all synonyms or external tables, export the data and after it your application/database will create again them without modifying your code.
Enjoy!
Lesson Learned #224:Hands-On-Labs: Checking the performance with Perf Collector Analyzer
This article is contributed. See the original author and article here.
One of the main problem that we have when we are working with multiple databases in an Elastic Databases Pool or Azure SQL Managed Instace (that are running on same SQL Instance) is how to consolidate all the information in a single place.
For example, if we use Query Data Store we need to review one by one the database finding the performance problem. Trying to resolve these insights we would like to suggest to use PerfCollector and PerfCollector Analyzer to allows you to capture and query the information collected for all the databases that you have in an Elastic Database Pool and SQL Managed Instance and even, all the databases associated in an Azure SQL Server or On-Premises.
How to collect the data?
Videos how to use?
- Lesson Learned #220:Hands-On-Labs: Activity Monitor in my Elastic Database Pool – Microsoft Tech Community
- Lesson Learned #221:Hands-On-Labs: Activity Monitor in my Azure SQL Managed Instance – Microsoft Tech Community
Where download the PerfCollector Analyzer
Advantages to use PerfCollector Analyzer:
- Aggregate Query Data Store collected information for all databases that you have in Elastic Database Pool, Managed Instance, Azure SQL or On-Premises
- Review best practices in performance:
- Missing Indexes
- Out-Of-Date Statistics
- Other topics.
Some Example queries to obtain the information from Query Data Store for all the databases:
- Highest wait durations at instance level
SELECT TOP 10
sum(total_query_wait_time_ms) AS sum_total_wait_ms,
ws.[wait_category_desc]
FROM [_xTotalxAcummulatedx_xQDSx_query_store_wait_stats] ws
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_plan] p ON ws.plan_id = p.plan_id and ws.dbname = p.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query] q ON p.query_id = q.query_id and p.dbname = q.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query_text] qt ON q.query_text_id = qt.query_text_id and q.dbname = qt.dbname
GROUP BY ws.[wait_category_desc]
ORDER BY sum_total_wait_ms DESC;
- Highest wait durations per database at instance level/elastic database pool level
SELECT TOP 100
sum(total_query_wait_time_ms) AS sum_total_wait_ms,
ws.[wait_category_desc],
q.dbname
FROM [_xTotalxAcummulatedx_xQDSx_query_store_wait_stats] ws
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_plan] p ON ws.plan_id = p.plan_id and ws.dbname = p.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query] q ON p.query_id = q.query_id and p.dbname = q.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query_text] qt ON q.query_text_id = qt.query_text_id and q.dbname = qt.dbname
GROUP BY q.dbname , ws.[wait_category_desc]
ORDER BY q.dbname, sum_total_wait_ms DESC;
- Highest wait durations per instance level/elastic database pool level and query
SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
rs.last_execution_time, p.dbname
FROM [_xTotalxAcummulatedx_xQDSx_query_store_query_text] AS qt
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_query] AS q
ON qt.query_text_id = q.query_text_id and qt.dbname = q.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_plan] AS p
ON q.query_id = p.query_id and q.dbname = p.dbname
JOIN [_xTotalxAcummulatedx_xQDSx_query_store_runtime_stats] AS rs
ON p.plan_id = rs.plan_id and p.dbname = rs.dbname
WHERE rs.last_execution_time > DATEADD(DAY, -10, GETUTCDATE())
ORDER BY rs.avg_duration DESC;
Video how to use it
Enjoy!
Introducing Azure Well-Architected Framework Assessments for Azure Stack Hub (Preview)
This article is contributed. See the original author and article here.
Azure Stack Hub extends the capabilities of Azure to on-premises and edge locations, enabling you to deploy, manage, and operate a subset of Azure services from your own datacenter or remote locations. The local control plane and Azure consistent developer experience provided by Azure Stack Hub can be used to address challenging technical or regulatory requirements, such as low latency, data sovereignty and security or compliance requirements. For more information, please see Why use Azure Stack Hub?
The Azure Well-Architected Framework (WAF) provides prescriptive guidance and recommendations for architects to use when creating or reviewing cloud solutions. The WAF guidance is organized into five pillars, Reliability, Security, Cost Optimization, Operational Excellence, and Performance Efficiency. Incorporating the recommendations into workload designs helps to ensure reliable, scalable, and performant architecture patterns are implemented for cloud solutions.
Today we are announcing two pillars of the Well-Architected Framework are available in Preview for Azure Stack Hub on the Microsoft Assessment Platform. These are the Reliability and Operational Excellence pillars. If you are using Azure Stack Hub to deploy and operate workloads for key business systems, it is now possible to answers questions for these pillars within the assessments platform. After completing the assessments, you will be provided with a maturity or risk score, together with prescriptive guidance and knowledge links that suggest possible improvements you could make to your architecture design and score.
Reliability
The Reliability pillar of the WAF for Azure Stack Hub focuses on User workload solutions that have been deployed on an Azure Stack Hub scale unit, with questions probing architecture design areas such as high availability, disaster recovery, app and data resiliency capabilities, error handling, performance, and monitoring.
When designing applications using the guidance in the Well-Architected Framework Reliability pillar, the objective is not to avoid any and all failures – it is to respond to failure in a way that avoids downtime and data loss. This is achieved by increasing the resiliency of your applications using patterns that include data replication and application failover capabilities. These design principles are used to provide high availability and increase system uptime, which is a key success-criteria for business-critical systems.
Operational Excellence
The questions in the Excellence pillar for Azure Stack Hub, focus on the Cloud Operator Persona. As a Cloud Operator, you use the administrator portal and PowerShell for day-to-day management and operations of Azure Stack Hub, such as populating the Marketplace, defining quotas, plans and offers, monitoring infrastructure health, and applying platform updates.
The questions in the Operational Excellence review focus on identifying what processes and procedures Cloud Operators are using to effectively manage and operate the Azure Stack Hub scale unit. Question topics include, identity and access management, network integration, monitoring, and alerting, offering services, capacity management, updates, business continuity and disaster recovery (BC/DR) and effective support and administration.
Cloud Operators can use the Azure Well-Architected Review to complete a set of easy-to- questions in order to obtain their maturity score, together with a list of curated / individual recommendations and guidance for how they can improve the processes and procedures they use for operating and managing Azure Stack Hub. An example of the assessment in action is shown below:
We are working to bring the three remaining pillars of the Azure Well-Architected Framework (WAF) to the Assessments Platform for Azure Stack Hub in the coming months. If you have any feedback or comments in relation to the Azure Stack Hub WAF Assessments, please send an email to AzS-WAF-Feedback@microsoft.com.
To get started with the Azure Stack Hub Well-Architected Review Assessments today, please visit this link: https://aka.ms/architecture/review
About the Author
Neil Bird is a Senior Program Manager in the Azure Edge & Platform Engineering team at Microsoft. His background is in Azure and Hybrid Cloud infrastructure, operational excellence, and automation. He is passionate about helping customers deploy and manage cloud solutions effectively using Azure and Azure Edge technologies.
Recent Comments