Multi-server Environment
Oct 1, 2007
We are currently managing about 75 SQL Server instances. Each instance contains jobs (backups, index work, admin, etc) which report back to a central instance for monitoring. This is working well, but each time I need to change one of the jobs, I am having to log into each instance to do so.
I have recently played with Multi-server Environment, using a master and target, to see if this might help. There is relatively little written about Multi-server in books-online, and I am left with a question.
All jobs appear to be exact duplicates. But I don't want exact duplicates. For example, on the various target servers, I would like for the backup jobs to run at different times, and write to different directories. How are the rest of you working around this situation, if you are using Multi-server Environment?
View 1 Replies
ADVERTISEMENT
Sep 23, 2014
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
View 1 Replies
View Related
Jan 6, 2004
My company uses a quad processor server connected to a SAN to load and summarize detail sales information from 2000 stores on a nightly basis. We poll and load around 5,000,000 rows of data each night. THis information is summarized up to various levels, then replicated to one or more secondary datamart servers for end user access via web reporting, BI tools like Proclarity/Analysis Services etc...
The initial data polling server is only touched by the development staff supporting the process (1-5 programmers) and is licensed for SQL server Enterprise using a CAL model. Each datamart server is licensed with MS SQL server processor licenses.
The question: We were told that the quad processor polling machine, which has no end user access allowed, must be licensed with processor licenses since it touchs the data ultimately consumed by end users. This makes no sense to me.
The Microsoft white papers discussing multi_tier environments don't seem to address this type of issue. They focus on applications that ultimately pass thru a data request to the SQL server machine. In this situation, user requests are handled by the datamart servers, which are licensed with processor SQL licenses.
Can anyone clarify?
Thanks, Mike
View 1 Replies
View Related
Jan 20, 2007
I was wondering if you guys might give me some advice on how best to handle a particular scenario i'm struggling with.
I have a client that basically wants web-based-update access to their sql server database. Specifically, for a group of users to be able to access a page where they select a record for editing. the caveat is that no two users should be able to pull up the same record at the same time. Originally I would have thought there was some easier record-locking-mechanism I could exploit within sql server or ado.net itself, but I haven't been able to come up with anything.. so this is my current approach:
The page they use starts-out with basically a blank form. there are custom-built paging controls at the bottom of the screen. they click page-forward to begin and a stored procedure is ran to select a record and update a field on that record to indicate "in-process". when they finish editing the record - or page on to the next record without updating - another stored procedure is ran - updating/resetting the status field on the record appropriately.
The entire page is encapsulated within an ajax.net updatepanel.
The entire page has caching disabled. This works well in conjunction with the first page being blank. if they get out of the app and try to get back in by clicking the back button - all they can do is get to the first (blank) page.
A piece of javascript window.onunload clicks a button on the page that releases the record they currently have selected in the event of a re-direct, clicking back, etc.. it appears to work with everything except a window close. in that case, i have a stored procedure running periodically on the server that checks how long a record has been selected - and if it exceeds the time indicated - resets the record as to allow it to be re-selected later.
In the event of session timeout, they are redirected to another page that tells them their session has timed-out (and since the window.onunload fires - it takes care of releasing the record if they have one on the screen).
The concept seemed to be working well until I started multi-user testing. Now it seems as if two users time it perfectly - they are actually able to both select the same record. it happens pretty rarely, but it does seem to happen. I'm guessing this has to do with how my stored procedure is structured - possibly allowing a tiny-enough window between the record being selected for editing - and the update running to actually status the record as in-process (2 separate sql statements within the one stored procedure).
I believe I also have a found a second quirk in my approach where something is causing the window.onunload event to fire twice in some strange situations.. but that's more annoying/confusing from a logging standpoint than anything..
I've read where people say to ensure you dont update a record that's already been updated - that you should compare the fields before you actually perform the update and ensure they haven't changed since you selected it.. but to me that doesn't solve anything.. if two people select the same record and both spend time working on it - the person that tries to update last has just wasted their time.
I've also toyed with the idea of maintaining a separate table in the database to hold the keys to the currently selected records and use that to keep multiple people from selecting the same record - but honestly i dont know if that approach is any better than what i'm doing now.
anyway, I was just curious if you guys had any advice in regards to how you'd handle a request like this.. or if you see any obvious problems/fixes with my current approach..
I would greatly appreciate any info you could provide-
thanks-
View 3 Replies
View Related
Aug 17, 2015
I am setting up sharepoint and sql server integration environment. I am considering the following topology: PowerPivot for SharePoint 2013 and Reporting Services in SharePoint mode Two Server Deployment
[URL]
I am looking to follow the topology example by the letter, which involves installing PowerPivot for SharePoint (aka SSAS in SharePoint mode) in the same server as my SQL, and installing SSRS in SharePoint (SP) integrated mode in the same server as SharePoint.
I understand, however, that if I wanted to install SSRS in SP mode in the same server as SQL, I could but only if the server contains the SP Object Model.
My first question is, what would involve having the SP Object Model in the SQL Server?
Would only installing SP binaries be enough; or Do I need to do a minimal install of SP in the SQL server enough for it to joing the SP farm? And most importantly, what would be the licensing implications for SP in case I want proceed down this route and have SSRS in SharePoint mode installed in the same server as the SQL?
View 4 Replies
View Related
Nov 27, 2006
I am currently working on a project in which I am using SqlTransaction objects with their IsolationLevel set to Serializable. I am wondering what would be the effect if multiple threads were to call this code at the same time? Would the second transaction (created by the second thread on the same connection), for example, be queued until the first one (created by the first thread on the same connection) finishes? Or would an exception be thrown for the second transaction? In case an exception is thrown, would using the synchronization (locks) around critical sections solve the issue?
I guess it is easier to understand the scope of transactions in terms of multiple users, but it is confusing (for me) when I start thinking in terms of multiple threads. The underlying data is held in a single table and the scope of the transaction spans multiple SELECTs and an UPDATE or an INSERT.
I have been trying to find a feasible answer, and haven't found anything credible yet. Help!
View 10 Replies
View Related
Mar 27, 2007
My product is growing rapidly and currently I have a db for each client with identical schema. Of course maintenance is pretty hard. I was thinking of using a shared db but having a schema for each client (sql 2005) - I have almost 100 tables in the schema which means with just 10 clients the db would pass 1000 tables. My gut is telling me this ain't going to fly!any ideas? and if it does work ... any thoughts on updating the internal schemas for each client?thanks-c
View 2 Replies
View Related
Feb 6, 2004
Hi,
I am developing reporting application (access project) which will be used in multi user environment.
Here is what I have:
1 SQLServer database for many users
Each report will be based on:
stored procedure which creates a table filtered for specific dates predefined views will use the newly generated table to show results to the client. However, if more than 1 person runs reports results will not be accurate if each person specified different dates because they will look at the same table and results will match only for a user who called the stored procedure last.
what can you recommend - how to report in multi user environment?
Many thanks
View 2 Replies
View Related
Apr 19, 2007
Hi!
In my database I have all business logic in stored procedures. For example there are procedures: ReadBike and UpdateBike. Bike is business object stored in 4 tables.
On my system work 100 employees and we have one problem with this. The stroy is;
1. User A reads data about Bike1
2. User B reads data about Bike1
3. User A updates data about Bike1 (user B have old data)
4. User B updates data about Bike1
So user B don't know about chnages made by user A. How to solve that ptroblem?
It's probably solved in ADO, but I want use business login in procedures.
Regards,
Walter
View 1 Replies
View Related
Oct 19, 2006
Hello--
Is it possible to install/configure SQL-Server 2005 on a multi-processor machine so that the relational DB utilizes a given subset of processors while Analysis Services utilizes another subset?
Thanks,
- Paul
View 4 Replies
View Related
Nov 16, 2005
I have finished a change request from our client. I need to update clients' database with the one in developments.Here is the changes i made to database:Added/Changed some tablesAdded/Changed some stored proceduresAdded data to some dictionary tableThe data in clients' current database MUST be kept. So how can I merge the changed information to clients' database?
View 3 Replies
View Related
Mar 27, 2007
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
Thanks,
Michael
View 5 Replies
View Related
Dec 5, 2006
Hi,
I need to connect to a SQL server thats running in say abc.trident.com and also sits in a shared environment..
I have couple of questions
1) That SQL server is accessible from my network, yet when I swtich on my enterprise manager I am unable to view that in the list of running SQL servers in the populated list.
Is it because its sitting in a shared environment I am unable to view that?
2) What is the connection striing I should use to connect to the server..When I try to configure a SQL server registrations it normally asks for SQL Server name along with the user authentication
Should I mention fill the Server: field as
abc.trident.com
or
// abc.trident.com/abc.trident.com
to connect to the clustered server.
Can some one tell me the proper connection string if both aforementioned ones are incorrect?
Thanks in Advance
View 5 Replies
View Related
Aug 18, 2005
I'd like to allow students in a lab to use the Express Manager and T-SQL to create their own databases. I think I've figured out just about everything - but the last step. Here's my thinking on how to do this:
1. Students are power users, not local admin
2. Created a login for the builtinpower users group as follows:
create login [BUILTINPower Users] from windows with default_database=[master]
go
exec master..sp_addsrvrolemember @loginame = N'BUILTINPower Users', @rolename = N'dbcreator'
go
3. Now students can start the Express Manager and connect to:
localhostsqlexpress using Windows Authentication
4. After they do, they can open and run a script creating a database and populating it with tables and records.
5. All good up to here... but when trying to access the new database using VB Express (file based, not a remote connection) access is denied.
The problem is in the NTFS file permissions being assigned to the new database mdf and ldf files. Local administrators and the builtin Network Service accounts have permissions, but the student who created the database gets nothing... So the student is denied access to the mdf and ldf files when trying to make a connection to them using VB Express.
I can go in with a different account having admin privileges are assign privileges to power users for the mdf/ldf files after they're created but this isn't what I need (students need to work whenever they want without waiting for an admin to fix the file permissions).
Maybe I'm going about this all wrong? If I have to I can insist on students always using VS 2005 instead of VB Express and use connections to a remote database (no problems doing this), but I'm still hoping for an express solution.
I'm new to this forum, so apologies if this is a dumb question (but even if it is - can anyone point me in the right direction?)
Thanks
Mike Morrison
View 1 Replies
View Related
Jan 18, 2008
Hi,
We currently have a SQL 2000 server setup with the data and log files (including those for the system) placed on a SAN. Our current server is old and starting to have problems so we are transitioning to a new server. I just wanted to see if anyone has done this before or if my plan makes sense.
1) Hook the new server into the the SAN Arrays holding the data and log files.
2) Take the old Server off-line, and bring the new one up.
3) Point the Master db to the location of its data and log files.
4) Restart the server.
In theroy, since the master "knows" where all of the other databases are located when the it starts the recovery process it should bring up all of the other databases on that server. Does this make sense or am I missing something?
Thanks in advance for any help!
~Jeff
View 1 Replies
View Related
Aug 19, 2007
I am experiencing a situation where certain functions work perfectly when I run it on my local machine under MVS, but when I upload it to the server, then it does not?!
Here is an example:
I am using a drop down box (in a FormView) that is databound in an online submission form. When I run the application in MVS, then I can edit the records by opening the form, and selection the new value in the drop down list. The new value is then also saved to the database when I hit the update button. When I upload the code to the server, then the drop down list shows the correct information (so the databinding to the control seem to work correctly), but the new value is not saved to the database.
Here is the code for the drop down list:
"DropDownList1" runat="server" DataSourceID="SqlDataSource3"DataTextField="UserName" DataValueField="UserId" SelectedValue='<%# Bind("UserId") %>'CssClass="text">"SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"SelectCommand="SELECT [UserName], [UserId] FROM [vw_aspnet_Users] ORDER BY [UserName]"> Here is the code updating the database with the record (I have removed some records as well as the Insert and Delete parts):
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tourism_connect1 %>"UpdateCommand="UPDATE Resorts SET typeid = @typeid, destrictid = @destrictid, UserId = @UserId, WHERE (resortid = @resortid)"OnInserted="SqlDataSource1_Inserted">
<UpdateParameters><asp:Parameter Name="typeid" /><asp:Parameter Name="destrictid" /><asp:Parameter Name="UserId" /><asp:Parameter Name="resortid" /></UpdateParameters></asp:SqlDataSource>
What I can mention further, is that if I connect to the database that is on the live server (so the application runs in MVS on my local machine, but it then retrieves the info from the online database), then it also works fine. It is just giving this issue when the online application is trying to update the values.
There is also no errors during the process.
I will appreciate any advise on how to overcome this, as I really do not know where to look anymore...
Thanks in advance!
Regards
Jan
View 3 Replies
View Related
May 26, 2004
I've been asked to look into the possibility of using SQL Server in a high availability environment. We have a few web based applications that use SQL Server back end DBs. What we are looking into is whether we can use multiple instances (on multiple physical servers) of SQL Server using some type of clustering/load balancing. I haven't worked with SQL Replication before, so I'm not even sure where to start in exploring the possible avenues we can explore.
Can anyone push me in the right direction? Any info would be greatly appreciated!
Thanks,
Bill
View 5 Replies
View Related
Jan 7, 2006
Hi all,I have posted the same topic under ASP-NET-Developers but had no reply;I then posted it under ASP.Net Community and had also no reply.Hence, i'm posting it here to prevent the impression of spam post ...I have created a SqlContextTrigger using ADO.NET 2.0 to create atrigger onSQL Server 2005.In the class, I create a file and specify its path to be in theApplicationDomain base directory such as:String Path = AppDomain.CurrentDomain.BaseDirectory.ToString()However, the path is always the SQL Server Binn Directory. I'd like totellthe program that I want the path to be related not to SQL Server BinnDirectory but to Visual Studio Solution's Project Bin Directory.How do I do that?Best regards
View 1 Replies
View Related
Oct 9, 2006
Hi,Please confirm.. is it possible to connect to MSSQL server by executinga shell script on HP-UX (UNIX) platform.Regards,Uncle Sam
View 3 Replies
View Related
Mar 12, 2007
We have a combined network with some users on the old Windows NT 4.0 network and others on the new Windows 2003 servers using Active Directory. Will my users on the old WinNT4.0 domain have issues accessing / running reports from Report Manager?
View 1 Replies
View Related
Oct 4, 2007
I am looking at combining / consolidating some of our servers and was wondering if I can install SQL 2000 and SQL 2005 in a clustered Environment on a san.
Cheers
Angie
View 6 Replies
View Related
Aug 9, 2006
We are running 2 SQL Server and both run in failover clustered Environment.
The Problem is now we need to Replicate a Database from one Virtual SQL Sever to the other.
The Second one (clusterd environment)is stroing their database localy while the First One (clustered environment)is storing database in a shared storage.
Note that Both Server are used for sperate purpose , but we now need to set replication on the Other Clustered Setup for Reporting Purpose.
Will it work if we configure replication from One SQL Server Clustered Setup to the Other Clstered Setup.
If yes, then please let me know how it can be done ?
Thanks
View 1 Replies
View Related
Aug 24, 2005
I have looked all over the MS site and can find very little information regarding SQL Server's support for the VMWare environment. Based on other information that I have obtained on other forums and web-site it looks like it is not supported by Microsoft, but I would like the company line if possible.
View 10 Replies
View Related
Aug 17, 2015
More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all?
2) Should DTC be in its own resource group or within the instance's group?
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
View 9 Replies
View Related
Feb 4, 2008
the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
View 2 Replies
View Related
Aug 11, 2006
I am new to database.
I have a website in a hosting environment and it uses sql server 2005 databases. I want to have a complete backup of my database in addition to the hosting company's routine backup. I want to write a program to automatically backup everything every night. And of couse have the ability to restored the whole database if necessary.
How should I do it? Are there any tools exist that I can use? What is the best way to deal with backup/restore in hosting environment?
Thanks!
View 1 Replies
View Related
Oct 10, 2001
Hello,
Yesterday, our server 'failed over' to the passive node in our clustered environment. We are running Windows NT, with MSSQL 7.0. What triggered the fail over, according to the logs, was an error in tempdb. Basically, the error said that tempdb could not be expanded, (could not create any more space for tempdb).
The drive that tempdb database is located on has more than enough room. The tempdb is set at 300M, and can grow to over 700M. The drive that tempdb is on
has over 19Gig of space on it.
One other important piece of information is that the drive where our production database is on and the tempdb is quite full. The 19 gig that is left over represents only about 5 percent of the disk.
Can anyone shed some light on why the sql server would have failed over when trying to expand tempdb, when there was sufficient resources to handle that growth? Am I barking up the wrong tree? Could this be another error? When we restarted the server (both nodes and the disk array) the production database came back as suspect. Most of the stuff in BOL points to the space issue. The production database is around 37 gig.
Any help would be appreaciated
thanks,
jim
View 1 Replies
View Related
Aug 23, 2007
I am creating a backup strategy for our non-production SQL Server 2005 data. We wish to utiliza the Master/Target Server scenario avaible in SQL Server 2005 with events directed to the Master Server. The question I have is what is the symptoms from the Target Servers if the Master Server fails and is unavailable for whatever reason there might be? I can change the event notification back to local server, but what other impact is there and are there any recommendations as to handle a Master Server outage so Maintenance plans can continue to execute as scheduled on Target Servers.
Thank you!
View 4 Replies
View Related
Apr 24, 2015
Need to set up a linked server in two node SQL Cluster pointing to a standalone SQL thru security setting "be made using current login's security context". It's double hop Kerberos setup. Cluster uses a domain account, so we manually set SPNs for that account with both instance name and protocol as well (set up these SPNs with SQL virtual name only).
Also, constrained delegation has been set to that stand alone SQL services (MSSQLSvc).
Both SQL cluster and standalone servers are in same domain, cluster service account is domain account and standalone SQL uses default SQL local service account.
Coming to SQL, when I create linked server, sometimes it lets me create without issues and sometime it throws this below warning and even if I create it won't work.
Login failed for user 'NT AUTHORITY/ANANOMOUS LOGIN'.
View 6 Replies
View Related
Jan 31, 2008
Hi all,
I must be missing something obvious. I'm not clear on how SQL Express can provide a decent development experience. Consider:
1. I have a class library that includes an express database file, set to always copy to output folder.
2. I have code that attaches to the database in the output folder at runtime.
As a result of the above, the first execution after restarting SQL Server will work. Subsequent builds will fail because SQL Server has a lock on the database file in the output folder. Or one of the two files will be copied (DB and log) and therefore they will be out of sync and detected as corrupt when the code attempts to connect. Either way, I have to restart SQL Server and rebuild before the DB connectivity will work again.
I figured I might need to detach from the DB in my shutdown code, but I cannot execute sp_detach_db from my code because it cannot be done through a connection to the database (database is in use). Moreover, I'm not sure I want to because the user might execute multiple instances of the application.
What am I missing here? Do I need to put steps in my build script to stop and start the local SQL Server instance?
Thanks for any help,
Kent
View 7 Replies
View Related
Jul 18, 2007
Hi,
We have a suite of applications which go through the full product life cycle and I was wondering which SQL Server edition should be installed in every each environment from Proof of Concept -> Dev -> -> UAT -> Systest...
Production is straight forward but I am just not sure about all the other environments.
thanks
Lyn
View 3 Replies
View Related
Feb 7, 2008
Greetings and warm thanks to those of you that have been so helpful on this forum.
We recently started using SQK 2005 SSIS. Using Business Intelligence Studio (Nice Job Microsoft!!) we created and tested packages on a developer workstation using a local instance of SQL Server.
Now we want to run these packages on the development server. You'd think this would be as easy as publishing a web page using ftp but we are stuck!
What pieces do you need on the server to run the packages and where do you find them? We have the super-duper MSDN premium, planning on purchasing SQL Server Enterprise Edition once we move our project into the production arena.
Thanks very much we are all scratching our heads today on this.
View 3 Replies
View Related
Apr 24, 2006
I have to spend an extra money on hardware if my PC hardware environment for installing SQL Server 2005 is not enough. Please tell me about it. It might be a RAM requirement? I don't know.
View 1 Replies
View Related
