Monthly Archives: July 2013

Notes: Be a Successful DBA in the World of Cloud and On-Premise Data (Grant Fritchey)

Notes from 24-hours of PASS webinar:

Grant Fritchey
Product Evangelist
Works for Red Gate

What is Azure?
– Database as a service?
WA Data Management:
Non-Relational Storage:
–  Blob Storage
–  Tables
–  HDInsight
Relational Storage:
–  SQL Server in a VM (WA VM)
–  Windows Azure SQL Database (WASD)
Infrastructure as a Service (IaaS):
–  SQL Server in a WA VM
Platform as a Service (PaaS):
–  Windows Azure SQL Database (WASD)

Today’s Agenda:
Why consider Azure?
Speed of delivery
– 45 seconds to a database
– 3 to 5 minutes to a server
– self-service development and POC
Easy to provision
– automate provisioning
– epand and contract on demand
Inexpensive
–  Pay only for what you need
–  Reduced licensing costs
–     Only if you use their images for VMs
–  Radically reduced service overhead
–     reduce hardware and facilities costs, reduced infrastructure
Smooth Integration
–  Virtual Network
–  Use your own VMs
–  Direct Integrations

Will I lose my Job?
–  No, Maybe, if  you don’t change with the coming changes.
Technologies that were supposed to kill DBA position, but didn’t:
– NoSQL, SSD, Object-Relational Mapping, Cloud Computing, Self-Tuning, ColumnStore

Won’t the training take a long time?
–  WASD has all types except SQL Variant
–  PowerShell should be on training map
– It is SQL Server and T-SQL
– DMO are mostly available
–  database level wait stats are available instead of servr level
– Full functionality
–  Automation handled by PowerShell
– Manage WASD with PS
– Manage Windows and SQL Server with PS
–  Yes there will be a learning curve, but it shouldn’t be overwhelming

Questions:
WASD management with a team?
–   Need to have a Windows Live ID, but can set permissions per live ID
Point-in-Time restore with WASD? 
–  No, no backup mechanism currently, but you can export a bacpak which is not transactionaly consistent.  Need to create a copy and then export bacpak to have transactionaly consistent export
– can schedule exprot processes as of two weeks ago
Optimizations needed?
– not necessary to optimize for most system
– high performance, high control systems are not good for Azure
– defined resources in Azure is now available, but may still not work for high performance systems
Hyper-V supported, but not VMWare.
How does Azure work for development?
– depends on how you are developing
– not as good for large data sets
–  good for POCs with small datasets, throwaway
– good for web based development
– data going up is free, but pulling it down costs
– it is an additional tool in the toolbox
Do you need to know VM management to use SQL Server on VM?
– No, but depends on system
– Availability groups will require additional knowledge
Database size limitations:
150 GB in WASD, need to shard data to go large
VM is 16 TB
Azure Tools?
3rd party and SSMS

 

 

SQL Server 2005 migration to 2012

Upgrade to SQL Server 2012:
http://msdn.microsoft.com/en-us/library/bb677622.aspx

Supported upgrade versions: 2005 SP4 or better
http://msdn.microsoft.com/en-us/library/ms143393.aspx

Things not to do:
http://thomaslarock.com/2011/12/7-mistakes-you-cant-afford-to-make-when-upgrading-to-sql-2012/

Technical guide
http://www.google.com/url?sa=t&rct=j&q=sql%20server%202005%20migration%20to%20sql%20server%202012&source=web&cd=3&ved=0CEsQFjAC&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F9%2F5%2F3%2F9533501A-6F3E-4D03-A6A3-359AF6A79877%2FSQL_Server_2012_Upgrade_Technical_Reference_Guide_White_Paper.pdf&ei=s4zwUeExxNbJAfDXgYAM&usg=AFQjCNGkAVIR2vx0T52CugXZKPM-sTUnLA

SQL Server Lifecycle

I have been in several meetings discussing the need to move off of SQL Server 2005.  Below are the support end dates for the primay SQL Server versions and a link to get update information for new versions.  As you can see, mainstream support for SQL Server 2005 ended in 2011.  Extended Support for SQL Server 2005 ends on 4/12/2016.

Important Dates for SQL Server:
10/08/2013 SP2 is required for support for SQL Server 2008 R2
01/14/2014 SP1 is required for support of SQL Swerver 2012
07/08/2014 SQL Server 2008 and 2008 R2 mainstream support ends
04/12/2016 SQL Server 2005 Extended support ends
07/11 2017 SQL Server 2012 mainstream support ends

 

