SQL 2012 :: Disaster Recovery Options For Multi-Database Multi-Instance Environment
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
ADVERTISEMENT
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
Sep 27, 2015
Would it be possible to get a multi-database functionality on the same instance option available in SSMS anytime soon?
View 6 Replies
View Related
Jun 25, 2014
We have SQL Server 2012 running on Windows 2008 Server. We need to copy five databases from our 'sandbox' to our test server and then to our production server. The database backup file sizes are 3 MB, 20 MB, 344 MB, 645 MB and 17 GB. We are planning on using the backup and restore method since we already have full backups and the scripts to recreate the logins/users/permissions. We believe this method provides more flexibility and control over the process. However, we have a few jobs, maintenance plans and ssis packages.
To get the jobs to the new server instance, the plan is to script out the jobs on the 'sandbox' and execute the scripts on the test server instance. Is this the best or only way to handle the jobs?How to get these maintenance plans to the new server instance? (There is no 'script out' maintenance plan option.) We may have to just recreate them on the new server instance. Is this the best or only way to handle the maintenance plans?
We have a few ssis packages. How to handle getting the ssis packages over to the new server instance (using the backup restore method)? These packages use the Project Deployment Model. Therefore, should we restore the SSISDB or open up the package file using VSS on the new server instance and change the connection information to point to the new server instance.
Just wondering if there is any reason or advantage to use the Detach and Attach method or Copy Database Wizard method? I have read where the Copy Database Wizard method handles the database's dependent objects like logins, jobs, maintenance plans, user-defined error messages and shared objects from the master database. Are there any other move/copy database methods to consider? Just trying to make sure we have thought out everything and are using the best method to copy our databases over to another server instance.
View 3 Replies
View Related
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
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
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
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
May 14, 2015
I have a multivalue parameter that has X options. Is it possible to implement a solution that when I go into the report through an action, to have one option (in this case is the first) not selected all all the others are checked?
View 4 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
Feb 13, 2007
Hi All
I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.
Many Thanks
Delli
View 4 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
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
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
Jun 28, 2005
Using Management Studio (April CTP), I tried to connect to integration services. There was no server listed, so I added my computer jaargeroxpctp and this went fine.
View 3 Replies
View Related
Nov 16, 2015
I have setup a WSFC that contains 3 nodes ( node 1,2 belongs to subnet A and node3 to subnet B), lets say node 1 is the primary host in the wsfc.
Each node has their own sql server instance, that i configure an Availability Group that will replicate between node1 and node 3), created an Availability Group Listener that will contain 2 vips for each subnet.
My question here and I am not sure if i missed something when setting up the Availability Group. If i failover an Availability group from sql server from node 1 subnet a to nde 3 subnet b, then the listener will be on the vip that belongs to subnet b and the primary instance will be the one in node 3, but this wont cause the WSFC to move the primary host from node 1 to node 3.
So at this point dont see what is the dependency between setting up the AG with WSFC. when fail over an AG doesn't cause the primary host in wsfc to fail over as well.
Why availability group relies on WSFC and how can i make it to depend so the primary host moves as well?
View 5 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 22, 2007
What is the effect of deploying Multi-Server instance on performance?
View 3 Replies
View Related
Nov 1, 2006
I have figured out that SSIS installs once for as many instances of SQL Server 2005 as there are on the machine. I also figured out that even if there is only a named instance on the machine, it will always install under the "Default" machine named instance.
I also figured out that you can change the instance viewed by modifying C:Program FilesMicrosoft SQL Server90DTSBinnMsDtsSrvr.ini.xml
What I am unclear about is that in SQL Server 2000, each Instance had an MSDB db which stored all of the jobs. In SQL Server 2005, each instance still has an MSDB DB but theSSIS also has a MSDB DB ?? or is this pointing to the MSDB for the instance specified in the xml file???
There is not much info out on this at all and would be good if the MSDB help explained this install once so it would all be clear!
THanks for any help... thank goodness for newsgroups!
View 6 Replies
View Related
Nov 17, 2015
OS - Windows 2012 R2 Standard Edition.
DB - SQL 2012 Enterprise edition
Total 3 nodes participates for AO setup, 2 Nodes for Local HA and 1 Node for another datacenter for DR. All the 3 Nodes are same domain name and member.
1. Local First 2 Nodes are same subnet XXX.XX.44.XX
2. DR Node another subnet XXX.XX.128.XX
Does it require to add two different IP address while creating cluster name? Not using shared disk SAN storage etc.. I am using Node majority quorum witness setting for failover.
View 10 Replies
View Related
Apr 27, 2015
We have two locations in US, I am thinking of having 2 node SQL cluster for Lync 2010, I alardy have One DB server running in one location, now we got new site where we are planning to have one more DB for redundancy.
View 5 Replies
View Related
Jul 7, 2001
SQL7
If I have a job that runs every minute, and that job happens to take more than one minute to run on occassion, will SQL Server start a second instance of the job or will it be serial - one after another ?
Thanks,
Craig
View 2 Replies
View Related
Mar 19, 2008
I understand most of the concepts for the Active/active setup. I am setting up a 2 node cluster. My question is can I use the default instance name (one default instance name per group) when setting up each instance or do I have to use named instances . I am assuming that since each group is a virtual machine that I can get away with using the default instance, but I cant find any documentation to confirm my frequently wrong perceptions.
I want to set it up like this
Instance1 Group
Group resources
Default SQL Instance Name Installation
Preferred Owner Node1
Instance2 Group
Group resources
Default SQL Instance Name Installation
Preferred Owner Node2
Thanks
View 4 Replies
View Related
Jul 20, 2005
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
View 5 Replies
View Related
Apr 26, 2015
I have 2 SQL server installs for 2008 R2 configured as multi instances. I have a product called Esri ArcMap 10.3 that can be used to generate a database. When I run the wizard against one installation, the wizard successfully creates the database. When I then run the same against the other installation it fails with the following error [Microsoft][SQL Server Native Client 10.0]Invalid cursor state
I've attempted to look at the configuration of each using
select *
from master.sys.configurations
From this I found several differences
Successful Mulit instance
Optimize for Ad hoc Workloads – False
Max Degree of Parallelism - 0
UnSuccessful Multi instance
Optimize for Ad hoc Workloads – True
Max Degree of Parallelism - 4
I attempted to co-ordinate the differences running the wizard for each iteration but it always failed with the same error above. The error always seems to occur when a particular store procedure is run. There are quite a number of scripts run prior to this and are technically under the covers and only discovered via tracing, in this case using SQL Profiler. I don't have access to individual scripts that I can run incrementally to replicate the issue. I have to rely only on the Esri Wizard.
Reviewing the error against several forums suggests that this is an ODBC error but the trace I ran using SQL Profiler finds that the driver used is Native.
My question then is "What are the conditions that would cause this error above (Invalid Cursor)?" "Is there other configuration settings that are not captured via the SQL identified above?" "Could this be caused by mapped drives for data, Logs and Temp?"
View 2 Replies
View Related
Feb 1, 2007
Hi,
I've went throught the SP4 documentation but I didn't find a clear answer about how to deal with this situation:
In the situation of a multi instances server in which all instances are SP3a, can I just upgrade a couple of instances to SP4 and leave the others in SP3a ? .
One of the instances I may have to leave in SP3a belongs to a merge replication environnement (Distributor and Publisher), while the Subscriber is also in SP3 on a remote server (which I don't administer..).(the point is that in this situation I have to upgrade the 2 servers simultaneously)
In other words, when I 'll apply the SP4 to just one instance, I understood that the all the tools will be upgraded to SP4.
Will the merge replication involving a SP3 instance on the same server still work?
Thanks for your comments/replies/ideas.
Regards,
jalal
View 3 Replies
View Related
Jun 8, 2007
Hi All
I am using SQL Server 2005 with SP2. I have multi select parameter in the report. In SP2 reporting services gives Select All option in the drop down.
Is there any way I can remove that option from the list?
Thanks
View 4 Replies
View Related
Aug 27, 2015
Is it possible to install 2 SSAS instance (one default and one named) and have them access a single sql server instance (default) all on the same server?
View 3 Replies
View Related
Oct 11, 2006
hello we want to evaluate TS foundation server but it says it won't support developer edition sql, only sql2k5 stanard. But now we have DEVELOPER version on our development sql server, DEVDATA1, i.e. instances DEVDATA1DEV, and DEVDATA1QA are both installed on the same sql server box with both SP1 applied.
How can we update DEVDATA1QA to Standard version while still have SP1 applied? we just install Standard CD and then apply SP1 again?
Because this instance is our QA version, before we do something silly, if any of you did that before, any advice would be really helpful.
thanks in advance.
View 1 Replies
View Related
Aug 17, 2015
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies
View Related
Feb 10, 2015
We are running SQL Server 2012 Enterprise on Windows 2012 R2. We have set up A WSFC with the primary Node in our East Coast Data Center and the Secondary Node in our West Coast Data Center each node is in a dfferent subnet. Since we have high latency between the sites we run in Asynchronous mode with manual failover only. My quesion concerns Quorum, everything I read would indicate we need a third node (odd number) (e.g. a fileshare Witness) with only the file share and the primary node having a vote...What I don't see is any need for a file share if we can only do manual failover.
View 7 Replies
View Related
