What are functions or keywords in SQL Server database which will not work in Azure SQL database

When migrating from SQL Server to Azure SQL Database, there are certain features, functions, or keywords that might not be available or might behave differently due to the nature of Azure SQL Database being a Platform as a Service (PaaS) offering. Some of the features that might not work or might have limitations include:

1. SQL Server Agent: Azure SQL Database does not support SQL Server Agent. You need to use Azure Automation, Azure Functions, or other Azure services for similar functionalities.

2. Cross-Database Queries: Azure SQL Database does not support cross-database queries. You can't reference objects in other databases directly. However, you can achieve similar functionality using Elastic Query.

3. Linked Servers: Azure SQL Database does not support linked servers. You have to use other mechanisms like Azure SQL Managed Instance or Azure Data Factory for similar functionalities.

4. SQLCLR: Azure SQL Database does not support SQL Common Language Runtime (SQLCLR). You can't use .NET assemblies or CLR stored procedures directly.

5. Filestream/Filetable: Azure SQL Database does not support FILESTREAM or Filetable. You can use Azure Blob Storage for similar functionalities.

6. Service Broker: Azure SQL Database does not support Service Broker. You need to use other Azure services for message queuing and asynchronous processing.

7. Extended Events: Azure SQL Database does not support extended events. You need to use Azure Monitor for monitoring and diagnostics.

8. SQL Server Integration Services (SSIS): While Azure SQL Database does not directly support SSIS, you can use Azure Data Factory for similar ETL (Extract, Transform, Load) capabilities.

9. SQL Server Analysis Services (SSAS): Azure SQL Database does not support SSAS. You can use Azure Analysis Services for similar OLAP (Online Analytical Processing) capabilities.

10. SQL Server Reporting Services (SSRS): Azure SQL Database does not support SSRS. You can use Power BI or Azure SQL Managed Instance for similar reporting capabilities.

11. Database Mail: Azure SQL Database does not support Database Mail. You need to use other mechanisms for sending emails, such as Azure Functions or external services.

12. SQL Server Profiler: Azure SQL Database does not support SQL Server Profiler. You need to use Extended Events or other Azure monitoring tools for performance monitoring.

13. Certain Database Engine Features: Some features of the SQL Server database engine might not be available or behave differently in Azure SQL Database due to the differences in architecture and service model.

Always refer to the official Azure SQL Database documentation for the most up-to-date information on features and functionalities supported in Azure SQL Database. Additionally, before migration, it's crucial to thoroughly test your application to ensure compatibility with Azure SQL Database.

How to migrate SQL Server database to Azure SQL Server database

Migrating a SQL Server database to Azure SQL Database involves several steps. Here's a general outline of the process:

1. Assessment and Planning:

   • Evaluate your SQL Server database schema, features, and dependencies to determine compatibility with Azure SQL Database.
   • Use tools like Data Migration Assistant (DMA) or Azure Database Migration Service (DMS) to assess the readiness of your database for migration and identify any issues or compatibility issues.

2. Prepare Azure Resources:

   • Create an Azure SQL Database server and database in the Azure portal. Choose the appropriate service tier and pricing tier based on your requirements.

3. Database Schema and Objects Migration:

   • Generate a schema script for your SQL Server database using SQL Server Management Studio (SSMS) or another tool.
   • Modify the schema script to ensure compatibility with Azure SQL Database. For example, review data types, unsupported features, and permissions.
   • Execute the modified schema script against the Azure SQL Database to create the schema and objects.

4. Data Migration:

   • Use tools like SQL Server Management Studio (SSMS), SQL Server Integration Services (SSIS), or Azure Data Factory to migrate data from the SQL Server database to Azure SQL Database.
   • You can also use Azure Database Migration Service (DMS) for online or offline data migration with minimal downtime.

5. Application Configuration Update:

   • Update your application connection strings and configurations to point to the new Azure SQL Database server.
   • Test the connectivity and functionality of your application against the Azure SQL Database.

6. Testing and Validation:

   • Perform thorough testing to ensure that data, schema, and application functionality are migrated successfully.
   • Validate performance and scalability of Azure SQL Database compared to your SQL Server environment.

7. Monitoring and Optimization:

   • Configure monitoring and alerting for your Azure SQL Database using Azure Monitor.
   • Optimize database performance and scalability based on usage patterns and workload.

