Although SQL Azure is based on SQL Server, there are some differences and limitations that you’ll need to be aware of.
The most common reason for any limitation is the services layer that sits on top of the real SQL Servers and simulates SQL Server to the consumer. This abstraction away from the physical implementation, or the routing engine itself, is usually the cause. For example, you can’t use the USE command in any of your scripts. To get around this limitation, you’ll need to make a separate connection for each different database you want to connect with. You should assume that each of your databases are on different servers.
Any T-SQL command that refers to the physical infrastructure is also not supported. For example, some of the CREATE DATABASE options that can configure which filegroup will be used aren’t supported, because as a SQL Azure user, you don’t know where the files will be stored, or even how they will be named. Some commands are outright not supported, like BACKUP.
You can only connect to SQL Azure over port 1433. You can’t reconfigure the servers to receive connections over any other port or port range.
You can use transactions with SQL Azure, but you can’t use distributed transactions, which are transactions that enroll several different systems into one transaction update. SQL Azure doesn’t support the network ports that are required to allow this to happen. Be aware that if you’re using a .NET 2.0 TransactionScope, a normal transaction may be elevated to a distributed transaction in some cases. This will cause an error, and you won’t know where it’s coming from.
Each table in your database schema must have a clustered index. Heap tables (a fancy DBA term for a table without an index) aren’t supported. If you import a table without a clustered index, you won’t be able to insert records into that table until one has been created.
All commands and queries must execute within 5 to 30 minutes. Currently the system wide timeout is 30 minutes. Any request taking longer than that will be cancelled, and an error code will be returned. This limit might change in the future, as Microsoft tunes the system to their customers’ needs.
There are some limitations that are very niche in nature, and more commands are supported with each new release. Please read the appropriate MSDN documentation to get the most recent list of SQL Azure limitations.
Why you can’t use USE
You can’t use the USE command in SQL Azure because the routing layer is stateful, because the underlying TDS protocol is session-based. When you connect to a server, a session is created, which then executes your commands. When you connect in SQL Azure you still have this session, and the fabric routes your commands to the physical
SQL Server that’s hosting the lead replica for your database. If you call the USE command to connect to a different database, that database may not be on the same physical server as the database you’re switching from. To avoid this problem, the USE command isn’t allowed.
Source of Information : Manning Azure in Action 2010
The most common reason for any limitation is the services layer that sits on top of the real SQL Servers and simulates SQL Server to the consumer. This abstraction away from the physical implementation, or the routing engine itself, is usually the cause. For example, you can’t use the USE command in any of your scripts. To get around this limitation, you’ll need to make a separate connection for each different database you want to connect with. You should assume that each of your databases are on different servers.
Any T-SQL command that refers to the physical infrastructure is also not supported. For example, some of the CREATE DATABASE options that can configure which filegroup will be used aren’t supported, because as a SQL Azure user, you don’t know where the files will be stored, or even how they will be named. Some commands are outright not supported, like BACKUP.
You can only connect to SQL Azure over port 1433. You can’t reconfigure the servers to receive connections over any other port or port range.
You can use transactions with SQL Azure, but you can’t use distributed transactions, which are transactions that enroll several different systems into one transaction update. SQL Azure doesn’t support the network ports that are required to allow this to happen. Be aware that if you’re using a .NET 2.0 TransactionScope, a normal transaction may be elevated to a distributed transaction in some cases. This will cause an error, and you won’t know where it’s coming from.
Each table in your database schema must have a clustered index. Heap tables (a fancy DBA term for a table without an index) aren’t supported. If you import a table without a clustered index, you won’t be able to insert records into that table until one has been created.
All commands and queries must execute within 5 to 30 minutes. Currently the system wide timeout is 30 minutes. Any request taking longer than that will be cancelled, and an error code will be returned. This limit might change in the future, as Microsoft tunes the system to their customers’ needs.
There are some limitations that are very niche in nature, and more commands are supported with each new release. Please read the appropriate MSDN documentation to get the most recent list of SQL Azure limitations.
Why you can’t use USE
You can’t use the USE command in SQL Azure because the routing layer is stateful, because the underlying TDS protocol is session-based. When you connect to a server, a session is created, which then executes your commands. When you connect in SQL Azure you still have this session, and the fabric routes your commands to the physical
SQL Server that’s hosting the lead replica for your database. If you call the USE command to connect to a different database, that database may not be on the same physical server as the database you’re switching from. To avoid this problem, the USE command isn’t allowed.
Source of Information : Manning Azure in Action 2010
|
|
0 comments
Post a Comment