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.
when i do a snapshot i have it set up to truncate before inserting. As a result I'm getting an error saying that it cant truncate a table reference in an indexed view. What settings should i use to allow for a snapshot in this instance? Should i manually drop the databinding then snap then recreate the databinding? there has to be a better way
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.
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.
Hi, Please let me know if it is possible to replicate a table with identity property defined in it. Both the publisher and subscriber tables have identity property defined. Which option should be used while setting up transactional replication to allow the identity values at the publisher pushed to the subscriber, which also has identity property defined? Not for replication option with the identity property also fails. Whichever option I choose I get the error, 'An explicit value for the identity column in table 'jobs_id_nfr' can only be specified when a column list is used and IDENTITY_INSERT is ON.' This works only if the identity property is not defined at the subscriber. But, I need to have the identity property defined at the subscriber also because the subscriber should be an exact copy of production.
I am replicating an 80GB database between NY can CT and would like toknow why table sizes are different between the two.Here is an example of sp_spaceused::NY IOI_2007_04_23 rows(279,664) reserved(464,832)data(439,960) index_size(24,624)CT IOI_2007_04_23 rows(279,666) reserved(542,232)data(493,232) index_size(48,784)Thanks,
When replicating a table which has an identity column I get the error: "Procedure cp_insert_tblname expects parameter @C1, which was not supplied.". The stored procedure appears to be called without any parameters so my insert stored procedure does not work. I know I'm missing something basic here!! Do I have to add the field names when telling replication to use a custom stored procedure. If not, how do arguments get passed to my SP, as globals somehow?
Client is running X- version of application and corresponding database size is huge. Now client's vendor is releasing Y-version of same application with many database schema changes (like new tables added, new columns added, renamed existing columns and etc) To upgrade to the Y-version, vendor is suggesting to my client that down the system and do the upgrade for application/database to Y-version. We are sure that this process will take days together to upgrade to the Y-version. My client is not ready to down the system for that long. So we are trying to find the solution with minimal down time.The approach we are thinking is,
1) Create the replicated database to another server (server2) from production server(server1) using golden gate with X-version
2) Create new tables/schema updated tables from Y-version database on same server1. Here for Updated schema tables we are planning to use the name <table_name_Y_version> as the same table name exists in X-version.
3)With above 2 steps, golden gate replicate the changes from production to server1 and server1 will have the new Y-version table schema (with different concatenate name ' _Y_version'). BTW , there is no affect for the production
4) At this stage we are planning to find best approach, to fill the '<table_name>_Y_version' from X-version tables. two challenges here a) all data needs to be moved to Y-version tables b) they have to sync data in real time.
we thought of going to
a) ssis package to pump the data to Y-version tables, but real time data will not sync.
b) trigger based technique, previous experience said, lot of load
I have a SQL 2005 Mobile db merge synching to SQL Server 2005. I've been using this for a few months now and generally it works well (except when adding not null columns with default values, but that's another story...). I just added a new table to the server, went into the publication articles and checked the new table. It told me I would need to generate a new snapshot for the table, so I then generated a new snapshot. Then I synched the mobile db and it worked with no errors, but the new table I added on the server is not on the mobile db (I checked using the Query Analyzer). I have also tried reinitializing subscriptions, but this didn't work either. Any ideas why this table would not be synched to my mobile db (and no error message on mobile device)?
It appears that every 5 or so days, my transactional replication is hanging. I see that I have a couple thousand undistributed commands and this number keeps growing. It forces me to generate a snapshot in order to sync up. When generating the snapshot, I will get frequent messages like "waiting for a response from server...". So I will keep stopping and starting syncronization. Eventually the snapshot will go out, with a delay after generation.
I am trying to replicate data from a view in the publisher to a table in the subscriber (transaction replication). I do not need the view's base table, or the view itself, replicated to the subscriber. I only want to data from the view to feed a table in the subscriber.
I have few tables, which are replicated and partitioned. They also have archival process. I want to avoid having to run that same process on the subscriber.
Replication of partition switching is easy. However I am not sure how to replicate merge range and empty filegroup/file drops.
There the following article options:
Copy file group associations Copy table partitioning schemes Copy index partitioning schemes
I am not sure if these are enough to implement the replication of merge range and empty filegroup/file drops.
I could not find and option to copy partition functions.
I am trying to replicated table A into table B withing the same server and the same database.
I am getting an error message when trying to configure the subscription: You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
I have been unable to create a replication from an SQL Server 2005 standard edition database to remote SQL Server 2005 Express Edition. The remote express edition is on a Virtual Private Server we are leasing from a hosting company. The name of the remote DB is similar to vs572.si-vs572.com. I can connect to this with SQL server authentication through Management Studio and also with SSIS. But, I have been unable to create a push subscription (I have tried a test push subscription with the same publication to a local SQL Express server here in our office and this works fine).
Here is the error message: SQL Server Replication requires the actual server name to make a connection to the server. Connections through server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'VS572SQLEXPRESS'.
The hosting company had originally installed a shared SQL server which would not support replication. They then installed SQL Express edition and I was hoping this would allow us to run a replication.
I tried to connect to VS572SQLEXPRESS with out any luck. I check the remote connections, made sure replication was installed, etc., but no luck.
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.
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?
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
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.
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 !!
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?
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.
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?
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?
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?
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 ?
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
