a to z for sql azure databases
TRANSCRIPT
![Page 1: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/1.jpg)
CHAPTER
A to Z for
SQL Azure DatabasesAthens, GreeceAntonios Chatzipavlis
SQLschool.gr Founder, Principal Consultant
SQL Server Evangelist, MVP on SQL Server
Apr 25, 2015
![Page 2: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/2.jpg)
I have been started with computers.
I started my professional carrier in computers industry.
I have been started to work with SQL Server version 6.0
I earned my first certification at Microsoft as Microsoft Certified Solution
Developer (3rd in Greece) and started my carrier as Microsoft Certified
Trainer (MCT) with more than 20.000 hours of training until now!
I became for first time Microsoft MVP on SQL Server
I created the SQL School Greece (www.sqlschool.gr)
I became MCT Regional Lead by Microsoft Learning Program.
I was certified as MCSE : Data Platform, MCSE: Business Intelligence
Antonios ChatzipavlisDatabase Arch i tect
SQL Server Evangel is t
MCT, MCSE, MCITP, MCPD, MCSD, MCDBA,
MCSA, MCTS, MCAD, MCP, OCA, ITIL-F
1982
1988
1996
1998
2010
2012
2013
CHAPTER
![Page 3: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/3.jpg)
Antonios
Chatzipavlis
Vasilis
Ioannidis
Fivi
Panopoulou
Sotiris
Karras
TEAM
![Page 4: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/4.jpg)
My day
… and more than 3500 databases
![Page 5: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/5.jpg)
Follow us in social media
Twitter @antoniosch / @sqlschool
Facebook fb/sqlschoolgr
YouTube yt/user/achatzipavlis
LinkedIn SQL School Greece group
Pinterest pi/SQLschool/
![Page 7: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/7.jpg)
• Getting started with Microsoft SQL Azure Database
• Configuration and pricing
• Using Azure portal, SSMS and PowerShell with SQL Azure Databases
• Differences between SQL Server and Microsoft Azure SQL Database
• Migrating databases on SQL Azure databases
• Securing SQL Azure Database
• Backup and Restore SQL Azure databases
• Designing and tuning for scalability and high performance
• Monitoring and management
• V12 new features
Agenda
![Page 8: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/8.jpg)
CHAPTER
Getting started with
Microsoft SQL Azure Database
![Page 9: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/9.jpg)
• Infrastructure as a Service ( IaaS)
• Platform as a Service (PaaS)
• Software as a Service (SaaS)
The Microsoft Azure cloud
![Page 10: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/10.jpg)
• Creating a Microsoft account• http://signup.live.com
• Creating a Microsoft Azure subscription• http://www.windowsazure.com.
Getting signed up for SQL Database
![Page 11: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/11.jpg)
DEMO
CHAPTER
Creating SQL Azure database
![Page 12: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/12.jpg)
CHAPTER
Configuration and pricing
![Page 13: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/13.jpg)
• An instance of SQL Server can host multiple databases. • Create an administrator account user name with a strong password
• Specify the geographical region where the server should be located physically.
• To achieve the best performance, you should choose the region closest to your consumers. • Be sure that any Microsoft Azure cloud Web sites and services are hosted in the
same region as the SQL Database servers they communicate with.
• By locating both in the same region, you will avoid the bandwidth-based fee that gets incurred when your cloud sites, services, and databases communicate across different Azure regions.
• You will also reduce latency, which results in significantly better performance.
Creating a server
![Page 14: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/14.jpg)
• Quick Create• This is a 1-GB Web Edition SQL Database with the default collation.
• Custom Create• You have control over the options edition, size, and collation.
• Import• Import an existing database from a BACPAC file.
• A BACPAC file is, essentially, a backup of an entire database (schema and
data), stored as a binary large object (BLOB).
Creating a database
![Page 15: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/15.jpg)
It is important to understand that the IP address that needs to be
specified is not your IP address on your local network, but the IP
address that the Microsoft Azure datacenter sees when you
attempt to access something.
Setting firewall rules
![Page 16: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/16.jpg)
• SQL storage
• Client bandwidth
• Backup storage bandwidth
• Optimizing your costs
Budgeting for SQL Database
![Page 17: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/17.jpg)
• SQL storage
• Client bandwidth
• Backup storage bandwidth
• Optimizing your costs
Budgeting for SQL Database
The biggest cost of using SQL Database is for the actual disk
space required for storage in Microsoft Azure
If you already have an on-premises SQL Server database and
would like to calculate the size of the database for
estimating the migration cost to SQL Database,
SELECT (SUM(reserved_page_count) * 8192) /1024 / 1024 AS database_size_MB
FROM sys.dm_db_partition_stats
![Page 18: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/18.jpg)
• SQL storage
• Client bandwidth
• Backup storage bandwidth
• Optimizing your costs
Budgeting for SQL Database
• If you connect to your SQL database from within the same datacenter,
you do not incur any bandwidth charges for the data flowing either in
to or out of the database.
• If you connect to the database from outside the datacenter, your
database incurs only “egress” charges for bandwidth usage, which
means that data flowing out of the database to clients is charged, while
“ingress” flows (data coming into the database) are free.
• The charges are also different in different areas of the world.
• Client bandwidth pricing is based on the location of the Microsoft
Azure datacenter, regardless of where the client accessing the database
is located.
![Page 19: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/19.jpg)
• SQL storage
• Client bandwidth
• Backup storage bandwidth
• Optimizing your costs
Budgeting for SQL Database
• If you have a 100-MB database and you have backups taken
every day, your bandwidth charges are as follows
0.1 GB X $0.12/GB X 30 days = $0.36/month
• If you choose to take backups each hour, your bandwidth
charges would be as follows
0.1 GB X $0.12/GB X 24 hours/day X 30 days = $8.64/month
• If you choose to run those backups within the same
datacenter, the bandwidth is free.
![Page 20: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/20.jpg)
• SQL storage
• Client bandwidth
• Backup storage bandwidth
• Optimizing your costs
Budgeting for SQL Database
• Don’t store BLOBs in the database.
• Cycle out old records and tables in your database.
• Place your SQL Database in the same datacenter as your
websites, mobile services, and other Azure components that
will be clients of the database.
• Use a strategy for removing old backups such that you
maintain historybut reduce storage needs.
• Insteadof using a remote storage account for your backups,
use geo-replicated storage to keep from incurring bandwidth
charges.
• If you intend to use a substantial amount of Azure resources
for your application, you can choose to use a volume
purchase plan.
![Page 21: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/21.jpg)
CHAPTER
Using Azure portal, SSMS
and
PowerShell with SQL Azure Databases
![Page 22: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/22.jpg)
DEMO
CHAPTER
Using Azure
Management Portal
![Page 23: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/23.jpg)
DEMO
CHAPTER
Using SSMS
![Page 24: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/24.jpg)
• Installing the Microsoft Azure PowerShell cmdlets• http://azure.microsoft.com/en-us/downloads/
Using PowerShell
![Page 25: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/25.jpg)
DEMO
CHAPTER
Using PowerShell
![Page 26: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/26.jpg)
CHAPTER
Differences between SQL Server
and
Microsoft Azure SQL Database
![Page 27: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/27.jpg)
• Agent Service
• Audit
• Backup / Restore
• Browser Service
• Change Data Capture (CDC)
• Common Language Runtime (CLR)
• Compression
• Database Object naming convention
• Extended events
• Extended stored procedures
• File streaming (FILESTREAM)
Unsupported features before V12 release
• Full-Text Searching (FTS)
• Mirroring
• Partitioning
• Replication
• Resource Governor
• Service Broker
• System stored procedures
• Tables without a clustered index
• Transparent Data Encryption (TDE)
• USE statement
• XSD and XML indexing
![Page 28: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/28.jpg)
• Allows connections only over TCP/IP.
• Does not support Windows authentication.
• Often requires that @<server> is appended to the login user name in connection strings.
• Communicates only through port 1433• Does not support static or dynamic port allocation like SQL Server does.
• Fully support of Multiple Active Result Sets (MARS)
• SQL Database connections can drop unexpectedly, and you need to account for this condition in your applications. • The latest version of the Entity Framework (EF6, Microsoft’s recommended data access API for .NET) has a
new Connection Resiliency feature, which automatically handles the retry logic for dropped connections.
• The Microsoft Enterprise Library Transient Fault Handling Application Block, lets you define and implement retry strategies to deal with dropped connections.
• The ADO.NET SqlConnection class has an OpenWithRetry extension method that handles the retry logic based on the default retry policy
SQL Database Connection limitations
![Page 29: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/29.jpg)
CHAPTER
Migrating databases
on
SQL Azure databases
![Page 30: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/30.jpg)
DEMO
CHAPTER
Migrating data using Transact-SQL scripts
![Page 31: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/31.jpg)
DEMO
CHAPTER
Importing a BACPAC to Microsoft Azure SQL Database
![Page 32: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/32.jpg)
CHAPTER
Securing SQL Azure Database
![Page 33: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/33.jpg)
• SQL Database Firewall restricts access to SQL Database based on the origin IP address of the connection.
• SQL Database Firewall rules allow connections to be made to a SQL Database server, they do not authorize access to objects within the SQL Database instance.
• You can configure database-level firewall rules using the sp_set_database_firewall_rule stored procedure found in each database.
• SQL Database server-based firewall rules are defined for an entire SQL Database server, they are stored in the master database
Configuring SQL Database Firewall
![Page 34: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/34.jpg)
• Windows Authentication is not supported
• CREATE LOGIN <name> WITH PASSWORD='<Password>‘
• Server-level permissions• loginmanager
• This role has prermissions to create logins in the SQL Database server
• Similar to the securityadmin role in SQL Server
• dbmanager• This role has permissions to create databases in a SQL Database server.
• Similar to dbcreator role in SQL Server
• CREATE USER <name> FROM LOGIN <login_name>
• EXEC sp_addrolemember 'loginmanager', <‘user_name’>'
• EXEC sp_addrolemember 'dbmanager', <‘user_name’>
Authenticating and authorizing users
![Page 35: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/35.jpg)
CHAPTER
Backup and Restore SQL Azure databases
![Page 36: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/36.jpg)
• BACPAC files do not provide transactional consistency• BACPAC is by copying tables individually, and modifications could occur between
the time that the first table and last table are copied
• The Database Copy feature creates a new database from an existing SQL Database that is transactionally consistent when the copy finishes. • It does this by replicating any changes that are made to the source database while
the database is copying at the end of the process.
• Database copies can be created either on the same SQL Database server or on a different server within the same region.
CREATE DATABASE <dbname> AS COPY OF <server>.<dbname_copy>
Copying a database
![Page 37: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/37.jpg)
SELECT name, state_desc FROM sys.databasesWHERE name = <‘dbname‘>
You can obtain additional details about the copy operation
SELECT * FROM sys.dm_database_copies AS c INNER JOIN sys.databases AS d ON c.database_id = d.database_idWHERE d.name = <‘dbname‘>
Monitoring the progress of a database copy operation
![Page 38: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/38.jpg)
Exporting a BACPAC
![Page 39: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/39.jpg)
Exporting a BACPAC
![Page 40: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/40.jpg)
Scheduling BACPAC exports
![Page 41: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/41.jpg)
CHAPTER
Designing and tuning for scalability
and high performance
![Page 42: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/42.jpg)
• Opening late, closing early
• Pooling connections
• Recovering from connection faults
Managing SQL Database connections
![Page 43: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/43.jpg)
• Encapsulate complex data access in stored procedures
• Batch using table-valued parameters
• Use client-side storage and caching
• Avoid retrieving metadata at runtime
Minimizing round trips
![Page 44: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/44.jpg)
CHAPTER
Monitoring and management
![Page 45: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/45.jpg)
DEMO
CHAPTER
Monitoring using the management portal
![Page 46: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/46.jpg)
Dynamic management views and functions
![Page 47: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/47.jpg)
CHAPTER
V12 new features
![Page 48: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/48.jpg)
• Users in Contained databases
• Table partitioning
• Larger transactions
• Online index build and rebuild
• Checkpoint support
• ALTER TABLE enhancements
• TRUNCATE TABLE enhancements
• More DBCC commands
• Dynamic data masking preview
• Row-level security (RLS) preview
• V12 New Features
• Window functions in Transact-SQL queries
• .NET CLR integration
• Change tracking for data
• Table as a heap
• Application roles
• Several DMVs are added in V12
• Parallel processing for queries
• Briefer I/O latency
• Increased IOPS
• Log rate
http://azure.microsoft.com/en-us/documentation/articles/sql-database-preview-whats-new/
![Page 49: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/49.jpg)
Thank you
![Page 50: A to z for sql azure databases](https://reader031.vdocuments.us/reader031/viewer/2022020208/55ab56cc1a28abeb7e8b463a/html5/thumbnails/50.jpg)
SELECT
KNOWLEDGE
FROM
SQL SERVER
http://www.sqlschool.gr
Copyright © 2015 SQL School Greece