Transcript
Unified Source Control: SQL Source Control and SQL Connect in Concert Use Cases for Source Control Interactions The table below enumerates the non-trivial use cases for interacting between SQL Server Management Studio with SQL Source Control, Visual Studio with SQL Connect and AnkhSVN (or equivalent), and file system based source control with TortoiseSVN (or equivalent). Each use case starts with a set of actions and the desired outcome is to propagate those actions through all environments (SSMS, VS, TortoiseSVN) and storage domains (database, repository, file system). Thus, the ending state in each use case is the same. Though Subversion is used as an example, almost any source control may be used just as effectively here. Environment →
SQL Server Mgmt Studio SQL Source Control
Visual Studio SQL Connect / AnkhSVN
Aspect →
Query result
Object Explorer
Commit Changes
Get Latest
Query result
Affects →
DB Visible change
DB Visible change
DB Local change
SVN Remote change
A ●
B ●
C ●
D -
Result → Steps ↓
1
Interpreting the table: Consider Use Case 1, Step 1. Once you add, modify, or drop a table in SSMS the change is visible when you run a query ([A1]). Ex., once you add a column “Select * from…” will show the new column. It is also immediately visible in the object explorer ([B1]). Similarly, since these are changes to the database, the Visual Studio aspects tied to the DB ([E1] and [F1]) also manifest these changes. Nothing is yet exposed to the file system or to the source control system ([D1] and [G1] – [L1]). VS synchronization (step 2) ties DB to file system, so the VS aspects tied to the file system have a state change ([G2], [H2], [I2]). This step will never affect SSMS or files outside the VS project, so [A2] – [D2] and [K2] – [L2] are greyed out. Committing in step 3 removes the local changes in SSMS ([C3]) but makes these changes now visible as remote changes in VS and in the file system ([J3] and [L3]).
1 Add / Mod / Drop In SSMS
Solution Explorer
DB Visible change
CMenu > Table Designer DB Visible change
E ●
2 Synchronize in VS
2
6
7
8
FS Local change
Tort > Ck for mod > Ck repo SVN Remote change
F ●
G -
H -
I -
J -
K -
L -
●
●
●
●
●
-
●
-
-
●
●
●
●
●1
●1
-
●
4 Update 1 Add / Mod / Drop In SSMS
●
●
●
-
● ●
● ●
● -
● -
-
-
-
-
2 Commit in SSMS
●
●
-
-
●
●
-
-
-
●
-
●
●
●
●
●
-
-
-
-
4 Synchronize in VS 1 Add / Mod / Drop In SSMS
●
●
●
-
●
●
-
-
-
-
-
-
2 Commit in SSMS
●
●
-
-
●
●
-
-
-
●
-
●
●
●
●
●
●1
●1
● ●
● ●
● -
● -
-
-
-
-
●
●
●
●
●
-
4 Update 1 Add / Mod / Drop In VS
●
●
●3
-
2 Synchronize in VS
5
Pndng > Recent Chgs SVN Remote change
●
3 Synchronize in VS 4
Pndng > Local File Chgs FS Local change
Explorer
FS Visible change
CMenu > Edit SQL in Project FS Visible change
3 Commit in SSMS
3 Update 3
Filesystem / TortoiseSVN
3 Commit in VS
●
●
-4
-4
●
●
●
●
-
-
-
●
4 Update 1 New / Edit / Delete In VS
-
-
-
-
-
-
●
●
●
-
-
-
2 Synchronize in VS
●
●
●
-
●
●
●
●
●
-
3 Commit in VS
●
●
-4
-4
●
●
●
●
-
-
-
●
4 Update 1 New / Edit / Delete In VS
-
-
-
-
-
-
●
●
●
-
-
-
2 Commit in VS
-
-
-
●
-
-
●
●
-
-
-
●
3 Synchronize in VS
●
●
-4
-4
●
●
●
●
-
-
4 Update 1 New / Edit / Delete In FS
-
-
-
-
-
-
-
-
-
-
●2
-
2 Commit in FS
-
-
-
●
-
-
-
-
-
●
-
-
3 Update in SSMS
●
●
-
-
●
●
-
-
-
●
4 Synchronize in VS 1 New / Edit / Delete In FS
-
-
-
-
● -
● -
● -
● -
-
-
●2
-
2 Commit in FS
-
-
-
●
-
-
-
-
-
●
-
-
-
-
●
●
-
-
●
●
●
●
-
-
3 Update in VS 4 Synchronize in VS
●
●
-
-4
5 Update in SSMS Legend Green marks the desired goal state. Grey marks states that will never exhibit changes. Arrows ( ) highlight state changes within each aspect of each use case. Add/Mod/Drop indicates action directly on DB objects. New/Edit/Delete indicate equivalent action on file representations of those objects. Abbreviations: SSMS=SQL Server Management Studio; VS=Visual Studio; FS=File System; SVN=source control repository; CMenu=Context menu.
Footnotes 1. Curiously VS arrives at a state showing both local and remote changes; however, they are the same changes so an Update reconciles and clears them. 2. Changes at the file system level must be accompanied by appropriate edits in the VS project file (*.rgproj) for the changes to appear and behave correctly when realized by VS. (This is nothing peculiar to DB projects; that is true for any VS projects.) 3. Because SSMS is closer to the database than VS, DB changes in VS show up not just as DB changes in SSMS, but also local changes from the source control perspective (you may need to do a refresh to see the change). 4. Similar to 3, source control changes are more closely tied to the DB in SSMS, so actions in VS or in TortoiseSVN can automatically reconcile and update the SSMS perspective.
Environment / Storage Interactions and GUI Manifestation This table shows which storage domain (DB, SVN, file) is affected by actions in each environment (SSMS, VS, TortoiseSVN) and whether this constitutes local changes that will be flagged/unflagged in the GUI source control client. Add Table Modify Table Synchronize Commit Update Drop Table Flags Flags Unflags Works On Moves Moves Moves changes? changes? changes?
TortoiseSVN
DB NEW ADD EDIT MOD DEL DROP
SSMS / SQL Source Ctrl VS / SQL Connect/ AnkhSVN
DB File sys
File sys
Yes
-
-
DB SVN
Yes
SVN DB
No Yes
DB File sys File sys DB
Yes
File sys SVN
Yes
SVN File sys
Yes
-
-
File sys SVN
Yes
SVN File sys
Source control should apply to all parts of your project—application code, user interface, help text, and database schema—to provide the wherewithal to properly manage revisions. Red Gate’s SQL Source Control, SQL Connect, and SQL Compare bring the elusive prize of source controlling your database within your grasp. Read the backstory to this wallchart in Database Source Control Basics: The Unified Solution. Copyright © 2012 Michael Sorens 2012.09.03, Version 1.0.1 Published on Simple-Talk.com
Virtuous Cycle The figure at right illustrates the domains that each environment connects to. At bottom, each environment is highlighted to show the specific actions that move data among the connected domains.
