SQL Cache Dependency With Cluster SQL Server && Web Farm
May 30, 2008
I have implemented SQL cache dependency to invalidate cache whenever the result of the stored procedure gets changed. The stored procedure is written obeying all the notification rules.
Our problem:
As long as we are working on Local machine, Development server or Pre production server its working fine means the cache gets invalidated whenever there is any change in the result set. But the same implementation doesn’t work in Production. Cache does not get invalidated.
Difference in Production environment and other environment:
Production uses cluster SQL server, others use standard SQL server. Production has load balancing means application is deployed on 6 servers which hits same clustered SQL server. In application’s global.asax file i have Start dependency so I see 6 different notifications started in SQL server which is right. But whenever there is a change in result set the cache doesn’t get invalidated in any of the application.
My question is
Do we have to follow any different kind of implementation of SQL cache dependency for web farm and cluster SQL server scenario?
On the same note I would like to add, on the same SQL server I have one more database and a different application is accessing this database. This application is also using SQL cache dependency and its working fine. The only thing this .Net application is NOT deployed on web farm. Its deployed on single application server.
Im getting this error when trying to set up a cache dependency...are there any special permissions etc?From CS:SqlCacheDependency dep = new SqlCacheDependency("MySite-Cache", "Products");Cache.Insert("Products", de.GetAllProductsList(), dep); From connectionStrings.config:<add name="SiteDB" connectionString="Data Source=localhost,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />Also tried this using my machinename<add name="SiteDB" connectionString="Data Source=<machinename>,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" /> From web.config: <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="MySite-Cache" connectionStringName="SiteDB" pollTime="2000"/> </databases> </sqlCacheDependency> </caching> EDIT: So making progress I can't seem to get the table registered for cache dependency:The sample i have says"aspnet_regsql.exe -E -S .SqlExpress -d aspnetdb -t Customers -et"and the command line response is "Enabling the table for SQL cache dependency..An error has happened. Details of the exception:The table 'Customers' cannot be found in the database."Where does this "Customers" table come from? There is obviously not an application specific "Customers" table in aspnetdb I'm confused probably more by the example than anything....
Hello all.. i'm having a major issue with my Sql Express 2005 database :( I'm using wicked code sitemap to allows for a sitemap to be stored in a database.. it uses sql cache dependency to invalidated the cache bla bla Problem: After i update a record / add new record the database generates a SqlQueryNotificationStoredProcedure But it never gets executed. I've done tests on the code it's calling everything accordingly so i'm assuming the problem lies in a configuration setting of the database done alot of searching around the net and i've found this GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username i'm using Windows Authentication how do i run this and will this solve the problem
I'm working off of the example shown here: http://www.c-sharpcorner.com/UploadFile/mosessaur/sqlcachedependency01292006135138PM/sqlcachedependency.aspx?ArticleID=3caa7d32-dce0-44dc-8769-77f8448e76bc
The tutorial shows that an entry must be made in web.config for a sqlCacheDependency node in web.config. When a dependency is added, they set an attributed called "connectionStringName" that references a connectionString established earlier in the web.config. My question is twofold: 1). First, I tried setting up my connection string using the connectionStrings node (my other apps use the AppSettings node), but when I try to extract the value in my code using the following syntax: connStr = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["devConnStr"].ConnectionString); I get the following compilation error: "ConfigurationManager does not exist in the class or namespace System.Configuration"; Am I extracting it with the wrong code? Does that setup exists in .NET v 1.4? No, I cannot use .NET v 2.0 for various reasons.
2). If I cannot use ConnectionStrings in that way and must use AppSettings, how do I set up SqlCacheDependency node to recognize that connection string?
Hello.I am having problems with SQL Cache dependency. I am using SQL 2005, ASP .net 2.0.Every time i try to load data from cache, this is null. It acts like someone is constantly changing everything in the db.Because of this my website makes hundreds of connections to the db instead of 5. This is a major issue that i cannot figure it out. PLEASE ADVISE.On my local machine everything seems to work just fine. On the testing server the cache is always null.Is cache dependency related - to the platform used? - to the number of IIS servers connected to the DB? - to the sql user used? Also a strange thing happens. When i change something in web.config the cache is working for about 1 min and after that it stops. Here is the code i wrote: public ManufacturerList GetAllManufacturers() { if (HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] != null) return HttpContext.Current.Cache[ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS")] as ManufacturerList; SqlCacheDependency dep = new SqlCacheDependency(ConstantsManager.Instance.GetDefaultAsString("DB_NAME"), ConstantsManager.Instance.GetDefaultAsString("TABLE_MANUFACTURERS")); _manufacturerList = new ManufacturerList(); DataReader reader = SqlHelper.ExecuteDataReader(WebContext.ConnectionString, CommandType.StoredProcedure, "dvx_web_MANUFACTURER_LoadAll", null); while (reader.Read()) { int manufacturerID = reader.GetInt("ID"); Manufacturer findManufacturer = _manufacturerList.FindByID(manufacturerID); if (findManufacturer == null) { findManufacturer = new Manufacturer(); findManufacturer.LoadFromDataReader(reader); _manufacturerList.Add(findManufacturer); } } reader.Close(); HttpContext.Current.Cache.Insert(ConstantsManager.Instance.GetDefaultAsString("CACHE_MANUFACTURERS"), _manufacturerList, dep); return _manufacturerList; } Thanks a lot.
I have a sqlCacheDependency element registered in web.config that I figured would set the database up to deliver all notifications for all its tables: <sqlCacheDependency enabled="true"> <databases> <add name="MyDb" connectionStringName="MyDbConnnectionString"/> </databases> </sqlCacheDependency> I've seen docs saying that's all I need to do with SQL Server 2005, but that doesn't appear to be the case. I get the error: The database 'MyDB' is not enabled for SQL cache notification.To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information. Are the docs I read wrong or am I missing something?
Our development team is building an application using .net framework 2.0 and sql server 2005 developer edition. They are using service broker and sql cache dependency classes in their code and we are running into a problem where the database used for their application is growing alarmingly in size, ends up using a log of memory and finally drags the machine. The database machine is a developer class machine used for development purpose only.
After we did our research we found the following:
1. the tables created by the team do not increase in size.
2. the system tables created by sql server for service broker and sql dependency grows very big during their usage.
sys.sysconvgroup sys.sysdesend sys.sysdercv
The above three tables have the same number of records and they are equal in size for eg, at one point they were 1.5 gb each amounting to total 5 gb out which the actual user created tables, indexes, sp amount to only 500 Mb.
There were some post in other forums that suggested to use triggers to close the open conversations and also use END CONVERSATIONS in the code. We have used that solution also but doesn't seem to solve the problem.
Is there a definite solution from microsoft for this issue.
Anybody has any insight to this. Thanks for your help in advance.
I was wondering if SQL Cache Dependency would be in fact invalidated if: 1. it was created based on a procedure type command. 2. if the select statement retrieves the data from multiple database tables Any help would be more appreciated. I am stuck with the fact that none of the data bases on sql dependency is invalidated. I spent literally hours to understand what i am doing incorrectly.
I am looking at the plan caches/cached pages from the perspective of sys.dm_os_memory_cache_counters and sql serverlan Cache - Cache Pages
For the first one I am using
select (sum(single_pages_kb) + sum(multi_pages_kb) ) from sys.dm_os_memory_cache_counters where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP' a slight change from a query in http://blogs.msdn.com/sqlprogrammability/
For the second just perfmon.
The first one gives me a count of about 670,000 pages only for the object and query cache and the second one gives me a total of about 100,000 pages for five type of caches including object and query.
If I am using the query from http://blogs.msdn.com/sqlprogrammability/ to determin the plan cache size
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB from sys.dm_os_memory_cache_counters where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
it gives me about 5 GB when in fact my SQL Server it can access only max 2GB with Total and Target Server Memory at about 1.5 GB.
I am developing a web app that uses a SQL Server backend and will be served by a Web Farm at some point in the future. Can anyone give me some guidance at to what the common architectual considerations are when using SQL Server with a Web Farm? Initially, would it be allright to continue to have a single SQL Server on the network, with several webservers connecting to it instead of just one, or would this be a big performance nightmare? Eventually, since the goal of using the Web Farm would be to reduce individual server load, I would think having multiple SQL server instances would be preferred. Is this even done in the real world? I'm not a dba, so I'm not quite sure of the how multiple instances would be synchronized so that they held the same data and how applications on varying web servers would know to connect to one or another (by varying the connections string, perhaps). Can anyone give me any guidance? Thanks!
I am trying to create a scale-out deployment of Reporting Services 2005 on a NLB cluster web farm by following the instructions in: http://technet.microsoft.com/en-us/library/ms159114.aspx
However, when I install and configure Reporting Services on the second node, and then go back to the first node to join the second node to the scale-out deployment (as described in step 11 under 'To install and configure the second report server instance"), I get the following exception:
ReportServicesConfigUI.WMIProvider.WMIProviderException: An unexpected error has occurred. Details:
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) ---
> System.Runtime.InteropServices.COMException (0x800706BA): The RPC
server is unavailable. (Exception from HRESULT: 0x800706BA) at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo) at System.Management.ManagementScope.InitializeGuts(Object o) at System.Management.ManagementScope.Initialize() at System.Management.ManagementScope.Connect() at ReportServicesConfigUI.WMIProvider.RSInstances.GetInstances(String machineName) --- End of inner exception stack trace --- at ReportServicesConfigUI.WMIProvider.RSInstances.GetInstances(String machineName) at ReportServicesConfigUI.WMIProvider.RSInstances.GetInstance(String machineName, String instanceName) at ReportServicesConfigUI.Panels.ClusterManagementPanel.ConfigureWebFarm(Object sender, RSReportServerInfo[] rsInfos)
I have spent hours searching for ideas, but so far no success. I have also tried the following:
1. Enabling DTC access 2. Ensuring RPC service is running on both web and db servers 3. Installing WMI Windows Installer Provider 4. Granting admin rights on both db and web servers to the account under which Reporting Services is running
Any help or suggestions would be greatly appreciated. It looks like this should be a straight-forward task.
Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented €œDBCC FLUSHPROCINDB (@dbid)€?? Is there a workaround for €œdbo€? to be able to flush procedure and data cache without being elevated to €œsysadmin€? server role?
PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans. Thx.
We are planning to upgrade the SQL Server in our production environment from SQL Server 2000 to SQL Server 2005. This is a 4 Node cluster environment with 3 Databases on 3 Virtual instances. The main requirement is to achieve this with no/minimal downtime.
Could you please suggest or direct me to any documentation for the best practices used to upgrade such an environment?
We're upgrading a SQL Server 2000 cluster (Active/Passive) running on Windows 2000 Server to a SQL Server 2005 Cluster running on Windows Server 2003. We can't purchase new hardware and we have no spare hardware. We also need to move from Windows 2000 Server to Windows 2003 Server at the same time. We want to keep downtime to a bare minimum.
What we were thinking was the following steps... Anyone try this?
1. Break the link between the servers.
2. Install a fresh copy of windows 2003 server on one side along with SQL Server 2005. While this step is running, the active node would still be live on Windows 2000 Server and SQL Server 2000 serving our customers.
3. Restore a copy of a backup from the active production side to the node we're upgrading and at that point we would bring the active node down, switching the active node to be the newly upgraded server.
4. As a final step, the old active node would now have the link to it broken, we would install a fresh copy of windows 2003 server on it and sql server 2005. At this point we would bring it back into the cluster and the cluster would be complete again.
Need your help and guidence for doing upgrading SQL Server 2000 Cluster to SQL Server 2005 Cluster.
Let me explain my current environment.
1. Currently SQL Server 2000 Cluster environment is running on Windows 2000 Server we need to upgrade this to SQL Server 2005 on Windows 2003 Server. >>> Production environment.
My Plans:
1. On Testing Environment Install SQL Server 2000 cluster on Windows 2003 Server and do a restore of databases from the produciton environment.
2. Upgrade In-Place from SQL Server 2000 Cluster to SQL Server 2005 Cluster.
My doubts
1. Can i install SQL Server 2000 Cluster on Windows 2003 Server. Is it possible or not.
Hi everyone, Anyone know how SQLDependency works in a web farm setup? Which machine will receive the notification - All of them or just the machine that started the dependency?Marc
Hi,I have over 200 stored procedures and they have dependencies on each other (not necessarily a cyclic dependency).Is there a way in SQL Server 2005 that I can disable a dependency check while I am executing a script that creates those stored procedures, and then re-enable it after?Thanks.
Probably a dumb question, but if one is using SQL Server 2000 instead of 'SQL Express' what has to change here, in order to set up caching? What changes in the paths?
I want find all dependent objects related to a table. I am using -
SELECT DISTINCT so.name INTO #tmp FROM dbo.sysobjects so JOIN dbo.SysComments sc ON sc.id = so.id WHERE sc.text LIKE '%tablename%'But, I want all those SP/functions/views that use the output of this query and so on...
Eg. Table -> used in usp1 -> usp1 used in usp2 ...and so on
I have a service that depends on SQL Server service. Basically we make sure that our service only starts after SQL server service started. Unfortunately this dependency does not ensure that database is available.
Basically I observed in Application log that SQL server reports that it started to listen on a port. And then I see that recovery process started. As I understand SQL server is not available while database is in the recovery state.
Note: I might have not used a correct terminology to describe SQL server recovery process as I don't have access to Application log with the exact message at the moment. I can verify the message if necessary.
Because SQL server service is started our service starts and attempts to connect to the database. Service fails to connect to database, because of the restoration process. Once restoration is done service connects successfully.
I observed that restoration process starts every time machine is rebooted. It only takes a few minutes to run, but it is enough to generate a number of error messages in event log about failed SQL connections.
My first question is: Is it normal for database to enter into recovery state every time machine is rebooted?
My second question is: If it is a normal operation, is there any way to detect that SQL server is truly available, so our code does not have to try retry establishing connection many times
Our topology: Internal corporate network domain utilizing Integrated Windows Authentication between local workstations and the web and database servers. (domain similiar to: http://reports.ourdomain.com)
Machine #1: Windows 2003 Server w/SQL Server 2005 (SP2 and hotfixes applied) running SSRS. Machine #2: Windows 2003 Server w/SSRS only. (pointing to the SQL DB on Machine #1) Machine #3: Windows 2003 Server w/SSRS only. (pointing to the SQL DB on Machine #1) Device #1: Arrowpoint load balancer for Machines #2 & #3 (w/sticky sessions).
We recently added 2 web servers and configured them to load balance the web requests to a seperate SQL Server machine. (the load balancing is done thru an Arrowpoint device not Microsoft NLB)
The installation of reporting services (on Machine #2 & #3) went smoothly and the configuration was a breeze. But we could not access the "http://reports.ourdomain.com/Reports" directory for reporting services on the 2 web server machines. We received the "401.1 - Unauthorized" http error.
Since we were using a domain name to access the report server we made sure to update the <URL> tag in the config files.
We still received the error, so then we added a couple of entries to the HOSTS file on each web server: File location --> C:WINDOWSsystem32driversetcHOSTS
In order for the load balancers to "see" themselves from the domain reference we had to update the HOSTS file on each web server to have the domain name "reports.ourdomain.com" point to the IP Address of the specific web server. (we also added the IP of Machine #1 just to be safe):
Then the web servers were able to authenticate the users using IWA and NTLM and we could access the report servers normally. Even the the scheduled reports were being load balanced and emailed from the web servers which we didn't expect to happen. (pretty cool!)
I hope this helps someone else as it tooks us about 4 days to figure this out.
I have to install several AG on a cluster for a whole SharePoint production farm, it's a nice to have (1 AG for Search database, 1 AG for Profil database, 1 AG for Content database, 1 for applicatif, etc...) We have 2 Front End load balanced, 1 application server and 3 SQL HADR: 2 nodes on synchrone (sql 2014 enterprise licence) and the 3rd asynchrone (standard licence).
The SharePoint farm is configured with the SQL Alias on the primary node, then if there is a failover on node 1, I must move the sql Alias to the second node manualy, unless I'd configured this Alias to point on the AG listener of the availability group in order to use the automatic failover. But as we have several AG, should I create one listener per each AG?
And which AG listener should I have to configure on the SQL Alias then ?
Dear, I have designed a SiteMap provider for SQL Server. But I am worried about the notification service of SQL Server. My SiteMap requires service broker enabled in SQL Server. When I have web farm (multiple clustered server) does the Sql Server service broker notify all the web servers? Or, it notifies only the server request? Have anubody implement such scenario? Looking forward for your reply. Sincere Regards,Sultan
In our current project, we are attempting to use Broker for the middleware for a "queuing" solution that helps in throttling & load-balancing incoming messages. These messages could potentially be either long-running jobs or simply an incoming flood of messages received from our integration web services that are called upon via BizTalk.
For the posting of messages onto the "to do" queue, we are implementing the "fire & forget" pattern that has been discussed here previously and on Remus' blog. This aspect of the design seems to work fine for us. Currently we have this setup where activation is occuring on the target queue, but we don't want to hold open a thread there on the server while waiting for our synchronous calls to various web-services on the processing "farm" to complete.
The next evolution of our design is to try and move activation from off of the primary SQL cluster itself (i.e. activation is currently happening on the clustered SQL boxes) onto the individual processing nodes. For this model, we are looking at using SQL Express on each of the nodes as has been suggested here on the forums for other similar scenarios.
For resiliance to any node failures, we do not want to "route" the messages to the individual nodes hosting their own queues but rather have those nodes do a "read" from the primary queue and do the per-message processing and performing either a committed EndDialog of EndDialogWithError based on the success of processing each task/message.
To invoke the processing on each of the nodes, we need some form of mechanism to send a "wake up" and do the "reads" since no message is being sent to the node itself for any form of activation based on a queue that receives the actual "job". On the same hand, we are considering having a "wake up" queue on the nodes where a simple "wake up" message could be sent to all nodes/queues and then activation on those queues would then invoke the processing "loop" for each node.
My question is how to best establish this "wake up" call to each node. I think I've read about a queue that is internal to Broker itself that receives a message when new items are received in any queue. My initial thought is to put activation on that queue and have a procedure that sends the "wake up" to each of the nodes in our processing farm.
I am looking for any input where others have attempted to solve this type of problem with Broker.
I am creating a report on a database where some of the table names start with the @ sign - ie @table1.
Reporting services picks this up as a parameter, instead of a table name in my query, even though I am encapsulating the table name in square brackets eg. [@table1]
I have several data sets in the report that i am using to populate valid parameters. These datasets are all variations of queries from tables that have @ as the first character.
When i then try to run the report i get an error message as follows: "The report parameter pool has a default value or a valid value that depends on the report parameter SD_POOLCONTRACTS. Forward dependencies are not valid"
This is frustrating as SD_POOLCONTRACTS is not a report parameter but one of the database tables that has @ for it's first character.
here is the query that i use to obtain the valid values for the pool report parameter that i am trying to set up.
SELECT distinct u_poolcode as Pool FROM OCRD INNER JOIN OCRG ON OCRD.GroupCode = OCRG.GroupCode INNER JOIN CRD1 ON OCRD.CardCode = CRD1.CardCode inner JOIN [@SD_POOLCONTRACT] INNER JOIN [@SD_POOLCONTRCT_LINE] ON [@SD_POOLCONTRACT].DocEntry = [@SD_POOLCONTRCT_LINE].DocEntry INNER JOIN [@SD_CONTRACTS] ON [@SD_POOLCONTRCT_LINE].U_DocNo = [@SD_CONTRACTS].DocNum ON case when len(ocrd.fathercard) = 0 then ocrd.cardcode else ISNULL(OCRD.FatherCard, OCRD.CardCode) end = [@SD_CONTRACTS].U_CardCode WHERE (OCRD.CardType = 'c') AND (OCRG.GroupName LIKE N'producer%') AND (CRD1.AdresType = 'b') ORDER BY OCRG.GroupName, FatherCard, OCRD.CardCode
I am trying to upgrade a SQL Server 6.5(Cluster) to SQL Serevr 7.0 (Cluster)..I already have an intsllation of 7.0(On a Cluster),so this means that 6.5 and sql 7 are on seperate cluster's ,if i try to upgrade from 6.5 Cluster to 7.0 Cluster is asks me to uncluster 6.5 and 7.0 is this correct ,assume i cannot break the cluster then what???.. what is the best way i can achieve this functinality.....
We are using RS2005 for a year now and never had any problems especially with mail subscriptions.
Since we transferred from single RS machine to web farm everything works ok except mail subscriptions. Subscription sometimes (?!?!?!?!) refuse to export report to a document of any kind. I repeat that this is a random thing which is more often with PDF and less with Web Archive or Excel. We are using shared schedules and on demand execution of AddEvent stored procedure by night batches to send mail to users. Also, original error from RS log says something that this kind of operation is not supported for server working in native mode. We don't have SharePoint so we never used it in any other way then native mode and it worked.
Any idea will be appreciated!
Thanks in advance.
Update 26.06.2007
It seems that problem is not loading dll used to export report to a file but loading email subscription extension (dll that needs to be loaded for execution of email subscription.)
Original error from RS Log:
w3wp!library!1!06/22/2007-11:06:43:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: The operation is not supported on a report server that runs in native mode., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: The operation is not supported on a report server that runs in native mode. w3wp!extensionfactory!1!06/22/2007-11:06:43:: e ERROR: Exception caught instantiating Report Server DocumentLibrary report server extension: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.TypeInitializationException: The type initializer for 'Microsoft.ReportingServices.SharePoint.SharePointDeliveryExtension.DocumentLibraryProvider' threw an exception. ---> Microsoft.ReportingServices.Diagnostics.Utilities.OperationNotSupportedNativeModeException: The operation is not supported on a report server that runs in native mode. --- End of inner exception stack trace --- --- End of inner exception stack trace --- at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck) at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache) at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache) at System.Activator.CreateInstance(Type type, Boolean nonPublic) at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes) at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.CreateExtensionObject(Extension extConfig).
I'm very confused about which dbms to use. I've two choices: MS SQL Server 2000 and Intersystems Cache. Although Cache is post relational I don't know anything about it. However it has object oriented features, I know how to do things in SQL Server. Can anyone give me the advantages and disadvantages of both systems in this situation? Thanks in advance.
