Replicating Design Changes
Mar 5, 2005
I am trying to create an auto off-site backup of an entire database. This would include databases and users. It should also include changes made throughout the day.
Something challenging about this is I want it to also include design changes that may have been done throughout the day.
I understand log shipping or replication can deal with the data part of my solution. But how can I copy over the logins, users, and design changes?
Is it possible to have design changes replicated from publishers to subscribers?
View 3 Replies
ADVERTISEMENT
Dec 13, 2007
I need to copy all the data from all the tables in a database to a copy of this database on another server.
What feature of SSIS should I take advantage of to accomplish this?
We have an SLA for 8am, most times the data warehousing jobs complete at 8:05am. Adding an additional process/set of tasks to this package would obviously make matters so I'm trying to update/copy/replicate the data in the fastest manner. Typically we're talking 2 marts (10-20GB) with 2 large tables (5-10 mill records) and 20 marts (0.5 - 5 GB) with many more smaller tables (~40 tables with record count ranging from 1 to a million)
Additionally please indicate if the design/feature you suggest can handle (pushing schema changes and additions to the target server) schema changes or new tablesviews added to the source database.
My only idea so far...is using the import wizard (in Management Studio) to create an SSIS package (top copy all the tables from one server to another) and saving it to the server, Then executing this package after the job is complete. However this would not work if the schema of a table changed, or if a a table is added. Moreover I don't think I can edit this package in visual studio.
View 3 Replies
View Related
Aug 30, 1999
I need to replicate from a SQL 7.0 server to SQL 6.5 server.
Has anyone done it, or does anyone know if it can be done?
Thanks in advance
Joe Gentile
View 2 Replies
View Related
Jul 23, 2007
Hi gang,
I'm 99% sure this is possible, but I wanted to confirm before I go upgrading one box in our replication scheme without having to do all the others (which are geographically dispersed):
Can an SP2 box replicate (merge replication in our case) with pre-SP2 servers? Most of our servers don't even have SP1 applied, and we're ready to upgrade, but I want to be sure I can do them one at a time rather than all at once.
Cheers,
Matt
View 9 Replies
View Related
Jan 11, 2002
Can we replicate views in SQL 2000 or SQL 7.0 ? If yes please let us know How
Thanks
View 1 Replies
View Related
Feb 22, 2001
We are performing snapshot replications from one server to another, however the permissions are not being taken across.
When we manually reset them, the next replication will quite happily remove all permissions.
Any help appreciated.
Peter
View 2 Replies
View Related
Mar 5, 2004
I am using Sql2000 and have 6 servers. On this 6 servers 4 servers have the same database
MY QUESTION is i need a script or advice that will help to do this:
Every time data chages in 1 of the 4 servers that have the same database, I want all changes to happen in the other 3 so that they must always have the same information
View 1 Replies
View Related
Apr 21, 2004
Good Day Gurus
I posted a question about replicating logins to the database and the answer i got about doing a DTS to transfer logins is not good for me, is it possible to replicate syslogins tables so that i can do this if so, how because they are not listed Database and Publications when i try to create a publication, only individual created databases can be seen.
The reason for this is because when the DBA decides to change the user permission, i want the info to be merge replicated to the subscriber. At this moment i can run the DTS to transfer the login but it won't know when the logins have been updated and hence i won't know when to run it.
Thanks in advance
View 2 Replies
View Related
Mar 31, 2008
Hi,
I am very new to SQL Server. Plenty of SQL knowledge but the whoe SQL server enviornment is new.
I am working with SQL Server 2005. My task is to generate reports without affecting our live database. I have setup a second server and installed SQL Server 2005 on that too. My thought was that maybe I could mirror or replicate the table I require over to this new server and run my queries from here. Is this easy to do ?
I read that mirroring might not work as it is solely for back up /fall over purposes and that data on the mirrored server would not be accessible.
I have also been looking at SSIS but at the moment this is all a bit like double dutch to me ! Can anyone point me in the right direction, preferably somewhere beginner friendly ie not overly complicated !!
Thanks a mill,
Sophie
View 3 Replies
View Related
Aug 11, 2006
I am using Sql 2005 and merge replication. I am relying on the feature where schema changes are replicated to subscribers but I have come across a situation where schema changes stop being replicated.
This is the scenario:
I create a database and publish it for merge replication.
I add subscribers.
If I need to change the published database I can use ALTER TABLE ddl and the subscriber gets the changes.
If I have to add or remove a merge article as part of a database change I specify the @force_invalidate_snapshot=1, @force_reinit_subscription=1. No any ALTER TABLE statements following the article change will NOT be replicated.
Is this a known 'feature'? Is it because @force_reinit_subscription is set to 1?
Can anyone help me?
Graham
View 5 Replies
View Related
Oct 10, 2006
Hello,
We have four mobile devices that are set up for merge replication via the web. We are not receiving errors but some of the data is not coming over to the devices. If we manually add a record that record will come over, but there is data that is on the server that isn't on the devices. If we run the snapshot for each device (We're using host_name as a filter) nothing happens. If we do validation check we get errors. If we reinitialize all devices it works but the next days data (sql job populates data to the publisher db at night) isn't on the device after syncing the next morning. Any help would be appreciated.
John
View 5 Replies
View Related
May 21, 2007
I have a sql 2005 publisher and distributor and a sql 2000 subscribers. for some reason on one of the subscribers i'm getting errors that it can't replicate the UDT's. i tried a new snapshot and made sure it was set not to replicate UDT's but i'm still getting Create Type errors.
would anyone have any idea why it's trying to create UDT's at the subscriber when i specify not to replicate UDT's?
View 5 Replies
View Related
Jun 26, 2006
Hi there,
I'm using Sql 2005 merge replication and I have noticed something, I'm not sure if this is true or not:...
My publication is set to replicate schema changes (replicate_ddl = 1). Now, I have noticed that schema changes are only replicated if the current snapshot is valid. Is this right? If so why?
My next question carries on from the first. If I'm about to run a TSQL script on my publisher that will add a column or two to a published table, how do I ensure my snapshot is valid inorder for the ddl changes to be replcated? Should I be using:
EXEC sp_mergearticlecolumn
@publication = <publicationname>,
@article = <article name>,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1
on each table I modifiy, after I have added the new column?
Thanks for your help
Graham
View 30 Replies
View Related
Mar 13, 2006
Hi,
I have a database that is being set up for merge replication (Sql 2005), but there is one table that I only want the schema replicating, not the data - I never want the data to be replicated in either direction. I can see from sp_addmergearticle that you can do something like this for sp's or functions but is it possible to do this for tables?
Regards
Graham
View 1 Replies
View Related
Nov 13, 2006
Hello,
I use a merge replication between Sql Server and Sql Server Express.
When I enable a DB for .NET features (eg RoleManager), I have new tables and roles that are created and some GRANT are given on SPs.
When I replicate these DB to another one, none of my roles are replicated and I also loose my roles. Is there a way to replicate also the roles and the permissions ?
Thanks
Pierre-Emmanuel Dautreppe
View 1 Replies
View Related
Jun 26, 2007
I am wondering if there is a way to replicate changes in a SQL 2000 DB to 2005 without backing up the DB and restoring it in 2005.
We are running an ERP system using SQL 2000 and are moving to a later version that supports 2005 and we want to test it out before going live but I'd like to sync with the current system from time to time instead of having to convert the DB and get it ready again and again everytime I want to update the data.
Thanks for any help you can offer,
Chad
View 1 Replies
View Related
May 13, 2001
Hi,
I have a number of "join" tables ie joins records from two other tables for example, an employee may be responsible for more than one product so the join table would look like this:
table name: employee_products
Employee_id foreign key from employee table
product_id goreign key from products table
My question is, how do I replication this table? Replication requires all table to have a primary key field. In this case, both fields are foreign keys and I dont have a primary key as the same data appears regularly in either field.
How should I get around this so I can implement replication? I dont want to have to add another field to be the primary key field.
Thanks
View 1 Replies
View Related
Apr 4, 2000
Hi,
My transaction replication is working perfecly.
I am doing some changes at publication database ,altering stored procedure
code , now i want that changes should also take place at subsciber
any one has idea please suggest me
Thank u.
Nil
View 2 Replies
View Related
Dec 27, 1999
Are there any known problems with replicating structure changes (New table, etc...), as well as data in MSSQL Server 7.0?
View 2 Replies
View Related
May 10, 1999
We're considering one database in the far east, using merge replication with a database in London.
There's a time difference of maybe 7 hours between the two sites - has anyone ideas on conflict resolution (thinking about the Far East updating a record at 16:00 their time, London updates the same record at 15:30 GMT - how does SQL Server know that London's time is the correct record to use?).
Any info gratefully accepted.
Thanks
Dan
View 2 Replies
View Related
Sep 23, 1998
I have a table that is used for reporting, the problem is that the data in the table is refreshed every 30 minutes with a bulk insert. I am trying to find a way to have two tables that are mirror images of each other and when the loading table is loaded, then the table assumes the identity of the reporting table. The basic prinicpal is I need to have the table be available almost all the time and when the bulk insert is happening, users cannot query. Any help would be greatly appreciated.
Thanks in advance
Marc Brown
View 1 Replies
View Related
Jun 17, 1999
We have a database that I would like to replicate on another server but am unable to use regular replication via publish/subscribe due to the fact that the production database has no primary keys on tables, only clustered indexes. The backup db needs to be as close to real-time synchronized as possible and will be in fairly active use most of the time. Has anyone had success in developing such a system? How did you do it and what are the pitfalls? Any advice would be greatly appreciated. Thank you.
W.
View 1 Replies
View Related
Mar 5, 2005
I have SQL Server running on my internal LAN. I want to have a second SQL Server running on a hosted (shared) website. I then would like these servers to talk to each other. At some scheduled time I need to publish data to the web, and I then need to subscribe to data input on the web by various clients. My internal LAN can see the Internet via our cable modem.
What is the best way to do this? What software will I need to run. I'm looking for the big picture.
Thanks for the help everyone.
View 4 Replies
View Related
Nov 27, 2005
I want to replicate to an archive database. This means that the subscriber will have data that has been removed from the publisher. In my reading, I haven't seen any discussion of this specific scenario.
Here's what I imagine the solution might be:
EXEC sp_addpublication_snapshot
@publication = N'My_Publication',
@frequency_type = 1 -- only create the snapshot once
GO
...
EXEC sp_addarticle
@publication = N'My_Publication',
@article = N'My_Table',
@source_owner = N'dbo',
@source_object = N'My_Table',
@del_cmd = 'NONE',
GO
I set the publication snapshot to only execute once, that would be during the maintenance window when it is initially installed. Then, on the tables that will contain archived data, I specify that deletes aren't replicated.
Here's my concern: aren't there times when you need to resync?
If you could push a new snapshot that dropped the tables on the subscriber and built the thing up from scratch, then things would sync-up just fine. But in this scenario if you drop the subscriber tables then you've just lost your archive.
What's the best way to handle this?
Thanks,
-=michael=-
View 6 Replies
View Related
Apr 6, 2008
I have a single table on database A that needs to be replicated to two databases B and C in as-near-to-real-time as possible. The table is cleared every morning, and receives about 40,000 records per hour. Records are only added to the table, never updated or deleted.
I'm wondering what is the best approach to replicate this table :
1 - Transactional Replication
2 - A trigger on the INSERT on table in A that then inserts the record into B and C
3 - Timed jobs running on B and C that pull new records from A
thanks all in advance.
View 1 Replies
View Related
May 7, 2007
Hi
I have EE 05 installed and need to replicated tables from an odbc connection. How would i go about doing this? I cant find a way to like import tables that i need from the odbc connection.
Thanks!
View 3 Replies
View Related
Jan 21, 2008
What do I have to do to set up replication over Citrix Metaframe? Right now, the best I can do is to export tables from one database, and then import them into the other database over the Citrix link.
Thanks in advance for any help.
View 3 Replies
View Related
Sep 21, 2005
We had a two-site system go badly wrong as a result of something. The netresult of this was that the publisher had to be completely reformatted andreinstalled. The server has, as a result of this, been given a differentname (went from being a named instance to not being one).Currently the publisher doesn't know it's a replicating system.Is there any way to join these two together so that they just startfrom where they left off, or will I have to re-do all the replicationfrom scratch by various SELECT INTOs to get the data across, thenpushing out fresh subscriptions?Thanks,Jim--Find me at http://www.ursaMinorBeta.co.ukJediGeeks http://www.jedigeeks.com"There's no 'I' in team, but there is a 'me' if you jumblethe letters up a bit." - Dr. House.
View 2 Replies
View Related
Apr 25, 2007
I'm not sure how to go about setting this up, please assist?
I need to replicate a survey database. With each new survey, a new table is created within the database. I only need to replicate the new survey table to a remote database. I tried using SQL Server Replication but the catalog is fixed and if a new table is created, it will not be selected as part of the tables to be replicated.
My setup for both locations are:
Windows 2003
SQL Server 2005
View 6 Replies
View Related
Aug 20, 2007
I can't seem to find the answer to this in the documentation.
I have several tables that I want to replicate via transactional replication. These tables do have some foreign keys between them.
What are the advantages/disadvantages of "one table per publication" versus "one publication with all of these tables listed as articles"?
If I do not define these in the same publication, will I get into trouble with the order that data is loaded to satisfy the foreign key constraints?
View 1 Replies
View Related
Apr 13, 2007
Hi, need some help.
Basically I need to replicate 2 tables from a Visual Fox Pro application to SQL server 2005
From an Intranet to a hosted Center. (Different domains)
Is this possible?
Any suggestions?
Thanks in advance
View 4 Replies
View Related
Mar 20, 2007
I need to find a solution for having the same database objects on two servers without the data, and be able to synchronize them on demand.
Whenever a table changes (alter, create, delete) I need to be able to replicate those changes to the destination server without affecting its data.
If I add, alter or delete a stored procedure, or other objects such as functions, etc. I need to be able to see those changes on the destination.
any ideas.
View 5 Replies
View Related
Dec 14, 2006
Replicating partial database
Hi there, actually I have a set of tables from my database model on SQL SERVER 2005 that I want to replicate (using merge replication) to a different server with SQL SERVER 2005 EXPRESS, Everything seems to be ok until I try so replicate just a portion of my model. I€™ve got some parent tables to replicate but I€™m not going to replicate some of their children, when I try to move them (just the parents and SOME children) to the other server using subscription, I have to erase some foreign keys (the children€™s foreign keys I wasn€™t supposed to migrate) in the target server to get my database migrated.
What Can I do to solve this problem?
View 1 Replies
View Related
