RE: Sql Server Patch Scripts

[Ken Schaefer]

Oops –I replied to the wrong email ☺

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On 
Behalf Of Ken Schaefer
Sent: Tuesday, 3 November 2015 10:42 AM
To: ozDotNet <ozdotnet@ozdotnet.com>
Subject: RE: Sql Server Patch Scripts

The other option might be to keep the Billion as a mere modem+NAT+router, then 
stick something behind that to handle your internal LAN and WiFi etc. I do that 
with a DLink 2890AL acting solely as a moden+NAT (turned off WiFi, single LAN 
uplink). I then have a Cisco SG-300 L3 switch (does routing, switching etc.) to 
handle the core LAN functionality, and a (getting old now) DLink  DAP2310 WAP 
from their “business range”. I’ve found that to be a lot more solid than 
relying on the “SOHO” all-in-one boxes, which just seem to get overloaded and 
fall over every so often. The SG-300 has a good web interface, as well as 
excellent doco. The DAP2310 not so much, but still easy to configure unless 
you’re going into the more advance functions (like VLANs) where the doco starts 
to get ambiguous.

Cheers
Ken

From: ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com> 
[mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of David Burstin
Sent: Monday, 2 November 2015 3:16 PM
To: ozDotNet <ozdotnet@ozdotnet.com<mailto:ozdotnet@ozdotnet.com>>
Subject: Re: Sql Server Patch Scripts

We use SQL Server projects for patching, version controlled with git.

For schema changes, we run a compare on the project and the dev database 
(assuming that is where the schema changes are) and create an upgrade script 
from that.

For actual data changes to be applied, we create separate scripts in the sql 
project specifically for those.

This seems to work pretty well for us. YMMV.

Cheers
Dave

On 2 November 2015 at 14:53, Grant Castner 
<gcast...@outlook.com.au<mailto:gcast...@outlook.com.au>> wrote:
Hi Tony,
We use dbup (https://dbup.github.io/) - it allows you to create a small visual 
studio project so that you can track scripts as well as check them in.

Cheers,
Grant


Grant Castner
Phone: 0458 770 749
Twitter: https://twitter.com/grantcastner
LinkedIn: 
au.linkedin.com/pub/grant-castner<http://au.linkedin.com/pub/grant-castner>



Date: Mon, 2 Nov 2015 14:48:56 +1100
Subject: Sql Server Patch Scripts
From: tonyw...@gmail.com<mailto:tonyw...@gmail.com>
To: ozdotnet@ozdotnet.com<mailto:ozdotnet@ozdotnet.com>

Hi all,

Almost every system I have developed has been against a SQL Server database, 
and every environment has needed to be patched as greenfields projects 
introduce new changes.

The reality is that I have found providing patch scripts for sql server quite 
problematic and have never been very happy about what we do to apply patch 
scripts.

I want to know what scripts have been applied to a database, and I also want to 
know that scripts applied are transactional (that is, if a script "breaks" when 
applied, nothing has changed.) I also want to be able to add data to be 
inserted into tables to my scripts.

What are people currently doing to solve these issues?

Warm regards,
Tony

RE: Sql Server Patch Scripts

[Ken Schaefer]

The other option might be to keep the Billion as a mere modem+NAT+router, then 
stick something behind that to handle your internal LAN and WiFi etc. I do that 
with a DLink 2890AL acting solely as a moden+NAT (turned off WiFi, single LAN 
uplink). I then have a Cisco SG-300 L3 switch (does routing, switching etc.) to 
handle the core LAN functionality, and a (getting old now) DLink  DAP2310 WAP 
from their “business range”. I’ve found that to be a lot more solid than 
relying on the “SOHO” all-in-one boxes, which just seem to get overloaded and 
fall over every so often. The SG-300 has a good web interface, as well as 
excellent doco. The DAP2310 not so much, but still easy to configure unless 
you’re going into the more advance functions (like VLANs) where the doco starts 
to get ambiguous.

Cheers
Ken

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On 
Behalf Of David Burstin
Sent: Monday, 2 November 2015 3:16 PM
To: ozDotNet <ozdotnet@ozdotnet.com>
Subject: Re: Sql Server Patch Scripts

We use SQL Server projects for patching, version controlled with git.

For schema changes, we run a compare on the project and the dev database 
(assuming that is where the schema changes are) and create an upgrade script 
from that.

For actual data changes to be applied, we create separate scripts in the sql 
project specifically for those.

This seems to work pretty well for us. YMMV.

Cheers
Dave

On 2 November 2015 at 14:53, Grant Castner 
<gcast...@outlook.com.au<mailto:gcast...@outlook.com.au>> wrote:
Hi Tony,
We use dbup (https://dbup.github.io/) - it allows you to create a small visual 
studio project so that you can track scripts as well as check them in.

Cheers,
Grant


Grant Castner
Phone: 0458 770 749
Twitter: https://twitter.com/grantcastner
LinkedIn: 
au.linkedin.com/pub/grant-castner<http://au.linkedin.com/pub/grant-castner>



Date: Mon, 2 Nov 2015 14:48:56 +1100
Subject: Sql Server Patch Scripts
From: tonyw...@gmail.com<mailto:tonyw...@gmail.com>
To: ozdotnet@ozdotnet.com<mailto:ozdotnet@ozdotnet.com>

Hi all,

Almost every system I have developed has been against a SQL Server database, 
and every environment has needed to be patched as greenfields projects 
introduce new changes.

The reality is that I have found providing patch scripts for sql server quite 
problematic and have never been very happy about what we do to apply patch 
scripts.

I want to know what scripts have been applied to a database, and I also want to 
know that scripts applied are transactional (that is, if a script "breaks" when 
applied, nothing has changed.) I also want to be able to add data to be 
inserted into tables to my scripts.

What are people currently doing to solve these issues?

Warm regards,
Tony

RE: Sql Server Patch Scripts

[罗格雷格博士]

That’s so funny Ken. I was just reading that email and thinking “I know that 
threads drift but I hadn’t realised that one had drifted that far”. It still 
seemed plausible ☺

Regards,

Greg

Dr Greg Low

1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/>

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On 
Behalf Of Ken Schaefer
Sent: Tuesday, 3 November 2015 10:44 AM
To: ozDotNet <ozdotnet@ozdotnet.com>
Subject: RE: Sql Server Patch Scripts

Oops –I replied to the wrong email ☺

From: ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com> 
[mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Ken Schaefer
Sent: Tuesday, 3 November 2015 10:42 AM
To: ozDotNet <ozdotnet@ozdotnet.com<mailto:ozdotnet@ozdotnet.com>>
Subject: RE: Sql Server Patch Scripts

The other option might be to keep the Billion as a mere modem+NAT+router, then 
stick something behind that to handle your internal LAN and WiFi etc. I do that 
with a DLink 2890AL acting solely as a moden+NAT (turned off WiFi, single LAN 
uplink). I then have a Cisco SG-300 L3 switch (does routing, switching etc.) to 
handle the core LAN functionality, and a (getting old now) DLink  DAP2310 WAP 
from their “business range”. I’ve found that to be a lot more solid than 
relying on the “SOHO” all-in-one boxes, which just seem to get overloaded and 
fall over every so often. The SG-300 has a good web interface, as well as 
excellent doco. The DAP2310 not so much, but still easy to configure unless 
you’re going into the more advance functions (like VLANs) where the doco starts 
to get ambiguous.

Cheers
Ken

From: ozdotnet-boun...@ozdotnet.com<mailto:ozdotnet-boun...@ozdotnet.com> 
[mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of David Burstin
Sent: Monday, 2 November 2015 3:16 PM
To: ozDotNet <ozdotnet@ozdotnet.com<mailto:ozdotnet@ozdotnet.com>>
Subject: Re: Sql Server Patch Scripts

We use SQL Server projects for patching, version controlled with git.

For schema changes, we run a compare on the project and the dev database 
(assuming that is where the schema changes are) and create an upgrade script 
from that.

For actual data changes to be applied, we create separate scripts in the sql 
project specifically for those.

This seems to work pretty well for us. YMMV.

Cheers
Dave

On 2 November 2015 at 14:53, Grant Castner 
<gcast...@outlook.com.au<mailto:gcast...@outlook.com.au>> wrote:
Hi Tony,
We use dbup (https://dbup.github.io/) - it allows you to create a small visual 
studio project so that you can track scripts as well as check them in.

Cheers,
Grant


Grant Castner
Phone: 0458 770 749
Twitter: https://twitter.com/grantcastner
LinkedIn: 
au.linkedin.com/pub/grant-castner<http://au.linkedin.com/pub/grant-castner>



Date: Mon, 2 Nov 2015 14:48:56 +1100
Subject: Sql Server Patch Scripts
From: tonyw...@gmail.com<mailto:tonyw...@gmail.com>
To: ozdotnet@ozdotnet.com<mailto:ozdotnet@ozdotnet.com>

Hi all,

Almost every system I have developed has been against a SQL Server database, 
and every environment has needed to be patched as greenfields projects 
introduce new changes.

The reality is that I have found providing patch scripts for sql server quite 
problematic and have never been very happy about what we do to apply patch 
scripts.

I want to know what scripts have been applied to a database, and I also want to 
know that scripts applied are transactional (that is, if a script "breaks" when 
applied, nothing has changed.) I also want to be able to add data to be 
inserted into tables to my scripts.

What are people currently doing to solve these issues?

Warm regards,
Tony

RE: Sql Server Patch Scripts

[Grant Castner]

Hi Tony,
We use dbup (https://dbup.github.io/) - it allows you to create a small visual 
studio project so that you can track scripts as well as check them in.
 
Cheers,
Grant


Grant Castner
Phone: 0458 770 749
Twitter: https://twitter.com/grantcastner
LinkedIn: au.linkedin.com/pub/grant-castner

 
Date: Mon, 2 Nov 2015 14:48:56 +1100
Subject: Sql Server Patch Scripts
From: tonyw...@gmail.com
To: ozdotnet@ozdotnet.com

Hi all,
Almost every system I have developed has been against a SQL Server database, 
and every environment has needed to be patched as greenfields projects 
introduce new changes.
The reality is that I have found providing patch scripts for sql server quite 
problematic and have never been very happy about what we do to apply patch 
scripts. 

I want to know what scripts have been applied to a database, and I also want to 
know that scripts applied are transactional (that is, if a script "breaks" when 
applied, nothing has changed.) I also want to be able to add data to be 
inserted into tables to my scripts.
What are people currently doing to solve these issues?
Warm regards,Tony 

Re: Sql Server Patch Scripts

[David Burstin]

We use SQL Server projects for patching, version controlled with git.

For schema changes, we run a compare on the project and the dev database
(assuming that is where the schema changes are) and create an upgrade
script from that.

For actual data changes to be applied, we create separate scripts in the
sql project specifically for those.

This seems to work pretty well for us. YMMV.

Cheers
Dave

On 2 November 2015 at 14:53, Grant Castner  wrote:

> Hi Tony,
> We use dbup (https://dbup.github.io/) - it allows you to create a small
> visual studio project so that you can track scripts as well as check them
> in.
>
> Cheers,
> Grant
>
>
> Grant Castner
> Phone: 0458 770 749
> Twitter: https://twitter.com/grantcastner
> LinkedIn: au.linkedin.com/pub/grant-castner
>
>
> --
> Date: Mon, 2 Nov 2015 14:48:56 +1100
> Subject: Sql Server Patch Scripts
> From: tonyw...@gmail.com
> To: ozdotnet@ozdotnet.com
>
>
> Hi all,
>
> Almost every system I have developed has been against a SQL Server
> database, and every environment has needed to be patched as greenfields
> projects introduce new changes.
>
> The reality is that I have found providing patch scripts for sql server
> quite problematic and have never been very happy about what we do to apply
> patch scripts.
>
> I want to know what scripts have been applied to a database, and I also
> want to know that scripts applied are transactional (that is, if a script
> "breaks" when applied, nothing has changed.) I also want to be able to add
> data to be inserted into tables to my scripts.
>
> What are people currently doing to solve these issues?
>
> Warm regards,
> Tony
>

Re: Sql Server Patch Scripts

[Craig van Nieuwkerk]

I do this as well with projects. It works well if you only have to deploy
to a couple of databases. If you have many databases (>3) then I find the
migrations approach described by Grant works very well.

On Mon, Nov 2, 2015 at 3:15 PM, David Burstin 
wrote:

> We use SQL Server projects for patching, version controlled with git.
>
> For schema changes, we run a compare on the project and the dev database
> (assuming that is where the schema changes are) and create an upgrade
> script from that.
>
> For actual data changes to be applied, we create separate scripts in the
> sql project specifically for those.
>
> This seems to work pretty well for us. YMMV.
>
> Cheers
> Dave
>
> On 2 November 2015 at 14:53, Grant Castner 
> wrote:
>
>> Hi Tony,
>> We use dbup (https://dbup.github.io/) - it allows you to create a small
>> visual studio project so that you can track scripts as well as check them
>> in.
>>
>> Cheers,
>> Grant
>>
>>
>> Grant Castner
>> Phone: 0458 770 749
>> Twitter: https://twitter.com/grantcastner
>> LinkedIn: au.linkedin.com/pub/grant-castner
>>
>>
>> --
>> Date: Mon, 2 Nov 2015 14:48:56 +1100
>> Subject: Sql Server Patch Scripts
>> From: tonyw...@gmail.com
>> To: ozdotnet@ozdotnet.com
>>
>>
>> Hi all,
>>
>> Almost every system I have developed has been against a SQL Server
>> database, and every environment has needed to be patched as greenfields
>> projects introduce new changes.
>>
>> The reality is that I have found providing patch scripts for sql server
>> quite problematic and have never been very happy about what we do to apply
>> patch scripts.
>>
>> I want to know what scripts have been applied to a database, and I also
>> want to know that scripts applied are transactional (that is, if a script
>> "breaks" when applied, nothing has changed.) I also want to be able to add
>> data to be inserted into tables to my scripts.
>>
>> What are people currently doing to solve these issues?
>>
>> Warm regards,
>> Tony
>>
>
>

Re: Sql Server Patch Scripts

[Tony Wright]

I have previously used FlyWay to perform database migrations, and in fact
there is even a course on Pluralsight for this.

I am currently generating scripts via Visual Studio Database Projects, but
there appear to be a few problems with this. Firstly, it doesn't seem to
scale well. It seems ok with a single database, but once you have multiple
databases in an environment operating at different versions, there doesn't
seem to be a way of knowing what version a database is at, and therefore
know which scripts to run to get it up to date. There is also no automated
way to get the database up to date by running a batch of the right scripts.
I'm also not convinced that the scripts run transactionally. I seem to
recall database scripts failing but leaving behind database objects - the
only solution of which was to either restore the database from backup and
apply a fixed script, or rerun Sql Compare to give me the new diff script.

I would have loved to use FlyWay and put the scripts generated by Visual
Studio Sql Compare into FlyWay versioned scripts, but that doesn't work, as
FlyWay doesn't run the scripts in SqlCmd mode, and therefore the variables
generated in the script all fail. Yes, I can go to some effort to modify
those scripts, but I'd rather not have to make too many changes to the
generated scripts (I am already reviewing those scripts every time it gives
the "data may be changed..." error).

I will take a look at DbUp. Does anyone know if that can handle SqlCmd mode
scripts?

On Mon, Nov 2, 2015 at 3:22 PM, Craig van Nieuwkerk 
wrote:

> I do this as well with projects. It works well if you only have to deploy
> to a couple of databases. If you have many databases (>3) then I find the
> migrations approach described by Grant works very well.
>
> On Mon, Nov 2, 2015 at 3:15 PM, David Burstin 
> wrote:
>
>> We use SQL Server projects for patching, version controlled with git.
>>
>> For schema changes, we run a compare on the project and the dev database
>> (assuming that is where the schema changes are) and create an upgrade
>> script from that.
>>
>> For actual data changes to be applied, we create separate scripts in the
>> sql project specifically for those.
>>
>> This seems to work pretty well for us. YMMV.
>>
>> Cheers
>> Dave
>>
>> On 2 November 2015 at 14:53, Grant Castner 
>> wrote:
>>
>>> Hi Tony,
>>> We use dbup (https://dbup.github.io/) - it allows you to create a small
>>> visual studio project so that you can track scripts as well as check them
>>> in.
>>>
>>> Cheers,
>>> Grant
>>>
>>>
>>> Grant Castner
>>> Phone: 0458 770 749
>>> Twitter: https://twitter.com/grantcastner
>>> LinkedIn: au.linkedin.com/pub/grant-castner
>>>
>>>
>>> --
>>> Date: Mon, 2 Nov 2015 14:48:56 +1100
>>> Subject: Sql Server Patch Scripts
>>> From: tonyw...@gmail.com
>>> To: ozdotnet@ozdotnet.com
>>>
>>>
>>> Hi all,
>>>
>>> Almost every system I have developed has been against a SQL Server
>>> database, and every environment has needed to be patched as greenfields
>>> projects introduce new changes.
>>>
>>> The reality is that I have found providing patch scripts for sql server
>>> quite problematic and have never been very happy about what we do to apply
>>> patch scripts.
>>>
>>> I want to know what scripts have been applied to a database, and I also
>>> want to know that scripts applied are transactional (that is, if a script
>>> "breaks" when applied, nothing has changed.) I also want to be able to add
>>> data to be inserted into tables to my scripts.
>>>
>>> What are people currently doing to solve these issues?
>>>
>>> Warm regards,
>>> Tony
>>>
>>
>>
>