SQL SERVER 2005 DATABASE MIRRORING For Large Number Of Databases

Jun 1, 2006

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"

WHY?

View 19 Replies


ADVERTISEMENT

SQL SERVER 2005 Database Mirroring For Large Number Of Databases

May 30, 2006

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.

View 1 Replies View Related

SQL Server Admin 2014 :: Database Mirroring For Large Number Of Databases

Oct 27, 2015

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.

View 0 Replies View Related

Large Number Of Databases On 2005

Feb 23, 2007

For anyone with a larger number of databases (500+): How many do you have in a single instance. If you are using multiple instance on a single server, how many dbs per instance. This is why I'm asking

We are experiencing 701 "out of system memory" and temporary (usually) system freezes when the error occurs. We have 32bit 2005 version 9.00.2153.00, 32GB of memory, AWE enabled, quad dual-core 3GHz hyperthreaded server. Nether the bPool or VAS show any pressure when the "out of system memory error" occurs. Since this error usually indicates a VAS problem we tried increasing VAS to 1GB w/the -g flag. It made no difference. PSS has been working on the case for 3 weeks. They dont seem to be finding any evidince of memory pressure either. When I last spole to the escalation engineer yesterday it seemed that they are going to recommend reducing the number of databases on the server. I asked for clarification as to whether we are hitting a 32 bit barrior, an instance limitation, or both. I am awaiting the answer. How many databases do you have on your server? We had between 1700 and 1900 (the number varies) at times when the error occured. We are now at 1500, and have not had the error in the 2 days since reducing the number of databases...

View 4 Replies View Related

SQL Server 2008 :: Deleting Large Number Of Rows With Foreign Key And Mirroring Setup

Feb 19, 2015

We have a database. It is enabled for mirroring. We need to delete the old records. That is around 500k records from a table. But it has foreign key relation. How to do in Production servers these kind of deletes?

View 2 Replies View Related

Mirroring Large Amounts Of Databases

Mar 6, 2008

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?

View 1 Replies View Related

Mirroring :: Async Mirror Setup For High Number Of Databases

Oct 27, 2015

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.

View 4 Replies View Related

SQL Server 2005 Slows Down After A Large Number Of Queries

Jul 4, 2007

Hi,

We are running SQL Server 2005 Ent Edition with SP2 on a Windows 2003 Ent. Server SP2 with Intel E6600 Dual core CPU and 4GB of RAM. We have an C# application which perform a large number of calculation that run in a loop. The application first load transactions that needs to be updated and then goes to each one of the rows, query another table get some values and update the transaction.

I have set a limit of 2GB of RAM for SQL server and when I run the application, it performs 5 records update (the process described above) per second. After roughly 10,000 records, the application slows down to about 1 record per second. I have tried to examine the activity monitor however I can't find anything that might indicate what's causing this.

I have read that there are some known issues with Hyper-Threaded CPUs however since my CPU is Dual-core, I do not know if the issue applies to those CPUs too and I have no one to disable one core in the bios.

The only thing that I have noticed is that if I change the Max Degree of Parallelism when the server slows down (I.e. From 0 to 1 and then back to 0), the server speeds up for another 10,000 records update and then slows down. Does anyone has an idea of what's causing it? What does the property change do that make the server speed up again?

If there is no solution for this problem, does anyone know if there is a stored procedure or anything else than can be used programmatically to speed up the server when it slows down? (This is not the optimal solution however I will use it as a workaround)

Any advice will be greatly appreciated.

Thanks,
Joe

View 3 Replies View Related

SQL Server 2005 Full Text Performance With Large Number Of Records

Dec 12, 2007

Hi
We are using the SQL Server 2005 Full Text Service. The data is not huge, but the kind of data is that each record is small and there are a large number of records. There are 35 million records now with 11 GB of data and about 1.6 GB of FT catalog on the table. This is expected to grow to at least 10 times the size of this data. The issue is with FTS taking a long time to return results when the number of hits (rows) getting returned from FTS is large for some searches, it takes a very long time. With the same data & catalog, those full text queries for less common words return timely. The nature of the problem doesnt allow us to only have top results. We need all the results. So it’s not about the size of data but the number of results getting returned from FT. (As the catalog is inverted). The machine is dual processor with 4 GB RAM.
 
