who am i? - microsoft...who am i? •sql server developer & dba •author on simple talk...
TRANSCRIPT
![Page 1: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/1.jpg)
![Page 2: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/2.jpg)
Who am I?
• SQL Server Developer & DBA
• Author on Simple Talk
• Co-leader of SQL South West User Group
• Co-organiser of SQL Saturday Exeter / Data In Devon (Apr 26/27 2019)
• SQLBits Committee Member
![Page 3: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/3.jpg)
Agenda
• Why do we want to keep a DBA happy?
• Development process• Requirements
• Good coding practice
• Source Control
• Unit Tests
• Continuous Integration
![Page 4: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/4.jpg)
![Page 5: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/5.jpg)
THIS IS NOT A PERFORMANCE TUNING SESSION
![Page 6: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/6.jpg)
Why do we want to keep a DBA happy?
![Page 7: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/7.jpg)
Why?• Who does database change deployment?
• Who is responsible for the databases in Production?
![Page 8: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/8.jpg)
The DBA
![Page 9: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/9.jpg)
Management We need this change live tonight.
Developer
DBA
Can we deploy this change now? I’ll see what
I can do.
It depends. . .
![Page 10: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/10.jpg)
Developers and DBAs need to talk!!
![Page 11: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/11.jpg)
What do you talk about?
• Development process
![Page 12: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/12.jpg)
Development Process
Continuous Integration
5
Requirements
1
Develop
2
Unit test
3
Source control
4
Source control
4
![Page 13: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/13.jpg)
Get management buy in and understanding
![Page 14: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/14.jpg)
What else do you talk about?
• How much lead time does the DBA need for deployment
• Discuss code requirements up front and during development process
• Any coding Do and Do nots
• Indexing – who does it?
• Any naming convention
• Testing
• Code/peer review
• How does the DBA deploy?
![Page 15: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/15.jpg)
Development Process
Requirements
1
![Page 16: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/16.jpg)
Requirements• What are they?
• Why are they important?
![Page 17: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/17.jpg)
![Page 18: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/18.jpg)
Produce a list of all people who have worked here for a long service award
Produce a list of all people who have worked here for over 10 years
Produce a list showing Firstname, Surname, Annual Salary and start date of all employees who have
worked here for more than 10 years
![Page 19: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/19.jpg)
Development Process
Requirements
1
Develop
2
![Page 20: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/20.jpg)
Good coding practice
• Comment your code
• Do not use select *
• Do not use subqueries/functions in where clause
• Do not use inappropriate data types
• Do not insert without a column list
• Use Set rather than select when populating variables
• Use temporary tables and table variables appropriately
![Page 21: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/21.jpg)
Development Process
Requirements
1
Develop
2
Unit test
3
![Page 22: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/22.jpg)
Unit tests• What is a unit test?
• What do you test?
• How do you unit test?
![Page 23: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/23.jpg)
What is a unit test
• A test that tests one piece of functionality ONLY
• Must be quantifiable and measurable
![Page 24: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/24.jpg)
Produce a list showing Firstname,
Surname, Annual Salary and start date
of all employees who have worked here for
more than 10 years
• Test: returns someone who has worked here for more than 10 years
• Test: does not return someone who has not worked here for 10 years
• Test: Multiple rows
![Page 25: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/25.jpg)
How do we test
• tSQLt
• Redgate SQL Test
![Page 26: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/26.jpg)
Source control• What is source control?
• What tools to use?
• How?
![Page 27: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/27.jpg)
What is source control?
![Page 28: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/28.jpg)
A component of software configuration management, version control, also known as revision control or source control, is the management of changes to documents, computer programs, large web sites, and other collections of information.
![Page 29: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/29.jpg)
What tools can I use?
Repository• Git
• Bitbucket
• TFS
• Etc. . . .
With• Redgate’s SQL Source Control
• ApexSQL
• VersionSQL
![Page 30: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/30.jpg)
Development Process
Continuous Integration
5
Requirements
1
Develop
2
Unit test
3
Source control
4
Source control
4
![Page 31: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/31.jpg)
Continuous integration
• What is it
• Why use it
• Tools
![Page 32: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/32.jpg)
What is continuous integration
• Continuous Integration (CI) is a development practice that requires developers to integrate code into a shared repository several times a day. Each check-in is then verified by an automated build, allowing teams to detect problems early.
![Page 33: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/33.jpg)
Why use continuous integration
• Integrates all developers code
• Check everything works together
• Runs all tests every time
![Page 34: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/34.jpg)
Tools for continuous integration
• Teamcity
• Bamboo
• Jenkins
• Azure DevOps (formerly known as VSTS)
• Etc. . . .
![Page 35: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/35.jpg)
ManagementWe need this
change live tonight.
Developer
DBA
We have a deployment to do
tonight is that going to be OK?
I’ll see what I can do.
Yep, that’s fine, I’ll be waiting, anything
I can do to help?
![Page 36: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/36.jpg)
Any questions????
![Page 37: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/37.jpg)
References• Code smells: https://www.red-gate.com/simple-talk/sql/t-sql-
programming/sql-code-smells/
• Building a Team City environment: https://www.red-gate.com/simple-talk/blogs/installing-and-setting-up-teamcity/
• tSQLt: https://tsqlt.org/
![Page 38: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/38.jpg)
How to contact me.
• Blog at https://www.red-gate.com/simple-talk/author/annette-allen/
• @Mrs_Fatherjack
![Page 39: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/39.jpg)
![Page 40: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/40.jpg)
![Page 41: Who am I? - Microsoft...Who am I? •SQL Server Developer & DBA •Author on Simple Talk •Co-leader of SQL South West User Group •Co-organiser of SQL Saturday Exeter / Data InAgenda](https://reader031.vdocuments.us/reader031/viewer/2022011911/5f95671d98c0f054b7423e16/html5/thumbnails/41.jpg)