Hi all,
Can a publisher be mirrored? What are the implications, issues, gotchas? Transactional, Merge or Transactional w/ Updating Subscribers is what I'm considering.
Bottom line is I would like to use mirroring, but only one mirror will not suffice.
Thank you in advance.
Ray Nichols
I would like to know people's thoughts on any special network considerations to take for mirroring and the logic behind them. Is it best to segregate mirroring traffic from other network traffic? Use a VLAN? Dedicate one NIC for mirroring and the other for general network traffic or just aggregate the two and let both types of traffic share the bandwidth?
I haven't seen much in this area from Microsoft's best practices and wanted to know what those who have implemented it have done and why. There are pros and cons for each method: Letting everything share one massive pipe with load balancing vs. trying to segregate traffic in some way so that general network connections etc. do not impact the mirroring capability.
I look forward to hearing from you.
-M-
Can someone point me to a resource for Table Design Considerations for Merge Replication? I have an ASP.Net/SQL2K5 app that I need to run on disconnected machines, then allow dfor data sync through merge replication. I assume that the first step is getting my tables indexed in a replication friendlt manner?
Many Thanks to anyone who can point me in the right direction!
After reading about it, i'm still confused. Is replication and database mirroring the same? they seem to accomplish the same thing.
View 3 Replies View RelatedWe are doing Reporting for a transaction system. since we do not want to hamper the live database we are planning to do the transactional replication.
Few questions for transactional system.
1. If we replicate a database , then what ever changes happened for the source db will be transferred automatically?
   for ex: If i change a column name of a table in source system, then will it transferred automatically to the replicated db?
