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 work for a mid size company with the usual collection of enterprise applications. We are trying to consolidate our SQL environment consisting of around 100 databases none of which are over 100GB with the average database size around 5GB. Recently a large server was purchased as the "Big Daddy" SQL box. Obviously not all the databases will be moved to this one box but within reason a number can.
My question: What is the best way to configure the box?
1 OS & 1 Default Instance - How do we isolate loops memory leaks etc... 1 OS & Multiple instances, ie:Dedicated instance for each application. - How do you allocate CPU & memory, allow for optimum performance without putting others at risk from a rouge process? Virtualized OS for each application with a default instance - Too much overhead impacts performance?
Future plans include offsite replication, perhaps mirroring with failover.
I'd appreciate anyone else's thoughts ideas? How have you tackled this problem in your environment?
SQL Server 2012: Out of all the databases in the instance we have a requirement in which we need to maintain a high availability for the databases around (128). Our team believed log shipping will apt for this requirement but not sure on it's limitations, how many databases are allowed or supports this log shipping from a single instance.
Each webmethod will be accessed thru a multiview on the page.
It would seem to me that I can describe the webmethods using a fully qualified stored proc name and that wouldn't cause a big problem. But, my question is what does that DATABASE=N'fadb' clause do?
Does anybody see a problem with using a single endpoint to access multiple databases?
Any advice would be greatly appreciated as this is my first web app using this data access method.
I just need to add multiple catalogs to the same linked server, in Sql Server 2000 or Sql Server 2005. Here i need only to add 3 databases in to a single linked server name.
Hi, I'm planning to develop a project on .NET using Sql server 2005. Just stuck at a point where I need to decide that Should I use 2 or 3 huge databases with a lot of tables and data into it or should i create a lot of databases which would contain small chunks of data. Please let me know which option would give me a better performance. Thanks in advance
God is great. We are not God. But we still can be great.
I have a backup that contains multiple databases. I am restoring it to a different server. How do I restore each database? Tried to restore under SQL Server Management Studio, but can only restore one of many from the backup (it appears it has only 1 logical file name in the backup) If you can refer me to a web page that would be great. I tried googling the topic but nothing seems to come up.
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
This is the scenario. Is it possible to create views or something (like Oracle DB Links) in a database in Instance 1 which can show data from another database in 2nd Instance ?
Hi, i am working with SMO object, At the time of accessing remote(LAN) database server I encountered the following message: "MirroringPartnerInstance" : Undefined error.
I m not able to figure out whats the exact problem is..
i had gone through the following link too but doesn't make any sense to me : msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.mirroringpartnerinstance.aspx
All i understand is i need to set some property of mirroring partner.
Does any on have any idea how to resolve this problem
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've got a question about failover with the database mirroring.
I have 3 servers, 2 with SQL 2005 and 1 with SQL Express as witness.
After getting some problems to install database mirroring with Windows authentification, i had to create an authentification by certificate.
All goes well but my problem is about my primary instance SQL alias.
The alias on the primary doesn't follow on the secondary when the primary is down.
I've searched on forums and in the internet SQL web sites, but i didn't find anything.
I create another alias on the secondary but i don't know how to modify it in T-SQL...
I would like to know if there's any solution to modify an SQL instance alias by transact-SQL? or if anyone has already install on its servers, a fail over automatic with one instance alias with data mirroring in T-SQL?
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?
I've read here at [URL] that with 32 bit it's recommended to only have 10 database mirrors per instance. However, is there a limit for 64 bit SQL Server 2012?
We're having a problem where we have about 300+ databases and now any new db's we add are timing out when it comes to setting up mirroring and am wondering if this is limited by the instance.
Alter failed for Database 'Northwind'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server network address "TCP://kookaburra.sydney.ssw.com.au:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)
I could connect to both instances:
C:Documents and SettingsSergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k ookaburra,5022 1> exit C:Documents and SettingsSergeiTchernykh.SSW2000>sqlcmd -U sa -P <password> -S k ookaburrasydney2005,5023 1> exit
I could ping my laptop
C:Documents and SettingsSergeiTchernykh.SSW2000>ping -a kookaburra
Pinging kookaburra.sydney.ssw.com.au [10.0.0.1] with 32 bytes of data:
Reply from 10.0.0.1: bytes=32 time<1ms TTL=128 Reply from 10.0.0.1: bytes=32 time<1ms TTL=128 Reply from 10.0.0.1: bytes=32 time<1ms TTL=128 Reply from 10.0.0.1: bytes=32 time<1ms TTL=128
Ping statistics for 10.0.0.1: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
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.
Communications to the remote server instance 'TCP://<mirror_server_neme>:5022' failed before database mirroring was fully started. The ALTER DATABASE command failed. Retry the command when the remote database is started.
This problem is only in production database any testing database include adventureworks mirroring sucessfuly. Why is problem:?
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 feasibility of going ahead with a async mirror setup as mentioned above.
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
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.
