Skip to main content

Differences between SQL Server and Microsoft Azure SQL Database


Azure SQL Database (PaaS) Vs SQL Server (IaaS)

Azure SQL Database(PaaS):

  • Minimized overhead and cost when compared with an on-premises virtual machine.
  • Provisioning time is very less than SQL server (IaaS)
  • No full support for the virtual network in Azure SQL database (PaaS)
  • Contains fewer features than SQL server in a virtual machine.

SQL Server (IaaS):

  • Low overhead and cost, as it doesn't require infrastructure support.
  • Provisioned with full virtual network support for SQL server in a virtual machine.
Note: If you're running a virtual machine only for SQL database, it is recommended to migrate your database to Azure SQL database due to its lower cost and faster provisioning.

Differences between SQL Server and Microsoft Azure SQL Database
Azure SQL database and SQL server differ in various aspects such as:

  1. Size Limitations: SQL server has no upper limit for database size it can extend up to 500,000 TB, whereas Azure SQL database has maximum database size limit up to 4 TB which comes in premium service tier.
  2. Connection Limitations: SQL server is more flexible than Azure SQL database in establishing and maintaining connections. Some such scenarios are:
  • SQL database doesn't support windows authentication.
  • SQL database requires @<server> as appended to the login username in connection strings.
  • SQL server supports static and dynamic port allocation.
  1. Features Support: SQL database differs in supporting many of the SQL server features. Some of them are listed below.
  • Agent Service (cannot use the SQL Server Agent service to schedule and run jobs on SQL Database.)
  • Audit (Records of server and database events are not featured in SQL database auditing.)
  • Backup and Restore (SQL Database supports an automated backup schedule that creates transactionally consistent backups in the form of BACPAC files created in Azure storage.)
  • File Streaming (SQL database uses azure blob storage for unstructured data, whereas FILESTREAM and FileTable are not supported in SQL database as SQL server does.)

Labels:

Comments

Post a Comment

Popular posts from this blog

Troubleshooting transient connection errors to Azure SQL Database

Troubleshooting transient connection errors to Azure SQL Database Dear friends, I'm Yogesh. At my work place, I have faced these type of issues at times.  Let us understand what is this transient error?  A transient error has an underlying cause that soon resolves itself. It causes occasionally is when the Azure system quickly shifts hardware resources to better load-balance various workloads. Most of these reconfiguration events finish in less than 60 seconds.  During this reconfiguration time span, you might have connectivity issues to SQL Database.  To handle them, implement retry logic in their code instead of surfacing them to users as application errors. If your client program uses ADO.NET, your program is told about the transient error by the throw of  SqlException . What is the solution? Can Retry logic works? When your program communicates with SQL Database through third-party middleware, ask the vendor whether the middle ware contains retry logic for tr
Post a Comment
Read more

Geo-Replication in SQL Azure Database

Geo-Replication in SQL Azure Database Geo-Replication  is one of the Azure SQL features which allows making 3 readable replicas to your database in same or different data centers. Geo-Replication option is available for all databases and service tiers in all region. If it is enabled, the application initiates to a secondary database. we will review how to set up Geo-Replication on Azure SQL databases. Geo-Replication is an Azure SQL database feature that allows you to create a readable secondary database in the same region or cross-region. We can failover to the secondary database in case of an outage for a long time on the primary database server. We can also use this feature to migrate a database from one server to another server in the same or cross region with minimal downtime. Geo-replication uses the Always-on feature to replicate committed transactions to the secondary database asynchronously. Select the database, Click on 'Geo Replication' in left hand
Post a Comment
Read more

DTU and eDTU in Azure SQL

DTU and eDTU in Azure SQL The performance of SQL Database is based on DTU. According to Microsoft, a DTU is a unit of measure of the resources that are guaranteed to be available to a single Azure SQL Database at a specific performance level within a single database tier. A DTU combines CPU, memory, data I/O, and transaction I/O. Databases can be placed into an  elastic pool  on a SQL Database server that shares a pool of resources among those databases. The shared pool of resources are measured by  elastic Database transition units (eDTU) . The advantages of an elastic pool are: They are scaled automatically. They provide predictable costs. They are widely used for  varying  and  unpredictable usage patterns . Determining DTUs for Workloads ·          If you are planning to move your on-premises Databases to Azure SQL Database, Azure provides a  DTU Calculator  to find the approximate DTUs required for setting up the database. ·         
1 comment
Read more