8. Post-Migration Cleanup:

   • Decommission the old SQL Server database and associated resources if they are no longer needed.
   • Review and update documentation, including backup and recovery procedures.

Throughout the migration process, it's essential to involve stakeholders, communicate effectively, and have a rollback plan in case any issues arise. Additionally, consider best practices for security, compliance, and governance when migrating to Azure SQL Database.

What is the difference between SQL Server database & Azure SQL database

SQL Server and Azure SQL Database are both relational database management systems (RDBMS) developed by Microsoft, but they have some key differences:

1. Deployment Model:

   • SQL Server: It's an on-premises database management system that can be installed and managed on physical or virtual servers within an organization's own data center.
   • Azure SQL Database: It's a cloud-based service provided by Microsoft Azure. It is fully managed by Microsoft, meaning users do not need to worry about infrastructure maintenance, backups, or updates.

2. Management:

   • SQL Server: Requires manual management of tasks such as patching, backups, and scaling. Administrators are responsible for provisioning and maintaining hardware resources.
   • Azure SQL Database: Managed by Microsoft, which handles routine maintenance tasks such as patching, backups, and scaling. Users only need to focus on database design, development, and performance tuning.

3. Scalability:

   • SQL Server: Scaling up or out often requires manual intervention and may involve downtime.
   • Azure SQL Database: Offers automatic scalability options such as scaling up/down resources or scaling out with sharding. These can be performed with minimal or no downtime.

4. High Availability and Disaster Recovery:

   • SQL Server: High availability and disaster recovery solutions need to be configured and managed manually, such as using SQL Server AlwaysOn Availability Groups or database mirroring.
   • Azure SQL Database: Provides built-in high availability and disaster recovery features with options like automatic backups, geo-replication, and automatic failover groups.

5. Cost Model:

   • SQL Server: Typically involves upfront costs for purchasing licenses and ongoing costs for maintenance, support, and hardware.
   • Azure SQL Database: Follows a pay-as-you-go model, where users are charged based on their resource consumption (e.g., storage, compute) and service tier.

6. Security:

   • Both SQL Server and Azure SQL Database offer robust security features such as encryption, access controls, and auditing. However, Azure SQL Database may provide additional security benefits due to being a cloud service, such as built-in threat detection and advanced threat protection.

7. Integration with Azure Services:

   • Azure SQL Database can seamlessly integrate with other Azure services such as Azure Active Directory, Azure Key Vault, Azure Monitor, and Azure Data Factory for advanced analytics, security, and monitoring capabilities.

In summary, while SQL Server and Azure SQL Database both offer relational database management solutions, Azure SQL Database provides additional benefits such as automatic management, scalability, high availability, and integration with other cloud services, making it an attractive option for organizations looking to leverage the cloud for their database needs

Page-level security in Power BI

Page-level security in Power BI allows you to control which users or groups have access to specific pages within a report. This feature is useful when you want to restrict access to certain data or visualizations based on user roles or permissions. Here's how page-level security works in Power BI:

1. Enable Page Level Security:

   • Page-level security is available in Power BI Premium and Power BI Pro licenses.
   • To enable page-level security, open your Power BI report in Power BI Desktop.

2. Create Roles:

   • In Power BI Desktop, go to the "Modeling" tab and click on "Manage Roles."
   • Define roles based on the criteria you want to use for page-level security. For example, you might create roles for different user groups or departments.

3. Assign Users or Groups to Roles:

   • After creating roles, assign users or groups to each role.
   • You can assign individual users or groups from your organization's Active Directory.

4. Set Visibility for Pages:

   • For each role, specify which pages in the report should be visible to users assigned to that role.
   • You can set visibility at the page level by right-clicking on a page tab and selecting "Page Information," then choosing the roles that should have access to that page.

5. Test and Validate:

   • After configuring page-level security, test the report to ensure that users only have access to the pages they are authorized to view.
   • Verify that users in different roles see the appropriate pages based on their assigned permissions.

6. Publish the Report:

   • Once you have configured page-level security and validated the report, publish it to the Power BI service.
   • Users accessing the report in the Power BI service will only see the pages they are authorized to view based on their assigned roles.

Page-level security in Power BI provides granular control over access to report pages, allowing you to tailor the viewing experience for different user groups or roles within your organization. It's particularly useful when you need to restrict access to sensitive data or visualizations while still providing access to other parts of the report.