My Structure Contains Mutli Databases In One Server, Will That Affect Mirroring ?
Mar 19, 2008
With the Synonym, I was encouraged to separate my db to several smaller dbs, like base,dynamic,static and security. Now I am trying to use mirroring, I see it may cause problem, I think I need mirror all them to another server. My question is when the server is down, will all db switch to mirror server in the same time? And one can manually set which db is the principal db, but in my case, it will not work if principal server of all four dbs are not the same.
In our production we have a database by name MyDb.The application team wanted another database by the name MyDbOld which contains the data of Mydb 1 month old. So I created a database MyDbOld in other instance(test) and restored MyDbOld from the one month backup of MyDb in production server. In test instance I backed MyDbOld as MyDbOld.bak...
In the production instance I created a new DB by the name MyDbOld and restored the MyDbOld in the production from the backup of MyDbold.bak in the test instance. Now I have 2 databases in the production instance ie MyDb and 2)MyDbOld. However I find the logical names of both the databases to be the same although the database name and the physical file name to be different.My questions are-:
1) Does it in any manner affect the integrity of the 2 databases if the 2 databases are in the same instance and have the same logical name? 2) Would the dml or data retrieval operations in the 2 databases have any conflict in any manner what so ever? 3) Having a unique database name, and unique file names for the physical files for any database in an instance what is the purpose and significance of additionally having a logical name for a database? 4) Which I could restore a database from the backup of another database in the same instance and at the same time change the logical and physical name of the files to correspond to the new database.
I am trying to enable database mirroring for 100 database. It goes error free till 59 databases (some times 60 databases) with the status (principal, synchronized) on principal. on the 60th or 61st database it gave the status (principal, disconnected). Also mirror starts acting abnormal. connection to mirror starts to give connection timeout and it is not enabling database mirroring on any more databases. I have SQL SERVER 2005 Enterprise with SP1 on the servers. witness is not included yet.
this are my test servers... i have more than 500 databases on my production servers.
principal and mirror both are using port 5022 for ENDPOINT communication.
I am trying to enable database mirroring for 100 database. It goes error free till 59 databases (some times 60 databases) with the status (principal, synchronized) on principal. on the 60th or 61st database it gave the status (principal, disconnected). Also mirror starts acting abnormal. connection to mirror starts to give connection timeout and it is not enabling database mirroring on any more databases. I have SQL SERVER 2005 Enterprise with SP1 on the servers. witness is not included yet.
these are my test servers... i have more than 500 databases on my production servers.
principal and mirror both are using port 5022 for ENDPOINT communication.
All of the databases are critical and all must be included in the Database Mirroring. so, after that I tried to implement database mirroring again...... System has 3 GB of RAM, SQL SERVER (Mirror) using 85 MB of RAM but still giving this error while trying to enable database mirroring for 37th Database.....
"There is insufficient system Memory to run this query"
I have a 2 node cluster having 4 cores each wherein having 3 instances of SQL 2008 R2 enterprise comprising of 60 databases, 20 on each instance. I need to setup mirroring for each of the databases to a secondary server having 4 cores and 3 instances. What i understand is that in this case the mirror server will be providing max of 512 worker threads and the 60 mirror databases would consume 240 threads.what all needs to be checked for looking into the feasabilty of going ahead with a async mirror setup as mentioned above.
I'm a VB.net programmer working in an environment that doesn't have a true sql db administrator. We have a situation where we're thinking about adding a new database (db2) with the same Tables used in an existing database (db1). We were hoping to avoid this, but circumstances are forcing us to think about it. Are there any sql tools out there to maintain a dual-db like this? What are the main disadvantages to this?
Any help here is appreciated, I work in a large software company that has many small teams. I am faced with the issue of some of the other teams are changing the structure of tables and views and even SPs and functions without letting the rest know of these changes.
My question is, is there a way of tracking these changes through a job to alert everyone else in case this situation happens.
Hi all I have a raid 5 NT server 4.0 with SQL 6.5 running. I perform backups on tranlogs every hour and db`s every night. I also have another 16G Nt server not raid 5. Can someone tell me if it is possible to mirror a device or a database into the regular 16G NT server from raid 5 server? HOW???
HOw about database mirroring??
And also whats the best solution when it comes to mirrorring in NT server?
I have been looking into mirroring a large amount of small databases approx 150 databases.
As I understand this won't be feasible because of the way mirroring threading works, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=441900&SiteID=1
As I understand it for every database being mirrored sql will ping the mirror second, causing a network bottleneck?.
Also that the amount of threads generated for each mirrored database will cause also cause a bottleneck?
At the moment our database servers are under very little pressure and as an estimate use about 10% of the resources allocated to them such as CPU utilization, memory, disk IO and network. Our server hardware is Dual Quad core Xeons with 4 - 8 gig of memory and variety of 10k SCSCI raid configurations from raid 5 or 1,0 and sql 2005 32bit.
Ive done some calculations on the log file generation rate compared to network bandwidth there is more than enough network bandwidth.
Has anybody had any luck in mirroring many small databases?
My concerns is how much traffic is caused by the pinging of the mirror for each database?,
How many threads will the mirroring cause and what is the max amount of threads sql can handle?
How much memory will be consumed by each one of these mirroring threads?
I have two databases db_A_primary and db_B_primary, both databases are on one Primary server.
db_B_primary has a View into db_A_primary.
Scenario: db_A_primary goes down and failsover to db_A_mirror on the Mirror server.
In this scenario when the View in db_B_primary is accessed will it automatically be redirected to look at the db_A_mirror database on the Mirror server?
Can someone explain what does this mean? This is from http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
<quote>However, all these techniques for coordinating client redirection with a database mirroring have an important limitation. Database mirroring occurs only at the database level, not the server level. Be careful if your application relies on querying several databases on a server, or uses multi-part object names to query across several databases. When several databases reside on one server, and they are mirrored to a standby server, it is possible that one of several databases might fail over to the standby but the others remain on the original server. In that case, you might need one connection per database that you are querying, so that you do not attempt cross-database queries on a standby server where only one database is a principal and the remaining are mirrors.</quote>
Also I read somewhere that if one mirrored DB fails, all other mirrored DB should also be transferred to the mirror.
For example: Let's say Srv1 (principle) and Srv2 (mirror) and Srv3(witness) are SQL servers with only default instances. The SQL instance has 4 DBs (DB1, DB2, DB3, DB4) and all of them are mirrored to Srv2.
An ASP.NET application has four seperate connection strings like: objConn1 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB1;Integrated Security=True; objConn1.connect();
objConn2 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB2;Integrated Security=True; objConn2.connect();
objConn3 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB3;Integrated Security=True; objConn3.connect();
objConn4 --> Data Source=Srv1;Failover Partner=Srv2;Initial Catalog=DB4;Integrated Security=True; objConn4.connect();
If DB2 failsover to Srv2 (mirror), why should all other DBs be failedover?
I have to mirror 3 DBs on my sql server instance . I have restored there full backups and log backups on mirror.I have also created endpoints on principal and mirror using default ports 5022 and 5023.
know if i can use the same endpoint for the databases on principal instance ?
I have a 2 node cluster having 4 cores each wherein having 3 instances of SQL 2008 R2 enterprise comprising of 60 databases, 20 on each instance. I need to setup mirroring for each of the databases to a secondary server having 4 cores and 3 instances.
What i understand is that in this case the mirror server will be providing max of 512 worker threads and the 60 mirror databases would consume 240 threads.
What all needs to be checked for looking into the feasibility of going ahead with a async mirror setup as mentioned above.
Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh
Hi everyone, not sure if a this topic has been covered yet (a have been looking all day), but as I am still very new to this, my problem is as follows: In the Try .. Catch block below, data is posted from a form and the SqlCommand.ExecuteScalar() statement returns a Unique Job ID. I am attempting to populate a subordinate table for qualifications which are selected from a ListBox, but rather than using qualification titles, I am using the values. My problem is that only one value (the first) gets posted multiple times, when multiple values are selected. Looking at the For Each loop in the inner Try Catch block, I am wondering whether there is some sort of Index pointer that needs to be incremented, in order to establish new values further down the list. I have seen no evidence that this is the case, save for the fact that the value stalls on just the first. Any help would be appreciated. ===== CODE === Try C4LConnection.Open() JobPostingID = SqlJobPost.ExecuteScalar()Response.Write("<br />Selected Item: " & Qualifications.SelectedItem.Value) Try ' Multiple Qualification EntriesSqlQualPost.Parameters.Add(New SqlParameter("@JobPostingID", SqlDbType.Int)) SqlQualPost.Parameters("@JobPostingID").Value = Int32.Parse(JobPostingID)SqlQualPost.Parameters.Add(New SqlParameter("@QualificationID", SqlDbType.Int)) SqlQualPost.Parameters("@QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value) If Qualifications.SelectedIndex > -1 ThenFor Each Item In Qualifications.Items If Item.Selected ThenResponse.Write("<br />SelectedItem Value: " & Qualifications.SelectedItem.Text) QualPostingID = SqlQualPost.ExecuteScalar()SqlQualPost.Parameters("@QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value) Response.Write("<br />Selected Item: " & Qualifications.SelectedItem.Value) End If Next End IfCatch Exp As SqlException failJobPost = True lblError.Visible = TruelblError.Text = "Could not add qualifications <br />" & Exp.Message End Try failJobPost = FalseCatch Exp As SqlException failJobPost = True lblError.Visible = TruelblError.Text = "Error: could not post job to database <br />" & Exp.Message Finally C4LConnection.Close() End Try
hi everyone, is that possible to access a table if the table is doing an insert command? im trying to insert data into a data while another function calling select statement on that same table. will the table be lock when its doing an insertion? or its free? sorry about the question structure. cheers, delete469
Can any one tell me the difference between single value parameter and multi value parameter ? Also please explaing with some examples( not only with technical words)..
I had a report (.rdl) using sql reporting services in sql server 2005, where it was running quite good. I have just installed sql server 2005 sp2. After that, when I run the report through report viewer, the result of the report contains the first record of the dataset keep on repeating for all other records. If I run the dataset the results are correct,but if I preview it, then the first record alone repeating for all other records.
I feel that sp2 might cause this rendering problem. Any suggestion please?
Has anyone managed to crack getting multi-select parameters to work from an SSRS 2005 report which is querying an SSAS 2000 cube.
SSRS 2005 does support Multiselect, however SSRS 2000 did not. Given that i am querying an SSAS 2000 cube, i get the impression that i am also limited to SSRS 2000 functionality regarding to multi-select parameters.
Is there anyone out there that ever managed to get a wor around, say by using filters on a table or anything really that coud simulate the same behaviour....
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Hi, We have SAN for our SQL server and all of DB backup copy pointing to one the SAN volume(ex. T). We are moving the bkp copy from this SAN volume into remoteserver for restoring the backup. We are getting a lot of Time out during this time. Is that copy process affect production time out?. Thanks,
every morning I have 7-10 identical messages in error log
1. Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.. 2. Error: 15457, Severity: 0, State: 1 3. Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
It is standby server with custom log shipping and DTS transfering logins every 15 min.
We think we're having performance problems, and among the areas of investigations is the tempdb database. Since it resets itself after SQL is restarted, is there a way to find out how big it has grown in the past ? Does leaving it at the default size cause a performace hit ?? Right now it's 8.75 MB, with 7.38 MB available, which sounds pretty harmless.
Everywhere I read, it states that running SQL Profiler can affect performance of your SQL Server. My question is - how much of an impact will it really make? Will I see a 1% degredation in peformance? 5%? 50%? I haven't been able to find a good answer. We currently have SQL Profiler running all day long for almost 3 years, and the databases are still humming.
Is it the amount of data you are requesting from the trace that affects performance? There are some compliance tools out there (Idera Compliance Manager, IPLocks, etc) that run a profiler trace to get data. There are other DBAs in my organization who don't want to use them because "profiler traces will degrade my SQL Server performance". How true is this really.
Any help I can get would be extremely appreciated.
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....
I need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.
any issue in setting up mirroring in this case as vlans are different.
Does multiplication with 1 affect query performance?I have a a stored procedure that converts results to another unit if required. In alternative 1 below, the results are returned with a separate select statement if no conversion is necessary - in other words, no multiplication with a conversion factor is required. However, the code is not very nice since I need to repeat the select statement again in case a conversion is required, this time including the conversion factor.Alternative 2 uses cleaner-looking code. The conversion factor is set to 1 if no conversion is required, and a single SELECT statement is used to return the data. The @factor variable is defined as a float.I would rather use alternative 2, but I wonder if there is any performance penalty for doing that if no conversion is required since the results are always multiplied with the @factor? Or can SQL server somehow understand that @factor = 1 and no multiplication is required?--- Alternative 1: ---IF @fromunit_sid = @tounit_sid-- Return unconverted results SELECT ISNULL(ls_totalWaterConsumption,0) AS ls_totalWaterConsumption,ls_theoreticalWaterConsumption AS ls_theoretical_WaterConsumption,ls_totalWaterConsumption - ls_theoreticalWaterConsumption AS ls_extra_WaterConsumption FROM Results WHERE scenario_id = @scenario_idELSEBEGIN -- Get conversion factor EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT -- Get the converted results SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_idEND --- Alternative 2: ---IF @fromunit_sid = @tounit_sidSET @factor = 1ELSE -- Get conversion factor EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_id And another question: is using an IF function considerably faster than making a call to another stored procedure?In alternative 2 above I use an IF statement to check if @fromunit_sid = @tounit_sid, and . But in fact the function getConversionFactor that I'm calling does exactly the same thing: if I pass in identical from- and to-values, it simply returns 1, so I could omit the IF statement completely and just use alternative 3. But is it slower?--- Alternative 3 -- Get conversion factor EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
Hi, I am wrote the following code in one store procedure called p_bcp_all. and then scheduled it to run over night. what if the first two bcp were successful but the third one failed. Is the whole procedure going to fail? also what if the first one failed, is the rest of the code going to be executed have the bcp process going for the second and the third table? thanks for your adivce
regards Ali
create procedure p_bcp_all as Exec master..xp_cmdshell "bcp servername..tblone in d:blone.txt /fd:formatfileblone.fmt /servername /Usa /password/b250000 /a8000"
I have a db which I have little control over most of it's makeup because of the vendor supplied tools. We currently have over 700 tables and 19000 columns. Has anyone seen a problem or saturation pont with these kinds of numbers? The database delivered to the clients will be from 2-50 gig depending on the site. I can probably through hardware at problems, but if anyone has been down this road any suggestions are appreciated.
