|
I hope this isn't considered too much of a programming question, and the solution shouldn't involve any programming.
At the moment, two of us are testing related applications on local copies of our test database, to not get in each other's way. However, I would like to set up a shared, central test database, where I can be sure the configuration, i.e. versions of views and stored procedures, are consistent and can serve as a baseline for releases. However, I would also like us to be able to independently manipulate data, on separate copies of the data, with identical configuration.
So, I'd like all DDL operations to always be replicated, but all DML operations to remain local most of the time. What aspects of replication should I be looking at to achieve this?
-- modified at 8:21 Monday 5th November, 2007
|
|
|
|
|
I'd actually consider using this[^] if this is the way you want to operate. This way, you can synchronise your test database as and when you need to.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Do you know of a tool or a way where we can actually track the dependencies and changes of DDLs? Something similar to source control only for databases.
Because this is giving me a headache to maintain versions for differing releases and the merging of them and all that.
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." - Rick Cook
"There is no wealth like knowledge, no poverty like ignorance." Ali ibn Abi Talib
|
|
|
|
|
The tool in the link I posted is designed to do just that. To use it, you just compare a source and a destination database and it generates the synchronisation scripts for you.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
On our main dev tree, we use Subversion for this, with an automated scripting and commit process. I'm sorry I don't have details right now as I am not involved in that side of things at the moment.
|
|
|
|
|
Use DDL triggers to trigger a change in both your DB's. You dont need to do anything for the DML part.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Relying on this behaviour is actually quite dangerous. What happens if the target database is down for instance? You need to ensure that ACID is followed in an operation like this, so the second update should really be disconnected from the main update.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O'Hanlon wrote: [Rep
I initially planned to host both test databases on the same instance as the 'master' test database, where at the moment they are locally hosted on our individual machines. Hosting them on the same server would mitigate the risk you mention.
|
|
|
|
|
Brady Kelly wrote: I initially planned to host both test databases on the same instance as the 'master' test database
But you are still going to be making your changes locally? If so, the risk is exactly the same.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
No, if I host one test database each, TestDB1 and TestDB2, and a master test database, TestDBM, on the same server, DML changes we individually make will apply to TestDB1 and TestDB2, not affecting each other, and DDL changes we make to TestDBM will always be able replicate to the other two because their availability will practically always be the same as TestDBM.
|
|
|
|
|
Brady Kelly wrote: No, if I host one test database each, TestDB1 and TestDB2
The point I am trying to get across is where are you going to be making the changes initially? Surely not to a test database? You should always develop against a development installation, and then replicate your changes across to the test environment. This is where the issue lies - not in synchronising between test databases, but synchronising between development and test databases.
In general, your development environment should be kept well away from your test environment.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I want us each to have a personal dev environment, as well as a personal test environment, the latter preferably hosted together with the master test environment.
DDL changes will be made locally, in our individual dev environments, yes, but will be manually deployed to the master test, from which they should propagate back to our individual test environments.
|
|
|
|
|
So, isn't an automated approach a better choice then? Remove the danger that changes are missed between the dev and test environments.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
It is and will be, but for this release an untested, automated approach seems a bit risky. I would like to move toward that though, using November to verify the the automated tasks before a December and future releases.
|
|
|
|
|
I wouldn't worry too much about it in a testing/development environment like what's being intended here. It's not production and whatever error that comes out of using DDL triggers are traceable and rectifiable.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
I always worry whenever I hear people comment that consistency between dev and testing isn't too big a deal. The issue you have here is that you introduce something else into your testing process that doesn't need to be there. Why is this an issue?
Well, you have a development environment, which may or may not be different to a testing environment, which may or may not be different to your production environment. How do you ensure that what you did in the development phase makes it into the production phase?
Testing should replicate the production environment as much as is practicable, and should really be done up front. It shouldn't need manual intervention to ensure that "script A is run and then script B and then script C, and oh yes, we've got to run script D as well - mustn't forget that because it recreates the views that only get hit once a week, but are absolutely vital".
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I agree with your point that the test env and the production env must be as similar as possible. But I can still write "intelligent" triggers that check for db existence, schema mismatches etc beforehand.
Our products go through QA in a lot of separate teams and it's impossible to replicate the production environment in all the QA setups. We neither have ownership of the test beds nor can we dictate anything. We just put in our own mechanism that gives us better insight that what the blunt test results which the QA gives us.
Triggers are not a bad thing to have. It's just plain stupid to have them in an OLTP table. But in a situation where it's fired only rarely, they can reduce a lot of code needed for a workaround.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
hi All
Am migrating from SQL2000 to SQl2005. the database had been migrated. i would like to know if i migrate, the Constraint in my table like "Primarykey", does it get shipped on the migration Process or they get lost? must i start create a Primary key for each table?
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
If you've used the setup migration wizard, everything usually works fine. But in case you used DTS/SSIS, the PK's may not have been replicated and you need to add constraints manually.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
parent son
A B
A C
A D
D E
D F
D G
G I
G J
i blank
j blank
b blank
c balnk
I need a query to get son and grandson, grandgrand sons (till Entire generation) …. Of ‘A’…..
|
|
|
|
|
VB.Net Developer wrote: I need a query to get son and grandson, grandgrand sons (till Entire generation) …. Of ‘A’…..
Very good. What have you tried so far? Plus, what database is this query in.
We don't do homework for you, and we don't do your thinking for you. We do help you if you've tried something and can't get it to work.
Hint - if you are using SQL Server, take a look at Common Table Expressions (CTEs) to accomplish this.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Trying to be a bit more helpful
But the solution has to do with CTEs, at least if your using SQL Server as this is not standard SQL:
<br />
with tmp(parent, son) as (<br />
select <br />
tab.parent, tab.son from tab<br />
where <br />
tab.parent='A'<br />
<br />
union all<br />
<br />
select <br />
tmp.super, tab.son<br />
from <br />
tmp join tab on tmp.son=tab.parent<br />
Beware of loops! In that case you need to introduce a recursion counter or a condition that restricts son<>'A'.
|
|
|
|
|
Hi all,
I Have the following statement...
SELECT convert(varchar,convert(varchar,datepart(yyyy,GetDate())) + case Len(datepart(MM, GetDate())) when 2 then convert(varchar,datepart(MM,GetDate()),101) when 1 then '0' + convert(varchar,datepart(MM,GetDate()),101) end + case Len(datepart(MM, GetDate())) when 2 then convert(varchar,datepart(dd, GetDate())) when 1 then '0' + convert(varchar,datepart(dd, GetDate())) end) AS [TodayDate]
Now what i planned this to do is add the leading zero in front of the day and month figure if the length is 1 to make the length 2... like this...
20071105 (notice the zero in front of the 5)
BUT it is still showing it like this...
2007115
How do i go about getting the zero in front of the 5 ??? My output needs to look like YYYYMMDD
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
Never Mind....
i have made this a bigger problem then it actually is... SORRY about that...
The easiest way to do this is:
CONVERT(CHAR(8), GetDate(), 112)
"Many of life's failures are people who did not realize how close they were to success when they gave up." Thomas A. Edison
|
|
|
|
|
better you can use like...
CONVERT(CHAR(8), GetDate(), 112)
/*101-12/17/2006
102-2006.12.17
103-17/12/2006
104-17.12.2006
105-17-12-2007
106-17 Dec 2006
107-Dec 17, 2006
108-00:00:00
109-Dec 17 2006 12:00:00
110-12-17-2006
111-2006/12/17
112-20061217
113-17 Dec 2006 00:00:00
114-00:00:00:000*/
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
