Database automated deployment and versioning ...for smart people
Download as PPT, PDF0 likes476 views
There are a lot of tools which allows us automate deployment process for databases. Those tools could be divided into two big groups: #1. Tools that uses general purpose language (Ruby, C#, Java, Python) for writing migration scripts. #2. Tools that uses SQL language for writing migration scripts. First group of tools gives for developers productive gain but leaves database administrator completely out of development process which is really bad idea. Second set of tools requires a lot of additional work - every single change should be written as separate database patch. This slows down our work => make it more expensive. Oblivious solution is to create the third set of tools... or at least just one which would be friendly to both DBAs and DEVs. What about auditors? They should be happy too!
![Running RoundhousE v0.8.0.305 against (local) - TestRoundhousE.
Looking in C:coderoundhousecode_dropsampledeployment..dbSQLServerTestRoundhousE for
scripts to run.
Please press enter when ready to kick...
==================================================
Setup, Backup, Create/Restore/Drop
==================================================
Creating TestRoundhousE database on (local) server if it doesn't exist.
==================================================
RoundhousE Structure
==================================================
Running database type specific tasks.
Creating RoundhousE schema if it doesn't exist.
Creating [Version] table if it doesn't exist.
Creating [ScriptsRun] table if it doesn't exist.
Creating [ScriptsRunErrors] table if it doesn't exist.
==================================================
Versioning
==================================================
Attempting to resolve version from
C:coderoundhousecode_dropsampledeployment_BuildInfo.xml using //buildInfo/version.
Found version 0.8.0.305 from C:coderoundhousecode_dropsampledeployment_BuildInfo.xml.
Migrating TestRoundhousE from version 0 to 0.8.0.305.
Versioning TestRoundhousE database with version 0.8.0.305 based on
http://roundhouse.googlecode.com/svn.](https://image.slidesharecdn.com/techtalkdbversioning-121205043120-phpapp01/85/Database-automated-deployment-and-versioning-for-smart-people-18-320.jpg)
More Related Content
What's hot (20)
Viewers also liked (10)
Similar to Database automated deployment and versioning ...for smart people (20)
Database automated deployment and versioning ...for smart people
- 1. 于仂仄舒亳亠从仂亠 于亠亳仂仆亳仂于舒仆亳亠 亳 亠仍亳亰 弍舒亰 亟舒仆仆 ...亟仍 仄仆亳从仂于 =)
- 2. 仂仗仂弍亠仄 仆舒亶亳 仂于亠 仆舒 仍亠亟ﾑ亳亠 于仂仗仂: 舒亠仄 仆亢仆仂 舒于仂仄舒亳亰亳仂于舒 仗仂亠 亠仍亳亰舒 亳亰仄亠仆亠仆亳亶 于 ? 舒亠仄 仆亢仆舒 亳亰仂仍亳 personal / dev / test / staging / production 仂从亢亠仆亳亶? 舒亠仄 仆亢亠仆 continious integration 仗仂亠? (仍仂仄舒仍-于亳亟亠仍-仗仂亳仆亳仍) 舒从 亟亠仍舒 仗仂亠 于仆亠亠仆亳 亳亰仄亠仆亠仆亳亶 于 从 仗亠亟从舒亰亠仄仄? 舒从 仗仂仍仆仂亠仆仆仂 于从仍ﾑ亳 于 continious integration 仗仂亠? 舒从 仗仂亟亟亠亢亳于舒 亳仆仂仆亳亰舒亳 仗亳仍仂亢亠仆亳 亳 仆舒 于亠 仂从亢亠仆亳?
- 3. 舒亠仄 仆亢仆仂 舒于仂仄舒亳亰亳仂于舒 仗仂亠 亠仍亳亰舒 亳亰仄亠仆亠仆亳亶 于 ?
- 4. 舒亠仄 仆亢仆舒 亳亰仂仍亳 personal dev test staging production 仂从亢亠仆亳亶?
- 5. 舒亠仄 仆亢亠仆 continious integration 仗仂亠? (仍仂仄舒仍-于亳亟亠仍-仗仂亳仆亳仍)
- 6. Hello. Am I at the office?! Certainly I am. I sleep on the operating table! 舒从 亟亠仍舒 仗仂亠 于仆亠亠仆亳 亳亰仄亠仆亠仆亳亶 于 从 仗亠亟从舒亰亠仄仄?
- 7. 舒从 仗仂亟亟亠亢亳于舒 亳仆仂仆亳亰舒亳 仗亳仍仂亢亠仆亳 亳 仆舒 于亠 仂从亢亠仆亳?
- 9. 仂亟亠仍 仍亳礌亳 束仗仂亟 亠仍亳亰損 (ad hoc merging model) 亅舒 仄仂亟亠仍 舒亰舒弍仂从亳 仗仂亰于仂仍磳 仆亠弍仂仍亳仄 从仂仄舒仆亟舒仄 舒亰舒弍仂亳从仂于 于仆仂亳 亳亰仄亠仆亠仆亳 于 仂亟仆 亳 亢亠 亠仍亠于 , 仗亳 仂仄 从舒亢亟亶 亳亰 仆亳 舒弍仂舒亠 仂 于仂亳仄 亳亰仂仍亳仂于舒仆仆仄 从亰亠仄仗仍仂仄 . 仂从仂仍从 舒亰舒弍仂亳从亳 舒弍仂舒ﾑ 仍亳仆仂亶 仂仆亳 仄仂亞 仗仂于仂亟亳 仂仍舒亟从 于仂亳 亳亰仄亠仆亠仆亳亶 于 亳亰仂仍亳亳. 亠 仆亢仆仂 于仆仂亳 仆亳从舒从亳 仆 仗舒于仂从. 仂舒仂仆仂 仗仂 亰舒于亠亠仆亳 舒亰舒弍仂从亳 亳亳 亟亠仍舒 diff 仄亠亢亟 舒弍仂亠亶 亳 亠仍亠于仂亶 亳 仗仂仍亳 SQL 亳亰仄亠仆亠仆亳礆亳. 亠仍亳从舒 于亠仂仆仂 从仂仆仍亳从仂于 仍亳礌亳 亳亰仄亠仆亠仆亳亶, 仗仂从仂仍从 舒亰舒弍仂亳从亳 亳仆仂仆亳亰亳ﾑ 于仂亳 亳亰仄亠仆亠仆亳 仂亟仆仂亶 亳 仂亶 亢亠 亠仍亠于仂亶 . 仍亳 舒亰舒弍仂亳从亳 于仆亠 亳亰仄亠仆亠仆亳 于 仂亟亳仆 亳 仂 亢亠 仂弍亠从, 仂 于亠仍亳从舒 于亠仂仆仂 仂 亳 亳亰仄亠仆亠仆亳 弍亟 弍亠仍亠亟仆仂 仗仂亠礌 于仂 于亠仄 亳仆仂仆亳亰舒亳亳. 仂于舒ﾑ 亠弍仂于舒仆亳 仗仂 从仂仂亟亳仆舒亳亳 仍亳礌亳亶 于 亠仍亠于 , 亳 仗亠亳仂亟亳仆仂 亟仂仍亢仆舒 弍 舒亟亠从于舒仆舒 亳仆亠仆亳于仆仂亳 于仆仂亳仄 亳亰仄亠仆亠仆亳亶. 亠 仗舒于仍亠仆亳 于亠亳礆亳 仆舒 仂于仆亠 仂弍亠从仂于 . 仍亳 弍仍 仂弍仆舒亢亠仆 弍舒亞, 仂 舒仆仂于亳 仂亠仆 仍仂亢仆仂 仂仗亠亟亠仍亳 仂 亳仄亠仆仆仂 仗亳于亠仍仂 从 亠亞仂 于仂亰仆亳从仆仂于亠仆亳. 仂仄亠 仂亞仂 仂仗亠舒亳 仂从舒舒 仆舒 仗亠亟亟 于亠亳 弍亠亰 仆仂亞仂 于仄亠舒亠仍于舒 弍亟亠 仂亰仆舒舒 仗仂亠 于亠 亳亰仄亠仆亠仆亳亶 亠从亠亞仂 亠仍亳亰舒.
- 11. 亠亳仂仆亳仂于仆亳亠 亳 仍亳礌亳亠 仆舒 仂于仆亠 仂弍亠从仂于 亠仄 舒亢亟亶 亳仆亟亳于亳亟舒仍仆亶 仂弍亠从 亠仄 仆舒仂于亳 于 于亳亟亠 仂亟亠仍仆仂亞仂 从亳仗舒 于 亳亠仄亠 从仂仆仂仍 于亠亳亶, 仂 仗仂亰于仂仍磳 仗仂仍亠亢亳于舒 亳仂亳 亳亰仄亠仆亠仆亳亶 从仂仆从亠仆仂亞仂 仂弍亠从舒. 舒亰舒弍仂亳从亳 从舒从 亳 于 仗亠亟亟亠亶 仄仂亟亠仍亳 舒弍仂舒ﾑ 仆舒仗礆 亢亳于仂亶 , 仂 仗仂舒亠 仂仍舒亟从 亳亰仄亠仆亠仆亳亶. 仂亢仆仂 亳仗仂仍亰仂于舒 仆亠从仂仂亠 于仂亰仄仂亢仆仂亳 亳亠仄 从仂仆仂仍 亟仍 仗舒于仍亠仆亳 仗仂亠从仂仄. 舒仗亳仄亠 于仂 于亠仄 从仂仄仄亳舒 仂舒于仍 从仂仄仄亠仆舒亳亳 亳 仗仂 亳仆仂仄舒亳 仆舒 仂于仆亠 仂亟亠仍仆 仂弍亠从仂于 亠仄 . 仂亢仆仂 舒仆舒于仍亳于舒 弍仍仂从亳仂于从 亳亰仄亠仆亠仆亳亶 仆舒 仂于仆亠 仂亟亠仍仆 从亳仗仂于 于仂 于亠仄 于仆亠亠仆亳 亳亰仄亠仆亠仆亳亶, 亠仍亳 亟舒仆仆亠 于仂亰仄仂亢仆仂亳 亠 亳亠仄 从仂仆仂仍 于亠亳亶. 仍舒亠 仗舒舒仍仍亠仍仆仂亶 舒亰舒弍仂从亳 从仂仆仍亳从 仍亳礌亳亶 舒亰亠舒ﾑ 仆舒仄仆仂亞仂 仗仂亠 亰舒 亠 仂亞仂 仂 亳亰仄亠仆亠仆亳 仆仂 仄亠仍从仂亟亳仗亠仆亶 舒舒从亠. 仗仂仍亰仂于舒仆亳亠 仂亶 仄仂亟亠仍亳 仆舒从仍舒亟于舒亠 弍仂仍 亟亳亳仗仍亳仆亳仂于舒仆仆仂 亳 弍仂仍亠亠 仂仄舒仍仆亶 仗仂亟仂亟, 仂 仄仂亢亠 弍 仆亠亟仂弍仆仂 舒亰舒弍仂亳从舒仄, 从仂仂亠 舒弍仂舒ﾑ 于 弍仂仍亠亠 亟亳仆舒仄亳仆仂仄 亠亢亳仄亠.
- 12. 仍舒亶仆 仄仂亟亠仍 舒亰舒弍仂从亳
- 13. 仍舒亶仆 仄仂亟亠仍 舒亰舒弍仂从亳 礆仂亠 亠亟舒从亳仂于舒仆亳亠 从亳仗仂于, 从仂仂亠 仆舒仂亟 仗仂亟 亳亠仄仂亶 从仂仆仂仍 于亠亳亶 于 仆亠从仂仂 仍舒 仄仂亢亠 弍 仗亠亳仄亠于仂仄 (仗亳于亠 仆仍仄 从仍亳亠仆舒仄 TFS, VSS). 亅仂 仗仂亠 仄亠仆舒亠 于亠仂仆仂 仂亟仆仂于亠仄亠仆仆仂亞仂 亠亟舒从亳仂于舒仆亳 仂亟仆仂亞仂 亳 仂亞仂 亢亠 从亳仗舒 于 弍仂仍亠亶 亠仗亠仆亳 亠仄 于 仗亠亟亟亠亶 仄仂亟亠仍亳. 仂亟于亳仆亠 SQL diff-亳仆仄亠仆 亳仗舒 SQL Compare 6 Professional 舒于仂仄舒亳亠从亳 仂仗亠亟亠仍磳 仗仂仍亠亟仂于舒亠仍仆仂 于仗仂仍仆亠仆亳 亳 从亳仗仂于 仆舒 亠仍亠于仂亶 仗亳仆亳仄舒 于仂 于仆亳仄舒仆亳亠 亳 于磶亳. 于亠 仆亠仄亠亟仍亠仆仆舒 仗仂于亠从舒 亳亰仄亠仆亠仆亳亶, 仗仂从仂仍从 仂仆亳 于仆舒舒仍亠 亟仂仍亢仆 弍 于仆亠亠仆 于 从亳仗, 舒 亢亠 仗仂仂仄 仆舒 亠仂于 . 仍 仂亞仂 仂弍 仗仂于亠亳 亳亰仄亠仆亠仆亳 亳 仆亢仆仂 仗亳仄亠仆亳 从 亠仂于仂亶 , 仂 亟仂弍舒于仍磳 亟仂仗仂仍仆亳亠仍仆亶 舒亞.
- 14. 仂亟亠仍 亳仆从亠仄亠仆仆 亳亰仄亠仆亠仆亳亶 (database migrations) SQL-based db migrator DSL-based db migrator
- 15. RoundhousE db versioning tools (part of the ChuckNorrisFramework) Name: Chuck Norris Email: chucknorrisframework@googlegroups.co m Website: http://groups.google.com/group/chucknor risframework Location: Chuck is everywhere Project: https://github.com/chucknorris/ roundhouse
- 16. 仆仂于仆亠 于仂亰仄仂亢仆仂亳 RoundhousE 仂亟亟亠亢亳于舒亠 Microsoft SQL Server, Oracle, Access MySQL 仂弍磦仍亠仆 从舒从 alpha (仄仂 亠舒仍亳亰舒亳, 从仂仂舒 弍仍舒 仂弍从舒舒仆舒 仆舒 仗仂亠从亠 TravelConfirm) 舒磦仍亠仆舒 仗仂亟亟亠亢从舒 PostgreSQL, SQLite 仗仂仍亰亠 SQL 亟仍 仄亳亞舒亳亶 舒仍亳亰仂于舒仆舒 仆舒 .NET, 亳仗仂仍亰亠 NHibernate (仄仂亞 仗仂亳仂于舒 仆舒 亟亞 弌丕 亳仍亳 仆舒 Mono/Linux/Mac OS X) Open source, 仍亳亠仆亰亳仂于舒仆舒 仗仂亟 Apache 2.0
- 17. 丕仆亳从舒仍仆亠 于仂亰仄仂亢仆仂亳 RoundhousE 弍仍舒亟舒亠 亟仂舒仂仆仂 仄仆仄 仗仂于亠亟亠仆亳亠仄 弍仂仍亠亶 亠仗亠仆亳 亟亢亠于亠仆仆舒 从 DBA 亠仄 亟亞亳亠 SQL- based 仄亳亞舒仂 于仂弍仂亢亟舒亠 仂 仆舒仗亳舒仆亳 仗舒亠亶/仄亳亞舒亳亶 亟仍 stateless 从亳仗仂于 亠 从亳仗 舒亰亟亠仍磳 仆舒 OneTime, AnyTime, Everytime 仂亟亟亠亢亳于舒亠 从亳仗, 从仂仂亠 亰舒于亳 仂 仂从亢亠仆亳 舒亶仆亠 亠从仂仄亠仆亟仂于舒仆舒 仗仂亠从舒仄 弍仂仍亳仄 从仂仍亳亠于仂仄 仗仂亠亟, 仆从亳亶 亳 仗亠亟舒于仍亠仆亳亶 仄亠仂 仂亟仆仂亞仂 从舒舒仍仂亞舒 仂 于舒仍从仂亶 仄亳亞舒亳亶 亳 仆亠从仂仍从仂 baseline, up, functions, procedures, functions 仂亟仂弍仆仂亠 仍仂亞亳仂于舒仆亳亠 仗仂亠舒 仄亳亞舒亳亳 仂亟仂弍仆舒 亠亞亳舒亳 仗亠仆 亳 仆亠亟舒仆 仄亳亞舒亳亶
- 18. Running RoundhousE v0.8.0.305 against (local) - TestRoundhousE. Looking in C:coderoundhousecode_dropsampledeployment..dbSQLServerTestRoundhousE for scripts to run. Please press enter when ready to kick... ================================================== Setup, Backup, Create/Restore/Drop ================================================== Creating TestRoundhousE database on (local) server if it doesn't exist. ================================================== RoundhousE Structure ================================================== Running database type specific tasks. Creating RoundhousE schema if it doesn't exist. Creating [Version] table if it doesn't exist. Creating [ScriptsRun] table if it doesn't exist. Creating [ScriptsRunErrors] table if it doesn't exist. ================================================== Versioning ================================================== Attempting to resolve version from C:coderoundhousecode_dropsampledeployment_BuildInfo.xml using //buildInfo/version. Found version 0.8.0.305 from C:coderoundhousecode_dropsampledeployment_BuildInfo.xml. Migrating TestRoundhousE from version 0 to 0.8.0.305. Versioning TestRoundhousE database with version 0.8.0.305 based on http://roundhouse.googlecode.com/svn.
- 19. ================================================== Migration Scripts ================================================== Looking for Update scripts in "C:coderoundhousecode_dropsampledeployment..dbSQLServerTestRoundhousEup". These should be one time only scripts. -------------------------------------------------- Running 0001_CreateTables.sql on (local) - TestRoundhousE. Running 0001_CreateTables_NH.sql on (local) - TestRoundhousE. Running 0002_ChangeTable.sql on (local) - TestRoundhousE. Running 0003_TestBatchSplitter.sql on (local) - TestRoundhousE. -------------------------------------------------- Looking for Run First After Update scripts in "C:coderoundhousecode_dropsampledeployment..dbSQLServerTestRoundhousErunFirstAfte rUp". -------------------------------------------------- -------------------------------------------------- Looking for Function scripts in "C:coderoundhousecode_dropsampledeployment..dbSQLServerTestRoundhousEfunctions". -------------------------------------------------- Running ufn_GetDate.sql on (local) - TestRoundhousE. -------------------------------------------------- Looking for View scripts in "C:coderoundhousecode_dropsampledeployment..dbSQLServerTestRoundhousEviews". -------------------------------------------------- Running vw_Dude.sql on (local) - TestRoundhousE. --------------------------------------------------
- 20. Looking for Stored Procedure scripts in "C:coderoundhousecode_dropsampledeployment..dbSQLServerTestRoundhousEsprocs". -------------------------------------------------- Running usp_GetDate.sql on (local) - TestRoundhousE. Running usp_SelectTimmy.sql on (local) - TestRoundhousE. -------------------------------------------------- Looking for Run after Other Anytime Scripts scripts in "C:coderoundhousecode_dropsampledeployment..dbSQLServerTestRoundhousErunAfterOt herAnyTimeScripts". -------------------------------------------------- Running createFiveItems.sql on (local) - TestRoundhousE. -------------------------------------------------- Looking for Permission scripts in "C:coderoundhousecode_dropsampledeployment..dbSQLServerTestRoundhousEpermissions ". These scripts will run every time. -------------------------------------------------- Running 0001_AppRole.sql on (local) - TestRoundhousE. Running 0002_AppReadOnlyRole.sql on (local) - TestRoundhousE. Running 0003_AppPermissionsWiring.sql on (local) - TestRoundhousE. LOCAL.GrantRobDataReaderDataWriterPermissions.ENV.sql is an environment file. We are in the LOCAL environment. This will run based on this check. Running LOCAL.GrantRobDataReaderDataWriterPermissions.ENV.sql on (local) - TestRoundhousE. TEST.GrantRobDataReaderDataWriterPermissions.ENV.sql is an environment file. We are in the LOCAL environment. This will NOT run based on this check. Skipped TEST.GrantRobDataReaderDataWriterPermissions.ENV.sql - No changes were found to run.
- 21. RoundhousE 于亠亟亠 仗仂亟仂弍仆 亠亞亳舒亳 皆河晦-从亳仗仂于
- 22. RoundhousE 仂仍亠亢亳于舒亠 亳舒亳亳 从仂亞亟舒 OneTime 从亳仗 弍仍 仂弍仆仂于仍亠仆
- 23. 仂仗仂? 31337