Working with live data..danger

[gstar]

Hi,
Not sure I am in the right place here but thought I would ask an open
question. We have an IIS website, mainly ASP files, .NET 1 & 2, this
is located in our DMZ. It connects back into the LAN using using port
1433 to our SQL 2000 server.

As the Sysadmin I try to ensure our development team only work on the
development SQL & IIS boxes but this is becoming more difficult by the
day. Very soon I am moving the production servers to a data center so
obviously any problems would cause extensive downtiume due to
distances I have to travel..

How do you all deal with the developer idea that you must work on live
servers during working hours? My view is that any bugfixes or software
release's should be carried out out of business hours, but I seem to
be hiting a brick wall as I am on my own..

Cheers

 

[Eliyahu Goldin]

You can setup a test site that will run a copy of the production code
connected to a copy of the production database. Once changes are tested
there, they can be deployed to production in after hours.

--
Eliyahu Goldin,
Software Developer
Microsoft MVP [ASP.NET]
http://msmvps.com/blogs/egoldin
http://usableasp.net

 

[Mark Rae [MVP]]

How do you all deal with the developer idea that you must work on live
servers during working hours? My view is that any bugfixes or software
release's should be carried out out of business hours, but I seem to
be hiting a brick wall as I am on my own..

I have three environments: dev, test and live.

Developers don't need to work on live servers - they may, however, need to
work on real data, in which case synchronise the live database(s) with the
dev and/or test database(s) at the beginning of the working day.

 

[gstar]

I have three environments: dev, test and live.

Developers don't need to work on live servers - they may, however, need to
work on real data, in which case synchronise the live database(s) with the
dev and/or test database(s) at the beginning of the working day.

Thankyou for you replies.
I too have dev, staging & live enviroment, thats what makes it so
annoying! They say if the MD needs a report in the middle of the day
or a customer rings in with a bug, it needs fixing on live right then!

I like the idea of synchronising the live data to the staging server,
is it easy to achieve, is that possible over the WAN? As you can see
we dont have a DBA so I am trying to firefight here! I also dont want
to sync the structure, just the data?

Again thankyou for your time, I know you must have answered questions
like this before..

 

[Mark Rae [MVP]]

They say if the MD needs a report in the middle of the day

That's not a bug, though! Why can't the MD use the live system...?

or a customer rings in with a bug, it needs fixing on live right then!

No! Staging and live must be running the same code, right? Otherwise there's
no point.

You really need to stand your ground here, and say the following:

If a customer rings in with a bug, it needs fixing on dev, testing on
staging and then deploying to live. If you can't deploy to your live system
during business hours, then you most certainly can't bug fix on your live
system either...

If your management is unable to see the rationale for this, then I strongly
suggest you look for another job. Hacking about with live systems is a sure
recipe for disaster - and when the disaster inevitably happens, who do you
think will get the blame...?

I like the idea of synchronising the live data to the staging server,
is it easy to achieve, is that possible over the WAN?

Sure - why not? You just need a tool which can connect to both your live
database(s) and your staging database(s). I use this:
http://www.teratrax.com/. This is also very popular:
http://www.red-gate.com/index.htm?gclid=CLyZkKThlY8CFRoGEgodyUi6eg, but it's
a bit more expensive. You can even write your own tool in raw T-SQL if
you're really strapped for cash, but why reinvent the wheel...?

As you can see we dont have a DBA so I am trying to firefight here!

In the words of Tom Jones, it's not unusual...

I also dont want to sync the structure, just the data?

Both the products I mention above will do that.

Again thankyou for your time, I know you must have answered questions
like this before..

All part of the service...

 

[Guest]

As Mark indicated, I wish I had a dollar for every time a disaster happened
because some developer was "fixing" something on a production web site and
they blew it because their fix didn't go through a sane testing process.
-- Peter
Recursion: see Recursion
site: http://www.eggheadcafe.com
unBlog: http://petesbloggerama.blogspot.com
BlogMetaFinder: http://www.blogmetafinder.com

 

[gstar]

Mark, again much appreciated..

This is not referring to the normal reports but to random information
only he would need. They currently connect to SQL to run these reports
as a one off! This is oftern where the problem begins if we have an
inexpereinced dev chap running code that wipes out the CPU!!

