August 31, 2001 at 3:12 pm
I don't like having development and production databases on the same SQL server. (Bad news waiting to happen. But many folks around here disagree. They think it makes the developer's life a lot easier.
How do you see this issue?
Bill Salkin
August 31, 2001 at 4:11 pm
I'd vote against doing it as well, at least on the same instance. Ideally your developers should only have to change the server name for everything to work when they move the code to production (in my opinion anyway!). Setting up a 2nd instance of SQL on the same box wouldn't be a bad compromise if you're short on hardware. Licensing is not an issue since if you have MSDN (who doesnt) you get a developer license for SQL to use on a separate box.
Plus, who wants developers running queries, making dts packages, etc, on a production box?
In what ways would it make their life easier?
Andy
August 31, 2001 at 7:47 pm
When you say "they think it makes the developer's life a lot easier", who are you referring to? Is it your supervisor, or the developer's bosses? What do the developers think?
Unless you have developers that are extrememly efficient and conscious of the impact their work has on SQL Server proceses, I would not even recommend having the development server on the same box, or even the same disk array. Lots of good intentions (hey, let's try this!) can bring a production server to its knees.
September 12, 2001 at 10:10 am
I'm currently considering setting up a second (development) instance on a SQL 2000 system.
The system is pretty hefty (8 processors, 6Gb RAM, 1Tb) and we have a massive amount of development to do to get code over from a SQL 6.5 system.
We are reporting from multiple disperate sources, and have built a series of infrastructure items that live in a fairly rigid database structure. Using multiple instances seems to be the way to go (as opposed to having different database names for live / test).
I am looking to assign the resources fairly down the middle between the two instances (reading up to ensure that we continue to benefit from AWE memory).
That way, in the future, when we no longer have a need for the 6.5 Server, we can upgrade this and re-allocate resources to give the Live Server instance a boost (it will have more work to do then too!)
Can anyone suggest any gotchas or potential problems with using Multiple Instances.
Many thanks
September 12, 2001 at 10:34 am
Haven't worked with multiple instances. I'd test on the 2nd instance and move to the first for production. The naming is more complex for 2nd instances, so if it works there, it should work on the other.
Personally, I try to keep development limited to databases and not dependent on the db name. This way, you can host multiple database instances on the same server. I have had the problem with a 3rd party app that requires the db to be named xxx. If I want a second copy of the system running, I have to setup a second server. This is poor design IMHO.
Don't confuse db with server. SQL Server is designed to support multiple databases within the same server. There is no reason you cannot have production, dev, qa, etc. on one box. The big issue is load. Keep in mind that developers testing queries( this includes DBAs), can sometimes implement a query that will drag the performance of the box down. This is a good reason to have separate boxes. but QA and dev on one box. Fine. Also will help force you to setup rights appropriately. Don't make developers sysadmins (http://www.sqlservercentral.com/columnists/sjones/sarant.asp)
Steve Jones
September 12, 2001 at 10:57 am
We're running a couple of servers with multiple instances, but that's for Active/Active clustering. Biggest issue is resource usage. As Sean already pointed out, a poorly written query in development can have an impact on the production side. You can allocate maximum memory usage, and I guess go to the point of dividing up processors, but there is still risk. If you have the option of a second server, I'd go with it.
With respect to making your developers' lives easier, probably not. If the db's are on the same instance, they'll have to deal with multiple database names. As Steve and Andy pointed out, though, there shouldn't be a need to explicitly state the db name within any code, but so far as defining connections, there's the db name to worry about. If you go to a second instance, then you've got issues to deal with where developers are either going to have to remember the alternate connection methods since they can't simply use a server name (which would go to the default instance) unless they define an alias. So it probably adds another step or two to their processes, so it doesn't seem like it makes it any easier at all.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
September 12, 2001 at 5:46 pm
You will also have to use named pipes for the connection to the 2nd or later instance if you're not running the latest MDAC (I think 2.6 or higher).
Nigel - have you considering just upgrading to 2000 and setting the compatibility mode to 65? We used the 65 mode for a few days after running into an item our testing didnt reveal, worked well. A few days on 2000 you wont want to go back ever!
Andy
September 13, 2001 at 9:53 am
Wow - what a great response!
Just to clarify a few things...
The work that will be performed on the Server is Reporting from a number of other systems. Some will be populated using SQL Backup / Restore (i.e. refreshed daily), one populated through a series of DTS jobs from SQL 6.5 initially
Other systems will be accessed through Linked Servers (SQL and Oracle)
I only have one server available for Development / Live at the moment (won't go into details)
By 'Live', I refer to the daily live running of published Reports
I realise that Load will be a problem.
I have had further thoughts and drafted an internal discussion document with the following possibilities:
o Multiple Instances
o Code directly within the database
o Write code pointing to Dev versions, then re-point to Live
o Use Views to code to a single database structure
(have the live Reporting db views pointing to the Live restored db, etc).
Discussions are ongoing, comments always welcomed.
Thanks.
September 14, 2001 at 4:16 pm
In the general case one isolates development. If anything I wish we had more smaller dev servers and less big shared dev boxes so I could partion sandbox and thus give them more rights on a box. (And also limit the damage they can do to others!)
I had a case somewhat like yours were the new system involved lots of DTS jobs pulling data from other systems. They were on a tight schedule and the application was one of those high priorities. I moved them to a test server which was fairly quiet at the time and that turned out to be a good solution. When you are limited by hardware you can only do some much...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply