Synchronising Databases With Different Schemas
Apr 28, 2008
Hey folks,
I'm looking to gather ideas on this before I start the project; so what better way to start than to ask here? ;)
The situation is this: we are in the process of replacing a large data driven application with a new one. Before turning the old'un off (that's not for a minimum of 6 months), we need to migrate data between the two entirely different schemas, and I've been tasked with writing the interfaces.
Now, here comes the kicker; they don't just want a "straight-forward data conversion", they want to run a period where they can still update both databases and any changes in the old one are to be pushed into the new db.
Because the schemas are not the same, I believe replication is out of the question so I was looking for advice as to what possibilies I have ahead of me to achieve this.
I fear I may not have expained this well enough; so as always if there are holes in the detail let me know and I'll do my best to fill them in :)
Old db = SQL Server 2000
New db = 2005
Thanks,
George
View 13 Replies
ADVERTISEMENT
Oct 4, 2001
Hi, can anyone point me in the right direction of how to sync an in-house SQL Server 7 with a remote SQL server 7.
We are a development house. We build website using SQL server 7 databases then move them up to an entirely different SQL server 7 database in a remote location.
Now, please don't laugh, currently we export all the data from the development server into a csv and import it back into the live server. As you can imagine, it's clunky, time consuming and unreliable.
Is there a way to automatically sync the two? I tried desperately to find any information but even the MicroSoft MCSE training manual on SQL doesn't say how to do this.
Any help would be greatly appreciated.
Thanks in advance.
Chris
View 1 Replies
View Related
Jun 9, 2015
I have a SQL Server 2012 Enterprise Edition install that I am needing to do the following:
- Provide db_owner access to a single user. Have the db_owner privilege apply for the user in all Schemas of the database (this is a generic AD account that is being used for an application).
They are wanting me to do this because they do not want to get so granular as to tell me all of the objects that the user will need specific access to in any of the given Schemas of the database(s).I have 8 databases, and each one has additional Schemas. These are all contained in a Default instance.I am finding that without explicitly applying permissions to the various objects in the Schemas that are not the default Schema for this user, I am a bit stuck.Is there any way to have the db_owner permission apply to the single user for all the Schemas in a given database (without making them a Sysadmin)?
View 4 Replies
View Related
Dec 11, 2007
Hi,
We have two databases right now that house miscellaneous small applications. We have them in place so developers can develop small applications in them without going through the database request process and bother the DBAs. This was set up before I got here - so I wasn't involved in the decision to do such a thing. The obvious drawback to this is that a restore would wipe out data for multiple applications. As we move to a new 2005 server we are re-evaluating our methods and would like some input on this - and some other aspects.
I know that we could use filegroups and put objects related to certain applications within them so restore is independent of other apps. Each application has its own user which is granted execute on it's stored procedures.
Are we missing anything here? I'm almost tempted to try to get separate databases created to uncomplicate it. If a developer can spend hours working on an app, they can spend 10 minutes on a form and wait 30 for us to create it.
Another thing I've noticed it that it can take quite a while to grant permissions to many stored procedures to a user in 2005. In 2000 there was a grid and you could arrow down and hit space, granting execute. You could also use code to do this, be we never really needed to since developers granted as they went and we would script the object and check the 'script obect level permissions' checkbox. This has since disappeared and granting execute is another step for us.
What do you think of 'sub schemas' for each role in the application - which is usually 'User' and 'Admin'? So we would have MylittleApp_User.ProcedureName and MylittleApp_Admin.ProcedureName with execute granted on the schema for each application user. Tables would be placed in MylittleApp schema.
In the past developers simply prefix their procedure with the application name to denote what application it belongs to.
Thanks for your advice on this - we don't want to get headed down the wrong path.
View 3 Replies
View Related
Aug 24, 2006
I have several distributed databases with identical schemas that I have added to my SQL server as a set of linked servers. For the sake of simplicity consider a schema with single table with one column called Info. I would like to present a view with columns DatabaseName and Info, that presents all the rows from all the databases in a single view.
How can this be achieved this using a SQL Server View?
If not is not possible using Views, what approach you recommend?
View 1 Replies
View Related
Jan 7, 2008
Hi,
I have two databases lets say DB1 and DB2.
Schemas for both databases is same.
In both database schemas there are tables which has identity columns as primary key.
Now i want to merge these two databases in a single database say DB3.
It may also possible that some master records in both databases are common so they should not repeat in DB3
Is there any way so that i can do it quickly and as soon as possible.
Thanks in advance
Rohit
View 1 Replies
View Related
Nov 19, 2001
Hello,
I've installed development server and created the databases, and restore the ddatabase from backups of production server, There the users in the databases which are not in sysxlogins in master, Please guide me how I can synchronise the logins? Can I bcp out sysxlogins from development and then bcp in in development server? Is there any way there even groups in the database and do I have to corncern about any other tables in master db
Thanks in Advance
Alpa
View 3 Replies
View Related
Dec 25, 2005
HiWe have an internal ms access database which we need to synchronise with ourwebsite with sql server 2005 backend which is hosted on a public host. Myquestion is; is there a way to keep the two databases synchronised using thetechnologies built in sql server 2005 and/or access? We also have thepossibility of an in-house sql server 2000. Can this be used in anyway tohelp, perhaps by moving data from in house access to in house sql server2000 and then synching sql server 2000 and remote sql server 2005?ThanksRegards
View 1 Replies
View Related
Jun 27, 2007
Hello guys,
I'm stumped. Is there a way to get a compact edition client (running on a standard PC) to automatically sync. (using merge replication) with a server on a schedule?
I can quite happily pull up SQL Managerment Studio and sync. on the client, but I want to have something running in the background basically doing this for me (again, on the client). I know I can do it in code, but we've not yet received our Visual Studio package, so for the moment, I want something already written to do it for me.
Surely this must be possible?
Thanks in advance!
View 8 Replies
View Related
Jan 23, 2008
I have a problem synching my xda mini s with my pc using vista. A previous thread was unhelpfully closed by Ilya as being off topic; well it isn't on topic anywhere else. At least not according to microsoft's own search engine. Ilya, why not try giving a helpful answer instead of 'not my problem'? My xda will not synch using vista; XP and ActivSync has been perfect. I've tried installing mobile device centre version 6.1 but I suspect vista isn't compatible with Windows mobile version 5.1. Should it be? If not, is there a patch or is upgrading to vista going to cost another couple of hundred pounds to get a compatible mobile device? If so, I'm off to a different operating system. Perhaps, like many others, back to XP and let Microsoft continue to dither around and experiment on others.
View 1 Replies
View Related
Dec 28, 2005
HiIs there a way to synchronise/replicate an access db with sql server 2000db, short of writing the code oneself?ThanksRegards
View 1 Replies
View Related
Nov 17, 2006
Hi
I have a production system running sql 2000 server 32 bit
Now i am planning to move to SQL server 2005 64 bit setup .
I need to have a set up of both SQL 2000 and sql 2005 in parallel for some time until it is sure that the sytem can completly move .
The problem is how do i synchronize the data in the two servers when they are operating parallely.....
Please advice.
Thanks in advance......
View 1 Replies
View Related
Feb 8, 2007
HI ,
I Created one database named login.sdf in sql Ce ,i created one table login having two fields (username and password).it is working well.I trying to synchronise these with SQL2000 using Active sync.it is not working ..And one more things ,i tried to connect the default pocket PC 2002 emulator with Active sync.it is also not working.can anybody give some solution for these problem
Thanks in Advance
Regards
Ihsan
View 8 Replies
View Related
Jan 28, 2008
I'm trying to understand the schemas in SQL Server 2005. I have a script which was given to me by someone as follow and I'm trying to run the script but it gave me an error at the create view section:
Assume an empty database has been created.
CREATE SCHEMA [mySchema] AUTHORIZATION [dbo]
GO
CREATE TABLE [tblABC] ( ... ) /*note: it didn't bind the table to the above schema*/
create view mySchema.vwMyView as
SELECT * FROM mySchema.tblABC
GO
However I'm confused because the above CREATE VIEW made a reference to the table with the schema. Can someone help clarify. When i tried to create the view, it gives me an error that it is unable to find the mySchema.tblABC table. To me, the error is correct as per my understanding of binding a table to a schema ---> If you do not create a table and bind it to a schema, you are not able to call it together with the schema.
What I'm trying to understand is, without explicitly stating [mySchema] during the table creation, is there a way where it is mysteriously binded thus the CREATE VIEW somehow understood that tblABC has been binded therefore it called the table in the view? or is this just a script error.
Btw, this script was generated from a working database.
View 5 Replies
View Related
Mar 1, 2001
Hi Guys,
I'm afraid the schemas of the Development and Production versions of a database are in fact diferent.
How may I compare two database schemas?
Thanks in advance !-)
View 1 Replies
View Related
Dec 12, 2011
Is there anyway to differentiate between two schemas between two SQL server instances?
View 2 Replies
View Related
Aug 15, 2007
Hi,Anyone know an online source for database schemas, or schemas for singletables i.e. where can I download a schema for an addresses table?--Richhttp://www.badangling.com -= Sea fishing badly explained =-
View 4 Replies
View Related
Mar 25, 2008
Is there any way to create a local database cache from an SQL 2005 database that uses non standard schemas (like AdventureWorks). I have tried in Visual Studio 2008 but any database that does not use .dbo will not allow any tables to be copied locally. Even setting the default schema for my login does not enable adding any tables in the Configure Data Synchronization dialog box.
If it is not possible via Visual Studio is there any other way? I notice all examples with Sql Compact conveniently use Northwind. Seemingly Compact edition does not support non .dbo schemas.
View 1 Replies
View Related
Dec 27, 2007
Hi,
I wanted to find out what the statement is for finding out the number of schemas in all your databases.
I know in 2005 the you can do a select * from sys.schemas
but how do you achieve the same thing in sql server 2000 ?
Please let me know..
Thanks,
View 1 Replies
View Related
Dec 26, 2007
Hi,
How do I find out the number of schemas in sql server 2000 and 2005 for each database?
Also, how can i find the number of tables in each database?
I'm trying to figure this out but can't find anything on google.
Please help.
Thanks,
Kapinak
View 4 Replies
View Related
Apr 25, 2007
I have schema called [sitex] for some tables .
I have to perform insert or update operations in to these tables through views only and these views were created with [site] schema
All stored procedures are written in the point of view only ( all DML operations into the tables are through views).and these stored procedures has schema same as views.
Now come to the permissions and authorization.
We have some users like sa,112,janu
These users must have permissions only on site schema not on [sitex].
I have a role called €œtestrole€? with 112,janu users and I gave authorization on site schema on for testrole
ALTER AUTHORIZATION ON SCHEMA::[site] TO [testRole]
Now I€™m getting error select permessions are denied on [sitex] schema.
But our requirement is 112 must don€™t have any type of permissions on tables
And also is there schema to schema permissions. (that mean [site] schema has permissions on [sitex] schema .
PLZ help me
View 3 Replies
View Related
Jan 7, 2006
Hello
Can anyone give an introduction to the use of schemas i SQL Server 2005. If have noticed the feature, but i would like to know why and how the use of schemas is a good thing.
If anyobe has a link to a whitepaper or practical insight that would be nice.
View 4 Replies
View Related
Nov 17, 2006
We're using Sql Server 2000. The one database contained tables and stored procedures which were possibly updated with some script information. Is there an application(commercial or free) or script I can use to compare the base database against this updated database to confirm there schema information is the same.
Thanks
View 1 Replies
View Related
Nov 27, 2006
I'm trying to find some documentation or procedures that will allow me to xfer existing users' roles, etc from sql2000 to sql2005.
When our sql 2005 database was restored on a new server (from sql2000), all of the database users were orphaned. I need to create identical server logins and then make the logins database users. It is also important to understand the significance of the new schema concept. The Microsoft SQL2005UpgradeTechRef.doc white paper does not explain any of this.
Ultimately, I would like to create a script for the above scenario once a sql2005 db is restored from a sql2000 db.
Could someone please help me out on this?
View 1 Replies
View Related
Dec 10, 2007
Is there a way to create a table in SQL Server Management Studio under a different schema? I've tried setting my default schema and adding a table, but that didn't work. I know I could do it with a SQL statement, but wanted to know if I could do it with the designer.
View 1 Replies
View Related
Mar 27, 2008
Is there an easy way to move a view from one schema to another?
View 2 Replies
View Related
Mar 28, 2008
I have some tables sitting in schema "A", then I created a view in schema "B" based on the tables in schema "A". Then I gave the user just access to schema "B" to only run the view but not see the tables in schema "A".
Could it be that if the user tried to ODBC to that db, they would only see the view in schema "B" and not the tables in schema "A"?
Thanks!
View 2 Replies
View Related
Jun 20, 2007
Hi eveyone, I'm reading about namespaces and schemas in MS SQL 2005, and that's exactly what I was looking for for the structure of my database.
I've a database and 4 different applications that will use it, and every application will create a new database every year, for example, i'd like to use the following schema:
MainDatabase.MyApplication.MyYear.MyTable
In MS SQL i've created my main schema using:
USE Mydatabase;
CREATE SCHEMA MyApplication
GO
Now I want to add sub-groups (in this example, I need to create Year2006, Year2007, and so on).
Even If i create those schemas, how can I "add" them inside the Salary main schema?
I've tryed with
ALTER SCHEMA MyApplication TRANSFER Year2006
but it doesn't work since Year2006 is not an object.
Thank you!
View 6 Replies
View Related
Jul 24, 2007
We have a system with multiple modules, for each module we have a separate database. Is it better to have multiple databases or have multiple schemas. Performance, advantages, disadvantages...
When is too much too much, 10-25-50-100 Schemas???
Course.dbo.table1
Program.dbo.table1
vs.
NEWDB.Course.table1
NEWDB.Program.table1
Thanks
View 5 Replies
View Related
Oct 24, 2007
Hi,
I am looking for some advice on securing a sql 2005 database.
I have a database to which I want to grant 3 different users access.
The first users, who we will call 'public' should have access to a set of stored procedures, and no access to tables without using the stored procedures they have access to.
The second users have again got access to a certain set of stored procedures, along with the stored procedures for 'public'. These should be called 'moderator'.
Finally the third set of users 'admin' should have access to all stored procedures, but again no access to the database unless its through a stored procedure.
Questions :
Using SQL 2005, how do I assign access to particular stored procedures to a user?
How do I ensure that a 'user' cannot access stored procedures I don't want them to access?
Some of the stored procedure use dynamic sql within them, will this affect my setup. If I deny direct access to the tables to all three users, but allow them access the stored procedures, can the stored procedures access the tables?
If schemas are the answer, how on earth do I set them up, Ive never seen something so badly documented online in my experience! In my development environment im using sql express, so will probably working through the query analyzer.
Thanks in advance.
Conor
View 2 Replies
View Related
Jul 20, 2005
Hi, I am storing information being sent to me weekly into a ms sqldatabase. The one twist I am running into is that later down the linethe information I recieve may require more columns, or columns mightbe renamed so having a static database call is out of the question. Iwas using mysql but for certain reasons switched to ms sql 2000.Currently before ms sql, I query the database for show fields whichreturns all the database data such as type, column name, etc... then Iuse a giant if statement structure to deal with matching andconverting data from my users display to the information into thedatabase.So my first question is how do I either query or using datatypes builtinto C# return information about a tables setup. I was hoping therewas some sort of table array object which could be returned with namesand types, if not then just an sql statement return.My second question is I have user data which needs to be displayeddifferently then the database raw data, how do I use a C# dataset, andconvert it's column data into my raw database data without having tocreate raw insert and update sql strings myself? I would really liketo use the sort of automatic update features of the dataset.Lastly am I going about this the wrong way? Are there other ways tomap & convert datatypes between a dataset column and a table column.
View 1 Replies
View Related
Jan 25, 2007
My problem is very simple and that is that I'm trying to copy some tables between databases, but these tables are in different schemas.
let's say I have
dbo.tableA
sch1.tableA
sch2.tableA
sch3.tableA
And I just want to copy let's say sch1.tableA to a Different DB.
If I use Transfer SQL Server Object task and select the table and save the package and try to open the task again, all the tables with name TableA will be selected!! it seems like although it does show the schema ( when I am selecting the table manually ) but it doe snot store the schema detail in the tablelist collection property of the task.
Would please recommend any other way to achieve this.
Many Thanks in advance
View 1 Replies
View Related