2. If we do any change to any of the tables in source system, do we need to recreate the replication and reload the entire data?
3. Also we are planning to enable cdc on this replicated db to enable incremental load to my warehouse. So if we disable the cdc and do a full load into the replicated db, then do we need to perform full refresh on warehouse?
4. Can we replicate on a table level? so that if we reload only the changed table and then reload then there wont be any impact on the over all flow of other tables.
Up to now we have gotten by without having any local DR copies of servers (if a sql server goes down we are usually able to get it back in less than 3 hours). But I want more now. I want to trim the "down" window to no more than 5 or 10 minutes. (Immedate failover would be nice but is not an essential requirement. The essential requirement is to loose no data!)
I have a spec of knowledge in these areas:
SQL 2005 Clustering (requires approved hardware, quorum disk, etc. involved)
SQL 2005 Replicaiton
SQL 2005 Log Shipping.
SQL 2005 Database mirroring. ( needs three servers)
Which approach do you think is the most straightforward, sparing of hardware, yet reliable way to get us back up and running after a sql server failure.
TIA,
barkingdog
Anyone know of a good "free" way to back up web files and SQL Server 2005 Express Database?
I was able to use Windows Server 2003 Backup utility to back up the folder where the Databases were stored, as well as the web files, with no errors.
But I have heard a lot of discussion that you can't just simply backup SQL Server data files?
I'm wondering how sound the backup I've created is...
Any suggestions?
Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)
For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?
Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....
View 5 Replies View RelatedWe have a sql server 2000 sp3 server on a win2k3 server standardedition. This is on dell hardware with a dell scsi dasd enclosuredirectly attached. We want to duplicate the hardware and replicate ormirror the database to the backup server and do a manual or automaticfailover in case of outage(more than likely manual). Wondering rightnow if replication - with say software like doubletake - is preferredover sql database mirroring or vice versa. Thanks
View 1 Replies View RelatedI am setting up transactional replication on a database A with read only subscriber database B (for reporting purpose). I have also setup mirror on database A. I tried to manually failover to mirror...mirroring works fine but transactional replication is interrupted.
I have followed the steps as in article
http://msdn2.microsoft.com/en-us/library/ms151799.aspx
I have also used PublisherFailoverPartner parameter...but replication still complains that it cannot connect to publishing database.
Any ideas or direction is greatly appreciated.
Hi MSSQL friend,
We have a SQL2005 server which contains some replicated databases. We use merge replication and the publications are all local.
In order to make the server failsafe we need some kind of mirroring. Is this possible with replication?
Any ideas how to accomplish a failsafe scenario with replication?
Sincerely Edward
Does anyone have an opinion on which is more efficient?
Currently have a cluster setup and I'd like to setup mirroring or transactional replication on top of that to replicate/mirror the data to an offsite building that is still on the local LAN as a DR solution to a SAN failure.
I'm using replication and want to mirror my publisher to get high availability. As I understand the documentation the principal and the mirror must both use the same distributor instance. So if the distributor fails the replication fails. This seem like a big weakness. Maybe I'm missing some thing. Is there a way to set it up so that replication continues if a distributor fails -- some mechanism that will switch in a "back-up" distributor? I'm looking for a configuration that will continue to run in the event of any single failure. This means that both the publisher and the distributor must be covered by mechanisms that keeps them going in the event of a failure. Mirroring covers the failure of the publisher but I don't see a mechanism that will conver the failure of the distributor.
My application is developed in .net / c#. Data is accessed through .net sql server data provider etc.
Hello y'all,
Can someone tell me what's the main difference between mirroring and replication?
Thanks! Kindest Regards
Looking for anyone that has had any experience with using SQL Merge replication while mirroring the publisher database. Thinking about doing this as a recovery plan in the event of a publisher failure.
Any advice would be appreciated.
Thank you!
dear all,
I have a case.
I have 1 production server and wanto to add 1 more server.
my production server is using sql 2000 stnd edition.
I want to have an indentical database from my production server.
the problem is my second server is using sql 2005 stnd edition.
I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.
any idea or suggestion will be great....
regards,
-dedys
Here is the situation we are trying to resolve. The client has 2 locations, each has local appliations running on a database. db schema on both locations are the same, data is different and won't overlap.
The requirements are:
1. at each location the application can read and write
2. near zero down time for applications on each site
3. db on one site also has the data from the other site for DR purpose
The client is running on SQL 2005 STD SP2
We looked at the approach of setting up db mirroring on each location + 2-way transactional replication between both locations. The mirroring was fine, and I was able to set up transactional replication from mirrored publisher to a non-mirrored subscriber. But, from what I experienced and from reading, there is no way to have the subscriber db to be mirrored, since Distribution Agents simply doesn't have the option to specify Failover Partnr for mirroring, so I guess it is not supported. Any comments on that?
Assuming that's correct, then the only way of using SQL out-of-box technique seems to be using Clustering on each location instead of mirroring, then the 2-way transactional replication works on clustered subscribers I think (although I haven't tested it). Peer-2-Peer replication would have been a good candidate between sites in this case, but STD version of sql 2005 ruled that out.
Any suggestions and comments are welcome.
Thanks
Hi everyboby,
Can anybody tell me the differences, advantages and disadvantages between these three solutions? When do I may use one or another?
Could you recommend me any documentation?
Thanks a lot,
Radamante71.
I am developing an enterprise class solution using SQL Server 2005 and MS .NET v2 and am tying determine if SQL Server 2005 (which edition and if so how) would be adequate for my proposed solution. Any feedback, tips, comments would be greatly appreciated.
As a background the solution I am developing will be web services based and used by multiple offices around the globe by over 500 users. I have already developed a prototype using a single SQL Server 2005 instance but as this solution is going to be used by offices around the world I want to have an IIS Server and SQL Server 2005 server instance in each office with "links" back to the primary SQL Server 2005 cluster in Australia.
One of my thoughts was to set up replication between the offices that would happen at midnight remote office local time and then set up triggers to update the primary cluster when assoociated data was changed on the remote sites or on the primary cluster. Does anyone know or can anyone suggest alternatives to this strategy?
I effectively need some sort of inter site caching functionality with store and foreward capabilities ...
Thanks
Andrew
I want to create a mirrored DB set for data entry in a extremely busy OLTP DB. I want to add transactional replication between the production server and a staging server outside my quorum that I will use to index the data and prepare it for reporting and warehousing purposes.
If/when fail-over takes place, what happens to my transactional replication between the former production sever (now presumably offline) and my staging DB? Does it switch to the new production server automatically or do I have to manually set the replication between the new production server and the staging DB?
Thanks in advance.
You will all have to excuse my ignorance. I'm a developer who also doubles up as a development DBA. I am however not particularly knowedgeable about all the really important DBA stuff.
We've built a small BI solution using SQL Server 2000. Our problem is that our server is getting on in years (5) and doesn't really have enough disk space or grunt. We havce a number of summary cubes that we've optimised quite successfully but our billing line level cubes run to 60 million rows and, well, they're about as quick as a dead ferret. Especially given the stupid queries our data analysts keep running.
We have however proved our point. That this can be done and indeed SQL Server can do it. So we're now looking at some infrastructure spend and some new copies of SQL2005.
But i need some advice. Our user base is climbing through the roof, we originally had 10, now we have closer to 50 and at this rate it'll be a couple of hundred by the end of the year. We're using a plugin called XLCubed to deliver that data into Excel from the Analysis Server.
The OLTP database that sits behind it is fairly robust but we have a number of web based apps (mostly lookup systems) that want to use the nice shiny new accurate tables of data we have created.
So I'm looking at a fairly big server to hold the OLTP DB, this will also serve up live data to our web apps. Its worth pointing out that the source data system is a batch system that processes overnight so we load data from yesterday at 6pm each evening and process our cubes and stuff overnight. Thus the data is a couple of days out of date. Don't laugh they used to use MS Access and got one mangy data set a month so this is a massive leap forward.
I wanted to mirror the DB to another machine but I also want to have a separate Cube Server. I wondered if the cube server could use the mirror to read its data from as opposed to loading the Main Server (the mirror would be an identical box) we would also have a separate box running some of our other systems acting as the witness.
I also wonderd about exporting the Cubes onto file shares for use locally as opposed to via the server which is how they connect now.
We have been using Reporting Services and some of the queries the devs write are not exactly efficient. So I was also planning on clustering a pair of smaller servers into a reporting farm. Could I use another SQL Server to serve data up to them? Could I use a DB snapshot to copy the data required to this server? What are the time / size implications of using a snapshot and replicating it over each night?
Any suggestions for places to read up on this? I've looked at the MS marketing blurb and while its big on buzzwords its light on specifics. Like how it actually works and how you would actually configure it to do some of this and what the implications would be.
Any advice?
many thanks
Steve
I have a table with about 200 million rows of data. I add a couple million rows of data each week to the table in a single load process. The table is used for reporting purposes only and there are never (not intentionally at least) any updates or deletes to the table. The data is always being added to the "end" of the table with the new AsOfDate being the main factor in the clustered index.
My question is this: Since I'm not "inserting" rows that would split pages, should I have my FILLFACTOR for the table set to 100, or am I missing something? I obviously want to save physical hard drive space, but I also don't want to slow down the import process.
BTW, I'm using SQL2000
We have about 150 SQL servers and basically we're considering the pros and cons of installing SSIS on a central SSIS server - that is responsible for all DTS jobs - as opposed to installing SSIS on the local SQL instance.
On the plus side so far:
1./ Central administration, alerting, change management etc
2./ Possible performance gain on the local instance not having SSIS installed?
On the negative side:
1./ Central point of failure
2./ Possibility that it would need to be a clustered...
3./ Compatibility issues may mean having to make the central SSIS server 32-bit?
4./ Possible performance cost of remote SSIS?
5./ With multiple DTS packages running at different times, when would we take the server down for maintenace...?
Would appreciate your thoughts.
Hello, I'm developing application which monitors network packets. The monitoring data are saved into table. Monitoring table maintains the data for fixed quantum time,for example during one 1 hour. So, every minute before or after insert new data, I delete the time-expired data. I doubt that the endless delete operation would results in some problems(increasing index,etc..).
Is this mechanism safe to the dbms?
Aren't there round-robin(?) style table?
I would like to know how to, if at all possible, to reconstruct the following trigger as to be able to handle multiple row insert when a single insert command is used - because the trigger will only be called once...I'm not familiar and don't know anything about cursors and i've read that its not the best way to go.
TRIGGER ON childtable INSTEAD OF INSERT
AS
BEGIN
DECLARE @customkey char(16);
DECLARE @nextchild int;
DECLARE @parent int;
DECLARE @date datetime;
SET @date = getdate();
SELECT @parent = parenttable FROM inserted;
SELECT @nextchild=count(*)+1 FROM childtable WHERE parenttable = @parent;
IF (@nextchild >= 9998) return;
SET @customkey = €˜type€™+ convert(char(4),year(@date)) + convert(char(2),month(@date)) + convert(char(2),day(@date))+convert(char(4),@nextchild + 1);
INSERT INTO childtable (customkey,parent) VALUES (@customkey,@parent);
END
I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
View 2 Replies View RelatedI may be overthinking this, but I want to make sure this is right. If you have a processor license of SQL Server Standard running both Reporting Service databases and the IIS interface, isn't it true that the underlying licenses of other servers containing your data are irrelivent in the context of serving the reports over the web? Example. Server 1 has SSRS as described above, processor license of Standard. Server 2 has user license of SQL Enterprise and serves data to a couple of reports on Server 1. This does not violate a license, correct? Doesn't Server 1 just take one of the CALs from Server 2?
View 1 Replies View Relatedin a prior "legacy" life we couldn't imagine 24x7 implementations because it was important to 1) reorganize databases periodically to remove fragmentation that adversely affected performance and 2) back up databases just in case.
In a 24x7 SQL Server 2005 implementation, high level only, how are these and other maintenance related things accomplished with confidence?
I dont think SQL cleanses its own page splits unsolicited. Are DBAs totally reliant on logs in full recovery installations where db must be up 24x7? What if the devices those logs sit on fail? What if the logs become too large? Is it likely that if you want 24x7 you're looking at Enterrise Edition only?
I'm totally aware of and confident in the sliding window partitioning thing but it seems to me there must be more out there in terms of periodic, more frequent maintenance activity.
Can someone point me to some good articles or perhaps directly supply some words of wisdom with regard to wise utilization of variables within a T-SQL script from and standpoint of conserving memory usage and improved execution cost?
For example:
(1) Is it better to use varchars, nvarchars, etc. defined with minimal lengths to support the needs of the script or is it just as efficient to declare all with a length of say 4,000?
(2) I've seen behavior that leads me to believe that when passing a variable as a parameter in a nested procedure call, if the declared types of the parameter and the variable being passed in don't match (i.e. one is numeric(38,10) and the other is int), then implicit type conversions hurt performance. Is this true and how broadly does it apply?
(3) Does the number of variables declared in a script materially impact the performance and / or resource utlization?
(4) Is it more efficient to have a series of variable value assignments in a single SELECT statement versus a series of SET statements? Should I always perfer one to the other? Only within a looping construct?
Thanks,
Shadowraven
When I issue this command:
ALTER DATABASE foo set PARTNER = 'TCP://10.3.3.1:1234'
I get this error message:
The database is being closed before database mirroring is fully initialized. The ALTER DATABASE command failed.
What does that mean, and how do I fix it?
Hi,
View 6 Replies View RelatedI set up a new mirror server. Everything is good except that the Database Mirroring Monitor is not working for one of the databases. In the monitor the principal data is showing up as blank
If I look at dbm_monitor_data on the principal most of the data columns (e.g. Send_queue_size) are null where as they have data for the other databases.
Both servers are SQL Server 9.0.2047 Enterprise Edition.
Any idea what might be going on here?
Thanks in advance.