No! Staging and live must be running the same code, right? Otherwise there's no point.

dev is for our development work [messy!]. The scripts or code are then
uploaded to the staging server for end user testing. The staging
server is a mirror image of live except for the data..

You really need to stand your ground here, and say the following:

If a customer rings in with a bug, it needs fixing on dev, testing on
staging and then deploying to live. If you can't deploy to your live system
during business hours, then you most certainly can't bug fix on your live
system either...

If your management is unable to see the rationale for this, then I strongly
suggest you look for another job. Hacking about with live systems is a sure
recipe for disaster - and when the disaster inevitably happens, who do you
think will get the blame...?

Thankyou, glad I am not on my own here, I was starting to think maybe
I was wrong for wanting to protect their data.. I wish a Sysadmins
best practice document or policy existed, I could then present it to
them to see its not me being difficult!

I will certainly look at these products you suggest, Im sure they will
help.

Cheers to you also Peter, its music to my ears to hear other people
agree..

 

[Mark Rae [MVP]]

This is not referring to the normal reports but to random information
only he would need. They currently connect to SQL to run these reports
as a one off! This is oftern where the problem begins if we have an
inexpereinced dev chap running code that wipes out the CPU!!

Hmmm - well, there is another potential solution here...

Many large companies run two copies of their database(s) - one for OLTP, and
the other for OLAP. The amount of data involved, and the acceptable level of
latency, dictact the frequency with which the OLAP server synchronises with
the OLTP server. This means that the OLAP database can be used for analysis
quite independently of the OLTP database, and it's never that far
out-of-date. For companies like Amazon which process thousands of
transactions a second, they simply can't risk their OLTP database being
crippled by a time-consuming query...

Depending on your organisation, this might be overkill, though...

Thankyou, glad I am not on my own here, I was starting to think maybe
I was wrong for wanting to protect their data.. I wish a Sysadmins
best practice document or policy existed, I could then present it to
them to see its not me being difficult!

I really would suggest you look at your local job market...

Cheers to you also Peter, its music to my ears to hear other people
agree..

Peter and I agree on occasion... ;-)

 

[gstar]

All comments taken on board and appreciated, it has helped me to
decide our approach and given me some much needed confidence that I am
doing the right thing!!

I really like the Teratrax product, have tested and it does the job
brilliantly. I cant see a method to automate this to occur each day at
a certain time and sync the servers without any input, will look
further into it later, but do you know if thats possible?

Ta

 

[Mark Rae [MVP]]

I really like the Teratrax product, have tested and it does the job
brilliantly. I cant see a method to automate this to occur each day at
a certain time and sync the servers without any input, will look
further into it later, but do you know if thats possible?

Ah, no - I don't think so...

This type of tool really is really aimed at ad-hoc development tasks rather
than regular and scheduled production tasks.

Luckily, though, if you need regular replication between production and
staging, then SQL Server already has this built-in:
http://www.google.co.uk/search?sour...IH_en-GBGB220GB220&q="SQL+Server"+replication

 

[gstar]

Ah, no - I don't think so...

This type of tool really is really aimed at ad-hoc development tasks rather
than regular and scheduled production tasks.

Luckily, though, if you need regular replication between production and
staging, then SQL Server already has this built-in:http://www.google.co.uk/search?sourceid=navclient&hl=en-GB&ie=UTF-8&r...

OK, I guess I could run it manually each week to uopdate the data
locally. I have however run into a few problems so I may also try Red-
Gate, on the first DB I attempted to synch it reported that out of 47
tables 23 had no primary key so could do anything with it!. I have
asked the dev team why, but they shrugged their shoulders.. Helpful
eh?

Cheers

Gary

 

[Mark Rae [MVP]]

OK, I guess I could run it manually each week to uopdate the data
locally.

As mentioned, these are very much tools for ad-hoc work. Once I've set up
the various tasks (TeraTrax calls them "projects"), I only ever run them on
a need basis... For regular synchronisation, I use replication - that's what
it's for...

I have however run into a few problems so I may also try Red-
Gate, on the first DB I attempted to synch it reported that out of 47
tables 23 had no primary key so could do anything with it!. I have
asked the dev team why, but they shrugged their shoulders.. Helpful
eh?

You're wasting your time with this company...

I suggest you show your boss this thread...