JNetDirect Combine™
Contents
1. By 7 Create Biling Tables CS MS 8 Get server and job info Web Databases Container Scripts associated with the Billing Databases Container Figure 12 A sample package that deploys scripts to all databases and servers in the Web Databases Billing Databases Finance Databases and DBA Databases Containers Page 19 2005 2015 JNetDirect Inc Combine ox Database tools that Scale Notice that each script in the sample package of Figure 12 includes a SQL statement that verifies that changes and objects created in the script are indeed deployed successfully For example once a table is created the script verifies that a valid OBJECT_ID is available for the new table i e OBJECT_ID TableName IS NOT NULL and returns a single row to inform the user of the rollout results Scripts in the package are executed according to their order in the package tree When developers run the package scripts will be deployed on databases in the Containers of the Development Environment listed in Figure 11 and the deployment results are given in Figure 13 When the package is passed to QA engineers the package is deployed by a click of a button on all the target databases in the QA Environment shown in Figure 11 without making any modification to the package configuration or package content Execution results in the QA Environment are presented in Figure 14 In the same manner after the package is sent to Production DBAs need not make2. DEVSVR2 af _Containerserver ContainerDatabase _ obid gaave e A ed E Devsva1 DEVSvRI DBAMaint 275b2a9 6752 4755 b0c4 31b3 244ad58 devsvrl Check database i Table1 DEVSVRI DBAMaint a7464d98 efd5 4572 b8ba 6f18304287cf devsvrl Check Server Sti peas DEVSVR2 DBAMaint 4a1b0H a c02d 4eb3 9939 04ffe0b578bf devsvr2 Check Server Ste ctual EJ Resutts Output j DEVSVA2 DBAMaint 743b38a1 cBa2 4498 b897 b8d817b0K4F1 devsvr2 Check database T Container G3 Package BA Object Br Script Execution Completed j DEVSVR2 aa Package Output DemoProject 4 ajam Eer a E Results E Output Script Execution Completed Figure 2 The results displayed by Combine after selecting top 5 rows from sysindexes and running EXEC msdb sp_help_job against the DBA Databases container Static vs Dynamic Containers Two types of Containers are supported Static Containers and Dynamic Containers Each type uses a different technique to store and identify the set of target databases A Static Container consists of a fixed group of databases Databases are added to the Static Container by specifying the typical connection information such as the database name and SQL server name or IP address To add or remove databases from the Static Container the user must open the Container Manager and manually edit the Container configuration When scripts and queries are run against a Static Container Combine will retrieve the identifiers
3. electronic mechanical photocopying recording or otherwise or for any purpose without the express written permission of JNetDirect Inc JNetDirect Inc may have patents patent applications trademarks copyrights or other intellectual property rights covering subject matter in this document Except as expressly provided in any written license agreement from JNetDirect Inc the furnishing of this document does not give you any license to these patents trademarks copyrights or other intellectual property 2005 2015 JNetDirect Inc All righ Combine ox Database tools that Scale Page 4 Key Features of JNetDirect Combine Combine is the first development change management and code deployment tool designed to automate the lifecycle of database projects and provide agile code deployment solutions from Development to Quality Assurance QA and to Production Combine is designed to scale as it allows developers to collaborate and work on DB project releases together and then deploy the entire database code release by a click of a button on any number of databases and servers in parallel Combine is therefore extremely useful for small mid size and up to very large SQL server environments Additional features in Combine include the ability to run queries and execute scripts on any number of databases and servers in parallel patent pending technology Some of these novel features are highlighted below a
4. about indexes jobs and all other database objects by a click of a button Container user defined databases Container2 DBA databases Container3 Web databases Figure 1 An example of mapping databases to Containers Page 7 se 2005 2015 JNetDirect Inc Database tools that Scale Combine ex s Combine File Edit View Package Container Query Tools window Help OB oe te A ana ak A z ZpS 29 25 99 JEES WA e Untitled 3 1 SELECT TOP 5 FROM sysindexes tie je Po Development DBA Databases bd 2 3 EXEC msdb sp_help_job DEVSVA1 FinanceDey DEVSVA1 Web1Dev DEVSVR2 Billing DEVSVR2 DBAM aint j DEVSVR1 DBAM aint DEVSVR2 Web2Dev SZ Web Databases J DEVSVR1 Web1Dev g DEVSVA2 Web2Dev aaan 2 gt EY Individual Res ContainerServer ContainerDatabase id status first indid root minien k Aggregated Ta aH DEVSVAI DEVSVAT DBAMaint 1 18 Ox0B0000000100 1 0x0B0000000100 42 1 E Tabel DEVSVAT DBAMaint 1 2 OxoF0000000100 2 OxOF0000000100 7 3 EE Table pevsvet DBAMaint 1 o Ox1F0000000100 3 Ox1F0000000100 9 2 ee DEVSVRI DBAMAint Z 18 0x180000000100 1 0x0E 0000000100 62 2 E a DEVSVR2 DEVSVR1 DBAMaint 2 0 0x400000000100 255 Ox400000000100 0 0 DEVSVR2 DEVSVR2 GS 2 g DEVSVR2
5. any package changes and can deploy the entire package on all target databases in the Production Environment by a click of a button as shown in Figure 16 below Notice that the ContainerServer and ContainerDatabase columns in the grids result in the images below are added automatically by Combine to reflect the target database from which each row in the grid is returned Results returned from the package execution are displayed as aggregated results from all target databases and also include the execution plan and results for each individual database Notes 1 Once a package is executed Combine performs a set of tests and verifications to ensure that scripts in the package will be executed successfully For example database and server connectivity as well as proper authentication and credentials are verified for all databases involved in the package execution before Combine deploys any of the scripts in the package If any tests and checks are not successful Combine will notify you of all issues and will not execute any portions of the package In addition several screens are displayed before the package scripts are deployed to provide users with better control and visibility to the execution These screens and many other details involving the package execution can be found in the Combine User Manual 2 If multiple Environments are used to deploy code from one client machine as in the example of Figure 10 then using the Container Manager the us
6. containe 4 Billing Databases container Figure 8 Containers and their target databases in the Production environment To summarize the following figure describes the flow of code deployment between Dev QA and Production where each color denotes the appropriate group of target databases i e Containers across all environments Page 14 2005 2015 JNetDirec Combine e Database tools that Scale Development Dev environment Quality Aassurance QA environment aad Ed Fad ES ad fd Fd ES FinanceDev wowma Figure 9 Database groups and the flow of code releases between the Dev QA and Production environments Page 15 2005 2015 JNetDirect Inc Combine ox Database tools that Scale Page 16 The Dev QA Production Release Process Change management and code release processes are supported in Combine through several key features 1 Code packages A code package consists of scripts Each script in the package is assigned to a Container When running a code package each script will be deployed on all target databases in the associated Container 2 Passing code packages between Dev QA and Production Scripts are packaged into a single cpa file This file contains the text of the scripts and the name of the Container assigned to each script Entire releases can therefore be saved as a single file that can be passed viewed edited and deployed by individuals running Combine Mo
7. i e database and server names of the target databases from the Container configuration and then run the scripts on all those databases using the authentication type and credentials entered for the Container Static Containers are therefore useful to store Page 8 2005 2015 JNetDirect Inc All rights re Combine ox Database tools that Scale Page 9 groups of databases that are relatively static i e when databases that belong to the group are not created dropped or moved between servers frequently To demonstrate this fact consider the following counter example where Static Containers should not be used A Static Container named MSDB Databases consists of all msdb databases over all servers in the production environment and assume that a new instance of SQL server is installed every day In order to ensure that the MSDB Databases Static Container indeed holds all msdb databases the user must manually add the msdb database to the Container for each new server daily This maintenance overhead can be overcome by using Dynamic Containers Note The main advantage of Dynamic Container is that they allow users to share Environment and Container information from a single data repository Using Dynamic Containers developers only need to configure the Environments and Containers in their user interface once and a single person can maintain the data repository from that point on Dynamic Containers assume that a lis
8. the code package in Figure 12 against the QA Environment Results can be sorted using tools in the grid Page 21 R 2005 2015 JNetDirect Inc All rig Database tools that Scale Combine ox Page 22 File Edit View Package Query Tools Window Help DB oF a F IE Ga oa Ae gt AA SS 2 9 og z t m v 2g iia 2 a93eaQ GF Package Explorer 1x Create Web tables 3x EF Properties 1x EEE z5 AGA DemoProject i Ta ne r Generar 1 CONSTRAINT UQ_utbWebU UserID UNIQUE ees AEN Create Web tables 28 aer neon iisi Name Create Web tables 5 2 Create Web procedures 29 FirstName NVARCHAR 64 NOT NULL Nang pom see AES 3 Populate Web tables 30 LastWeme NVARCHAR 64 Container Web Databases FG 4 Add Web logins users and roles 31 Enail ddress NVARCHAR 64 NOT NULL CONSTRAINT DF_utbWebUsers_Email ddress DEFAU Description S ME 5 Finance Databases 32 AddressID INT ies 1 Create Finance tables 33 InsertTimeStampGMT DATETIME NOT NULL CONSTRAINT DF_utbWebUsers_InsertTimeStamp6 2 Create Finance stored procedures 34 UpdateTimeStampGNT DATETIME NOT NULL CONSTRAINT DF_utbWebUsers_UpdateTimeStampG 3 Populate Finance data 35 _co 4 Add Finance logins users and roles 36 a DBA Databases 37 Rollout verification 1 Create DBAMaint objects 368 IF OBJECT_ID utbWebUsers IS NOT NULL 2 Jobs DAA 2 39 SELECT Table utbUebUsers created success
9. JNetDirect Combine Introductory Guide Combine Database tools that Scale a o 2005 2015 JNetDirect Inc All ri Combine e Database tools that Scale JNetDirect Combine Copyright and Disclaimer c cccccscsssesessessessesseesessessessesesseseseseeaes 3 Key Features of JNetDirect Combine eseneseeseesessssesersesesresseserssesseseessresseserssressessrssressesnt 4 Containers and Environments seeeseseessesetssrereestssttetestestserestessesttestesstsetestensesetestesseestesrensetee 6 UO CUE POTONE E EE AE E E E 6 COMAE erisera E E E A E E R E E ES 6 Static vs Dynamic Containers ssseesseeeseseseseseseessttssttssessetssseeesstessresseeeserssseessresstessesset 8 MEU SUMS INS easiieii niiin a e a e aae eaaa a a aaa aei 12 The Dev QA Production Release Process cccssssccescseceeeesessececececeesesensaececeeeeeesensnteaeees 16 Best Practices I Sharing Environments and Containers by using a Combine Repository with Dynamic Continet S zesse ieaie e E E E EE E E 22 Best Practices II Tracking Deployments and DB Changes by Using a Change History REDOSHOLY eree e uments aes snes cies aeaatnae E E aanionaun 23 Page 2 2005 2015 JNetDirect Combine ox Database tools that Scale Page 3 JNetDirect Combine Copyright and Disclaimer This document and all sample applications therein are provided as guidelines and for informational purposes to JNetDirect Combin
10. P TABLE utbVebUsers 2s G9 26l 27 CREATE TABLE dbo utbWebUsers e UserID INT IDENTITY 1 1 CONSTRAINT UQ_ucb ebUsers_UsexID UNTOUE 29 FirstWeae MVARCHAR 64 NOT NULL 30 LastWame BVARCHAR 64 nj Esail ddress MVARCHAR 64 NOT MULL CONSTRAINT DF_utbWebUsera_EwailAddress DEFAUST R 32 AddcessID INT 331 Inser tTineStamp GT DATETIME XOT WULL CONSTRAINT DF_uthWebUsers IngereTiaeStampCHT DEFAULT 34 Up ateTineStaap OMT DATETIME WOT WILL CONSTRAINT DF_uthWebUsera_UpdateTineStampGMT DEFAULT 35 36 37 Rollout verification 35 IF OB2ECT_ D uebUebUsere IS NOT MULL 39 SELECT Table uth ebUsers created successfully on db name AS RolloutRes 40 ELSE at SELECT Ecrox cresting table uthWebUeers on db name AS RolloutRhes 42 oo 43 aa IF ORZECT_ED urbbogina I3 NOT NULL 45 DROP TABLE utbLoqins e eae T Contanas Package B Obyect Br Read 0 00 00 Orows Ln 9 Coh 1 L EIG Package Oupa 4 XxX Scripts associated with the MIS 2 Create Web procedures MIE 3 Populate Web tables Mey 4 Add Web logins users and roles ag 5 Finance Databases By 1 Create Finance tables Scripts associated with the Sy 2 Create Finance stored procedures Finance Databases Container LS 2 Populate Finance data 4 Add Finance logins users and rol ME 6 DBA Databases Scripts associated with the MB 1 Create DBAMaint objects S 2 Jobs DBA Databases Container Th 1 Create DBA Maintenance jobs
11. a I Package Results E Output DemoProject 1 Create Web tables OVR Figure 15 Non sorted execution results of the code package in Figure 12 against the Production Environment Results can be sorted using tools in the grid Best Practices I Sharing Environments and Containers by using a Combine Repository with Dynamic Containers Environments and Containers defined under the MyEnvironments node in the Container Manager are stored on the local user machine This includes Environment names Environment Variables Container names databases in Static Containers as well as the Reference Repository Static Container and the Queries used by Dynamic Containers In order to share the settings of Environments and Containers you can use a Combine Repository see installation scripts and manual for the repository on SQL Farms website or in the application F1 help and add it to the Container Manager Once added you can define your Environments and Container settings directly in the repository Alternatively you can first create Environments and Containers under the MyEnvironments node and then copy paste them to the repository Once the settings are available in the repository then all users can share the same configurations and settings 2005 2015 JNetDirect Inc All righ Combine o Database tools that Scale Page 23 The repository includes three built in roles read only change managers and admins Using these roles
12. bases as well as the script 08 are mapped to the DBA Databases Container and script 07 is associated with the Billing Databases Container Page 18 r 2005 2015 JNetDirect Inc All rig Database tools that Scale Combine e Be EAR Yew Pakage ery Tonk ido te A zl Pe Sh ee 7 Bie H 2S IEEE create web tables v x pt Preperies wae 1 Ol Create Web tables a E x 3l Drop new foreign keys B mee 1 Cieste Web taste Nane Create Wob tables DLS 2 Creste Web procedures a H FIS 2 Popadate Web tables S IF UDP ECT_I FR_utblogins utblebisers IS MOT MULL ET Cortaner Sk Delakaane E 4 Add Web logne users ande 6 ALTER TABLE utbkogins r E 5 Finance Databaces A DROP CONSTRAINT FK_utblogins_uthWebUsers Haupa Ciba Wah i Dy 1 Create Finance tables amp o 7 Ta gy 2 Coste Finance stored pr s Sy 2 Popdate Finance data 10 i EA 4 Add Finance logins user ti IP OBIECT ID PK utb eblzezrPayes_utbVebPages IS WOT MULL 5 Ga 6 8A Databaser i2 ALTER TABLE ucbWenUserPages EID 1 Ceste DRAMaint objects 13 DROP CONSTRAINT FK_utbWebUserPages_utb ebPages a 2 debs sd E Dy 1 Geste 984 Mamena 18 j RA Hepe 16 Ef UERECT_ID 7K uthWebUserPages utbtebUsers IS PYT NULL a ag crx 17 ALTER TABLE utbWebUserPages 18 DROP CONSTRAINT FK_utbWebUserPages_utbWebUsers 19 _ 69 20 21 Creete new scheme 22 23 IF OBTECT_ID uchWebUsexs IS NOT NULL 24 DRO
13. d Table 7 4 rows lt i w gt IES Package Results E Output Figure 13 Execution results of the code package in Figure 12 against the Development Environment Ele Edit View Package Query Tools window Help DBS Ala 4 wooo a 29 90 TEET IEE T GJ Package Explorer 3 x 7G Create web tables x T Properties ax Si gt Gt a e y 26 7 Ea JA DemoProject 27 CREATE TABLE dbo utbWebUsers p 2 Cene Web iati 28 UserID INT IDENTITY 1 1 CONSTRAINT UQ_utbWebUsers_UserID UNIQUE Name E Wer A gy 2 Create Web procedures 29 FirstName NVARCHAR 64 WOT NULL rnp ppc PEAS AE 3 Populate Web tables 30 Lest ane VARCHAR 64 Container Web Databases By 4 Add Web logins users and roles 31 EnailAddress NVARCHAR 64 NOT NULL CONSTRAINT D F_utbWebUsers_EmeilAddress DEFAU Descsiption AG 5 Finance Databases 32 AddressID INT GI Relativeras teate Web table Igy 1 Create Finance tables 33 InsertTimeStampGNT DATETIME NOT NULL CONSTRAINT DF_uthWebUsers_InsertTimeStampGl T IE 2 Create Finance stored procedures 34 UpdateTineStanpGMT DATETIME NOT NULL CONSTRAINT DF_utbWebUsers_UpdateTimeStamp6l My 3 Populate Finance data 35 GO E3 4 Add Finance logins users and rol 36 6 DBA Databases 37 Rollout verification ME 1 Create DBAMaint objects 38 IF OBJECT_ID utbWebUsers IS NOT NULL E 2 Jobs 39 SELECT Table utbWebUsers creat
14. e users only JNetDirect Inc makes no warranties either expressed or implied in this document Information in this document including samples URL and other Internet Web site references is subject to change without notice The risks of using this document or the results of the use of this document are the sole responsibility of the user The primary purpose of this document as well as the samples diagrams concepts and all other content provided in this document is to demonstrate reasonable use of particular features of Combine Most samples diagrams and other examples provided in this document do not include all of the code and operational scenarios that would normally be found in a full production system as this document is only focused on concepts and fundamental associated with the basic operation of Combine Technical support is not available for the samples demonstrated in this document Unless otherwise noted the example companies environments organizations databases people and events depicted throughout this document are fictitious and are not associated with any real company environment organization database person or event is intended or should be inferred Complying with all applicable copyright laws is the responsibility of the user Without limiting the rights under copyright no part of this document may be reproduced stored in or introduced into a retrieval system or transmitted in any form or by any means
15. e Quality Assurance QA environment and the Production environment In most companies databases and servers used by developers to write SQL code are separate from the databases and servers used by software engineers in QA which are also distinct from the databases and servers in production By using Combine Environments it is now possible to map groups of databases between these physical environments on the basis of their functionality The Development environment Assume that developers write code and test scripts on two SQL servers namely the DevSvr1 and DevSvr 2 servers see Figure 6 The DevSvr1 server contains the FinanceDev Web1 Dev and DBAMaint user databases whereas the DevSvr2 server contains the Billing Web2Dev and DBA Maint user databases For the purpose of this example assume that the schema in the Web1 Dev and Web2Dev databases is similar so that scripts developed for Web1Dev must also be deployed on the Web2Dev database Finance Databases container Billing Databases container Figure 6 Containers and their target databases in the Development environment Page 12 2005 2015 JNetDirect Inc All rig Combine ox Database tools that Scale Page 13 The QA environment In the QA environment assume that three SQL servers are available namely QASvr1 QASvr2 and QASvr3 as illustrated in Figure 7 When scripts written by developers for the Billing database in Development are passed to QA th
16. eStampGNT DATETIME NOT NULL CONSTRAINT DF_utbWebUsers UpdateTimeStampG By 3 Populate Finance data 35 GO 2 4 Add Finance logins users and rol 36 J 6 DBA Databases 37 Rollout verification TIB 1 Create DBAMaint objects 38 IF OBJECT_ID utbWebUsers IS NOT NULL a nee Bete ee 39 SELECT Table utbWebUsers created successfully on db_name AS RolloutRes 2 1 Create DBA Maintenance ji ee 40 ELSE aS i ERRE 41 SELECT Error creating table utbWebUsers on db_name AS RolloutRes B server and job info E pe 43 44 IF OBJECT_ID utbLogins IS NOT NULL 45 DROP TABLE utbLogins i i j Coe cl ill i gt E Resuts Static Container Container M i Object Brow GF Package Ex Ready aie S T Export to O Aggregated Table 2 2 rows ContainerServer ContainerDatabase RolloutFies Aggregated Table 3 2 rows DEVSVR1 Web1Dev Table utbWebUcers created successfully on Web1Dev Gi Individual Results DEVSVR2 Web2Dev Table utbWebLIsers created successfully on Web2Dev j DEVSVA1 Web1Dev g DEVSVR2 Web2Dev E Table 1 1 rows Table 2 1 rows E Table 3 1 rows 3a Actual Execution Plan B Messages 0 items EB 2 Create Web procedures E Aggregated Table 1 2 rows B Individual Results By 3 Populate Web tables E Aggregated Table 1 8 rows E Aggregated Table 2 16 rows Aggregated Table 3 4 rows Aggregated Table 4 4 rows Aggregated Table 5 10 rows Aggregated Table 6 4 rows E Aggregate
17. eb Databases QASVAI Web104 QASVR2 Web204 QASVR3 Web3Q4 OASVR3 Web4Q4 Figure 10 Environments and Containers in the Container Manager where the settings of all three Environments are defined in Combine Note that the folders names and Container names must be the same in the Dev QA and Production Environments Page 17 2005 2015 JNetDirect Inc Combine Database tools that Scale 282 As stated earlier it is sufficient for developers to maintain the Dev Containers for QA engineers to maintain the Containers that belong to the QA Environment and for DBAs to keep the Production Environment Container settings In this case the following figure shows the Container Manager viewed by developers QA engineers and DBAs respectively when all the Containers are Static Containers Keep in mind that Containers in different Environments need not be of the same type Static Containers in one Environment could correspond to Dynamic Containers in another Environment as long as they have the same Container name and they are placed under folders with same names in the Container Manager ale ele ORs eg My Environments e J My Environments E My Environments sern sem Piode amp 0a DBA Containers 0 DBA Containers 0 DBA Containers 3 DBA Databases J DEVSVR1 DBAMaint DEVSVR2 DBAM aint S 0 Financial Containers 5 I Billing Databases J DEVSVR2 Biling Tj Finance Databases
18. ed successfully on db_name AS RolloutRes A 1 Create DBA Maintenance jobs 30 SUSE Ra 7 Ceas Bia Talles a seu2ct error creating tebte utbtentuees on dimane AS RottoutRes B server and job in 42 oo s o az m z i Name gt E Resuts Indicates the name used when displaying the object BD Package Ex Ready 0 00 00 Orows Ln 36 Col 1 Ch1 S S oe 7 Z Export to GO Aggregated Table 2 4 rows ContainerServer ContainerDatsbase RolloutRes Aggregated Table 3 4 rows OASVR2 Web204 Table ulbWebUsers created successfully on Web20A Gi Individual Results QASVRI Web10A Table ulbWebUsers created successfully on Web10A amp Pea Rae gasvaa Web304 Table utbWebUsers created successfully on Web30A a el LN nd Pacer ASvAa Web4QA Table utbWebUsers created successfully on Web4QA H QASVRS Web40A Table 1 1 rows Table 2 1 rows EE Table 3 1 rows Fg Actual Execution Plan A Messages 0 items E By 2 Create Web procedures E Aggregated Table 1 4 rows E Individual Results By 3 Populate Web tables E Aggregated Table 1 16 rows Aggregated Table 2 32 rows Aggregated Table 3 8 rows Aggregated Table 4 8 rows Aggregated Table 5 20 rows E Aggregated Table 6 8 rows E Aggregated Table 7 8 rows Aggregated Table 8 28 rows s3 KA z mM n II Package Results E Output DemoProject 1 Create Web tables ovr gure 14 Non sorted execution results of
19. elow Combine will then automatically connect to all databases defined in the Container and execute the script on those databases In addition if any result sets e g data sets data tables are returned from one or more target databases in response to the deployment of the script Combine will automatically format and aggregate the results returned from all servers and will then display the unified results to the user As an example consider the following diagram that describes three different Containers Container consists of all user defined databases on the DevSvr1 and DevSvr2 servers The 2005 2015 JNetDirect Inc All rights re Combine o Database tools that Scale target databases of Container2 are the DBA databases named DBAMaint on the two servers and Container3 holds the set of Web databases namely Web1 Dev and Web2Dev With these mappings the user can now run scripts and queries against several databases in parallel For instance if we execute the SQL statement SELECT FROM sysindexes against Container1 then the content of sysindexes will be returned from all six databases in the container Similarly running a script that creates a stored procedure against Container3 will create the stored procedure on the Web1Dev and Web2Dev databases at the same time Additional examples are provided in the images below By running scripts and queries against Containers database administrators can easily collect information
20. er must set the Active Environment against which the package will be deployed At any given time only a single Environment can be active and the active Environment is the one displayed in bold letters in the Container Manager for example in Figure 10 the Development Environment is the active Environment By setting the appropriate active Environment in the Container Manager the Dev QA Production release process can also be followed from a single client machine that has access to all databases and servers Page 20 z 2005 2015 JNetDirect Inc All rights rese Database tools that Scale Combine ox File Edit Yiew Package Query Tools Window Help OP oF at ih Ga 2 9 99 TEET TES z F Package Explorer 2 X B Create web tables zs ax EAE A ot zej 27 CREATE TABLE dbo utbWebUsers 28 UserID INT IDENTITY 1 1 CONSTRAINT UQ_utbWebUsers_UserID UNIQUE 2 Diese Web ocean 29 FirstName NVARCHAR 64 NOT NULL Nadar Hee Won tbis 2 3 Populate Web tables Sarns EVER Mi Container Web Databases 2 4 Add Web logins users and roles 31 EnailAddress NVARCHAR 64 NOT NULL CONSTRAINT DF_utbWebUsers_Email ddress DEFAU Description AS 5 Finance Databases 32 AddressID INT Pat eate TIB 1 Create Finance tables 33 InsertTineStanpGNT DATETIME NOT NULL CONSTRAINT DF_uthWebUsers_InsertTimeStampG a IE 2 Create Finance stored procedures 34 UpdateTim
21. ese scripts must then be deployed on the Billing database on the QASvr2 server Similarly scripts composed for the FinanceDev database on the DevSvr1 server are later deployed on the FinanceQA database in the QA environment In the same manner code developed on the Web1 Dev and Web2Dev databases is then deployed on the Web1QA Web2QA Web3QA and Web4QA databases in QA and the same concept applies to the DBAMaint databases as well DBA Databases container Web Databases container Finance Databases container Billing Databases container Figure 7 Containers and their target databases in the QA environment The Production environment Releases that pass all quality assurance tests are forwarded to production for final deployment Here assume that there are four SQL servers ProdSvr1 ProdSvr2 ProdSvr3 and ProdSvr4 see Figure 8 In production scripts developed for the Billing database are deployed on the Billing database on the ProdSvr4 server scripts written for the FinanceDev database are now executed on the Finance Prod and Finance2Prod databases whereas all Web scripts are now run on five production databases namely Web1Prod Web2Prod Web3Prod Web4Prod and Web5Prod The same idea is followed by the DBAMaint databases 2005 2015 JNetDirect Inc All Combine e Database tools that Scale DBA Databases container Web Databases container Finance Databases
22. ew the content and settings of the package and deploy the package on the target databases and servers in the QA environment without modifying the package settings In addition QA engineers can choose to deploy only parts of the package or deploy code only on selected databases and servers The same concept applies when sending packages to Production Furthermore deployment results from each environment can be saved into a single file stored for auditing purposes or sent back to the developers 3 Run queries on multiple databases and servers in parallel Users can run scripts and queries against a group of target databases on any number of servers in parallel or against a single database When running queries against multiple databases Combine automatically connects and executes the queries on all target databases Results returned from all databases are then formatted and displayed together and can be automatically saved to central database for monitoring and reporting applications 2005 2015 JNetDirect Inc All rights reserved Combine o Database tools that Scale Page 5 4 Easy configuration and maintenance Combine does not require a designated repository database To make best use of the tool users can configure the groups of target databases in Dev QA and Production by using a rich set of built in configuration options and features If users already maintain a repository database with information abou
23. fully on db_name AS RolloutRes ate DBA Maintenance jobs gt 40 ELSE BB 7 ig Tables 41 SELECT Error creating table utbWebUsers on db_name AS RolloutRes IE 8 Get server and job info cael en d az a 2 am m Resuts Indicates the name used when displaying the pansa aS aai object Gi Container M P Object Brow G Package Ex Ready 0 00 00_0 rows _Ln 39 Col 27 Ch 27 5 rows _ _ aa d Table 2 5 rows ContainerServer ContainerDatabase RoloutRes Aggregated Table 3 5 rows PRODSVR2 Web2Prod Table utbWebUsers created successfully on Web2Prod Z Individual Results q PRODSVR1 WeblProd Table utbWebUsers created successfully on Web1 Prod E a j pao Teip PRODSVA4 Web5Prod Table utbWebUsers created successfully on Web5Prod z 3 proosvaa oe a PRODSVAS web3Prod Table uttWebUsers created successfully on Web3Prod PRODSVA3 Web4Prod PRODSVR3 Web4Prod Table utbwebUisers created successfully on Web4Prod SG PRODSVR4 WebSProd Table 1 1 rows Table 2 1 rows Table 3 1 rows 2g Actual Execution Plan 2 Messages 0 items CE Da E By 2 Create Web procedures Aggregated Table 1 5 rows S D Individual Results By 3 Populate Web tables Aggregated Table 1 20 rows Aggregated Table 2 40 rows Aggregated Table 3 10 rows Aggregated Table 4 10 rows E Aggregated Table 5 25 rows Aggregated Table 6 10 rows Aggregated Table 7 10 rows s s a j c
24. gj DEVSVA1 FinanceDev Tj Web Databases 5 IJ DBA Databases J QASVR1 DBAMaint QASVR2 DBAM aint QASVR3 DBAMaint 5 9 Financial Containers S IJ Billing Databases J GASVR2 Biling 5 IB Finance Databases B QASVRI FinanceA I DBA Databases J PRODSVA1 DBAMaint J PRODSVR2 DBAMaint PRODSVR3 DBAM aint J PRODSVR4 DBAM aint 39 Financial Containers 5 IJ Billing Databases J PRODSVRG Billing I Finance Databases g DEVSVA1 Web1Dev Web Databases PRODSYVA1 Finance1 Prod J DEVSVR2 Web2Dev J QASYRI Web104 J PRODSVR2 Finance2Prod QASVR2 Web204 Web Databases QASYR3 Web304 J PRODSVR1 Web1Prod QASVR3 Web404 PRODSYVR2 Web2Prod PRODSYVR3 Web3Prod PRODSYR3 Web4Prod PRODSVR4 Web5Prod Dev QA Production Figure 11 Environments and Containers in the Container Manager seen by developers QA engineers and Production DBAs when users only configure their own Environment Passing packaged between Dev QA and Production using Combine guarantees fast deployment in each environment as now demonstrated see the section titled Code Packages to learn more about packages Consider the code package in Figure 12 Each script in the package is associated with a Container name In this sample package scripts 01 to 04 are associated with the Web Databases Container scripts under the Finance Databases folders are associated with the Finance Databases Container scripts under DBA Data
25. ment with Containers having the same name as in Dev and Production DBAs only need the Production Environment again with same Containers names as in Dev and QA As an example below is a snapshot of the Container Manager that stores the configuration of all three Environments and Containers for the physical Dev QA and Production environments previously described in Figure 9 when all Containers are Static Containers 2005 2015 JNetDirect Inc All rights re Combine o Database tools that Scale W Container M anager a i My Environments EX Development 9 DBA Containers 3 DBA Databases DEVS A1 DBAMaint DEVSVR2 DBAMaint 9 Financial Containers 5 Biling Databases ig DEVS R2 Billing 7 Finance Databases J DEVSYAI FinanceDev 4 Web Databases DEVSVA1 Web1Dev DEVSVR2 Web2Dev E Production 9 DBA Containers 5 DBA Databases PRODSYA1 DBAM aint PRODSVR2 DBAM aint PRODSVR3 DBAM aint PRODSVR4 DBAM aint Financial Containers Biling Databases J PRODSVR4 Biling 2 Finance Databases PRODSYA1 Finance Prod PRODS R2 Finance2Prod 3 Web Databases PRODS A1 Web1Prod PRODS R2 Web2Prod PRODSVA3 Web3Prod PRODSVA3 Web4Prod PRODSY R4 Web5Prod ae oA G DBA Containers DBA Databases QASVA1 DBAMaint QASVR2 DBAMaint QASVR3 DBAMaint Financial Containers 2 Tp Biling Databases J QASVR2 Biling 5 2 Finance Databases J QASYR1 FinanceQA W
26. n Figure 4 2005 2015 JNetDirect Inc All rights Combine Database tools that Scale 282 DBA Databases Dynamic Container DBServerMap Static Container Finance2Prod Figure 3 Using the ServerRepository Reference database to build the DBA Databases Dynamic Container Finance1Prod NameOfServer IPAddress NameOfDatabase ISDBA Prodsvrl 192 168 1 21 DBAMaint 1 Prodsvrl 192 166 1 21 WeblProd 0 ProdSvr2 192 168 1 22 DBAMaint 1 ProdSvr2 192 168 1 22 Web2Prod 0 ProdSvr3 192 168 1 23 DBAMaint af Prodsvr3 192 166 1 23 Web3Prod 0 ProdSvr3 192 166 1 23 Web4Prod 0 Prodsvr4 192 168 1 24 DBAMaint 1 Prodsvr4 192 168 1 24 Web5SProd D ProdSvr5 192 168 11 25 DBAMaint 1 2005 2015 JNetDirect In database Figure 4 The server database mappings in table DBServers on the Reference Combine o Database tools that Scale Now either one of the following queries or many other queries will return the set of DBAMaint target databases SELECT NameOfServer AS ServerName NameOfDatabase AS DatabaseName FROM DBServers WHERE ISDBA 1 SELECT DISTINCT NameOfServer AS ServerName DBAMaint AS DatabaseName FROM DBServers Next we create a Static Container named DBServerMap in Figure 3 that holds the ServerRepository target database Finally we create the Dynamic Container using the Dynamic Container Wizard and when prompted assign the DBServerMap Container and the query as par
27. nd are discussed throughout this document for a complete list of features please visit our Web site at http www jnetdirect com 1 Collaborative code development code packaging one click package deployment on all databases and servers Using Combine much like Visual Studio for Net developers database developers can use source control and change management systems to collaborate and compose project releases together When done developers package all SQL scripts queries and any other SQL code components for their release into a single code package file Each script in the package is associated with a group of target databases The entire code package is then deployed by a click of a button onto any number of databases and servers as the tool will automatically connect and execute each script on all the appropriate target databases in the group 2 Easy transfer and agile package deployment between Dev QA and Production Combine allows users to map groups of target databases in Development to a corresponding group of target databases in QA and in Production Each group of target databases is identified by a user configured name When developers compose a code package the name of the desired target database group is assigned to each script Packages are composed and configured once by the developers When the code package is ready developers send the package file to QA QA engineers can open the package using Combine revi
28. oduction Release Process General Note Be sure to register all servers that you will be working with in the Object Browser in Combine before defining Containers or executing code in the editor Combine servers are used throughout the application and hold the connection information for all databases and servers Containers A Container is a group of one or more databases either on the same server or on different servers Each database in the group is identified by the database name and its SQL server instance name or IP address A single database can belong to several Containers In other words a database that belongs to one Container can belong to other Containers as well Containers allow users to group multiple databases into a single entity so that scripts and queries could be run against all databases in the Container in parallel Throughout this document the term script is used to denote all types of SQL and T SQL statements such as table and user creation statements stored procedure and SQL job execution commands queries or any other data definition or data manipulation statements Think of the group of databases in a Container as the set of target databases on which SQL scripts will be executed To deploy code and scripts on several databases simultaneously the user is only required to create a Container that consists of all desired target databases and then execute the script against the Container see samples and figures b
29. re importantly once all Containers are configured properly in the Dev QA and Production environments in the Combine Container Manager each Container in Dev has a corresponding Container i e Container with the same name in QA and a matching Container in Production This fact ensures fast release deployment for the following reasons After developers write the release code and build a code package software engineers in QA can easily open the package and deploy the entire package on the servers in QA by aclick of a button without altering the package content Since each script in the package is already associated with a Container name code deployed on target databases of Containers in Development is now deployed on the target databases of the corresponding Containers in QA This principle also applies when passing packages from QA to Production Examples that demonstrate the transfer and fast deployment of code packages between Dev QA and Production are provided below 3 Sharing Environments and Container settings Once Environments and Containers are defined users can utilize a Combine Repository to share the definitions and settings For additional information please refer to the Best Practices section below Important note The three Environments Dev QA Production need not be defined on each Machine running Combine Developers only need the Dev Environment with the correct Containers settings QA engineers need only have the QA Environ
30. t of servers and databases is already available in some tables Throughout we use the term Repository or Reference to denote the database in which the server to database mappings reside When a script is run against a Dynamic Container Combine first connects to the Reference database and runs a user provided query that returns the identifiers of all target databases Then as in the case of Static Containers Combine connects and deploys the script on all target databases The following steps are required to create a Dynamic Container 1 Locate the Reference database and table s that holds the server and database information 2 Write a query that returns the database and server names for all target databases 3 Create a Static Container and add the Reference database to it The Reference database should be the only database in this Container 4 Use the Dynamic Container Wizard to create the Dynamic Container When prompted associate the Static Container in 3 and the query in 2 with the Dynamic Container The example below illustrated the concept of Dynamic Containers Here we create a Dynamic Container with five DBAMaint target databases on five different servers and call this Container DBA Databases First a Reference database is required Assume that the ServerRepository database on ProdSvr5 has the server database mappings and that the data is stored in a table named DBServers the content of the DBServers table is given i
31. t of the Dynamic Container configuration Once the DBA Databases Container is created every time scripts are run against this Container Combine performs the steps in Figure 5 to deploy code on all DBAMaint target databases 1 Run the query against the target databases listed in the DBServerMap Static Container 2 Get query results The returned results are the identifiers of the target databases of the Dynamic Container 3 Execute the script against all target database found in 2 Figure 5 The steps taken by Combine to execute a script against the DBA Databases Dynamic Container Page 11 p 2005 2015 JNetDirect In Combine ox Database tools that Scale Environments Each Environment consists of any number of Static and Dynamic Containers with the restriction that Container names in a single Environment must be unique However Containers that belong to different Environments can and in many cases should have the same name Environments are introduced in Combine to relate groups of databases i e Containers between separate physical SQL server environments The primary benefit of Environments is that they allow developers software testers and DB administrators to pass SQL scripts and code packages between Development QA and Production respectively while guaranteeing fast deployment on each environment For now consider three Environments namely the Development Dev environment th
32. t their databases the tool can be easily configured to retrieve the group settings by querying the repository to find out more about the repository database or to implement such a repository please mail to JNetDirect support at support jnetdirect com In addition once a single person configures the target groups these settings can be exported and imported by other users 5 With security in mind Combine is designed to be secure Developers QA engineers and Production DBAs only need to configure the sets of target databases in their own environment Moreover users can choose whether to store user credentials using strong encryption techniques or to require credentials to be entered at each use 2005 2015 JNetDirect Inc Combine ox Database tools that Scale Page 6 Containers and Environments Introduction Containers are used in Combine to deploy SQL code and execute queries against multiple databases in parallel and retrieve unified results from all queried databases Containers and Environments are extremely useful for passing SQL code packages between the Development Dev Quality Assurance QA and Production SQL server environments and easily deploying release packages on any number of databases and servers in those environments The use of Containers and Environments in the Dev QA Production change management and code release process is described below and continued in the section titled The Dev QA Pr
33. you can restrict the access and update permissions to the repository content For additional information please refer to the Combine Repository user manual and installation scripts on SQL Farms website or in the application F1 help Best Practices II Tracking Deployments and DB Changes by Using a Change History Repository Combine enables users to install a Change History repository and then record deployments and package executions in a central repository database for tracking and auditing purposes Users can then access the repository using the Change History tool to view and search information relating to previously deployed code packages The scripts to create a Change History Repository database are available on SQL Farms website as well as in the SQL Farm Combine application installation directory For additional information please refer to the built in F1 help in the application 2005 2015 JNetDirect Inc A
Download Pdf Manuals
Related Search