Products Released Lifecycle Start Date Mainstream Support End Date Extended Support End Date Service Pack Support End Date Notes
SQL Server 2005 Compact Edition 2/19/2007 4/12/2011 4/12/2016 7/10/2007
SQL Server 2005 Developer Edition 1/14/2006 4/12/2011 4/12/2016 7/10/2007
SQL Server 2005 Enterprise Edition 1/14/2006 4/12/2011 4/12/2016 7/10/2007
SQL Server 2005 Enterprise Edition for Itanium-based Systems 1/14/2006 4/12/2011 4/12/2016 7/10/2007
SQL Server 2005 Enterprise X64 Edition 1/14/2006 4/12/2011 4/12/2016 7/10/2007
SQL Server 2005 Express Edition 6/1/2006 4/12/2011 4/12/2016 7/10/2007
SQL Server 2005 Express Edition with Advanced Services 7/16/2006 4/12/2011 4/12/2016 7/10/2007
SQL Server 2005 Standard Edition 1/14/2006 4/12/2011 4/12/2016 7/10/2007
SQL Server 2005 Standard Edition for Itanium-based Systems 1/14/2006 4/12/2011 4/12/2016 7/10/2007
SQL Server 2005 Standard X64 Edition 1/14/2006 4/12/2011 4/12/2016 7/10/2007
SQL Server 2005 Workgroup Edition 1/14/2006 4/12/2011 4/12/2016 7/10/2007
SQL Server 2008 Developer 11/6/2008 7/8/2014 7/9/2019 4/13/2010
SQL Server 2008 Enterprise 11/7/2008 7/8/2014 7/9/2019 4/13/2010
SQL Server 2008 Express 11/11/2008 7/8/2014 7/9/2019 4/13/2010
SQL Server 2008 Express with Advanced Services 11/22/2008 7/8/2014 7/9/2019 4/13/2010
SQL Server 2008 R2 Datacenter 7/20/2010 7/8/2014 7/9/2019 7/10/2012
SQL Server 2008 R2 Developer 7/20/2010 7/8/2014 7/9/2019 7/10/2012
SQL Server 2008 R2 Enterprise 7/20/2010 7/8/2014 7/9/2019 7/10/2012
SQL Server 2008 R2 Express 7/20/2010 7/8/2014 7/9/2019 7/10/2012
SQL Server 2008 R2 Express with Advanced Services 7/20/2010 7/8/2014 7/9/2019 7/10/2012
SQL Server 2008 R2 Parallel Data Warehouse 11/9/2010 7/8/2014 7/9/2019 Hardware products will receive 5 years of support following Microsoft’s end of sales date for the Major Product version. Minor Product releases follow the Support Lifecycle of their respective Major Product versions.
SQL Server 2008 R2 Standard 7/20/2010 7/8/2014 7/9/2019 7/10/2012
SQL Server 2008 R2 Standard Edition for Small Business 7/20/2010 7/8/2014 7/9/2019 7/10/2012
SQL Server 2008 R2 Web 7/20/2010 7/8/2014 7/9/2019 7/10/2012
SQL Server 2008 R2 Workgroup 7/20/2010 7/8/2014 7/9/2019 7/10/2012
SQL Server 2008 Standard 11/6/2008 7/8/2014 7/9/2019 4/13/2010
SQL Server 2008 Standard Edition for Small Business 11/6/2008 7/8/2014 7/9/2019 4/13/2010
SQL Server 2008 Web 11/6/2008 7/8/2014 7/9/2019 4/13/2010
SQL Server 2008 Workgroup 11/6/2008 7/8/2014 7/9/2019 4/13/2010

Desired State Configuration in Windows Server 2012 R2 PowerShell

 

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/MDC-B302#fbid=yIkpgRpThsW

  • 8:36 – Demo PowerShell Code to Install IIS and Explain the syntax
  • 21:15 – Demo of how it can fix your mistakes –(fixes two webservers in seconds)
  • 26:55 – Demo – per node information and differences
  • 32:30 – Demo expanding to 3 servers from 2 with a simple text edit.
  • 37:40 – Success – 3 Servers now running
  • 38:45 – How does this work –
  • 42:16 – Eric Winter SCVMM “the Cloud OS Virtual Machine Role” working with DSC
  • 47:25 – Julian Dunn from OpsCode.com
  • 1:07:30 – The Pull Model vs Push Model

 

Azure SQL Database Service (formerly SQL Azure)

==========================================================================
==========================================================================
24 Hours of PASS: SQLCAT: Windows Azure Data Platform

Premium version has been released for non shared resource utilization.  This will allow for high performance databases to use ASDS.

ASDS provides a primary database and two replicas. Failover to the replica is automated and will generally happen within 30 seconds of primary DB going down

SQL Azure Federations used for scale out (range based)
– requires additional round-trips causing increased latency
Custom sharding – done at development
–   generally using sharding with parition mapping.

Start small “lone database” when moving to cloud.