I am considering splitting the table and hence the catalog and using multiple servers to do full text searches in smaller catalogs. Is there any other way this issue can be solved ?
 
If splitting is the only way, can you give me an idea as to what is a statistical/standard limit to the number of search results/cataog size as which FTS gives good results
 
Thanks in advance

View 1 Replies View Related

Number Of Databases In Sql Server 2005

Sep 20, 2007

Hello,If I have a database of 5 GB with only 20 tables in it, how many ofthose can we put in how many database servers on a high end machine(Dual Xeon 8 GB on windows 2003)?It is to maka a quotation and an estimation of the cost.TIAMario

View 1 Replies View Related

SQL Server 2005 Database Snapshots With Mirroring

Mar 8, 2007

Hello, My question is simply: according to BOL, a DB snapshot taken from a Mirror is read-only.  Say I wanted a temporary (disposable) read-write DB created from the snapshot.  Is there a way to generate a writable temp DB (that's not tempdb) from the snapshot? Thanks,AK 

View 1 Replies View Related

Database Mirroring Support Also By SQL Server 2005 Without SP1

Sep 27, 2006

Hi All,

I get a general question about the feacture "Database Mirroring". Is it support by SQL Server Standard. Edition without SP1?? Of i still need to upgrade the server from SQL Server 2005 to SQL Server 2005 with SP1?

Another question: are there any problem if the one machine(server) has installed SQL server 2005 Standard edition and the other with "Developer Editon".

I hope some one can answer my question. Thanks anyway!

Regards,

Pat

View 5 Replies View Related

Mirroring :: Limit To Number Of Database Mirrors Per Instance In 2012?

May 13, 2015

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.

View 3 Replies View Related

SQL Server 2005 Evalution Copy Database Mirroring In NT 4 Domian

Jul 17, 2007

Dear All,



I have a download "SQL Server 2005 Evalution Copy". I installed it to 3 machine, 1 - XP Professional (Witness) and 2 - Windows Server 2000 (Principle and Mirror) . And I want to try Database mirroring.



However, after restore the Log into mirror server I try to execute the following statement in Principle Server:

ALTER DATABASE TestDB
SET PARTNER=N'TCP://[10.0.200.2]:5022'
GO

The above 10.0.200.2 is the mirror server IP address.

it show error message as:

Msg 1416, Level 16, State 31, Line 1
Database "SCMPGroup" is not configured for database mirroring.



In Miorror Server

I execute the statement :

ALTER DATABASE TestDB
SET PARTNER=N'TCP://[10.0.200.1]:5022'
The above 10.0.200.1 is the principle server IP address.

It shows sucess



ALTER DATABASE TestDB
SET WITNESS=N'TCP://[10.0.15.146]:5022'

it show error message as:

Msg 1416, Level 16, State 1, Line 1
Database "SCMPGroup" is not configured for database mirroring.



Please help me! I down't know if NT4 domain or evalution copy problem in there.

View 3 Replies View Related

Article: Database Mirroring Not Ready For Production When SQL Server 2005 Ships

Sep 16, 2005

Can someone at Microsoft comment on this article, specifically, how it relates to SQL Replication?  Will SQL Replication fall into the category of what this article describes, or only the mirroring feature?

View 1 Replies View Related

Database Mirroring | Can Witness Live On Mirroring Server?

May 3, 2008



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?

View 7 Replies View Related

Mirroring The Databases Without Witness Server?

Jul 10, 2013

I need to start mirroring the databases without witness server.

View 3 Replies View Related

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.

Any thought?
thanks

View 3 Replies View Related

Maximum Number Of Databases Used For Each Query In 2005?

Mar 22, 2006

In MS Sql Server 2000, if you run a stored procedure that query's morethan 8 databases, you get this error gets raised:Maximum number of databases used for each query has been exceeded. Themaximum allowed is 8.In 2005, does this maximum number still exist? if so, is it still 8, orhas the number been increased?Thanks,Mike

View 2 Replies View Related

Mirroring :: Any Limit For Configuring Number Of Secondary DB Server

May 7, 2015

In log shipping is there any limit for configuring no.of secondary db server???

View 3 Replies View Related

SQL Server 2008 :: Update Statistics With Large Databases

Feb 5, 2015

Currently our database size is around 350G. It will grow up to 1.5 TB

We have the

Auto create statistics option :True,
auto update statistics option :True,
auto update statistics asynchronously option : False

at database level

we have a weekly job, update statistics running very long time. It is created through maintenance plan using the option full scan.

Previously they tested with sampling but instead of full scan running with the sampling effected the queries.

Is there option to avoid the long time job duration.

If we didn't run the statistics manually what will happen? How do you maintain statistics with large databases

View 9 Replies View Related

SQL Server Express Login Issue With Large Databases

Feb 6, 2007

Hello,

My company works with SQL Server 2005 express locally with Visual Studio to develop websites. Everything works very well.

We use SQL Server 2005 express on our production server as well. We change the database over to a non-User Instance when the site is ready to go live. Everything works fine here as well.

We run into issues when databases get near 100 MB. This is well below the stated database size limit for Express of 4GB.

At that point, about once a day, a site with a "large" database will stop responding. The error that we'll get is "Cannot open database 'DBNAME' requested by the login. The login failed. Login failed for user 'DBUSER'

The only way we've found to fix this is to restart the SQL Express service. Obviously, that isn't a very useful alternative.

Has anyone run into anything like this? Could we have some setting wrong?

Would moving to the full version of SQL Server 2005 fix this?

View 3 Replies View Related

What Is The 'Data Type' For A Field Containing Both Text And Number, In VS Studio 2005, Databases?

Dec 1, 2007

For a field in a database to accept both text and number what 'Data Type' should I be using, i.e. 23t5

View 1 Replies View Related

Deleting Large Number Of Rows In SQL Server 2000

Jan 26, 2004

Hi,

I have some problems with our database which is growing too large, and was hoping someone might have some tips on what I can do!

I have about 100 clients, each logging about 10 000 rows of status logs a day. So after just a few days the db is growing very large.

At present it's manageable, since I don't need to "dig" into the logs more than a few times a day. The system it self is not affected by the size of the log or traffic on the server. But it will increase to about 500 clients in 2004, and 1000-1500 in 2005. So I really need a smarter solution than what I have today to be able to use the log efficiently.

98-99% of these rows are status-messages which are more or less garbage during normal operation. But I still need to keep them in case an error occurs, and we need to go back an hour or two (maybe a day) to see what went wrong. After 24-48 hours these 98-99% are of no use. I do however like to keep the remaining 1-2%, they are messages like startup, errors, etc. Ideally they should be logged in two separate tables by the clients, but unfortunatelly I cannot make the clients change their logging.

This presents problems on multiple levels. Mainly in searching, which often times out, but also with backup and storagespace. At the moment I check the system for errors, and every other day I just truncate the log-file. It works, but it's not exacly elegant......

The server is a 1100 MHz P3 / 512MB / Windows 2000 Server /
SQL Server 2000. Faster hardware would help, but the problem is more of a "bad design" than "slow hardware" problem.

My log is pretty simple, as follows:

LogId - int - primary key - clustered index
ClientId - int - index asc
LogTypeId - int - index asc
LogValue - nvarchar[2500], ikke index
LogTimeStamp- datetime - index asc


I have deducted 3 different solutions:

Method 1:
Simply run "Delete from db_log where logtyipeid <> stuff_I_want_to_keep".

This is the simplest and the one i prefer, but it takes too long time to complete. Any tips to speed this process up?


Method 2:
Create a trigger which runs something like "Delete from db_log where logtypeid <> stuff_I_want_to_keep and date < today_minus_two_days" every hour or so. This will ensure that the db doesn't grow to large. But if I'm away from work a few days we might loose data we'd wanted to keep.


Method 3:

Copy what I want to keep into another table, and empty the log. Sort of like "Insert into db_log_keep stuff_to_keep; drop db_log; create table db_log; " (or truncate, but that takes a long time too)

But then I would be stuck with two log tables, "48-hour_db_log" and "db_log_keep". I could use a view to "union" them so they would appear as a single table, but that's not ideal either.

However, it seems as this method is what will work best for my set-up, unless there are other suggestions??

Method 4:

...eagerly awaiting ideas!!! :-)




