Replication System Disk Performance Issue After 1 Month
May 14, 2007
Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.
Performance was good. Latency was ~ 5-7 seconds.
May 10 we noticed that the DB was behind (latency was 12 hours).
All performance counters seem good with the exception of the disk.
. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.
. During this period, Disk % Busy (1 - Disk % Idle) is 100%
The publisher DB publishes about 50-52 xacts/sec.
Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.
I know this because several weeks ago, the network went down, we were 24 hours behind.
When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.
What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.
We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).
I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.
My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.
Thanks for any help on this!
Jack Griffith
Configuration:
Subscribing System:
SQL Server: 2000, SP4 - 8.0.2039
CPU - 2.8GHZ Xeon, Quad Dual-core
Memory - 3.5GB RAM
Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:
C: 39GB System and Programs
D: 97GB Log space
E: 982 GB Data space
Replication configuration:
- nosynch, continuous Transactional Replication
- Distribution db is on Subscription system
- distribution - Publication of approx. 50 transactions / second
Subscriber DB configuration:
DB size: 64458 MB
Logging: Simple (at this point)
distribution
DB size: 3111 MB
Logging: Simple (at this point)
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
I 'inherited' a group of SQL Server server class machines. They aretrue server technology but the disk sub-systems are lacking. There isone hot-swap backplane that all the drives share (with one SCSIchannel) thusly even though there are three logical drives (composedfrom 6 to 8 hard drives), they all go through one channel. This iscreating a performance issue that is noticable and can be seen invarious performance counters that Microsoft recommended one shouldmonitor in terms of disk I/O. For a cheaper 'fix', I can add aseperate two drive bay (with its own SCSI channel) with mirroreddrives. I would then mostly likely place the transaction log files onthis new channel. Or I could place the indices filegroup files onthis new channel for DBs with mainly searching going on (not muchupdating). If I went this route I would be leaning towards thetransaction log move since the second method would require me movingDBs around quite a bit. Any input on this solution (besides spendingmore money)?What I would prefer to do is get a better server class machine or addan external drive bay solution (not a SAN). I would try to get threeor four SCSI channels in the new hardware to split the differentfile/filegroups out (i.e. transaction logs files, data filegroup,indices filegroup, etc.). My only concern here is: would this moreexpensive solution be worth the money? As far as replacing servers, Ihave only two kinds of experience...replacing somewhat underpoweredservers with slightly less underpowered servers and replacing overkillservers with even more overkill servers. In both cases, the disksub-systems were fairly equivalent from the old system to the new one.Will going the three/four channel route really get data moving along?We have one server in particular that hosts a database (one of many onit) for a web application that gets decent traffic (it is a privatelogin based system for internal use and external use by our clients'agents). Periodically throughout the day, there are 2-5 minute burstswhere performance slows to a crawl. I want to spend more timeprofiling queries and such before recommending we spend more money,but the folks I am working for want quick results and there is quite abit of stored procedure logic to profile and investigate. I know thedisk sub-system is definately in need of an overhaul, but I would liketo get an idea of peformance gains from adding either one additionalchannel over the existing single channel as well as going thethree/four channel route over the existing single channel setup.Any information would be greatly appreciated.Regards,Tony
View 2 Replies
View Related
Mar 2, 2006
Hi all,
Need some help with configuring the Disk Sub system using Raid 10
1. What shud be the disk configuration to have 4000 inserts per second on a RAID 1+0 ?
2. What is the best way to maintain a Hot Stand by Server for a very high OLTP system ( say for a day Trading Database ).
Thx
Venu
View 3 Replies
View Related
Aug 19, 2007
Hi all,
I'm having some performance-wise thoughts about my new sql-server 2005 installation ...
I have my SQL installed on one partition (the system dbs are also on that partition by default), my regular databases (non system dbs) are on different partition.
The question is - if my sys dbs are on different partition, could I experience some performance issues ?
One senario that I can think of is when the SQL looks for SPs starting with sp_ in the master DB, the disk will have to check a different partition. Perhaps such senario was solved using some kind of caching methods on the sql server itself.
Hope my thread is in the right forum.
Cheers everyone.
View 6 Replies
View Related
Aug 10, 2007
***cross-posted to MSDN SQL Server forum***
Hi All. Hopefully I posted this to the right section.
I would like help identifying if I have a disk performance issue or not. First the background: we have a j2ee application using the MS SQL 2005 JDBC driver and Hibernate on 4 application servers, and an active-passive SQL Server 2005 cluster. All of the servers reside in the same physical rack and switch.
Our application is typically bounded by CPU on the app server, or throughput from the database. Several months ago we were using SQL 2000 and would often max out the CPU on the database server before anything else, but often the database could keep up and we would max out the app servers CPU. Now we have 2005 on a much more powerful machine and more app servers, but we seem to be running up against a problem with throughput from the database.
The issue is not CPU. The total cpu average on the database server, as monitored in perfmon on 30 second intervals, stays consistently below 40%. The app servers stay well below 30%. But what concerns me is the Average Disk Queue Read Length on the database server, particularly for our E: drive. On this db server, the transaction log, the system and temp dbs, and our application's database are all on separate EMC SAN shares, connected via fibre chanel. The E drive houses the app data and is a 15-way meta device (fifteen 10GB logical devices striped at 960k for a 150GB device) in a RAID-S configuration, EMC Symmetrix array located in the same rack. The database is roughly 30GB.
I have read various articles online describing how to interpret the Average Disk Queue Read Length performance counter with regards to SQL Server. Some have said this should not exceed the number of physical spindles * 2. We are seeing values of 32 consistently, averaging over 60 during peak processing hours, and spiking to well over 100 on a scale of 1.0. (3-second sample interval).
So since our application servers seem to be waiting on their database calls (a lot of inserts with frequent, but small-resultset selects) and do not show I/O issues either with their local storage, memory, or network interface. The database server again has no CPU, network, or memory issues. I should add the the Average Disk Queue Write Length counter does not have any issues; its always below 1 (on a 1.0 scale). The EMC array has both read and write caching. The indexes of the application database are rebuilt weekly and defragmented every day, with stats rebuilt after the defrag.
So how can I further determine where my performance problem lies? All thoughts appreciated! Thanks!
-tuj
View 2 Replies
View Related
Oct 25, 2007
I am running SQL server 2000 SP4 on a server with 2 Dual core 4G processors with data attached via a SAN>
I have a 70G database with 10 users that is giving attrocious performance. I have just tried to run a count(*) accross a couple of tables and am still waiting for the results 15 mins later. When I look at the disk queue it is around 50/60. I thought the target for this was around 2. I am sure that the hardware that we have in place is capable of running this db. However I`m not sure how to fully analyse what is going wrong here.
Any tips would be greatfully received.
Si
View 2 Replies
View Related
Aug 9, 2007
Hi All:
I would like help identifying if I have a disk performance issue or not. First the background: we have a j2ee application using the MS SQL 2005 JDBC driver and Hibernate on 4 application servers, and an active-passive SQL Server 2005 cluster. All of the servers reside in the same physical rack and switch.
Our application is typically bounded by CPU on the app server, or throughput from the database. Several months ago we were using SQL 2000 and would often max out the CPU on the database server before anything else, but often the database could keep up. Now we have 2005 on a much more powerful machine and more app servers, but we seem to be running up against a problem with throughput from the database.
The issue is not CPU. The total cpu average, as monitored in perfmon on 30 second intervals, stays consistently below 40%. But what concerns me is the Average Disk Queue Read Length, particularly for our E: drive. On this machine, the transaction log, the system and temp dbs, and our application's database are all on separate EMC partitions, connected via fibre chanel. The E drive houses the app data and is a 15-way meta device (fifteen 10GB logical devices striped at 960k for a 150GB device) in a RAID-S configuration.
I have read various articles online describing how to interpet the Average Disk Queue Read Length performance counter with regards to SQL Server. Some have said this should not exceed the number of physical spindles * 2. We are seeing values of 32 consistently, averaging over 60 during peak processing hours, and spiking to well over 100 on a scale of 1.0. (3-second sample interval).
So since our application servers seem to be waiting on their database calls (a lot of inserts with frequent, but small-resultset selects) and do not show I/O issues either with their local storage, memory, or network interface. The database server again has no CPU, network, or memory issues. I should add the the Average Disk Queue Write Length counter does not have any issues; its always below 1 (on a 1.0 scale). The EMC Celerra array has both read and write caching. The indexes of the application database are rebuilt weekly and defragmented every day, with stats rebuilt after the defrag.
So how can I further determine where my performance problem lies? All thoughts appreciated! Thanks!
-tuj
View 9 Replies
View Related
Mar 2, 2004
Have a sql 2000 db which I have no say in design, just make it run. My typical sql counters such as system queue, and buffer cache and cache hit ratio are all good. If I need to monitor disk activity (mainly how fast my data is being read, how long the user is waiting for that data for both reads and inserts), what are the best counters for this, and what value should throw up a red flag.
View 1 Replies
View Related
Feb 11, 2006
We have an application that is experiencing I/O contention,particularly in tempdb but also in two other databases. The data isstored on mirrored PowerVault 220's, each with 10 of 14 possible disks.The PowerVaults are JBOD devices, not true SANs. The current config hasfour separate groups of physical drives assigned to distinct logicaldrives for log files, tempdb, and the two app dbs. This means, forexample, that tempdb resides on one mirrored drive. The standard advicewhen faced with disk contention is to add spindles if possible. With 4empty slots, we would presumably assign the new physical disks to themost stressed db, e.g. tempdb.An alternative arrangement would be to combine all the physical drivesinto one logical drive, and put all the files, log and data, onto thesingle logical drive. The hope for this configuration is that thePowerVault would automagically distribute the data among the drivessuch that all drives were in use, all spindles reading and writing atmaximum capacity when necessary. It is my understanding thatfull-featured SANs, like NetApps and EMC models, do this. My questionis whether this configuration is best for the PowerVault, as well. Oris this the essential difference between JBOD and a true SAN?Has anyone tried both arrangements?Advice is much appreciated.
View 2 Replies
View Related
Jul 20, 2005
Fellas!!This is a very complicated one and it took me a few days to figure outexactly what's going on, but here's the final story:I have a production environment running on .NET with a SQL Server(2000, SP3). The SQL Server is on a dedicated Proliant computer with2GB RAM (the actual SQLServer.exe process has dynamic memoryassignment and can reach up to 1.6GB RAM). Nothing else is running onthat specific computer.Once the SQLServer is started, it hits 300MB RAM (the minimum that wasset in the configuration of the server - remember, it is dynamicallyaquired).Then there is a .NET program that requests just about all the data theSQL Server contains (apart from a single table that contains roughly1.6 million rows and another table that contains about 10000 rowswhich are all of type IMAGE).Once all the data is retrieved, the RAM is at about 400MB. From thereon, every update I make to the data on the server causes the RAM to goup by a bit (that updates are done in a Transaction which of course iscommitted at the end). It seems that BLOB updates are the majorproblem in all of this. For some reason, uploading a blob of size 9MBcauses the RAM to go up by roughly 20MB and after commit it gose down10MB (total gain of roughly 10MB RAM). Eventually the SQLServerprocess hits its upper limit (1.6GB) and at this point it startsslowing down.Some performance checks showed me the SQLServer has a lot of diskactivity, it seems it is reading and writing pages of data from/to theHD all the time (which causes the queries to be much much muchslower).We have a development environment running the exact same code (it isthe exact same in everything, except for the amount of data stored inthe DB). This does not happen there at all.I have a few questions:1. Why is the RAM going up after BLOB updates?2. Why is the RAM going up at all?3. How can I tell the DB which tables should remain in the RAM at alltime (never swapped back to the HD?) - DBCC PINTABLE does not seem todo the job.It does not seem to have anything to do with the .NET code.Thank you very much,M Yamo.
View 4 Replies
View Related
May 29, 2008
Depending on the way I write a query, I come up with these 2 stats.
Is there a sure winner in this race, keeping in mind the overall health of the server?
(I'm not sure of the specs of the server, as I can't log on to it :/ but are there any sql variables that would show cpu speed and # of cpus?)
I almost am leaning towards the single cpu query because of lower resources used -
or are most of the "reads" in the parallel'd query not read directly from the HD, but using the Table Spool created internally (query plan shows it)?
CPU Reads Writes Duration
Parallel: 200k 3.2m 2400 62s
Solo: 79k 1.1m 600 79s
View 9 Replies
View Related
Mar 14, 2000
Hi,
I did a same server upgrade from SQL 6.5 to 7.0 about a month ago. I never deleted the old 6.5 installation/databases. Yesterday, for some reason SQL 7.0 wouldn't start. When I looked at SQL 6.5 enterprise manager, it showed that 6.5 was started. So I stopped it. I tried to start 7.0 back up and it would just start 6.5. I tried a reboot... that didn't work. Then I uninstalled 6.5, rebooted, and SQL 7.0 finally came up. I check the error logs and it showed that there was a "short stack dump" (details below)
Would a "stack dump" have caused sql to revert back to 6.5?
Thanks,
LC
Dump thread - spid = 10, PSS = 0x3505b4b4, EC = 0x3505b664
Stack Dump being sent to e:MSSQL7logSQL00003.dmp
************************************************** *****************************
*
* BEGIN STACK DUMP:
* 03/13/00 15:07:33 spid 10
*
* Input Buffer 66 bytes -
* SELECT category FROM z_guarantees WHERE z_guarantees.code ='GGTD'
*
************************************************** *****************************
-------------------------------------------------------------------------------
Short Stack Dump
0x77f67a2b Module(ntdll+7a2b) (ZwGetContextThread+b)
0x0077fc6b Module(sqlservr+37fc6b) (utassert_fail+1a0)
0x005b0ebe Module(sqlservr+1b0ebe) (XCB::Rollback+4a)
0x005aa843 Module(sqlservr+1aa843) (bds_defect_xact+84)
0x0051abea Module(sqlservr+11abea) (clean_process+200)
0x005b22fd Module(sqlservr+1b22fd) (language_exec+5f9)
0x4106135c Module(opends60+135c) (execute_event+659)
0x4106164b Module(opends60+164b) (process_commands+f3)
0x4109285a Module(ums+285a) (ProcessWorkRequests+ed)
0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139)
0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce)
0x77f04ee8 Module(KERNEL32+4ee8) (lstrcmpiW+be)
2000-03-13 15:07:34.09 spid10 CImageHelper::GetSym Error - The specified module could not be found.
0x00000000 Module(sqlservr+ffc00000)
2000-03-13 15:07:34.16 kernel SQL Server Assertion: File: <xcbmgr.cpp>, line=1299
Failed Assertion = 'pss->IsXcbLocked ()'.
Dump thread - spid = 10, PSS = 0x3505b4b4, EC = 0x3505b664
Stack Dump being sent to e:MSSQL7logSQL00004.dmp
************************************************** *****************************
*
* BEGIN STACK DUMP:
* 03/13/00 15:07:34 spid 10
*
* Input Buffer 66 bytes -
* SELECT category FROM z_guarantees WHERE z_guarantees.code ='GGTD'
*
************************************************** *****************************
-------------------------------------------------------------------------------
Short Stack Dump
0x77f67a2b Module(ntdll+7a2b) (ZwGetContextThread+b)
0x005b5af3 Module(sqlservr+1b5af3) (ex_raise2+35c)
0x00506417 Module(sqlservr+106417) (ex_raise+5f)
0x0077fbfe Module(sqlservr+37fbfe) (utassert_fail+37e)
0x005b0ebe Module(sqlservr+1b0ebe) (XCB::Rollback+4a)
0x005aa843 Module(sqlservr+1aa843) (bds_defect_xact+84)
0x0051abea Module(sqlservr+11abea) (clean_process+200)
0x005b22fd Module(sqlservr+1b22fd) (language_exec+5f9)
0x4106135c Module(opends60+135c) (execute_event+659)
0x4106164b Module(opends60+164b) (process_commands+f3)
0x4109285a Module(ums+285a) (ProcessWorkRequests+ed)
0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139)
0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce)
0x77f04ee8 Module(KERNEL32+4ee8) (lstrcmpiW+be)
2000-03-13 15:07:34.22 spid10 CImageHelper::GetSym Error - The specified module could not be found.
0x00000000 Module(sqlservr+ffc00000)
2000-03-13 15:07:34.44 spid1 Closing file e:mssql7datarain.mdf.
2000-03-13 15:07:34.83 spid1 Closing file e:mssql7datarainlog.ldf.
Dump thread - spid = 8, PSS = 0x2110b4b4, EC = 0x2110b664
Stack Dump being sent to e:MSSQL7logSQL00005.dmp
************************************************** *****************************
*
* BEGIN STACK DUMP:
* 03/13/00 15:07:34 spid 8
*
* Input Buffer 62 bytes -
* select interface from z_property where ( property ='DALMAN' )
*
************************************************** *****************************
-------------------------------------------------------------------------------
Short Stack Dump
0x77f67a2b Module(ntdll+7a2b) (ZwGetContextThread+b)
0x0077fc6b Module(sqlservr+37fc6b) (utassert_fail+1a0)
0x005b0ebe Module(sqlservr+1b0ebe) (XCB::Rollback+4a)
0x005aa843 Module(sqlservr+1aa843) (bds_defect_xact+84)
0x0051abea Module(sqlservr+11abea) (clean_process+200)
0x005b22fd Module(sqlservr+1b22fd) (language_exec+5f9)
0x4106135c Module(opends60+135c) (execute_event+659)
0x4106164b Module(opends60+164b) (process_commands+f3)
0x4109285a Module(ums+285a) (ProcessWorkRequests+ed)
0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139)
0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce)
0x77f04ee8 Module(KERNEL32+4ee8) (lstrcmpiW+be)
2000-03-13 15:07:34.92 spid8 CImageHelper::GetSym Error - The specified module could not be found.
0x00000000 Module(sqlservr+ffc00000)
2000-03-13 15:07:35.00 kernel SQL Server Assertion: File: <xcbmgr.cpp>, line=1299
Failed Assertion = 'pss->IsXcbLocked ()'.
2000-03-13 15:07:35.06 spid8 Using 'sqlimage.dll' version '4.0.5'
View 2 Replies
View Related
Jun 22, 2004
We've got an internal database that replicates with another database server for our website.
Not all tables are replicated, some use merge and the others are snapshot based and published regularly to the public website facing server.
However, there's a lot of data (well, large textual data) that's being transferred and it seems to be generating massive log files that continue to grow and grow.
I'm fairly new to adminning an SQL Server box, so was wondering if anyone can tell me what the best way to keep it under control is? I've heard its possible to truncate the logs, effectively deleting any data that has already been processed by subscribing servers etc.?
As I said, I'm very much new to this and would really appreciate some guidance, if only to the right part of the SQL Server Books Online :)
Thanks,
Paul
View 2 Replies
View Related
Mar 6, 2007
Somebody now what is the correct process to move the Distribution BD to another Disk with out affect the defined replication
View 4 Replies
View Related
Dec 28, 2006
hello,all
I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .
is it possible to do it ?
if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?
thanks in advanced!
View 1 Replies
View Related
May 17, 2007
Hi,
I am developing an editorial system(MS-SQL). There are a public part(frontend for reader) and an editorial part (backend for editor, admin,...).
The public part of system will be used approximately 1000 acesses/s.
A possible solution:
I solve whether I have to use two databases:
1.DB all data
2.DB with data for frontend
acesses...............trigers
--------->2.DB <----------1.DB
or more of databases or else?
The purpose is, a reader should have good response that the reader don't wait.
Could you give me any advice ?
Thanks
View 3 Replies
View Related
Nov 8, 2001
I am developing a new application and am trying to find the best way to deliver XML documents to the app. Here's the scenario: each set of xml docs will have a unique identifier. The app needs to retrieve a known xml doc by using this identifier. So the dilema I am having is as follows: I could create a folder for each identifier and load the xml docs relevant to each id into the folder. For the app to retrieve the document, it would simply construct a file path and the windows B-tree search would find it and return it very quickly. Alternatively, I could create a table with a primary key that stores the unique identifier, cluster it, and store the xml in a blob field.
Does anyone have any experience, references, or thoughts about which would be the best way to do this? I am an avid database developer and the app will be using sql 2000 anyway, however it seems in this situation the database might not be the best option in terms of speed, performance, and resource useage. Thoughts?
Thanks,
Mike
View 5 Replies
View Related
Feb 8, 2007
Hi,I am starting a new job next week. Part of what I am required to do isa "system diagnostic" on a SQL Server 2000 box to determine what areascan use improvement - this would include configuration settings,backup/recovery, sql tuning, etc... and anything else I may not havementioned here!What I need is a thorough and systematic approach to doing this. Cananyone please give me advice, or point to a FAQ or other links thatdiscuss this. I am running out of time.THANKS MUCH
View 2 Replies
View Related
Apr 5, 2008
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
View 3 Replies
View Related
Oct 23, 2007
MS documentation is short when it comes to explanation of Quiescing a system for peer to peer replication. It seems that for any change the whole p2p machine has to stop ? This would cripple our development/releases... Is it really the case ?
Can all p2p nodes be quiesced except one, the one when I would also make changes ?
i.e. N1 <> N2 <> N3 I would stop inserts/updates/deletes for N1 & N3 and keep inserting/updating/deleting for N2 as I'm making schema change on N2 (additional table, additional field etc). Would it work ?
Also, would I need to stop selects for N1 & N2 ?
Thank you.
View 5 Replies
View Related
Dec 24, 2002
Hello,
I just wanted how we can find the system performance without using tool like performance monitor or profiler.
I just need the query like equalent to peformance monitor for see the systmem status of CPU ,IO ,Memory and etc..
Thanks,
Ravi
View 2 Replies
View Related
Jan 16, 2007
Hi,
I am trying to write a table-valued function in SQL Server 2005 (SP1) to return all active directory groups a user belongs too, using managed code (VB.NET).
Testing the code with a simple winform I get the list of groups in about 0.4 seconds. However the table-valued function takes upwards of 17 seconds to run! Is this normal for managed code in SQL Server?
Imports SystemImports System.TextImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports System.CollectionsImports System.DirectoryServicesImports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions#Region "Constants" ''' <summary> ''' The connection string for Active Directory. ''' </summary> 'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string> ''' <summary> ''' The LDAP search filter need to find a user in Active Directory. ''' </summary> 'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"#End Region ''' <summary> ''' Gets all active directory groups for the user. ''' </summary> ''' <returns>All dataset permissions for the user.</returns> <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _ Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable ' Setup the active directory search. Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING) searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName) searcher.SearchScope = SearchScope.Subtree searcher.PropertiesToLoad.Add("distinguishedname") ' Run the active directory search. Dim result As SearchResult = searcher.FindOne() Dim userEntry As DirectoryEntry = result.GetDirectoryEntry() Dim userGroups As New ArrayList GetActiveDirectoryGroupsForEntry(userEntry, userGroups) Return userGroups End Function Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars) GroupID = New SqlChars(CType(source, String)) End Sub ''' <summary> ''' Recursively gets the active directory groups for the directory entry. ''' </summary> ''' <param name="entry">The active directory entry.</param> ''' <param name="groups">The list of groups.</param> Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList) For i As Integer = 0 To entry.Properties("memberOf").Count - 1 Dim memberEntry As New DirectoryEntry("LDAP://" + entry.Properties("memberOf")(i).ToString()) groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString()) GetActiveDirectoryGroupsForEntry(memberEntry, groups) Next End SubEnd Class
View 9 Replies
View Related
Sep 10, 2007
If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
View 1 Replies
View Related
Nov 13, 2007
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
View 16 Replies
View Related
Nov 15, 2006
Hello,
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
John
View 4 Replies
View Related
Jun 25, 2006
I'm having a tough time finding any good resources on Sql Server Replication Performance. Are there any benchmarks / state of any kind? How well does replication scale out?
In my scenario, I have one central publisher and several large tables, all with hundreds of millions of records. Every day I may insert/update millions of records in the publisher, and then I need to replicate the changes (in a few hours at most) onto a pool of subscribers, while they remain online.
Is the replication story robust enough to handle a situation like this?
View 1 Replies
View Related
Feb 20, 2001
Hello,
this is my configuration :
1) 3 disks in RAID5 that hold the SQL data
2) 1 disk in RAID0 that holds the only paging file.
What will happen to the SQL data (DB) when the disk that holds the paging file crashes?
Kindest regards,
Luc.
View 1 Replies
View Related
Jul 23, 2005
Hi, i restored a backup from a database thas has replication configured.When i restored it the system tables that the merge replication createsare restored too. I was investigating on internet and I found that i candelete it using this query:sp_configure 'allow updates', 1goreconfigure with overridegoDROP TABLE aonflict_SiacDataEEC_security_info...sp_configure 'allow updates', 0goreconfigure with overridegoDo somebody know if i use this queries to delete this tables i candamage the database or is correct to use it.Thanks a lot for your help.*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Oct 26, 1999
Can anyone suggest hwo can I improve the performance of the replication process and make it faster.
Pran
View 1 Replies
View Related
Mar 9, 2004
Hi all,
I am keen to hear peoples perspectives on how much additional load Transactional Replication will have on a server.
Obviously this will depend greatly on the level of transactions in the database, but a general indication would be great (eg 10% increase in overheads).
I am thinking of encorperating this into a new server structure which we are going to be setting up and am unsure as to whether to make the primary server BOTH the publisher and distributor; or make the secondary server the distributor to reduce the load on the primary to only being the publisher.
Basically the secondary server will simply be a 'hot swap' of the primary - so i/o load on the secondary is not going to be an issue.
There may be 2 primary's (if that makes sense) replicating to the hot swap so that if either primary is dropped the hot swap could take over either servers load/responsibilities - not sure if this will make a difference on where to put the roles?
Anyone have any thoughts on this?
Thanks in advance.
Cheers
View 6 Replies
View Related
Aug 24, 2006
Hello,could you please advice on how to measure replication performancein Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?I've got two servers with databases installed and configured,I prepared set of data using DBGEN from TPC and I already imported theminto databases.Also, I configured the replication.Now I have to do a test with a few kind of replications methodimplemented in these RDMBS, but I don't know which tool or reports or"v$iews" should I use to measure replication performance.The replication is configured only between the same RDBMS, I meanOracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.Most of applications are great for checking performance of local DB,not for replicated/distributed.I've found description of CA Unicenter Database Performance Managementfor distributed RDMBS, and I think it could be the right one, but Ican't find any demo or trial version :(Could you please advice any place to download it, or other application,script, description, just whatever.Perhaps just any other idea how to check the replication mechanismefficiency ?Regards,Mark
View 5 Replies
View Related
Jul 20, 2005
I'm seearching for information regarding database replicationperformance. We need to compare the performance of replication for SQLServer and Oracle and it is urgent! Anyone who can describe theperformance bottlenecks for each database when performing replication,or can point me to a white paper or webpage.
View 1 Replies
View Related
Apr 18, 2006
Hello,
I am trying some replication sample.I create a table with thousands of records in the publisher side. as I create a subscription(the subscription database is on a remote machine),
the whole table is created on the remote database.
I wanted to measure the performence as:
1. how much time was taken in filling the whole table in the subscriber side?
2. If i insert some 10000 records on publisher side, I want to measure, how much time was taken in inserting the same records on the subscriber?
How do I measure this ? Can I use some Log reader stuff.
thanks in advance
View 3 Replies
View Related
