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
ADVERTISEMENT
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
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
View Related
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
Nov 20, 2003
I was wondering what is the best solution to store large amounts of text in a SQL 2000 field. This text will be entered into a multiline textbox.
ex) what data type?......should I use BLOBs?
Thanks,
Trey
View 4 Replies
View Related
Mar 31, 2008
Hi,
I was wondering if any one could help me, I need to store large amounts of data in my database, at present I have it set to nvchar (8000), I've looked around and noticed you can use text which stores up to 2 million, but is slow in displaying the information.
Any ideas or points in the right directions would be great.
Thanks
View 6 Replies
View Related
Sep 8, 2005
Does anyone have ideas on the best way to move large amounts of databetween tables? I am doing several simple insert/select statementsfrom a staging table to several holding tables, but because of thevolume it is taking an extraordinary amount of time. I consideredusing cursors but have read that may not be the best thing for thissituation. Any thoughts?--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict254055.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877392
View 4 Replies
View Related
Jul 20, 2005
We are looking to store a large amount of user data that will bechanged and accessed daily by a large number of people. We expectaround 6-8 million subscribers to our service with each record beingapproximately 2000-2500 bytes. The system needs to be running 24/7and therefore cannot be shut down. What is the best way to implementthis? We were thinking of setting up a cluster of servers to hold theinformation and another cluster to backup the information. Is thispractical?Also, what software is available out there that can distribute querycalls across different servers and to manage large amounts of queryrequests?Thank you in advance.Ben
View 10 Replies
View Related
Jun 24, 2005
I have a dataset with 300,000 records and I'm getting the following error with MS Reporting Services. "An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.
View 11 Replies
View Related
May 8, 2007
Greetings
I need to be able to graph roughly about 150 employees/ supervisor and their monthly cell phone usage in minutes. I understand that I will need to group this on say one graph for every ten employees so it doesn't look messy and cluttered. I have read some threads here but they dont seem to work for me.
So again each supervisor has 100+ subordinates and I need to graph theie phone usage by month
thoughts???
km
View 2 Replies
View Related
Mar 20, 2007
this may seem like a simple question, but I have a report/lease agreement I need to put together and wanted to know the simpliest way to add large amounts of text. Basically its all the legal stuff most leases include in the amount of some 14 pages.
Should this be just one long string-- or does ssrs have another way to format this
thanks as always
KM
View 2 Replies
View Related
Mar 3, 2006
I was wondering what is the fastest way to UPDATE lots of recods. I heard the fastest way to perform lots of inserts in to use SqlCeResultSet. Would this also be the fastest way to update already existing records? If so, is this the fastest way to do that:
1. Create a SqlCeCommand object.
2. Set the CommandText to select the datat I want to update
3. Call the command object's ExecuteResultSet method to create a SqlCeResultSet object
4. Call the result set object's Read method to advance to the next record
5. Use the result set object to update the values using the SqlCeResultSet.SetValue method and the Update method.
6. repeat steps 4 and 5
Also I was wondering do call the SqlCeResultSet.Update method once per row, or just once? Also would it be possible and faster to wrap all that in a transaction?
Would parameterized updates be faster?
Any help will be appreciated.
View 3 Replies
View Related
Aug 12, 2005
I am running into a problem inserting large amounts of text into my table. Everything works well when I test with a few simple words but when I try to do a test with larger amounts of text (ie 35,000 characters) the appropriate field is left blank. The Insert still performs (all the other fields recieve their data, but the "Description" field is blank. I have tried this with both "text" and "ntext" datatypes. I am using a stored procedure with input parameters. As I mentioned, the query goes off flawlessly with small amounts of data (eg "Hi there!") but not with the larger amount.I check and the ntext field claims to be able to accept 1073741823 bytes of data. Is there some other thing I should consider with large amounts of text?
View 6 Replies
View Related
Apr 20, 2001
p.s. my email was incorrect in the last mail.
Hi all,
is there a sql 2k thread. Am interseted in finding out what the largest database size of a sqlserver database people have worked with.
We have a 1.2 Terabyte db with about 150-200 million new rows being processed everyday. Would like to share some thoughts on this with other people who are working with this much data and what they are doing with it.
bhala
----------------------------------------
Please check us out at: http://www.bivision.org/bivision
View 2 Replies
View Related
Jul 20, 2005
I'm in the process of migrating a lot of data (millions of rows, 4GB+of data) from an older SQL Server 7.0 database to a new SQL Server2000 machine.Time is not of the essence; my main concern during the migration isthat when I copy in the new data, the new database isn't paralyzed bythe amount of bulk copying being one. For this reason, I'm splittingthe data into one-month chunks (the data's all timestamped and goesback about 3 years), exporting as CSV, compressing the files, and thenimporting them on the target server. The reason I'm using CSV isbecause we may want to also copy this data to other non-SQL Serversystems later, and CSV is pretty universal. I'm also copying in thisformat because the target server is remotely hosted and is notaccessible by any method except FTP and Remote Desktop -- nodatabase-to-database copying allowed for security reasons.My questions:1) Given all of this, what would be the least intrusive way to copyover all this data? The target server has to remain running and berelatively uninterrupted. One of the issues that goes hand-in-handwith this is indexes: should I copy over all the data first and thencreate indexes, or allow SQL Server to rebuild indexes as I go?2) Another option is to make a SQL Server backup of the database fromthe old server, upload it, mount it, and then copy over the data. I'mworried that this would slow operations down to a crawl, though, whichis why I'm taking the piecemeal approach.Comments, suggestions, raw fish?
View 2 Replies
View Related
Sep 19, 2007
I know the standard Microsoft recommendation is to make the pagefile at least 1.5 to 3 times larger then the amount of physical memory. However, if you're talking about a server with lots of memory such as 16GB or 32GB, would following this rule be unnecessary. With SQL 2000 running on Windows 2000 Server or Windows Server 2003 I typically see pagefile usage no more then 12% for a 2GB pagefile. Anything over 15% means I need to look at other indicators to see if a memory bottleneck has developed. If I have 32GB of physical memory and make the pagefile only 1.5 x 32GB I have a 48GB pagefile. 10% of this is 4.8GB, which I would hope I never see consumed.
Any thoughts?
Thanks, Dave
View 11 Replies
View Related
Oct 21, 2007
HiI have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.Any ideas?ThanksEd
View 1 Replies
View Related
Jul 20, 2005
Hello,Currently we have a database, and it is our desire for it to be ableto store millions of records. The data in the table can be divided upby client, and it stores nothing but about 7 integers.| table || id | clientId | int1 | int2 | int 3 | ... |Right now, our benchmarks indicate a drastic increase in performanceif we divide the data into different tables. For example,table_clientA, table_clientB, table_clientC, despite the fact thetables contain the exact same columns. This however does not seem veryclean or elegant to me, and rather illogical since a database existsas a single file on the harddrive.| table_clientA || id | clientId | int1 | int2 | int 3 | ...| table_clientB || id | clientId | int1 | int2 | int 3 | ...| table_clientC || id | clientId | int1 | int2 | int 3 | ...Is there anyway to duplicate this increase in database performancegained by splitting the table, perhaps by using a certain type ofindex?Thanks,Jeff BrubakerSoftware Developer
View 4 Replies
View Related
Feb 4, 2006
I'm trying to move my current use of an sql 2000 db to sql 2005.
I need to update a table definition (to change a field to an Identity)
I'm getting a dialog box (in SQL server management studio) on save saying :
'xxxx' table
- Saving Definition Changes to tables with large amounts of data could
take a considerable amount of time. While changes are being
saved, table data will not be accessible.
I press 'Yes' to the dialog box.
After 35 seconds, I get another dialog box saying:
'xxxx' table
- Unable to modify table.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Well, the server is responding and I can query that talbe and other, I
can add/delete rows to other columns. I can modify other
(smaller) tables.
Any ideas where I can change this timeout?
Daniel
View 10 Replies
View Related
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
Oct 21, 1998
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?
View 1 Replies
View Related
Jul 10, 2013
I need to start mirroring the databases without witness server.
View 3 Replies
View Related
Oct 12, 2006
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?
Barry.
View 3 Replies
View Related
Jul 7, 2007
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?
Thanks
View 10 Replies
View Related
Feb 12, 2006
Hi,
My MSQSQL 2000 application inone company has a backup file of more than 6 GB. The total number of tables are more than 280 while the number of fields are more than 8000.
The last months I noticed that the backup database file is increasing rapidly. Is there a particular reason or anyone fas an idea why?
My largest table consists of more than 160 fields and has amore than 1,2 million records and 32 indexes. I know this is not very good, and I'm thinking methods to fix it since I have noticed performance problems. Can you have some advices?
I read that with MSSQL 2005 I can partition my table horizontally and vertically. However I think that this option is available with the enterprise edition which costs more than 10,000 Euros. Is this correct?
Is there another way to increase performance? My server is Windows 2003 with 4 GB memory. I think that Windows 2003 doesn't support more than 4 GB memory. Is this true?
Are there some advices for my case from the experienced users of this forum??
Regards,
Manolis
View 5 Replies
View Related
Dec 30, 2007
Hello,
I have been working as a SQL Server 2000/2005 db administrator (small-medium dbs), in the near future I am going to work as a DBA for large DB on SQL Server 2005 (200 - 300 GB and over) but I have not experences.
Do you have any suggestions, Recommendations or useful docs, books or web links and everything else to suggest me on how to administer large databases.
Thank a lot
View 4 Replies
View Related
Jun 10, 2014
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 ?
View 3 Replies
View Related
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
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
Jun 19, 2007
We are going to be getting a new system to house our large databases. Some of these databases will need to be encrypted. Ideally we'd like to set up encryption in SQL server rather then have to purchase an appliance to encrypt the data. What is the trade off of using SQL server 2005 to encrypt data? Does performance suffer a great deal with a large database (500g-4tb)? Also, how much more/less sure is the encryption for SQL Server? Some of our users need to be able to read the data as unencrypted while working, but we need to keep the system as secure as possible. Answers to any of these questions would be incredible helpful.
Thanks,
Robb S.
View 4 Replies
View Related
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
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
Aug 29, 2013
For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?
View 4 Replies
View Related
