Transcript ModernDBAx
The modern DBA Presented by Jean-René Roy SQL Server MVP Who is Jean-Rene Roy? • • • • Independent Consultant for 25 years SQL Server MVP Software , DB Architect and team lead for many enterprises Program Lead for OttawaSQL.Net and .NET Ottawa Community • Founder of SQLTeach and DevTeach • SQL Server / CRM Dev / TFS Specialist • System Architect for TBS, HoC, CCA, Justice.ca DevTeach Call for Speakers Modern DBA Agenda • Disclaimer • Tools that help the Modern DBA • SQL Server Tips and Tricks • What the Modern DBA need to know about EF • What the Modern DBA need to know about SQL Azure • What the Modern DBA need to know about SQL Server Project Modern DBA Disclaimer • Opinions of the Modern DBA come from my 25 years of experiences not MS. • The Presentation is more for DBA supporting Development team not Operational DBA • These subjects are open for Debate if you pay the Beer! • The presentation focus on the WHY not the how. • Links to Code samples, Articles and blog will be provided in this presentation material. What does DBA mean? • DataBase • Administrator ? • Default • Blame • Acceptance Tools that help the Modern DBA • Searching in the Database Use sys Object and sp_help Code and Code T-SQL Use Red-gate Free search SSMS add-on Tools that help the Modern DBA • Data & Schema Compare and script Read Gate Data Compare SQL Delta Visual Studio SQL Server Data Tools (SSDT) Tools that help the Modern DBA • Data Diagram & Compare all in one tools • Data Compare and more in Visual Studio Using SSIS with Kingsway • Use it to Migrate and integrate data with: Dynamics CRM, AX, GP, AD, SharePoint… You will need the Balance Data Distributor SQL Server Tips and Tricks Use FOR XML with Path(“”) Use XML Schema when possible Dynamic SQL vs simulate dynamic SQL Show the new CREATE SEQUENCE Using SSB for tracking Change Using Output command in Insert and Updates Using Temporal Table in SQL Server 2016 Using For JSON in SQL Server 2016 Query Store in SQL Server 20016 SQL Server 2016 Row Level Security What is NoSQL? DBA thinks NoSQL is just for the Dev guy’s and it will go away like the Harper government. You may be right about the Harper government but NoSQL is here to stay and it’s something that is more related to the DBA role. DocumentDB NoSQL • What is Document DB? • Not for Document but for JSON • Query language is like TSQL • It got Stored Procedure, Index… What is IoT? • Internet of Things Azure Data Lake and Factory Get started with U-SQL What the Modern DBA need to know about Entity Framework (EF) • Do you have application that use EF in production? • EF use Dynamic Query • LINQ to Entity and LINQ to SQL • Can be use for OData Taking advantage of dacpac and bacpac • dacpac = Data-tier Application (Schema definition) • bacpac = Data-tier Application with data What new in SSMS 2016 for SQL Azure • • • • • • • Open in Management Portal Reports Properties Design Select Top 1000 Rows Edit Top 200 Rows Full-Text Indexes What the Modern DBA need to know about SQL Azure • How to Deploy your local DB to SQL Azure Using SSMS Deploy Using bacpac file Using a generated script Using a SQL Server Database Project (Publish) • Using SQL Azure mean some restrictions No KEY features (SSB, Agent, …) R Integration in SQL Server will be Revolutionary Backup to Windows Azure • Simple configuration UI • Easy creation of Azure credential • No overhead Backup to Windows Azure Tool ? http://www.microsoft.com/enus/download/details.aspx?id=40740 • Stand-alone Tool that adds backup to Windows Azure capabilities and backup encryption to prior versions of SQL Server One Cloud Backup strategy across prior versions of SQL Server including 2005, 2008, and 2008 R2 Adds backup encryption to prior versions, locally or in the cloud Takes advantage of backup to Azure Easy configuration SSMS reports single backup completion status for local storage and Azure storage Get it from this link: • http://www.microsoft.com/enus/download/details.aspx?id=40740 Hybrid cloud solutions Example of Availability Group Spanning On-premises and Windows Azure redmond.corp.microsoft.com Availability Group uswest.internal.cloudapp.net CSNB2252108-TA CSNB2252109-TA Virtual Network VPN Device Windows Cluster AGHybrid2 28 Easy on-ramp to cloud Deploy databases to Windows Azure VM • New wizard to deploy databases to SQL Server in Windows Azure VM • Can also create a new Windows Azure VM if needed • Easy to use • Perfect for database administrators new to Azure and for ad hoc scenarios • Complexity hidden • Detailed Azure knowledge not needed • Almost no overhead: defining factor for time-totransfer is database size Scenarios: DB1 Node1 Windows Azure Storage DB1 DB1 Migrate Database to another machine Basic Disaster Recovery Separation of Compute and Storage For Azure VM, expand drive and size of Xdrive Node2 Better together: IaaS VM per minute billing SQL Server Data Files in Windows Azure Using Visual Studio SQL Server Project • Where is the source of your Database? • How do you update SP and Function? • How do you update your environment Dev, UAT , Prod? Questions ?