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.
I have a server which is using total of 12 cores running an instance of SQL server. I was told to dedicate only 8 cores of CPU to be used by the instance for licensing purposes.
what the best way is to go about limiting CPU cores?
Is there a performance limit on the number of indexes per table / database ? With Filtered indexes there appear to be many more opportunities for more finely defined, and therefore smaller indexes resulting in many more indexes on a single table.
View 4 Replies View RelatedIn log shipping is there any limit for configuring no.of secondary db server???
View 3 Replies View RelatedThe database is in high-safety mode.If off Mirror and Witness both the Principal goes into Disconnected / In Recovery. How turn online this database? Similarly, if off and Principal Witness both the Mirror goes into Disconnected / In Recovery. Is it possible to turn online database?
View 7 Replies View RelatedI 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 ?
We have an architecture of two production SQL 2005 Servers and two mirrors. The traffic has spiked on the two front servers, so the decision was made to add a new server to the top tier. Why the powers that be won't pop for the additional mirror server is another story. The question at hand is whether or not it is possible to mirror the 3 tier I servers to the 2 tier II servers?
Brandon Forest
SQL DBA
bforest@directapps.com
Various posts have noted that mirroring over distance is not advisable or that either async connections should be used.
Are there any limits/recommendations i.e. if two datacenters are a couple of files part with 10GBs fibre links and <50ms response times would this be acceptable for high-availability mirroring?
command in principal server
ALTER DATABASE database name
SET PARTNER = 'TCP://<mirror_server_name>:5022'
return:
Msg 1413, Level 16, State 1, Line 1
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:?
size? database > 9GB
slow HW? Principal database Intel D 3Ghz,4GB RAM, 4x 15k RPM HDD RAID 5 / Mirror database 2x Xeon 3Ghz,4GB RAM,6x HDD 10k RPM RAID 10
Slow LAN? both servers connect 1Gb/s
please help me
THX
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"
WHY?
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 RelatedI 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 RelatedHow to limit the length of a database field such that only first 12 characters are shown in the result?
For example, I have show the only first 12 characters of employee name.
I am trying to configure database mirroring with sql server 2012.
My principal instance and my mirror instance are on different server.
Both SQL Server service run with NT SERVICEMSSQLSERVER.
Now when I am configuring database mirroring, which service account should I use ? database account or server account ?
Instead of using domain account, can I :
1- create local account on my two server
2- Add these 2 logins in my 2 instances.
3- use these local account for configuring database mirroring.
I have created a new login in principle server and provided dbowner permission to principle db.
How do I transfer this login to mirror server and assign the same permission to mirror dd?
missing witness server information and the fail-over is broken suddenly? 4:00am no maintenance job. I have one sql job on 10pm for backup on database transaction log only.
I can see the primary have problem then perform fail-over to mirror database, the auto fail-over was broken.
I re-build the sql mirror is OK , but i want to find the root cause.
Windows application event was full when there have many failed event, i have increase log size for application event.
Is it possible to restore two databases simultaneously in same sql instance?
View 3 Replies View RelatedScenerio : To keep a very large system running optimally in a VM cluster, Take PR01 and make PR02, PR03, PR04. Distribute the 45 databases and 9T+ of disk across multiple VM guest.
Each PR## is a SQL Server 2012 Enterprise guest on a VM 5.1 cluster.
So instead of PR01 needed 16 core and 128g of memory, each one will have 4 core and 32g of memory. Making VM HA more manageable. (yes, DRS rules will apply). Also provides more HBA paths and distributes i/o over more physical disk on the SAN.
Instead of a connection string having to know PR01.dbo.UserDB01, PR02.dbo.UserDB03, ect the connection would be PRDB.dbo.UserDB01. That way if needed 1) UserDB can be moved to any of the PR## 2) new PR05, PR06 can be added as needed. The end user and processes are not allowed to touch system databases, no PR## will have a user DB called the same name.
There are seperate VM guests on other VM clusters and Citrix servers that need access to PRDB. As things expand and move around, none of the connection strings need to be changed.
I am looking into RadWare and modifing level 7 information, but that is iffy and $$$$$$.
Looking for what others have done to keep a copy of a database, for read only, on another instance. Need to do this once a day early in the morning with no, or minimal, downtime at the source and target. We have applications that access this copy 24/7, so prefer not to disconnect active users, as a detach/attach or backup/restore might do. Permissions are different on each instance, so would prefer not to overlay users on destination database. Options we are looking at right now are...
Log Shipping
Snapshot Replication
Transfer SQL Objects Task (SSIS)
Our environment for this is SQL 2012 on Windows 2012, in the same AD domain located in the same server room. The database size is 1gb. Needs to be copied around 6:30am daily. Does not need to be updated thru the day.
We have SQL Server 2012 running on Windows 2008 Server. We need to copy five databases from our 'sandbox' to our test server and then to our production server. The database backup file sizes are 3 MB, 20 MB, 344 MB, 645 MB and 17 GB. We are planning on using the backup and restore method since we already have full backups and the scripts to recreate the logins/users/permissions. We believe this method provides more flexibility and control over the process. However, we have a few jobs, maintenance plans and ssis packages.
To get the jobs to the new server instance, the plan is to script out the jobs on the 'sandbox' and execute the scripts on the test server instance. Is this the best or only way to handle the jobs?How to get these maintenance plans to the new server instance? (There is no 'script out' maintenance plan option.) We may have to just recreate them on the new server instance. Is this the best or only way to handle the maintenance plans?
We have a few ssis packages. How to handle getting the ssis packages over to the new server instance (using the backup restore method)? These packages use the Project Deployment Model. Therefore, should we restore the SSISDB or open up the package file using VSS on the new server instance and change the connection information to point to the new server instance.
Just wondering if there is any reason or advantage to use the Detach and Attach method or Copy Database Wizard method? I have read where the Copy Database Wizard method handles the database's dependent objects like logins, jobs, maintenance plans, user-defined error messages and shared objects from the master database. Are there any other move/copy database methods to consider? Just trying to make sure we have thought out everything and are using the best method to copy our databases over to another server instance.
Why would I see high CXPACKET waits on a database instance with only one CPU? Since the server only has 1 CPU parallelism can't be used, or are I'm missing something here!?
SQL version
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
max degree of parallelism is set to 0
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
Thanking you all in advance
Vivek
I have a 3 node cluster on which I have installed SSAS as it's own insntance. I have created this as a named instance and can connect to it by serverinstance if I'm on the server itself. However from my desktop I get the error saying instance was not found on server name.
I have defined an alternate port and setup firewall rules and can connect via server:port but not serverinstance. Prior to making this change SSAS was running on default port of 2383 and I could connect just by servername.
I have read many articles for previous versions saying that clustered SSAS will always use 2383 and that you must connect just using servername. However and this is were it gets strange. I have a 2 node UAT cluster with SSAS setup exactly the same way I've described above and I can connect from my desktop as serverinstance.
Should I be able to connect as serverinstances for a named clustered instance in 2012 ?
is there a limitations of number of records that shows in the table when you use show data table?
View 1 Replies View Related
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?
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
I am doing some SELECT queries on my database through ASP, but for example, I only want to return the 50 most recent entries that match the criteria. Is there any easy way to limit the number of results returned?
View 1 Replies View RelatedHow do you limit the number of records returned in a recordset? I only want the 10 most recent and I've got a Date column in my database.
View 1 Replies View RelatedIn other SQL programs I have seen the use of Limit to tell it how many rows to return.
Is there an equivalent in MS-SQL that will let me do a quick Select clause and tell it how many rows to return.
In other words if I just wanted to see the first 10 rows what would I add to Select * from tableA
Thanks!
Hi,
Here's my problem.
I have an Area Chart, and I need to plot more than 300,000 records. Problem is RS cannot seem to handle this huge dataset. It would retrieve for 20mins, then after that it will just have an error "Internet Explorer cannot display the webpage". I am not encountering this if I just have a few records to plot. Please help
Does sql server limit max number of connections, or max connections open from a given source, or over a given time period?
View 3 Replies View RelatedProcessing Association Rules model on SQL 2005 Standard edition produced following error:
"Error (Data mining): The 'WO_3' mining model has 6690 attributes. This number of attributes exceeds the attribute limit of 5000 allowed by the current version of the algorithm associated with the mining model."
How can I limit number of attributes in a model?
Thank you