(Also, whatever tips and/or links to info on maintaing VLDB's are greatly appreciated. )

Thanks in advance for your help! :-)

Nikolai

View 4 Replies View Related

SQL Server 2012 :: Delete Large Number Of Records?

Sep 8, 2015

I have the following scenario:

SQL database on SQL 2012

Large Production table 15 Million record

The table has 3 years of data

New monthly data is being added every month.

A New Monthly data is being loaded, checked and finally approved after 6 or 7 iteration before approval.Because of this iteration the monthly data set is being added then deleted then added then deleted few times.Because the table is big this process takes time, any thoughts on how to make the delete insert process faster.Keep in mind I cannot do much because it is a production table and is being access by other users to do other analysis.

Delete is done based on trx_date which is a year/month combo, like 201508.

The table has monthly sales by customer aggregated.

The table structure is:

CREATE TABLE [dbo].[Sales](
[batch_key] [int] NOT NULL,
[Company_key] [int] NOT NULL,
[customer_key] [char](22) NOT NULL,
[Trx_Date] [int] NOT NULL,
[account] [nvarchar](35) NOT NULL,

[code].....

View 9 Replies View Related

How To Insert More Number Of Rows Into Sql Server 2005 Database At Once.

Apr 14, 2008

Hi,
Good morning to all.My table: User_Group_Map(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER)
Now, I want to write one stored procedure that can insert rows into the above table, but more number of rows at-once.
Means, the program should allow multiple insertions without the need to call the stored procedure from front-end more number of times.
Can anyone please help me on this...
Thanks in advance...Ashok kumar.

View 3 Replies View Related

SQL Server Admin 2014 :: MDW Data Collector - Large Number Of SPIDs

Nov 6, 2015

I've installed the MDW (Mangement Data Warehouse) database on our central monitoring SQL Server. I've then added a number of servers to be monitored. The data is collected on the servers that are being monitored and uploaded to the central MDW Monitoring server.

On the servers that are being monitored, I'm seeing a large number (over 1000) of SPIDs being generated by 'SQL Server Data Collector'.

Is this normal behaviour? I've seen more blocking as a result of this.

Is there any way to reduce the number of SPIDs generated?

View 0 Replies View Related

SQL Server Integration Services - Stop Large Number From Being Converted To Exponential

Sep 23, 2006

I am importing a text file with a column (serial numbers) with alphanumeric data, some mixed and some only numeric. The very large values that are all numeric are being converted to exponential when I run it thru an import package in SQL Server Integration Services (2005)

Ex. 4110041233214321 --> 4110040000000000 (displays as 4.11E+15)

In the past I dealt with this by importing the text file into Excel and changing the format of the column to number. This works even when many of the values contain alpha characters. I am not sure how to accomplish this same thing without going thru Excel. If you have any ideas on this I would be happy to hear from you.

I am importing the text file into a sql table.

View 1 Replies View Related

2005 Database Mirroring && Witness

Mar 14, 2006

I'm devlving into 2005 and specifically mirroring.Having read the following article (http://www.microsoft.com/technet/pr...5/dbmirror.mspx ),is mirroring a sensible option as it indicates that it should not beused in a production environment, yet the SQL2005 seminars I've been toindicate that this is a worthy option.Do we also need a witness server, or can this be done (sensibly) withtwo SQL2005 servers ?Currently we have 2xSQL2000 (clustered) servers and a SAN for the data.If we go down the route of mirroring, it looks like we can get rid ofthe SAN and Cluster and use the mirroring instead.Any advice on the pro's / cons would be appreciated.ThanksRyan

View 5 Replies View Related

Mirroring For Certain Tables From The Database In Sql 2005

Nov 7, 2006

Can I know is it possible using database mirroring for only certain tables from the database, not for the whole database in sql server 2005. Hopefully able to get any assistance here. Thx alot.

Best Regards,

Hans

View 4 Replies View Related

SQL 2005 Database Mirroring - AD Required?

Oct 10, 2005

Hi, for SQL 2005 - to set up database mirroring, is it required to have Active Directory installed or to be part of a domain? I am looking to use a workgroup with the SQL machines only and to not reference any domain and cannot find this information - is this possible?

View 5 Replies View Related

Database Mirroring Between 2005 Editions

Feb 1, 2007

Can I use Database mirroring between 2005 Std and 2005 Ent editions?

View 2 Replies View Related

To Get The Total Number Of Databases In A Server

Dec 5, 2007

Hi,
I want to create a web application which will give the information of the total databases in a server or local machine.  also I want to know about the number of tables, its properties in each databases. Pls help me.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved