![Page 1: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/1.jpg)
DevOpsforDatabasesWithexamplesinMSSQLServer.
SergeyOlontsevSQLServerMVP,[email protected]@SergeyOlontsevhttp://olontsev.ru/http://SergeyOlontsev.com/
![Page 2: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/2.jpg)
OlontsevAcademy©2017
SergeyOlontsevSoftwareExpert,KasperskyLab
SQLServer12+years.ETL,HighLoad andBigData,performancetuning.
[email protected]@SergeyOlontsevhttp://olontsev.ru/http://SergeyOlontsev.com/
![Page 3: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/3.jpg)
OlontsevAcademy©2017
DevOps
![Page 4: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/4.jpg)
OlontsevAcademy©2017
CI&CDContinuousIntegration(CI) isadevelopmentpracticethatrequiresdeveloperstointegratecodeintoasharedrepositoryseveraltimesaday.Eachcheck-inisthenverifiedbyanautomatedbuild,allowingteamstodetectproblemsearly.
Byintegratingregularly,youcandetecterrorsquickly,andlocatethemmoreeasily.
ContinuousDeployment(CD) iscloselyrelatedtoContinuousIntegrationandreferstothereleaseintoproductionofsoftwarethatpassestheautomatedtests.
![Page 5: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/5.jpg)
OlontsevAcademy©2017
Databasevs.Application
![Page 6: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/6.jpg)
OlontsevAcademy©2017
DatabaseStructure&Codecreate table [dbo].[Orders] (
[OrderId] int not null,[CustomerId] int not null
);go
create procedure [dbo].[GetOrders]asbegin
set nocount on;
select[OrderId],[CustomerId]
from [dbo].[Orders];end;go
![Page 7: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/7.jpg)
OlontsevAcademy©2017
DBA
![Page 8: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/8.jpg)
OlontsevAcademy©2017
DatabaseConfiguration&OtherStuff
• Securitypermissions• Serverconfiguration• Serverlevelobjects(linkedservers,jobs,custom
errors,etc.)• DatabaseIndexes&MaintenancePlans• Backups
![Page 9: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/9.jpg)
OlontsevAcademy©2017
InfrastructureasCode
![Page 10: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/10.jpg)
OlontsevAcademy©2017
DatabaseTesting
UnitIntegrationPerformance
![Page 11: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/11.jpg)
OlontsevAcademy©2017
WhatisTDD?
Test-firstdevelopmentJWriteenough codetopasstests
Unfortunately,notsowidelyusedbydatabasedevelopers.
![Page 12: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/12.jpg)
OlontsevAcademy©2017
SomeConcepts
Alltestsshouldbeautomatedandeasilyre-runnablebyanydeveloperinyourteam.
Probably“one-clicksolution”orevenfullyautomatedone.EitherintegratedinyourIDE(VisualStudio)orarecreatedusingthirdpartyframework
Itcouldbeevenstoredproceduresorpowershell scriptswrittenbyyou.
![Page 13: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/13.jpg)
OlontsevAcademy©2017
Benefits
• Easyandquickcodecheckingforcorrectbehaviorinspecifiedusecases.
• Simpleensuringthatnothingisbrokenwhenaddingnewfeatureorchanginglogic.
• Helpsyoueasilychangecodewrittenbyotherteammembers.
![Page 14: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/14.jpg)
OlontsevAcademy©2017
Whystillnotsoused?
• Lackofknowledge.• Notimeordesiretolearnanewtool.• Ittakesalotmoretimetowritetestsandpreparethe
infrastructure.
![Page 15: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/15.jpg)
OlontsevAcademy©2017
PerformanceConsiderations
• Yourtestsarejustcodethatrunsinsideyourdatabase.• Trynottoclearandinserttestdatabeforeeachtest.• Choosetoolsthatallowyoutoreusetestscode(for
example,executingaseriesofstoredproceduresonebyone).
![Page 16: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/16.jpg)
OlontsevAcademy©2017
UnitTesting
![Page 17: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/17.jpg)
OlontsevAcademy©2017
NotaPerfectApproach
• Youcannotimagineallthescenariosthatcouldhappenintherealworld.Butyou’lldefinitelyreducetheamountofbugs.
• Createintegrationtestingsolutionstocheckhowyoursystemworksasawhole.
• Loadtestingwillhelpyoutochecktheperformanceandbehaviorofcriticalqueries.
![Page 18: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/18.jpg)
OlontsevAcademy©2017
ContinuousIntegration
• Eachcommittriggersanautomaticallydeploymentandfulltestcircle.
• Don’tcommitsmallchangestothecommonrepository.
• Runsimpletestsonyourlocalmachinefirst.• AvailableinTFS,TeamCity,etc.
![Page 19: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/19.jpg)
OlontsevAcademy©2017
PerformanceTesting– TheWrongWay
![Page 20: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/20.jpg)
OlontsevAcademy©2017
PerformanceTesting– TheRightWay
![Page 21: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/21.jpg)
OlontsevAcademy©2017
NewVersion
![Page 22: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/22.jpg)
OlontsevAcademy©2017
GeneratingDeploymentScripts
![Page 23: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/23.jpg)
SSDTDemo
![Page 24: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/24.jpg)
OlontsevAcademy©2017
DevCon 2013
![Page 25: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/25.jpg)
OlontsevAcademy©2017
DeploymentPipeline
![Page 26: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/26.jpg)
OlontsevAcademy©2017
Tools
![Page 27: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/27.jpg)
OlontsevAcademy©2017
OneKeyDifference
![Page 28: DevOps for Databases - Microsoft€¦ · DevOps for Databases With examples in MS SQL Server. Sergey Olontsev SQL Server MVP, MCM. sergey@olontsev.biz @SergeyOlontsev http](https://reader036.vdocuments.us/reader036/viewer/2022071101/5fda4916dd4fa3013169449f/html5/thumbnails/28.jpg)
OlontsevAcademy©2017
Questions?