WA SQL Database will sometimes throttle connections until it can rebalance the system across the cluster.
– soft throttling will tell to retry
– hard throttling will kill connection
– Transient Fault handling application block added to provide retry functionality
–   It allows devleopers to set retry counts and retry intervals

An ISV in Asia is currently hosting 10s of 1000s of databases with 100s of 1000s of users

How are ASDS backups handled.  Don’t provide data recovery via backups.  An export is required to have data secured.

Data warehousing capabilities available via WA VM.  Office 365 and Power BI give BI capabilities.  Warehousing may come later as platform matures.
==========================================================================
==========================================================================

 

SQL Server 2012 SP1 CU2 and SQL Server 2014 support Azure backups using To URL.

Tools and Utilities Support:
http://msdn.microsoft.com/en-us/library/windowsazure/ee621784.aspx
Business Continuity in Windows Azure SQL Database:
http://msdn.microsoft.com/en-us/library/windowsazure/hh852669.aspx

Table Partitioning

Partitioned Tables and Indexes in SQL Server 2005:
http://msdn.microsoft.com/en-US/library/ms345146(v=SQL.90).aspx
Handling Large SQL Server Tables with Data Partitioning:
http://www.mssqltips.com/sqlservertip/1200/handling-large-sql-server-tables-with-data-partitioning/
Using Table and Index Partitioning:
http://msdn.microsoft.com/en-us/library/ms162136.aspx

Table Partitioning Resources:
http://www.brentozar.com/sql/table-partitioning-resources/

Support for 15,000 Partitions in SQL Server 2008 SP2

Data transfer using Partitioning:
http://msdn.microsoft.com/en-us/library/ms191160(v=SQL.105).aspx
Switching data in and out of a SQL Server 2005 data partition:
http://www.mssqltips.com/sqlservertip/1406/switching-data-in-and-out-of-a-sql-server-2005-data-partition/
Useful queries: http://www.sqlwebpedia.com/content/partitioned-table-queries

--Just about everything here, including Total_page count for indexes. 

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, pf.name AS PFName, ps.name AS PSName, ds.name AS FGName, pv.value ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'Range Right' ELSE 'Range Left' END AS Type ,t.name AS DataType, pp.max_length, pp.PRECISION, pp.scale
,ps.is_default ,pv.parameter_id, pf.fanout AS PartitionCount
,i.index_id AS Index_ID,  p.partition_number, rows AS ApproxRowCount,
au.total_pages
--select *
FROM sys.partitions p  JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
LEFT JOIN sys.partition_range_values pv ON pf.function_id = pv.function_id
    AND p.partition_number = pv.boundary_id
JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id 

         AND dds.destination_id = p.partition_number
JOIN sys.partition_parameters pp ON pf.function_id = pp.function_id
JOIN sys.types t ON t.system_type_id = pp.system_type_id
JOIN sys.data_spaces ds ON ds.data_space_id=dds.data_space_id
JOIN (SELECT container_id, SUM(total_pages) AS total_pages
FROM sys.allocation_units
GROUP BY container_id) AS au ON au.container_id = p.partition_id
ORDER BY partition_number

–Without the datapage information:
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, pf.name AS PFName, ps.name AS PSName, ds.name AS FGName, pv.value
,CASE WHEN pf.boundary_value_on_right = 1 THEN ‘Range Right’ ELSE ‘Range Left’ END AS  Type ,t.name AS DataType, pp.max_length, pp.PRECISION, pp.scale
,ps.is_default ,pv.parameter_id, pf.fanout AS PartitionCount  ,p.partition_number ,rows AS ApproxRowCount
–select *
FROM sys.partitions p
JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
LEFT JOIN sys.partition_range_values pv ON pf.function_id = pv.function_id
AND p.partition_number = pv.boundary_id
JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
JOIN sys.partition_parameters pp ON pf.function_id = pp.function_id
JOIN sys.types t ON t.system_type_id = pp.system_type_id
JOIN sys.data_spaces ds ON ds.data_space_id=dds.data_space_id
WHERE i.index_id = 1
ORDER BY partition_number

–Very simplified version of above. But if you haven’t applied PF to a table yet, you need this.
SELECT  pf.name AS PFName, ps.name AS PSName, ds.name AS FGName, pv.value AS RangeValue  ,CASE WHEN pf.boundary_value_on_right = 1 THEN ‘Range Right’ ELSE ‘Range Left’ END AS Type    ,t.name AS DataType, pp.max_length, pp.PRECISION, pp.scale ,ps.is_default
,pf.fanout AS PartitionCount
–select *
FROM sys.partition_schemes ps
JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id
JOIN sys.data_spaces ds ON ds.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values pv ON pv.boundary_id = dds.destination_id
JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
JOIN sys.partition_parameters pp ON pf.function_id = pp.function_id
JOIN sys.types t ON t.system_type_id = pp.system_type_id