Looking For Suggestions Cluster Or Mirror
Oct 2, 2006
Hello,
We currently have one instance of SQL2k5 SP1. We have a couple of publications, and 30 subscribers, on the instance and are considering going to either a cluster environment or db mirroring. Currently our instance seems to be busy and I am wondering if clustering really gives it a performance boost. What are your thoughts/suggestions on going to a cluster environment versus just db mirroring? Can mirroring be used for real-time failover as we need to add that as well? Thanks in advance.
John
View 2 Replies
ADVERTISEMENT
Dec 28, 2007
Greetings Team,
please see my post in the previous thread;
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2606008&SiteID=17&mode=1
We are making our final decission of using either transaction log replicant or a database mirror replicant and which solution would provide the biggest ease of provisioning as well as the fastest turn around in recovery.
"Good Morning Team,
I have a client whom has a 2 node SQL 2005 cluster with over 100 databases, their schema design is that they create a new database for every customer that signs to their services.
The problem I am encountering is that for every new database created in the cluster we like to setup a SQL2005 mirror replicant at a remote site for disaster recovery should the cluster ever fail.
i'm looking at the SQL professionals to help me with 2 questions.
#1 Upon creation of a new database in the cluster is there an easily scripted method that could be used to automatically create the mirror replicant, what about if not being aware of the new databases having been created? since we do not know when the customer creates databases or perhaps a SQL job that could be done based on new databases created that would automatically script the procedure of a database mirror.
#2 understanding the procedures required in a mirror replicant from a cluster primary involving, database backup, database restore to mirror replicant, transaction log backup and then transaction log restore with both points of the restore leaving the mirror replicant in NO RECOVERY mode and lastly creating the connection end points.
has anyone encountered such a situation with a database cluster with a mirror replicant consisting of such a wide span of individual smaller databases and would anyone have suggestions on how to make this highly available with a mirror replicant?
i look forward to hearing back, happy holidays."
View 2 Replies
View Related
Jun 1, 2015
I have configured active passive cluster in production environment. And we also have a dr which we have configured with asynchronous mirroring with no witness. Currently active node(node
a) is in sync with dr. When failover happens and the second node(node
b) becomes active, the mirror is broken and goes to disconnected mode.
But when we failback again to node a mirror is connected again and is in sync again. In our setup we have active passive cluster and a standalone server as dr.
View 11 Replies
View Related
Nov 9, 2007
if an app sits in an environment where both clustering and mirroring are available for HA, are there some simplified best practices, perhaps patterns or .net classes suggested by MS for making the app aware of both clustering and mirroring?
If the mirror db is also contained in the original connection string as a retry partner and SQL failed over automatically, the app wouldnt have a say in first retrying against the cluster. It might prefer cluster first because the mirroring is asynchronous and therefore a bit less up to date than the cluster. This of course assumes the app is ok with the longer failover time. If the partner isnt included, the app needs to be aware of two connection strings.
Is there some feature in SQL for having the best of all worlds, ie one connection string but having a "say" or "rule" in exactly what sql does and doesnt do for you and in what order?
View 1 Replies
View Related
Nov 30, 2006
Hi folks,
We are going thru the process of scoping an active/active cluster at one site.
I was wondering whether there will be any issues with mirroring (DB by DB) off the cluster into non clustered server at an alternate DRP site.
Regards, Brian
Sutherland
View 1 Replies
View Related
Jun 14, 2015
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
View 4 Replies
View Related
Jan 31, 2008
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
Thanks,
View 9 Replies
View Related
Dec 27, 2006
I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.
View 1 Replies
View Related
Oct 10, 2015
During the installation of Adding node to a SQL Server failover cluster(On passive node) getting error like.. The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.We run the below commands but didn’t get any resolution & got the same above error .
1<sup>st</sup> Method…
1. Open console command (Run->CMD with administrator privileges).
2. net stop winmgmt
3. Rename folder %windir%System32WbemRepository to other one, for backup purposes (for example _Repository).
4. net start winmgmt
2<sup>nd</sup> Method..
1. Disable and stop the WMI service.
a) Command : - sc config winmgmt start= disabled
b. Command : - net stop winmgmt
2. Run the following commands.
a). Command: Winmgmt /salvagerepository %windir%System32wbem
b). Command: Winmgmt /resetrepository %windir%System32wbem
3. Re-enable the WMI service
Command: sc config winmgmt start= auto
Last command to run after above steps
4. Command: mofcomp "%programfiles(x86)%Microsoft SQL Server100Sharedsqlmgmproviderxpsp2up.mof"
File not found Error for above command.
View 3 Replies
View Related
Jun 10, 2015
I am in the process of moving databases from a SQL 2005 Standard version to a 2-node 2014 cluster.All of my 2005 databases back up successfully.They all restore without issue except for one database that has a full text catalog. I get this message
Msg 7610, Level 16, State 1, Line 2
Access is denied to "fileStoragedataMSSQLSERVERFullTextCatalog", or the path is invalid.
Msg 3156, Level 16, State 50, Line 2
File 'sysft_FTCatalog' cannot be restored to 'fileStoragedataMSSQLSERVERFullTextCatalog'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
[code]....
I went as far as giving the folder full access to everyone temporarily and received the same error.
View 1 Replies
View Related
Apr 10, 2014
I ran the Advanced cluster preparation for a new sql instance on an existing cluster.
Slq Server 2012.
After is completed, it was successful, I realized I specified the wrong Instance Root directory.
Is it possible to remove what the preparation installed? Or is it possible to change the root directory?
View 3 Replies
View Related
Jul 6, 2015
Can I build a cluster by adding the cluster service, then the SQL instances, then add the other nodes and their passive SQL instances?I would lean to building the cluster first, the add the SQL instances.
View 4 Replies
View Related
Feb 18, 2014
I´ve been reading that SQL Server 2012 Always On is dependent on having a Windows Failover Cluster setup. Is that correct ?
View 6 Replies
View Related
Nov 6, 2014
I have following script which i am planning to run to drop all non-clustered primary keys on a database and then created as clustered. I am using someone else's script so don't know how to modify this. Some of primary key columns are used in references in other tables.
is there anyway i can drop the existing primary keys and using their original script then create again as clustered including restoring all foreign and reference keys and unique or no unique.
DECLARE @table NVARCHAR(512), @tablename NVARCHAR(512),
@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX), @sql3 NVARCHAR(MAX),
@column NVARCHAR(MAX);
DECLARE @indexname NVARCHAR(512);
SELECT name As 'Table'
[Code] ....
View 8 Replies
View Related
Oct 8, 2015
can we join a node in a windows cluster which is already in a different cluster?
We have this requirement as we need to setup readable secondary ( always on AG) on the third node.
View 2 Replies
View Related
Jul 15, 2015
We are planning to change all IPs of PRODUCTION Failover Cluster Setup. In my cluster setup ... we have 2 Physical Nodes with windows-2008, Roles are MSDTC and SQL-2008R2.
IP change for:
1. Both Nodes(Physical)
2. MSDTC
3. SQL Server
4. windows Cluster
So Almost... All IPs are going to change.
Im DBA here, I need to take care of SQL cluster and MSDTC. But I haven't performed this activity before.So I'm worrying about Impacts and consequences of this change. steps how should I perform this activity.
View 9 Replies
View Related
May 20, 2003
Hi all,
We have many tables which have cluster index on column with datatype 'Char(200)'.
Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
Thanks,
Deepak
View 2 Replies
View Related
Jul 26, 2015
Wanted to know about difference between cluster and non cluster index with example.
When to use cluster index and non cluster index .
View 3 Replies
View Related
Jun 10, 2015
CREATE TABLE [dbo].[Access](
[AdminID] [int] NOT NULL,
[FnID] [int] NOT NULL,
CONSTRAINT [PK_AccessFn] PRIMARY KEY NONCLUSTERED
[Code] ....
why the cluster and non cluster indexed created on the same key?
View 3 Replies
View Related
Jul 2, 2007
here is my schema...
Board of Zoning Appeals
Parcel#
BZACase#
ApplicantID
OwnerID
DateFiled
Size
Zoning
VU (Variance of Use)
BZACase#
ProposedUse
Comments
VDS (Variance of Developmental Standard)
BZACase#
OrdinanceReq
RequestedDim
ProposedUse
Comments
SE (Special Exception)
BZACase#
CurrentUse
ProposedUse
OrdinanceReq
RequestedDim
Comments
Applicant
ApplicantID
FirstName
LastName
CompanyName
Line1
Line2
City
State
Zip
PhoneNum
Owner
OwnerID
FirstName
LastName
CompanyName
Line1
Line2
City
State
Zip
PhoneNum
Now i know what im doing with the applicantID and ownerID...but the BZAcase# is a number/unique identifier that looks like this....2007-VU-000, 2007-VU-001, 2007-VU-003....so my question is
1. how do i get the last three numbers to increment each time a new application is created?
2. how do i retrieve the last record in the table???
3. Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"???
View 11 Replies
View Related
Mar 26, 2003
I am requesting suggestions to solve my problem.
Background: We are changing the way we pay commissions to our rep groups. We used to pay when the order was placed, now we want to pay when the invoice is paid.
Problem: The commision information is currently stored in the customer order, not in the invoice. These orders get deleted a couple weeks after the order was completed (shipped).
I want to create another, rather dynamic, table/structure that will store the order number and the commission percentage.
This info in this table should:
Be deleted: if the order has been deleted and the invoice either does not exist or was payed some period of time ago (maybe 6 months)
Be updated: if the customer order has been updated (i.e. the commission was changed)
Be inserted: if the order exists but the order number is not in the new table.
That is it in a nutshell.
Thanks,
Brian
View 1 Replies
View Related
Sep 25, 2006
hi
i have written a procedure for stock report.
its working fine. please go through the sp and give me some Suggestions. please tell me where i need to improve my code. thanks
Note: User is required to execute this procedure daily.
i am taking the sum of issues,purchases,returns,physical adjustments for each and every product from last updated date to today's date and storing it in a table i,e stock_Dump. from this table i generate the date wise stock report
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.spUpdateStock
@strReturn varchar(70) output
AS
BEGIN
declare @maxDt smalld atetime
if exists(Select * from Stock_Dump where Txn_Date=
Convert(varchar,Getdate(),101))
BEGIN
set @strReturn='Stock Table already generated
for the day. cannot generate it again'
END
ELSE
BEGIN
TRUNCATE TABLE Stock_Dump_Temp
select @maxDt=max(Txn_Date) from Stock_Dump
/* insert (Opening stock) Closing stock for all
all the products from last max Date*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,
Closing_Stock as Opening_Stock ,
0,0,0,0,0,0,0 from Stock_Dump Where
Txn_Date=Convert(varchar,@maxDt,101)
/* Issues*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,
Sum(Qty) as Issue_Qty,0,0,0,0,0,0 from Issue_Details
Where Issue_No IN(Select Issue_No from Issue_Hdr
Where Issue_Date > Convert(varchar,@maxDt,101) and
Issue_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Goods receipt*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
Sum(Qty) as Purchase,0,0,0,0,0 from Dlv_note_Details
Where Dlv_Note_No IN(Select Dlv_Note_No from
Dlv_Hdr Where Dlv_Note_Date > Convert(varchar,@maxDt,101) and
Dlv_Note_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Rejection after receipt*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,Sum(Qty) as Rejected,0,0,0,0 from
Rejection_Details Where Rejection_No IN
(Select Rejection_No from Rejection_Hdr Where
Rejection_Date > Convert(varchar,@maxDt,101) and
Rejection_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Issues returns*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,Sum(Qty) As Issue_Returns,0,0,0 from
Issue_Return_Details Where Issue_R_No
IN(Select Issue_R_No from Issue_Return_Hdr
Where Return_Date > Convert(varchar,@maxDt,101) and
Return_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Physical Stock + */
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,0,Sum(Var_Qty) as Phy_Qty_P,0,0 from
Physical_Details Where Var_Qty>0 and Txn_No
IN(Select txn_No from Physical_Hdr Where
Txn_Date > Convert(varchar,@maxDt,101) and
Txn_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* Physical -*/
INSERT INTO Stock_Dump_Temp Select Product_code,
convert(varchar,GetDate(),101) as Txn_Date,0,0,
0,0,0,0,Sum(Var_Qty) as Phy_Qty_M,0 from
Physical_Details Where Var_Qty<0 and Txn_No
IN(Select txn_No from Physical_Hdr Where
Txn_Date > Convert(varchar,@maxDt,101) and
Txn_Date <= Convert(varchar,getdate(),101))
Group by Product_Code
/* insert all the records into actual table i,e Stock_dump from Stock_dump_temp (temporory table)*/
INSERT INTO Stock_Dump Select Product_code,Txn_Date,
Sum(Opening_Stock) as Opening_Stock,Sum(Issue_Qty) as
Issue_Qty,Sum(purchase) as Purchase,Sum(Rejected) as
Rejected,Sum(Issue_Returns) as Issue_returns,
Sum(Phy_Qty_P) as Phy_Qty_P,Sum(Phy_Qty_M) as
Phy_Qty_M,0 as Closing_Stock from Stock_Dump_Temp
Group By ProducT_Code,Txn_Date
/* update closing stock*/
UPDATE Stock_Dump Set
Closing_Stock=abs((Opening_Stock+Purchase+Issue_Returns+Phy_Qty_P)-(Issue_Qty+Rejected+Phy_Qty_M))
Where Txn_Date=Convert(varchar,Getdate(),101)
/* delete unwanted records */
DELETE From Stock_Dump Where Opening_Stock=0 and
Issue_Qty=0 and Purchase=0 and Rejected=0
and Issue_Returns=0 and Phy_Qty_M=0 and Phy_Qty_P=0
set @strReturn='Stock Table Update Successfully'
return
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
suji
View 1 Replies
View Related
Nov 21, 2006
I have a database which contains more than 20000 stored procedureswhich were created withansi nulls off. This i found out using the querySELECT name,AnsiNullsOn FROM(SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOnFROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0Is there any way that i can set this property to 1 for all the storedprocedures i have??I know the alternate method is to drop the procedure and execute thescripts again with AnsiNullsOn = 1.Is there any other simple ways?? It will be very helpful for me..
View 2 Replies
View Related
Dec 8, 2006
I want to transform textfiles to sql server set based and not row based.what would be the best way to transfer.
let me know.
View 10 Replies
View Related
May 4, 2008
Hello all!
I have this simple sp.
SELECT VisName
FROM tblVis
WHERE (VisID = 1)
Now I have lets say VISID 1 to 50. I'm using this SP to change the text on a button. Now I have 50 buttons. So I run this SP, then I run this in my vb.net code
Code Snippet
Dim constr As New SqlConnection(PVDBConn)
Try
'Variable to hold the results
Dim results As String = String.Empty
cmdUpd = New SqlCommand("SelVis1Name", constr)
cmdUpd.CommandType = CommandType.StoredProcedure
constr.Open()
'Set results to the value returned from ExecuteScalar()
results = CType(cmdUpd.ExecuteScalar(), String)
constr.Close()
'Set our buttons text to that value
Button1.Text = results
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
At any time, when I start my program, I may need to label 10 buttons, or up to 50. Now I will have this number in a text file. Can I grab that number from a text file, and pass it into a SP?
And can I write this SP only once, to work for more than one label per time. Or do I have to write this sp 50 times?
TIA!
Rudy
View 5 Replies
View Related
Feb 9, 2008
I have a database that will be used by two or more organizations. I would like to use pass phrase encryption to encrypt a couple of columns.
I'm looking for suggestions on how I might set up the db to let the organization change the pass phrase that is used for their encryption?
I don't really want to hard code it into stored procedures or select statements with parameters. I will be using SSL if that should make a difference with what you suggest.
Any thoughts are appreciated.
Thank you
View 4 Replies
View Related
Sep 5, 2006
I have two stored procedures (l'll call them P1 & P2). P1, after a lot of processing, creates a temporary table that is used by P2 after an "exec P1" is done. I've separated the logic into two stored procedures because, ultimately, other sprocs will need the output of P1.
I get an error if I use #tempTable as the output table in P1 because it no longer exists after P1 finishes. ##tempTable works, but I'm concerned about concurrency issues. Any suggestions on what construct(s) I should be using?
Thanks in advance!
View 2 Replies
View Related
Oct 4, 2005
Hi All,
I am new to SQL Server but have been doing database programming since last 3 years. I recently attended MOC (Microsfot Official Curriculum) training on SQL Server and have started to use at my company. I am comfortable with SQL but want to dig deeper into T-SQL side. I searched on the Internet but not many good books available in that either they are ranked very low or are very old i.e. written around 1999/2000 or covers SQL Server 2000 as a whole. Can anybody suggest me any T-SQL book which was written recently and focuses purely or majorly on T-SQL?
Thanks to all for your time and advice in advance.
Regards:
Prathmesh
View 3 Replies
View Related
Jun 29, 2006
I'm looking for some help on how i should index this table.
current table has about 500k records in it.
the fields in the table are:
member_num (varchar(12), not null)
first_name (varchar(20), null)
last_name (varchar(20), null)
ssn (varchar(50), null)
address1 (nvarchar(200), null)
address2 (nvarchar(200), null)
city (nvarchar(200), null)
state (nvarchar(200), null)
zip (nvarchar(100), null)
phone1 (nvarchar(50), null)
all of the fields are searchable through an asp.net webform.
my first stab at this consisted of creating a clustered index on member_num and then creating a separate index for each of the remaining fields.
View 4 Replies
View Related
Sep 7, 2007
What I have.
I have a spreadsheet that is used in 4 or more locations on a daily basis by 1-3 ppl per locations. The spreadsheet is used to gather Quality Control information. So everyday there are a couple of spreadsheets from each system that is used to generate weekly and monthly reports. This is becoming to much work and I would like to automate the process.
What I have access to.
I currently run a Sharepoint 2007 Server for all our collaboration and document needs.
I also have the ability to setup any sql server.
What I want.
I want the QC techs in each system to be able to upload the data at the end of each day and be done with it. This way they do not have email or do a weekly report.
I would prefer to use Sharepoint and create reports weekly and monthly that can be pulled just by going to a site.
I'm knowledgeable in Sharepoint and Excel. I have some skills in VBA. I haven't dealt with SQL any, but willing to learn.
Also I'm knowledgeable in Microsoft Access as well.
Any suggestions on how I could accomplish this would be appreciated.
TIA
View 1 Replies
View Related
Apr 26, 2004
Hi Guys,
Well, as a VB/VBA applications developer I'm not well prepared for this, but it looks like I will be riding herd on a production SQL Server.
TSQL I know well enough to get along, but where can I get a fast fix on all the logins, security, and process management info? Today we had a DTS package crash overnight and it took me forever to figure out that it had left half a dozen tables locked. (Note that the scripts for the DTS package are being re-written as we speak with use of transactions and NOLOCK.) Meanwhile tech support was handling a whole mess of grumpy users.
Are there any books you would recommend as resources/references? Is there a particular author who is good at writing the stuff you really need to know in English that can be read by a mere mortal like I? I am fond of the Microsoft resources/help files but I'd like to have somthing that holds highlighter and post-it flags a bit better. Not to mention something that focuses more on the beast as a whole rather than the minutia at length.
Thanks for any suggestions!
View 14 Replies
View Related
Jul 20, 2007
I have a set of tables with about the same structure
dataID, recordID, 15 other columns
dataID is unique but is never referenced in queries
recordID is one of the most referenced columns but only has a cardinality of about 30%
The current structure has a clustered PK on (dataID,recordID)
Someone suggested reversing the clustered PK to (recordID,dataID) because of the number of references to recordID but that didn't seem to boost performance any
After staring at this for a while I came up with something but I'd like some advice whether it makes sense or not.
create a non-clustered PK on dataID
create a non-unique clustered index on recordID
Let me know if any other information is needed. Thanks
View 5 Replies
View Related
Aug 15, 2006
I would be teaching an applied database course to buisness majorundergrads. I'm looking for a book that introduces database conceptsusing SQLServer as the database. I would really appreciate if you couldrecommend me a few such books.ThanksNemo
View 4 Replies
View Related
