SQL 2012 :: Setup Replica For Databases For Reporting?
Jun 17, 2015
I would like to setup replica for one of the databases for reporting. The current environment is a 2 node cluster(active/passive). I would like to add a 3rd node that can server as a secondary replica. The secondary replica will be on asynchronous commit mode.
The database that needs to have alwayson setup has column level encryption enabled.
* Do I need to backup and restore the service master key on secondary server in order to have the column level encryption to work on secondary server?
* What would be preferred Quorum settings?
* What is the setting for 'readable secondary' for primary and replica db?
* What should be the setting for 'Connections in Primary Role' for primary and replica db?
* We are trying to setup without a Listner. Do I need to setup AG Listner? Can the application exclusively use the [secondary instance name].[replica DB name] without a listner?
View 2 Replies
ADVERTISEMENT
Jul 9, 2014
We have a vendor that is exposing our database via a High Availability replica. They are geographically far away from us though so we would like to extract portions of the database over to our side for our reporting /warehousing purposes. I was curious if it is possible to setup snapshot replication on a high availability group?
View 0 Replies
View Related
Jun 9, 2014
I have 2 SQL Server replicas configured on SQL Server 2012 AlwaysOn. e.g. SQL1 & SQL2.
I have configured backup job on both SQL Server with the following statement. and the job occurs every 10 minutes.
•declare @DBNAME sysname,@sqlstr varchar(500)
set @DBNAME = 'dba'
IF (sys.fn_hadr_backup_is_preferred_replica(@DBNAME)=0)
BEGIN
--Select 'This is not the preferred replica, exiting with success';
[Code] ...
I turned off SQL2 for Windows maintenance. So there is only SQL1 is online. Afterwards. I checked the backup folder and didn't see any new backup files was created after SQL2 was offline. I rerun the job. It still doesn't backup database on the Primary Replica. Then I searched on SQL Server Book online. It says
Prefer Secondary
Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default option.
According to what it says, it should backup on the Primary Replica.
View 2 Replies
View Related
Apr 14, 2015
Here are my scenarios:
We have an application with replicated environment setup on sql server 2012 . Users will have a replica on their machines and they will replicate to the master database. It has 3 subscriptions subscribed to the publications on the master db.
1) We set up a replica(which uses sql server 2012) on a machine with no sql server on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 33gigs and ldf has grown to 41 gigs. I went to sql server management studion . Right click and checked the properties of the local database. over all size is around 84 gb with little empty free space available.
2) We set up a replica(which uses sql server 2012) on a machine with sql server 2008 on it. After the initial synchronization(used replmerge tool) the mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
3) We set up a replica(which uses sql server 2012) on a machine with sql server 2012 on it. We have dropped the local database and recreated the local db and did the initial synchronization using replmerge tool. The mdf file has grown to 49 gigs and ldf has grown to 41 gigs. I went to sql server management studio , Right click and checked the properties of the local database. over all size is around 90 gb with 16 gb free space available.
Why it is allocating the space differently? This is effecting our initial replica set up times.
View 0 Replies
View Related
May 14, 2015
is there any limition in AlwayOn to add database in secondary replica?
View 2 Replies
View Related
Apr 22, 2015
In an existing ssrs 2012 report, I want to basically add the following logic to a new dataset that will be used by a new parameter called 'Inventory'.
SELECT DISTINCT Inv_number as value, Inv_name as label
FROM s.dbo.fnUser( @endYear, 0, 0, 0)
WHERE (@report is not null)
order by label,value
In this ssrs report, there are 15 reports that can automatically be generated. I basically want this new parameter called 'Inventory' to only be active when the 4th report is selected to be generated. Thus in the query I listed above, how can I change the query to only be active when the 4th report is selected?
I tried to change the where clause to (@report(4) is not null), but that did not work.
View 5 Replies
View Related
May 27, 2015
in my secondary server the database which is in restoring state , when i checked in always on dash board "This secondary database is not joined to the availability group" ,
View 3 Replies
View Related
Jul 2, 2014
I have an availability group with read only replicas, readable secondary set to yes and allow all incoming connections. I have also configured the read only routing (at least I'm pretty sure this is correct).
If I login to SSMS with a user in the sysadmin role I can view the objects in the read only replica database. If I login with a user in the public role I'm unable to get past the obvious error:
"The database databaseA is not accessbile. (ObjectExplorer)"
I've also tried adding the "ApplicationIntent=ReadOnly" option.
View 4 Replies
View Related
Jun 9, 2015
SQL is generating dump every 1 min with error
EXCEPTION_ACCESS_VIOLATION reading address 0000000000000020 at
Server is running SP2 CU2.
View 1 Replies
View Related
Oct 14, 2015
Environment:-
Windows 2012 R2,
SQL 2012 (Primary Replica)
SQL 2012 (Seondary Replica)
SQL 2012 (Secondary Replica over WAN site)
There are database replicating on three SQL servers. WAN line is having performance issue because of limited bandwidth I have to remove SQL secondary replica over WAN site temporarily and add it again later when the WAN line is upgraded with between bandwidth What is the best practice to remove secondary replica and replicating database and add later from SQL management studio without interruptions on databases?
View 6 Replies
View Related
Jun 17, 2015
I would like to setup replica for one of the databases for reporting. The current environment is a 2 node cluster(active/passive). I would like to add a 3rd node that can server as a secondary replica. The secondary replica will be on asynchronous commit mode.
The database that needs to have alwayson setup has column level encryption enabled.
Other Questions,
* Do I need to backup and restore the service master key on secondary server in order to have the column level encryption to work on secondary server?
* What would be preferred Quorum settings?
* What is the setting for 'readable secondary' for primary and replica db?
* What should be the setting for 'Connections in Primary Role' for primary and replica db?
* We are trying to setup without a Listner. Do I need to setup AG Listener? Can the application exclusively use the [secondary instance name].[replica DB name] without a listener?
View 4 Replies
View Related
May 21, 2013
I have an AlwaysOn Availability group configured between 2 nodes (Synchronous)
Automatic failover was working fine until recently
I can failover between the nodes manually but automatic failover doesn't seem to be working. In my earlier test, I would shut down the SQL Service on the primary and within seconds, the secondary replica would take over. Recently I have performed the same test and the secondary replica enters the resolving state and the DB in unavailable.
I have tried everything here: [URL] ....
The only change I made was changing the availability mode from Synchronous to Asynchronous - Could that be the cause?
View 3 Replies
View Related
Apr 11, 2014
Looking for info on ole/requirement of primary and secondary replica under SQL Server 2012 AlwaysOn.
View 4 Replies
View Related
May 6, 2014
How can we change the backup replica in Always on Cluster. I am not able to take backup because of this.
View 7 Replies
View Related
Aug 19, 2014
After failing over to the DR replica. All databases are out of sync. DR replicas were setup as async the other 2 are set up as sync. Is this by design. No data has been updated to any of these as they are test dbs. So all dbs should be the same, no data loss.
View 0 Replies
View Related
Oct 21, 2015
Trying to add a second replica to my SQL 2012 AG. for some reason i cant click the next button.
View 3 Replies
View Related
Feb 1, 2015
We have a 2 node clustered instance(SQL 2014) with 26 databases and we would like to enable alwayson for one of the databases for reporting (only one secondary and do not need high availability setup). I'm thinking if the reporting application/queries can explicitly connect to the secondary database(Instance namedatabase name) without using a listener and setup the secondary in asynchronous commit mode. Read about the REDO thread blocking due to reporting workload. How does this affect if I implement the secondary in this way.
View 3 Replies
View Related
Mar 28, 2014
What happens when an automatic failover occurs, in a two server AlwaysOn Availability Group configuration, where the secondary replica is configured as read-only?
Will it only allow read-only connections, or will it become read-write and can accept INSERT, UPDATES and DELETES when assigned the new role as Primary?
Is it correct that adding a third server/node, that just acts as passive and should be used for automatic failover, to support true HADR, would NOT need another license .. and that licenses would only be required for the previous Primary and Secondary (Read-Only) replicas?
View 3 Replies
View Related
Aug 6, 2014
I was working on a job to send me info each morning about database file free space and was noticing some odd things when looking at the log file VLFs for one of my databases in an AlwaysOn availability group.When I run DBCC LOGINFO on the secondary replica for this database, I get what I expect and most VLFs have a status of 0 (indicating the VLFs are reusable or unused). When I run DBCC LOGINFO on the primary replica, all of the VLFs have a status of 2 (active or recoverable).
Since log backups on the secondary replica in AlwaysOn still truncate the log in the primary replica, I would expect that the VLFs in the primary replica would also be mostly in a reusable or unused state. My log file sizes are the same size on each server and my backups are completing successfully. what might be causing the VLFs on the primary replica to have a status of 2 in DBCC LOGINFO when taking log backups from the secondary replica?
View 6 Replies
View Related
Sep 5, 2014
Setting up a test AlwaysOn Availability Group for one database.
However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.
Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.
I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.
View 2 Replies
View Related
May 26, 2015
We have SCCM 2012 primary site and Remote SQL 2012 server. Due to hardening and password reset we are facing reporting issue.
while we Open the SRS report in SQL server and try to edit the Report (Report Builder) we are getting following error due to which we are unable to configure Reporting Service point in the SCCM 2012 server. We created the New Reporting server database still we are getting the below error.
View 14 Replies
View Related
Mar 23, 2007
Hi, i read article on how to install the AdventureWorks on my PC, basically i downloaded the sample db from microsoft website, but after i install several times, i seems cant get to see it in my sql server 2005, please assist on this, i have been craking head for this. Thanks :)
View 1 Replies
View Related
Oct 27, 2015
I have a 2 node cluster having 4 cores each wherein having 3 instances of SQL 2008 R2 enterprise comprising of 60 databases, 20 on each instance. I need to setup mirroring for each of the databases to a secondary server having 4 cores and 3 instances.
What i understand is that in this case the mirror server will be providing max of 512 worker threads and the 60 mirror databases would consume 240 threads.
What all needs to be checked for looking into the feasibility of going ahead with a async mirror setup as mentioned above.
View 4 Replies
View Related
Jul 31, 2004
I installed and started playing around with Reporting Services and realized that you are required to run IIS on your SQLserver in order to use RS. Is this 100% true?
Please tell me there is an alternative...this is huge roadblock due to company policy.
Thanks.
View 1 Replies
View Related
Feb 3, 2007
i just installed the SQL 2005 Standard just for reviewing before installing enterprise edition on a server...with cluster.
I noticed you get path 90 and mssql.1, mssql.2, mssql.3
1...for SQL
2...for analysis olpa
3..sql reporting.
And it all goes on one server and Configures IIS.
Is it advisable to have all these servicess running on one server especially the IIS SQL Reporting and Analysis...
If i have 10 developers all doing net and cubes they could be overloading the entire Server
If anyone has this configured where all three services analysis, sql reporting working ...i like to your configuration settings .....do you have all these on one Server
View 8 Replies
View Related
Sep 10, 2007
HI, this will prob be a stupid question but i have setup reporting services and am trying to use it, however, when creating a new project i only have Visual Studio Solution under other project Solutions and not any of the other types i.e. Visual Basic Solution etc. How do i get the other types? Do i need to install anything further?
Thanks David
View 2 Replies
View Related
Mar 5, 2008
Hello Room,
I am newbie to SQL Reporting.
I want to setup SQL 2005 Reporting in window server 2003 box.
1. I installed IIS
2. Open the IE browser http://10.1.1.1/xReport. It works.
3. Bring up Reporting Services Configuration Manager
- Server Status : Running
- Report Server Virtual Directory:
- Name: ReportServer
-Website: aReport
- Report Manager Virtual Directory
- Name: Reports
- Website: aReport
- Windows Service Identity
- Service Name: ReportServer
- Service Account: LocalSystem
4. When I tried to deploy a report from MS Visual Studio (BIDS). The Reporting Services Login window pop up and it ask login id and password.
Questions:
1. Where can I find the reporting services credential login.?
2. Is the reporting services configureation manager setup correct based on MS best practice.?
3. Any advise how it should setup.?
Thanks for the help.
Regards,
TJ
View 1 Replies
View Related
Apr 14, 2008
Hi Room,
I am new to SQL reporting 2005 and wanted to setup SQL 2005 reporting on stand along server 2003 box.
SQL Server 2005 server and reporting services were installed.
Can anyone provide me some hints how to setup the sql 2005 reports.?
Any articles or links.?
Thanks for the help.
TJ
View 1 Replies
View Related
Oct 4, 2005
I need to install Reporting Services on a machine.
Wehe can I find it ? (I have SQLServer Entreprise and Dev).
Is it included in SQLserver Ent or should I get another CD ?
Thanks
View 1 Replies
View Related
Aug 15, 2006
I have posted this to the general SQL Server group but without any response. I have also searched all of the forums and have not found an answer. For some reason all of a sudden, my Reporting Services is failing with this message when trying to connect:
TITLE: Connect to Server
------------------------------
Cannot connect to UKCINTUKCAZOSQL.
------------------------------
ADDITIONAL INFORMATION:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.1399.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<meta name="StackTrace" content />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline}
</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>An internal error occurred on the report server. See the error log for more details. (rsInternalError) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.1399.00" target="_blank">Get Online Help</a></li><ul>
<li>Could not load type 'Microsoft.Web.Services.ScriptHandlerFactory'. (c:inetpubwwwrootweb.config line 45)</li><ul>
<li>Could not load type 'Microsoft.Web.Services.ScriptHandlerFactory'.</li>
</ul>
</ul>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</body>
</html>
--. (Microsoft.SqlServer.Management.UI.RSClient)
------------------------------
BUTTONS:
OK
------------------------------
I have now uninstalled SQL Reporting Services, reinstalled it, but I am still getting the same error. From what I can infer, there is a problem with the httpHandler in the web.config file. It cannot load Microsoft.Web.Services.ScriptHandlerFactory.
Does anybody know anything about this or what else I might need to check? This has been going on for two days now and short of totally uninstalling all of SQL, I don't know what to do.
Thanks.
View 13 Replies
View Related
May 13, 2013
I'm setting up my first pair of Sql 2012 servers using AlwaysOn. I set up backups to run on the primary and I understand that you can set up backups to run on both the primary and secondary servers but the secondary will fail. Is there a way I can stop the secondary server from sending out error messages about failed backups? Is it possible to script it so that the server looks at whether it's primary or secondary and turns on or off alerts based on that?
View 8 Replies
View Related
Apr 29, 2014
I need to set up the services locally.
I deployed the web services locally to iis. When I browse that web service, I am getting
<%@ ServiceHost Language="C#" Debug="true" Factory=" " Service=" " %>
What is that mean
I am using iis 8 version and windows 8
View 1 Replies
View Related
Apr 27, 2015
Working with a brand new environment. During install (both SQL 2012 and 2014) on any one of four servers, they all fail on the service accounts page with a "RPC server is unavailable" error and "The SQL Server service account login or password is not valid."
I've verified that RPC server is unavailable is related to only the agent, and the account invalid is only for the engine. Verified this by trying each one separately and putting the other back to default. For the latter (account login or password is not valid), that's definitely not accurate being that I can use that account & password successfully elsewhere.
The best article I've found so far is here - [URL] .....
I've verified all 5 are in place and changed the group policy. Still errors out every time. Even if I can fix the RPC issue, not sure what's wrong with the engine account? I think both issues still stem back to the RPC, even though the engine doesn't report it in the setup. In the detail log, I'm assuming "Slp: Sco.User.OpenRoot - root DirectoryEntry object already opened for this computer for this object" is the core of my problem.
Here's a full output of a failed run:
(08) 2015-04-27 18:28:19 Slp: Sco: Attempting to determine if the password is required for account 'MyDomainsvcSQLEngine'
(08) 2015-04-27 18:28:19 Slp: Sco: Attempting to determine if the account 'MyDomainsvcSQLEngine' is Virtual Account
(08) 2015-04-27 18:28:19 Slp: Sco: Attempting to get account sid for user account MyDomainsvcSQLEngine
(08) 2015-04-27 18:28:19 Slp: Sco: Attempting to get sid for user account MyDomainsvcSQLEngine
[Code] ....
View 8 Replies
View Related
