안녕하세요.

SQL Azure 관련해서 궁금한 점이 있습니다.

아래 질문은 MS Azure 영업담당자에게 질문을 한 내용인데, 답변을 주지 않아서 여기에 동일하게 질문드려 봅니다.

MS에서 제공하는 서비스의 범주가 "IaaS" "PaaS" "SaaS" 로 알고 있고, SQL Azure서비스는 "SaaS" 서비스로 알고 있습니다.

SQL Azure에서는 저희가 사용하는 주요기능이 모두 안되는 것으로 알고 있습니다.

온프레미스 환경처럼 SQL Server를 자체구축 하고 싶은데, "IaaS" 서비스를 받으며, SQL Server는 자체구축이 가능한 상품이 있는지 알고싶습니다. 만약 있다면, 라이센스나 기타 비용은 어떻게 되는지도 알고싶네요.

정리하면, 현재 구축 중인 서버환경을 클라우드로 옮길 때, 가장 적합한 솔루션을 알고 싶습니다.

현재 저희회사 솔루션에서 운영되는 RDBMS 환경은 MS-SQL2008 입니다.

주로 스크립트 언어를 이용하여, RDBMS를 제어하는데요.

제가 조사한 내용을 보니, 지원되지 않는 명령어들이 꽤 있더라구요.

저희 솔루션에서 사용하는 주요 기능들 입니다.

- CREATE DATABASE

- BAKUP / RESTORE

- DATABASE ATTACH / DETACH

- SQL Server Agent Service 

- SQL Server 인스턴스 자유롭게 추가/삭제

- 동적 프로시저 사용/실행

- 동적테이블 사용

자사 솔루션이 웹로그 분석 솔루션이다 보니,  로그통계처리를 위해 24시간 프로시저가 실행된다고 보면 됩니다.

읽어 주셔서 감사합니다.

아래는 제가 따로 조사한 내용 입니다.!!!

Azure 환경에서 제공되는 RDBMS라서  지원되지 않은 명령어들 [http://www.sqler.com/395723]

지원되는 T-SQL

http://msdn.microsoft.com/en-us/library/ee336270.aspx

지원되지 않은 T-SQL

http://msdn.microsoft.com/en-us/library/ee336253.aspx

SQL Azure에서 제약사항 [“pro_sql_database_for_windows_azure_2nd_edition” 책에서 발췌]

Limitations in SQL Database

As you’ve seen so far, creating databases and users requires manual scripting and switching database connections.

The fundamental differences between SQL Server and SQL Database lie in the basic design principles of cloud

computing, in which performance, ease of use, and scalability must be carefully balanced. The fact that user

databases can be located on different physical servers imposes natural limitations. In addition, designing applications

and services against SQL Database requires you to have a strong understanding of these limitations.

[Security]

Chapter 3 covers security in depth, but the following list summarizes important security considerations before you

deploy your SQL Database instances. From a security standpoint, you need to consider the following constraints:

• Encryption. Although SQL Database uses SSL for data transfers, it doesn’t support the dataencryption

functions available in SQL Server. However, SQL Database provides support for

hashing functions.

• SSPI authentication. SQL Database only supports database logins. As a result, network logins

using Security Support Provider Interface (SSPI) aren’t supported.

• Connection constraints. In certain cases, the database connection is closed for one of the

following reasons:

• Excessive resource usage

• Long-running query

• Long-running single transaction

• Idle connection

• Failover due to server failure

• Disallowed user names. Certain user names can’t be created, for security reasons:

• sa

• admin

• administrator

• guest

• root

• Login name. In certain cases, you may need to append the server name to the login name

to correctly log in, in this format: [loginName]@[servername]. So, avoid using the arrobas

character (@) in login names.

• TCP port 1433. Only TCP Port 1433 is allowed. It isn’t possible to define another listening port

for SQL Database.

[Backups]

Backing up your SQL Database instance is somewhat different from backing up traditional SQL Server databases. You

can’t back up a SQL Database instances in the traditional sense, nor can you restore a SQL Server database in SQL

Database. You do, however, have the ability to create a transactionally consistent clone of a SQL Database instance

before you export/import your data. You can expect the following regarding backups:

• Backup/Restore operations. These operations aren’t available. In addition, you may not

attach or detach a SQL Database instance.

• Clone operations. You may create a clone of a SQL Database instance into another one using

the CREATE DATABASE statement.

• Log files. You can’t access the database log files, nor can you create a log backup.

[Objects]

Certain objects available in SQL Server aren’t available in SQL Database. If your applications depend heavily on

these features, you may have difficulty using SQL Database, and you may need to rethink your application design to

accommodate these limitations. The following are some of the limitations that currently apply to SQL Database:

• CLR. The.NET CLR isn’t available in SQL Database. As a result, you can’t create extended

stored procedures or extended functions.

• System functions. SQL Database supports many system functions, including Aggregate

functions and Ranking functions. However, SQL Database doesn’t support RowSet functions,

including these:

• OPENQUERY

• OPENXML

• OPENROWSET

• OPENDATASOURCE

• System stored procedures. Only a small subset of system stored procedures are available in

SQL Database, in the following categories:

• Catalog stored procedures

• Database engine stored procedures

• Security stored procedures

• System tables. None of the system tables are available.

• System views. A subset of system views is available; you can access some of them from the

master database and others from user databases. The following are some of the system views

available (for a complete list, refer to the online MSDN library for SQL Database):

• sys.sql_logins

• sys.views

• sys.databases

• sys.columns

• sys.objects

• Heap tables. SQL Database doesn’t allow the use of heap tables. All tables must have a

clustered index.

[Miscellaneous]

In addition to the limitations outlined so far, additional components and options offered by SQL Server aren’t

available in SQL Database. For the most part, these limitations shouldn’t affect your application designs, but they’re

good to keep in mind:

• Maximum number of databases. You can create no more than one hundred and fifty user

databases.

• Distributed transactions. Although SQL transactions are supported, distributed transactions

aren’t supported across SQL Database instances.

• Collation. SQL Database supports collation at the column and database levels, or using an

expression at execution time. Server- level collations can’t be changed and are set to SQL_

LATIN1_GENERAL_CP1_CI_AS. Once set at the database level, the collation cannot be changed

using the ALTER DATABASE command.

• English language. SQL Database only supports the English language.

• Database size. You can only create databases of specific sizes, as outlined previously.

• Row Versioning-Based Isolation Levels. READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_

ISOLATION options are set at the database level, and cannot be changed.

• Database file placement. You can’t choose how the database files are deployed physically;

you can’t control filegroups, either. This is handled automatically by the Microsoft data center

for optimum performance.

• Trace flags. Trace flags aren’t available.

• SQL Server configuration options. None of the general SQL Server options are available,

including CPU and I/O affinity.

• Service Broker. The Service Broker isn’t available.

• Global temporary tables. The global temporary tables aren’t available. However, you can use

local temporary tables.

• SQL Server Agent. The SQL Server Agent isn’t available.