May 28, 2022 at 5:59 am
Hi guys, I hope someone of you has the time to help me. I am relatively new to SQL and SQL Server administration. I am looking for an elegant solution for version control of SQL.
I'm using a Azure SQL DB and Azure Dev Ops Services and we are a very small team accessing this database (2 people developing and administrating), making changes, adjusting stored procedures, adding a column to the tables if necessary, add or edit DB Jobs etc.
I know repositories from other developer use (Python for example), but I somehow lack the imagination how this works with SQL.
I've heard of RedGate SQL Source Control, but would prefer to stay in the Microsoft cosmos.... my question would be, if someone can give me a hint how to implement a version control for my Azure SQL database, so that when the small development team makes changes to this DB (customize stored procedures / tables / jobs etc.) they are versioned reasonably?
May 29, 2022 at 6:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 29, 2022 at 11:58 am
I've heard of RedGate SQL Source Control, but would prefer to stay in the Microsoft cosmos....
Kind of an odd sentence. RedGate is in the greater Microsoft cosmos as their products are basically codependent. Good tools cost money and generally save developers' time.
my question would be, if someone can give me a hint how to implement a version control for my Azure SQL database, so that when the small development team makes changes to this DB (customize stored procedures / tables / jobs etc.) they are versioned reasonably?
Well, here goes my biased editorial. Don't build your own that's for sure. By "versioned reasonably" implies not DR which is an important distinction. Backup, storage, and recovery are separate topics imo. One way which seems to make sense to me could be to use Git/GitHub and create your own utility program which enters SQL code/whatever into the repository in CI/CD before deployments. Then the Git history would contain procedural code and the SQL it's intended to reference
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 29, 2022 at 1:38 pm
Here's a table valued function which takes as input a (2-part named: schema.name) stored procedure (as column type SYSNAME) and which produces a SQL script some of which could be entered into source control. The script is built to support a "3 procedure" dev process which begins with %_dev and %_mock versions of the procedure and a synonym. If the procedure has definition(s) it/they get selected from meta data.
If the _prod version exists and _dev does not it create _dev as a copy of _prod from meta data.
If the _prod version doesn't exist and _dev doesn't exist it creates _dev from template
drop function if exists dbo.coda_endpoint_codegen;
go
create function dbo.coda_endpoint_codegen(
@procedure sysname)
returns table as return
with
procedure_input(proc_string, schema_string, proc_verb) as (
select v.proc_string, v.schema_string,
substring(v.proc_string, len(v.proc_string)-cndx_rev.verb+2, cndx_rev.verb-1)
from (values (parsename(@procedure, 1), parsename(@procedure, 2))) v(proc_string, schema_string)
cross apply (values (charindex('_', reverse(v.proc_string), 1))) cndx_rev(verb)),
definitions(proc_string, schema_string, proc_verb,
order_num, suffix, definition_name, quoted_schema_proc, use_table, use_template, sql_string) as (
select inpt.proc_string, inpt.schema_string, inpt.proc_verb,
d.order_num, d.suffix, d.definition_name,
concat(quotename(inpt.schema_string, '[]'), N'.', quotename(concat(inpt.proc_string, d.suffix), '[]')),
d.use_table, d.use_template, object_definition(sp.[object_id])
from procedure_input inpt
cross join (values (1, N'_prod', N'Production', 1, 0), /* prod must be #1 because case exp. below depends on it */
(2, N'_dev', N'Development', 1, 1),
(3, N'_mock', N'Mock', 0, 0))
d(order_num, suffix, definition_name, use_table, use_template)
left join sys.procedures sp on concat(inpt.proc_string, d.suffix)=sp.[name]
left join sys.schemas sch on sp.schema_id=sch.schema_id
and inpt.schema_string=sch.[name]),
procedure_template_definitions(order_num, sql_string) as (
select d.order_num,
concat(N'--drop proc if exists ', @procedure, suffix, ';',
replace(replace(replace(hv.coda_proc_template,
N'schema_to_replace',
d.schema_string),
N'procedure_to_replace',
concat(d.proc_string, d.suffix)),
N'definition_name_to_replace',
lower(d.definition_name)), char(13))
from definitions d
join dbo.http_verbs hv on d.proc_verb=hv.verb
where d.use_table=1),
procedure_sys_definitions(order_num, sql_string) as (
select d.order_num, d.sql_string
from definitions d
where (d.use_template<>0
or d.use_table<>0)),
procedure_definitions(order_num, sql_string) as (
select 0 order_num, concat(N'/* create procedures */', char(13)) sql_string
union all
select td.order_num,
case when (sd.sql_string is null and td.order_num=1) then null
when (sd.sql_string is null and td.order_num<>1) then td.sql_string
else sd.sql_string end
from procedure_template_definitions td
left join procedure_sys_definitions sd on td.order_num=sd.order_num),
exec_definitions(order_num, sql_string) as (
select d.order_num,
concat(N'/* execute procedure */', char(13),
replace(replace(hv.coda_exec_template,
N'schema_to_replace',
d.schema_string),
N'procedure_to_replace',
concat(d.proc_string, d.suffix)))
from definitions d
join dbo.http_verbs hv on d.proc_verb=hv.verb
where d.use_template=1),
mock_definitions(order_num, sql_string) as (
select d.order_num,
concat(char(13), N'/* mock procedure */', char(13),
replace(replace(hv.coda_mock_template,
N'schema_to_replace',
d.schema_string),
N'procedure_to_replace',
concat(d.proc_string, d.suffix)))
from definitions d
join dbo.http_verbs hv on d.proc_verb=hv.verb
where d.use_template=0
and d.use_table=0),
synonym_create_statements(order_num, sql_string) as (
select d.order_num, concat(N'--drop synonym if exists ', @procedure, ';', char(13),
iif(syn.[name] is not null, N'--', N''), N'create synonym ', @procedure, N' for ', @procedure, d.suffix, N';', char(13),
N'--create synonym ', @procedure, N' for ', @procedure, N'_prod', N';', char(13))
from definitions d
left join sys.synonyms syn on d.quoted_schema_proc=syn.base_object_name
and d.proc_string=syn.[name]
left join sys.schemas sch on syn.[schema_id]=sch.[schema_id]
and d.schema_string=sch.[name]
where d.use_table=1
and d.use_template=1),
synonym_create_string(order_num, sql_string) as (
select 0 order_num, concat(char(13), N'/* drop / create synonyms */', char(13)) sql_string
union all
select order_num, sql_string
from synonym_create_statements)
select concat_ws(char(10),
(select string_agg(pr.sql_string, char(13)) within group (order by pr.order_num) from procedure_definitions pr),
(select string_agg(ed.sql_string, char(13)) within group (order by ed.order_num) from exec_definitions ed),
(select string_agg(md.sql_string, char(13)) within group (order by md.order_num) from mock_definitions md),
(select string_agg(cs.sql_string, char(13)) within group (order by cs.order_num) from synonym_create_string cs)) sql_string
go
To execute an example (where the proc doesn't exist)
declare @x nvarchar(max);
select @x=sql_string from dbo.coda_endpoint_codegen(N'id.api_policies_delete');
print(@x);
Produces a script like this. If the proc had existed then it's definitions would be populated from sys.procedures, sys.schemas, sys.synonyms
/* create procedures */
/* development procedure definition */
create proc id.api_policies_delete_dev
@headersnvarchar(max)=null,
@paramsnvarchar(max)=null,
@bodynvarchar(max)=null,
@test_valuebit output,
@responsenvarchar(max) output
as
set nocount on;
set xact_abort on;
begin transaction
begin try
--delete jas.requests;
if @@rowcount=0
throw 51000, N'No records deleted', 1;
select @test_value=cast(1 as bit),
@response=(select N'Ok' reply_message
for json path, without_array_wrapper);
commit transaction;
end try
begin catch
select @test_value=cast(0 as bit),
@response=(select * from dbo.get_error_metadata()
for json path, without_array_wrapper);
rollback transaction;
end catch
set xact_abort off;
set nocount off;
/* execute procedure */
declare
@headersnvarchar(max)=null,
@paramsnvarchar(max)=null,
@bodynvarchar(max)=null,
@output_test_valbit,
@output_responsenvarchar(max)=null;
exec id.api_policies_delete_dev
@headers, @params, @body,
@test_value=@output_test_val output,
@response=@output_response output;
if @output_test_val>0
select concat('test_value: ', @output_test_val) test_value, oj.*
from openjson(@output_response) oj;
else
select * from frm.get_output_error_metadata(@output_response);
go
/* mock procedure */
create or alter proc id.api_policies_delete_mock
@headersnvarchar(max)=null,
@paramsnvarchar(max)=null,
@bodynvarchar(max)=null,
@test_valuebit output,
@responsenvarchar(max) output
as
set nocount on;
select @test_value=cast(1 as bit),
@response=(select @headers headers, @params params, @body body
for json path, without_array_wrapper);
set nocount off;
go
/* drop / create synonyms */
--drop synonym if exists id.api_policies_delete;
create synonym id.api_policies_delete for id.api_policies_delete_dev;
--create synonym id.api_policies_delete for id.api_policies_delete_prod;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 29, 2022 at 5:43 pm
This was removed by the editor as SPAM
May 31, 2022 at 2:58 pm
Hi.
Full disclosure, I work for Redgate. So, you can weigh everything I'm about to say.
Microsoft has a tool built into Visual Studio, so you have to have a license for that, but then, the tool is free. It's called SQL Server Database Tools (SSDT). It can do what you're describing, allow you to get your SQL Server database code (whether local, on a VM, or on Azure SQL Database, it supports all SQL Server) into source control. Then, from there, you can deploy through the generation of a dacpac. You can implement that through Azure DevOps or GitHub Actions or any of the standard mechanisms that can work with Visual Studio and the command line there. This is how I first did my own database deployment automation (prior to working at Redgate). It will get the core job done.
Now, that said, there are limitations here. First, SQL Server only. If you later decide to expand into PostgreSQL or any other database management system, you're going to have to get another tool, then incorporate and maintain two different deployment mechanisms. Second, the dacpac is a brittle way to deploy. It uses what is called state-based deployments, comparing what it has within to what your database is, to arrive at differential scripts. Most of the time, these are fine. Some of the time, they could involve data loss and the dacpac simply won't deploy at those times. So, in these cases, you have to manually intervene.
I lean heavily towards recommending you look at Flyway. There's a fully open-source aspect of the tool, so no licensing costs at all. It does two things SSDT doesn't. First, it uses migrations to deploy, which gets it around the more dangerous problems of a dacpac. Second, it supports 27 different database systems. Now, the free version doesn't do all the object scripting into source control that SSDT does. For that, you'd have to purchase a license for Flyway Desktop. However, then you get everything, object level change tracking and migrations based deployments.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply