Showing posts with label SQL 2012. Show all posts
Showing posts with label SQL 2012. Show all posts

Thursday, March 21, 2013

[SQL 2012] Always ON Secondary database Read Only Connection.....Please help.

[SQL 2012] Always ON Secondary database Read Only Connection.....Please help.

Always ON Secondary database Read Only Connection.....Please help.

Posted: 21 Mar 2013 02:17 AM PDT

Dear All,I have configured SQL always on with two server and enabled SQL Server 2012 AlwaysOn Availability Groups Read-Only Routing by using below query.But when i am checking the Read only connection using (sqlcmd -S X.X.X.X -E -d DatabaseName-K ReadOnly) the connection is still routing to primary serever only...Please help if any additional things to do--Specify a read_only_routing_urlALTER AVAILABILITY GROUP AlwaysOnAGProdMODIFY REPLICA ON 'ALWAYSON-AG1'WITH( SECONDARY_ROLE ( READ_ONLY_ROUTING_URL='TCP://ALWAYSON-AG1.TESTDOMAIN.local:1433' ))-------------------------------------------------------------------------------------------------ALTER AVAILABILITY GROUP AlwaysOnAGProdMODIFY REPLICA ON 'ALWAYSON-AG2'WITH( SECONDARY_ROLE ( READ_ONLY_ROUTING_URL='TCP://ALWAYSON-AG2.TESTDOMAIN.local:1433' ))---------------------------------------------------------------------------------------------------Specify a read-only routing listALTER AVAILABILITY GROUP AlwaysOnAGProdMODIFY REPLICA ON 'ALWAYSON-AG1'WITH( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =('ALWAYSON-AG2') ))-------------------------------------------------------------------------------------------------ALTER AVAILABILITY GROUP AlwaysOnAGProdMODIFY REPLICA ON 'ALWAYSON-AG2'WITH( PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =('ALWAYSON-AG1') ))-------------------------------------------------------------------------------------------------Testing Method:C:\ sqlcmd -S X.X.X.X -E -d DatabaseName -K ReadOnly: Select @@serverName: GoPrimary server Name am getting.!

SQL server 2000 upgrade to SQL server 2012

Posted: 20 Mar 2013 04:21 PM PDT

While migrating from SQL server 2000 databases to SQL server 2012 databases , we need to follow one of the below approach .SQL server 2000>>SQL Server 2005 SP4 >> SQL server 2012• Clean approach to migrate Remediation of Non ANSI SQL 2000 code to ANSI complained code • TOC of SQL server 2005 is less than the TOC for SQL server 2008• Amount of remediation for data type deprecation, syntax changes is high from SQL 2000 to SQL 2008 migration SQL server 2000>>SQL Server 2008 R2 >> SQL server 2012• Remediation for SQL 2000 and SQL 2005 will be handled in on go• TOC of SQL server 2008 is higher than the TOC of SQL server 2005• Most of the remediation issue will be address at the intermediate migration Kindly let me know your views about the best approach .

system FlushCache happening often

Posted: 24 Sep 2012 03:28 AM PDT

SQL 2012 - enterprise... OLTP environment...Experiencing the following - several times per hour. Has anyone experieced the following:FlushCache: cleaned up 513008 bufs with 448519 writes in 75641 ms (avoided 92621 new dirty bufs) for db 10:0average throughput: 52.99 MB/sec, I/O saturation: 104, context switches 5758last target outstanding: 80800, avgWriteLatency 0The system is generating the flush cache and causing severe performance degradations.

An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue'

Posted: 20 Mar 2013 10:10 PM PDT

What is this? How do I fix it? I get a ton of these and it slows down the server for 5 minutes.An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue' for its status. Error: 1222, State: 51. Lock request time out period exceeded. This is an informational message only. No user action is required.

Video tutorials for MDS and DQS for 2012

Posted: 20 Mar 2013 04:59 PM PDT

Hi Team,Can any one point me to good video tutorials on using features in MDS and DQS.I am good with installing the MDS. Thanks in advance

Licensing for Evaluation before Purchase?

Posted: 20 Mar 2013 04:34 AM PDT

We are looking to create a new SQL box, and there is also a desire by management to migrate from 2008 to 2012 SQL Server when they do it. Anybody know Microsoft's policy on evaluation before purchasing licenses (i.e. before they by licensing for 2012 on the box they want to make sure upgrading doesn't break their existing system)?

Data size error in SSAS 2012 Tabular Table Import Wizard for n/varchar(max)

Posted: 20 Mar 2013 11:06 AM PDT

Does anyone know the actual maximum string length values for columns of type varchar(max) or nvarchar(max) when using the Table Import Wizard in a tabular project?I'm working my way through the Wrox book Professional SQL Server 2012 Analysis Services with MDX and DAX and trying my hand at tabular projects for the first time. A couple of the tables provided in the AdventureWorksDW relational database have nvarchar(max) fields. When attempting to add these tables via the Table Import Wizard, I receive an error. No error number or code is provided, just this:[quote]The size of a data value in table '<table name plus long identifier string>' column '<column name>' was too large to fit in that column. The current operation was cancelled because another operation in the transaction failed.[/quote]If I filter out the column from the import, the rest of the tables import fine, but the text didn't mention needing to exclude the columns.I checked out the [url=http://msdn.microsoft.com/en-us/library/gg492146.aspx]Data Types Supported[/url] in BOL, and it contains the useful tidbit [quote]You cannot import from a varchar(max) column that contains a string length of more than 131,072 characters.[/quote] "OK," I thought, "These fields are nvarchar(max), so that would probably max out at 65,536 characters since each character requires twice the bytes. There must be strings longer than that in my data."However, when I ran both MAX(LEN(<field>)) and MAX(DATALENGTH(<field>)), one of the tables having the issue returned a maximum length for the field in question of only 34,568 characters and 69,136 bytes, well under the limit in BOL. Thinking it may be an issue with nvarchar(max) as opposed to varchar(max), I copied the data to a new table, moving the nvarchar(max) data to a varchar(max) field. Attempting to import from the varchar(max) field (now only 34,658 bytes) still generated the same error.I haven't been able to locate any other information online about the error or about actual maximums enforced or whether or not it may be impacted by available memory (since it all has to be stored in-memory for the VertiPaq engine). I'd much appreciate any insight anyone has to offer.

Upgrade SQL/SSRS 2008R2 to 2012 SP1

Posted: 20 Mar 2013 04:07 AM PDT

I'm testing upgrading SQL/SSRS from SQL 2008 R2 SP2 to SQL 2012 SP1 (11.0.3349). Most of my reports' data source is a Dynamics NAV DB where most of the number fields are decimal(38,20). I'm finding that when I have a zero value in a column on a report that the report loses formatting and throws xml exceptions when rendered to Excel. So a field formatted for currency would become 0.00000000000000000 and renders as text in Excel 2010, but if there's a value in the cell then the formatting is fine. I'm looking on the MS site and there is documentation that this is an Excel issue and was supposed to be fixed in a CU for Office 2010 Excel, but I didn't see it in the release notes for the CU. The error is "Excel found unreadable context.." There are no errors in the report itself only rendering. Rendering to other formats is fine. I've found a workaround where I change the value in a report by using an if statement to make it equal to zero (IIF(value=0,0,value), but I've got several hundred reports in my library and I wouldn't even know how many cells I'd have to change. I'm just looking to see if anyone else has ran into this issue during their upgrade testing process.I can repeat the error in a new report if I use as a Dataset Select Cast(0 as decimal(38,20)) d1 , CAST(0 as decimal(10,2)) d2Export to Excel gets this error<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <recoveryLog xmlns="<logFileName>error056200_05.xml</logFileName><summary>Errors">http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error056200_05.xml</logFileName><summary>Errors were detected in file 'C:\Users\...ME...\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5\P42V8D4D\_TestExcel.xlsx'</summary><repairedRecords summary="Following is a list of repairs:"><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet1.xml part</repairedRecord></repairedRecords></recoveryLog>The d1 column is now formatted as text 0.00000000000000000000 instead of as currency and the d2 column is formatted correctly
Posted by at No comments:
Labels:

Wednesday, March 20, 2013

[SQL 2012] AWE on SQL SERVER 2012 and Windows Server 2008 Data Center?

[SQL 2012] AWE on SQL SERVER 2012 and Windows Server 2008 Data Center?

AWE on SQL SERVER 2012 and Windows Server 2008 Data Center?

Posted: 20 Mar 2013 02:58 AM PDT

We have SQL Server 2012 Enterprise 64-bit installed on a Windows Server 2008 Data Center 64-bit. For some reason AWE is allocating all the unused memory from the OS. It's my understanding that AWE was depricated from 2012, which would make sense since I can't find anywhere to disable it in the SQL config. Is there some other way that AWE would be installed and enabled? Some other applicaion? 2008 is not installed and this is a fresh install with 2012, not an upgrade.Any thought you have would be appreciated.Thanks.

Blob data handling - best option?

Posted: 19 Mar 2013 07:31 PM PDT

HiI am a programmer and need to do the following.Currently we are using SQL Server 2000 and we are handling lots of resume/cv in .DOC and PDF formats. we use some 3rd party tools to extracts the contents of the file and store it in NTEXT column which is full-text indexed. Now we are planning to go with SQL Server 2012 Standard and i would like to get some advice on this scenario. 1. I dont want to use the text extracter (3rd party tools). Should i store the files as single BLOB and full-text index on it. This is main point as we search these very often to find the best matched candidate for a job requirement. Are is there any other best method to handle this situation? Thanks.

SSDT - Apply Different Security per Target Environment

Posted: 19 Mar 2013 09:19 PM PDT

HiHopefully the title makes my request obvious but just to elaborate. I am using SQL Server Data Tools for a SQL 2012 database and have set up 3 publish.xml files in order to publish the database to 3 different environments.I want to be able to alter the security that is applied to the DB post deployment depending on the environment I am targetting. Any pointers on how to do this?ThanksBen
Posted by at No comments:
Labels:

Tuesday, March 19, 2013

[SQL 2012] Reports Designed in SQL 2005

[SQL 2012] Reports Designed in SQL 2005

Reports Designed in SQL 2005

Posted: 19 Mar 2013 01:24 AM PDT

Hi. I have installed SQL 2012 into a dev environment to allow me to test ERP System reports. Using Visual Studio 2010 I have attempted to execute a standard sales report. This returns a rendering error "Index was out of range. Must be non negative and less than the size of the collection. Parameter name index".The report in question was developed for SQL 2005 and has hidden fields which were added in order to display header information on multiple pages. I understand this fudge is no longer required since SQL 2008.So my question. Where to start! I was hoping I would be able to push a magic button and update the format of older reports so they execute.Any tips advice welcome, I have hundreds of reports. Some execute but I am sure many will not.Kind Regards,Phil.

Can't execute SSIS package from remote machine

Posted: 18 Mar 2013 09:26 PM PDT

OS: Windows 2012, standard, 64-bitSQL: 2012 + SP1 + CU2 = 11.0.3339We recently decided to "break apart" our BI environment. We used to have everything on one box, DB Engine, SSIS, SSAS & SSRS. Everything has been running fine, but we now have other projects using these services, so we decided to break them apart into their own boxes.We now have DB Engine on one Server, SSIS & SSRS on another server and SSAS on yet another server, so we now have three boxes that replaced one box. All are Windows 2012, standard, 64-bit with SQL Server 2012 + SP1 + CU2.Since some of our SSIS packages have to access external resources, we used a domain account for it's service account. The DB Engine and SSAS box are using the default service accounts when installed. I can execute the packages fine on the SSIS server, I can even execute them via SQL Agent jobs on the SSIS box (we did install a default instance of SQL on the SSIS box), however when I try to execute a package from my laptop, it fails with the ugly "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'". I immediately double checked my SPNs and they all looked correct for the SSIS server and the service account we are using (and we had no duplicates). I also double checked the User Rights Assignment in the Local policy editor and all the correct Rights have been assigned (Log on as a service, Bypass traverse checking, Impersonate a client after authentication). I'm stumped here. Anybody have anything else I can check or that I have overlooked?Thanks-A.

Connect button Grayed Out

Posted: 15 Jun 2012 01:43 AM PDT

I'm just starting to play with the 2012 version of SSMS, I got the 2012 Express with SSMS installer and installed an instance of 2012 on my laptop. I downloaded the 2012 Adventure works DB from codeplex and ran this query to install it:CREATE DATABASE AdventureWorks2012 ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_Data.mdf') FOR ATTACH_REBUILD_LOG;This generated a level 20 error:Msg 948, Level 20, State 1, Line 1The database 'AdventureWorks2012' cannot be opened because it is version 706. This server supports version 661 and earlier. A downgrade path is not supported.Uh oh, looks like I connected to my default 2008R2 instance, not my new 2012 instance.I went to press the connect button next to the database drop down list, I noticed that both connect and disconnect are grayed out.I know that a Level 20 error will kill your connection, but usually the connect icon becomes available. I was able to get around this by just executing a "USE Master" command to reconnect, then disconnect and connect to the proper instance, but I found this behavior odd.I couldn't find any info on this via Google or searching the forums here. Anyone else noticed this? To verify that this is caused by an error, I ran the following and it too grayed out both connect and disconnect:RAISERROR (N'This is message %s %d.', -- Message text. 20, -- Severity, 1, -- State, N'Error', -- First argument. 5) WITH log; -- Second argument.
Posted by at No comments:
Labels:

Monday, March 18, 2013

[SQL 2012] AlwaysOn Availability Groups Read-Only Routing

[SQL 2012] AlwaysOn Availability Groups Read-Only Routing

AlwaysOn Availability Groups Read-Only Routing

Posted: 17 Mar 2013 09:16 AM PDT

I have setup Always on with four serverServer Role AvilabilityMode Failover Mode ReadabilityModeServer1 - Primary Syncranized Automatic Failover YesServer2 - Secondary Server Syncranized Automatic Failover ReadOnly IntentServer2 - Secondary Server Syncranized Manual ReadOnly IntentServer2 - Secondary Server Asynchranized Manual NoI have given two connection string in application one for Readwirte and other for Readonly access and the same is validatingconnectionString="Data Source=192.168.1.1\SQL;Initial Catalog=DBNAME;User ID=XXXX;Password=XXXX;" providerName="System.Data.SqlClient"connectionString="Data Source=192.168.1.1\SQL;Initial Catalog=DBNAME;User ID=XXXX;Password=XXXX;" providerName="System.Data.SqlClient"Aplication Inetent=ReadonlyHere i have two doubts.1. Using second connection string which server wil be active for read only ( server2 or server3 ? or Both)2. If anything happend in my second server inbetween the trasaction what wil happend ?3. If failover comes to second server is it first server will automatically will readyonly load ?Thanks in advance
Posted by at No comments:
Labels:

Saturday, March 16, 2013

[SQL 2012] Instance won't start after changing Processor Affinity

[SQL 2012] Instance won't start after changing Processor Affinity

Instance won't start after changing Processor Affinity

Posted: 15 Mar 2013 04:55 AM PDT

I have a server which I'm trying to setup identically to our running production server and I'm running into issues. The hardware is identical, as follows:2x 8-Core Opteron 621232gb RAM670GB of Disk Space split 60 C: / 610 E:OS is Windows Server 2008 R2 EnterpriseSQL Server is 2012 SP1 with rollup (11.0.3128)The server has four instances installed, across which we're trying to split resources by setting Max RAM and Process/IO Affinity, like this:\NCR - 8GB, Process on Cores 1,2,3, I/O on Core 4\GTA - 8GB, Process on Cores 5,6,7, I/O on Core 8\ENT - 8GB, Process on Cores 9,10,11, I/O on Core 12\DOCINDEX - 4GB, Process on Core 13, I/O on Core 14Leaving 4GB, and Cores 0 and 15 free for the OS. The production server is setup like this and running fine. The development server is fine right up until I change the Process and IO affinity. As soon as I do that and restart the SQL Server Services or the whole box, GTA and DOCINDEX's services will start, then immediately stop. Since I can't get them started, I end up uninstalling and reinstalling the instance. Then they run fine with no process or IO affinity set. I can change the Max RAM, but if I change the Process or IO affinity again they do the same thing. I've tried stopping all the SQL services and just turning on DOCINDEX or just turning on GTA, thinking that it might be a problem with resource availability, but they still won't start. I've even gone as far as reformatting the entire box again from scratch, making sure I follow the procedure I wrote up when I built the production box and run into the same issue.The system event logs have no helpful information at all. The only event related to the services in question are a bunch of "This is informational" events describing the startup, then one saying "The service has entered the stopped state". The ERRORLOG has the same lack of information. I can post both if needed. Help!Jon
Posted by at No comments:
Labels:

Friday, March 15, 2013

[SQL 2012] how to create user that can login to create and edit but cannot delete?

[SQL 2012] how to create user that can login to create and edit but cannot delete?

how to create user that can login to create and edit but cannot delete?

Posted: 14 Mar 2013 12:45 PM PDT

Circumstances require a user that can be restricted to login to a specific database to create and edit data entered into tables but cannot delete anything. I presume this type of security has been around awhile but I could not find anything searching around so I ask is it possible and if so I need to learn how.I may also need some help with the T-SQL because the database is being generated by script that was generated by a tool and I will have to modify that generated sql script to create the user and login.

Can a database be part of an AlwaysOn Availability Group and also mirrored to another server?

Posted: 14 Mar 2013 05:39 AM PDT

Hi,If a database is already part of an AlwaysOn Availability Group, can it also be mirrored independently to another target server?In other words, can we have mirroring and AlwaysOn-Avail Groups applied on the same databases?
Posted by at No comments:
Labels:

Thursday, March 14, 2013

[SQL 2012] Is installing an instance of SQL 2008 on a box with SQL 2012 a supported option?

[SQL 2012] Is installing an instance of SQL 2008 on a box with SQL 2012 a supported option?

Is installing an instance of SQL 2008 on a box with SQL 2012 a supported option?

Posted: 14 Mar 2013 12:27 AM PDT

Not that we have immediate plans to do so. This is more about moving forward. We build really large clusters and stack instances on them for many different apps. We have been doing this with SQL 2008 for two years now. Now we would like to start moving to building 2012. We know that we can easily run existing 2008 alongside new 2012 instances, but the big concern is if the business buys an app that will not run on 2012 (which in health care happens a lot; the vendors are slow and cautious when approving new platforms) and we are forced to install a new instance of SQL Server 2008. It would be cost prohibitive to build a new cluster just for the 2008 install, so the question becomes whether we pre-install five or six 2008 instances and turn them off for future expansion, or if we can go back and add them individually if really needed. I know this would NOT work on, say, 2005 and 2000, but I am wondering if MS has improved interoperability by this time.
Posted by at No comments:
Labels:

Wednesday, March 13, 2013

[SQL 2012] table partitioning

[SQL 2012] table partitioning

table partitioning

Posted: 13 Mar 2013 12:37 AM PDT

I planning to do table partitioning using the region wise. in my region table there are 18 region are there.... is it required for 18 partition ?Could any one suggest me to do best in scenario.

Linked Server SQL 2000 - SQL 2012 Issues

Posted: 10 Jul 2012 11:44 AM PDT

We installed SQL 2012 recently... linked server between SQL 2012 and SQL 2008 using SQLNCLI11 is working fine; however, we have issues creating linked server between SQL 2000 and SQL 2012. [b]Msg 8522, Level 16, State 3, Line 1Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.[/b]I am not sure why I am getting this error when the linked server with SQL 2008 is doing fine and there is not MS DTC error also linked server using the same SQL 2000 server with SQL 2008 server is working fine as well so I don't think there is issue with configuring MSDTC. Thanks a lot for your help.

SQL Query

Posted: 13 Mar 2013 02:58 AM PDT

Hopefully, I am posting this in a right area if not I am sorry. Dont mind the $$$$,#### and **** I am trying to get the last 60 days of records from today, and I am having issued writing the query that returns that value....RDMS its tie to SQL/Oracle database and here is my query that is currently working but I just like to see the last 60 days if anyone can possible help!!!SELECT INFORMENT.PRODUCT_OFFER_PURCHASE.*******_#######, INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_ADDED, 'YYYYMMDD'), INFORMENT.DEPOSIT_$$$$$$$.BAL_LEDGER_CURRENT, INFORMENT.PRODUCT_OFFER_PURCHASE.INTEREST_RATE, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_OPEN, 'YYYYMMDD'), To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_CLOSE, 'YYYYMMDD'), INFORMENT.PRODUCT_OFFER_PURCHASE.*******_STATUS_CODE, INFORMENT.PRODUCT_OFFER_PURCHASE.CLIENT_DEFINED FROM INFORMENT.PRODUCT_OFFER_PURCHASE, INFORMENT.INVOLVED_PARTY, INFORMENT.DEPOSIT_$$$$$$$$$ WHERE INFORMENT.PRODUCT_OFFER_PURCHASE.INVOLVED_PARTY_ID_PRIMARY = INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID AND INFORMENT.PRODUCT_OFFER_PURCHASE.******** = INFORMENT.INVOLVED_PARTY.*********AND INFORMENT.PRODUCT_OFFER_PURCHASE.******** = INFORMENT.DEPOSIT_$$$$$$$.******** AND INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = INFORMENT.DEPOSIT_$$$$$$$.APPL_ID AND INFORMENT.PRODUCT_OFFER_PURCHASE.*******_######## = INFORMENT.DEPOSIT_$$$$$$$$.******_####### AND INFORMENT.PRODUCT_OFFER_PURCHASE.********* = '020'and(INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = 'SAV' or INFORMENT.PRODUCT_OFFER_PURCHASE.APPL_ID = 'DDA')

SSAS/MDX Unit Testing

Posted: 12 Mar 2013 08:31 PM PDT

Hi All,I've been doing a bit of searching round for ways to unit test cubes and the results seem to be limited. What we're trying to do is make sure that any releases we make to live don't alter the existing numbers, unless we are aware that this should be the case, obviously we'd then make a change to the tests.I've been toying with the idea of creating framework using ADOMD.NET, but this appears to be limited to single result set numbers, e.g.[code="sql"]Select [Measures].[Total Sales Value] On 0From [My Cube]Where [Date].[Year - Month - Day].[Date].&[20130313][/code]Does anyone have any ideas? In the meantime I'll continue searching round for ADOMD.NET stuff and post back any results I find.Cheers,Jim.

We have some issues on configuring IIS 7 with SQLSERVER 2012

Posted: 12 Mar 2013 01:32 PM PDT

Can anyone help us on configuring IIS 7 with SQLSERVER 2012?Regards,Ram
Posted by at No comments:
Labels:

Tuesday, March 12, 2013

[SQL 2012] upgrading PowerPivot for Sharepoint from 2008R2 to 2012

[SQL 2012] upgrading PowerPivot for Sharepoint from 2008R2 to 2012

upgrading PowerPivot for Sharepoint from 2008R2 to 2012

Posted: 12 Mar 2013 04:31 AM PDT

I am trying to upgrade my Powerpivot for Sharepoint 2008R2 to 2012 and struggling with many problems...I try to follow [u][url=http://msdn.microsoft.com/en-us/library/ee210646.aspx]Upgrade PowerPivot for SharePoint[/url][/u] which refers to [u][url=http://msdn.microsoft.com/en-us/library/hh230964.aspx]Upgrade PowerPivot for SharePoint (PowerPivot Configuration Tool)[/url][/u] In this latest article, I am supposed to use the new tool "Power Pivot Configuration Tool" that I can't see in my start up menus...Any idea how to install this tool?If I try to install "PowerPivot for Sharepoint", this is rejected because there is already an instance called "POWERPIVOT" (normal since I installed it with 2008R2)I am a bit confused... :doze:

Event Notifications

Posted: 12 Mar 2013 01:38 AM PDT

Hello,I am trying to set up event notifications on an instance of SQL Server in order to send me an email when a DDL statement is executed in that instance.I have created the queue, service, route and then the event notification but am unsure how what to do now.Does anyone have any documentation on what I need to do next in order to get this working?Thank you,Andrew

NUMA and PLE on SQL Server 2012

Posted: 04 Feb 2013 11:26 PM PST

I've read both Paul and Jonathan's blogs regarding this issue (http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/) and started looking at the PLE counters for each individual NUMA node. I can't seem to wrap my head around why there is such a widespread discrepancy between the NUMA nodes. We are running SQL Server 2012 Enterprise Core. Any insight would be greatly appreciated.Thanks,Tommy[url=https://skydrive.live.com/redir?resid=EB98D18648791013!7197&authkey=!AN6DKRDzgyJlarU]https://skydrive.live.com/redir?resid=EB98D18648791013!7197&authkey=!AN6DKRDzgyJlarU[/url][img]https://skydrive.live.com/redir?resid=EB98D18648791013!7197&authkey=!AN6DKRDzgyJlarU[/img]

Multiple instances referencing same master.mdf

Posted: 11 Mar 2013 03:37 AM PDT

Okay, this one has me stumped (or I have missed something about instance installations!)I have added some more instances on our server at work:MIRROR, STAGE and DEVELOP (these are in addition to the first installed instance SQL_EXPRESS). Each one has a [b]different[/b] data folder.Installations worked fine, however they now all show the [b]same[/b] list of databases as SQL_EXPRESS.I have checked MSDN and have checked the startup parameters ([b]-d[/b] for [i]c:\path[/i]\master.mdf) of the services for the other instances and they are correct. I know it's checking them as I gave a false path and it raised an error. I also checked the registry settings for each instance.The only thing I can think is that during installation I specified the same [i]Instance Root Directory[/i] - I would test this theory but I can't do a reboot. I would have thought it would have told me if I had misconfigured? Surely each instance has the option listed because each instance can run from a separate master.mdf and therefore data folder?Thanks in advance :-)

Feature Inquiry

Posted: 11 Mar 2013 10:14 AM PDT

Hello,Does 2012 offer any feature which allows for the collection of Instance data across all my production installs? In other words, what is a good technique to centralize (for documentation)All SSIS package (document only)All Jobs and schedualesAll logins and database mappingsInstance configuration parametersI can write SQL queries and run them on each server and centralize them manually...or I can deploy procedures an maintain those across all instances. But I'd like to do this with as little footprint as possible. Preferable none.What I'd like, is a feature where one of my instances is a repository and collects this information from the servers I choose. Does this make sense? How are people doing this and is there something in 2012 to help.
Posted by at No comments:
Labels:

Wednesday, March 6, 2013

[SQL 2012] SQL Server Always On

[SQL 2012] SQL Server Always On

SQL Server Always On

Posted: 05 Mar 2013 08:53 PM PST

Hi,I have setup a clustered environment for my SQL Server database and configured Always On replication with no issues, everything seems to be working perfectly, however I have one problem that I'm hoping someone has the answer to?!I have configured Read Only Intent on my secondary replica and setup a dummy report in SSRS that simply gets the @@SERVERNAME property. I have configured the applicaton intent = read only within my connection string and when I run the report it works perfectly, returning the secondary server name as my result set.What I want to know is there a way that I can do this within a SP so that when it is executed it automatically offloads the query to the secondary replica?? I've been scouring the internet for awhile trying to find an answer to this but so far I'm coming up blank!!!!!!Any help is appreciated.Thanks

always on availability group performance

Posted: 05 Mar 2013 05:16 AM PST

I'm looking for a way (DMV query possibly) to see how far the secondary replica has fallen behind under heavy database load (inserts, updates, deletes) on the primary. In other words, is there a way to see how many transactions the secondary needs to apply so it is caught up and in sync with the primary? I would like to do some performance testing in our lab.Also, is there an easy way to see the performance overhead cost of sync compared to async?

Using 'dbghelp.dll' version '4.0.5'

Posted: 04 Feb 2013 11:32 AM PST

Hello,This isn't specific to SQL 2012 after doing a bit of Googling, but it's writing to my Sql Server log roughly once every 5 seconds so pretty annoying.We've just installed sharepoint portal on it. Any ideas how I can fix it please?thanks

Enabling Always On Option in sql server 2012

Posted: 05 Mar 2013 03:57 AM PST

Hi , I hope to enable this Always On Availability group option the server must be in failover clustering. In our environment we have one sql server 2012 enterprise edition and two sql server 2008r2 standard edition. Is it possible to configure fail over between these nodes . Any option to enable Always On option in sql server 2012 . Kindly share your views. Need to make use of this option in sql server 2012 .
Posted by at No comments:
Labels:

Tuesday, March 5, 2013

[SQL 2012] Failing to deploy MDS package with MDSModelDeploy.exe

[SQL 2012] Failing to deploy MDS package with MDSModelDeploy.exe

Failing to deploy MDS package with MDSModelDeploy.exe

Posted: 25 Nov 2012 08:54 PM PST

Heya guys, i am deploying a .pkg for MDS using MDSModelDeploy.exeI am doing exactly what MS says. But get an error.this is my CMD code[code]c:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration>mdsmodeldeploy.exe listservicesMDS services (Service, Website, Virtual Path):MDS1, Default Web Site, MDSMDS2, Default Web Site, MDS1MDS3, ISPMasterDataService,MDSModelDeploy operation completed successfully. Elapsed time: 00:00:00.3989524c:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration> MDSModelDeploy.exe deploynew -package "C:\Programles\Microsoft SQL Server\110\Master Data Services\Configuration\Finance_data.pkg" -model Finance -service ISPMasterDataServiceMDSModelDeploy operation failed. Elapsed time: 00:00:00.0634293Error:The DeployNew command failed because either there is a missing - option or the name is not in quotes. All names that contain spas must be enclosed in quotation marks.c:\Program Files\Microsoft SQL Server\110\Master Data Services\Configuration>[/code]any idea why?thanks

Migrating from 2008R2 to 2012... any considerations?

Posted: 04 Mar 2013 07:04 PM PST

Hi,This week we will start migrating from SQL2008-R2 to SQL2012. Are they are known issues to consider? Or is it just a matter of restoring the databses from a backup and continue?Thanks,

Can't find options to retain partitions in SSAS Tabular Deployment?

Posted: 04 Mar 2013 11:05 AM PST

So I have created a new Tabular cube on 2012, deployed it for a while. And created a few new partitions via SSMS in the server to cover all the data. Now I have made some changes in the project, planning to deploy it to the server, but can NOT find an option to ignore existing partitions (that are not defined in the project itself) I have tried it on a test db, and no matter what i do... the project seems to deploy its definition, and overwrite what's on the server. meaning all the partitions that i created after deploy are WIPED! I have billions of rows of data, so reprocessing all the missing partitions are really not preferable... and managing the partitions in project but not in ssms is also not preferable as we typicall use scripts to add / manage partitions after it goes livewe have found some blog posts about changing .deploymentoptions file... but we don't know what value we should change to (not in BOL).. we changed it to "RetainPartitions" as a test.. but that doesn't workanyone has been through this? thanks

Error for Reinitialize Subscription in Snapshot Replication

Posted: 04 Mar 2013 06:37 AM PST

Hi all, I have Snapshot replication setup. I added a new article to my publication. I then went to the Replication monitor and right clicked on Reinitialize all Subscriptions. I get the error below. But when I manually run the SQL job which creates the snapshot and then run the job for the subscriber, everything runs fine. I don't understand the Reinitialize all subscriptions error message. I first thought this was a permissions issue, but have played around with the permissions in the distribution database and nothing has resolved the error. [i]Replication Monitor could not reinitialize one or more subscriptions that support automatic initilization. Additional Information: An exception occurred while executing a transact sql statement or batch. (Microsoft.SQLServer.ConnectionInfo)The subscripttion status of the object could not be changed. Could not update the distribution database subscription table. The subscription status could not be changed. Changed database context to "TestDatabase". (Microsoft SQL Server: Error 14068) [/i]
Posted by at No comments:
Labels:

Monday, March 4, 2013

[SQL 2012] Disk configuration

[SQL 2012] Disk configuration

Disk configuration

Posted: 03 Mar 2013 11:05 PM PST

Hi,I'm getting a new server this week and have some say in the disk configuration. Basically i've got 2 600GB SAS drives configured in RAID1 for the OS (Windows Server 2008R2 64Bit Std). I've then got 12 7.2k SAS disks for the data/logs giving 6TB of storage.My question is this....Is there any performance advantage to be had by separating logs from data files. This is going to be a data warehousing server...Ignoring the OS partition because there's nothing i can really do there....Is it more sensible to have a) 4 disks in a RAID 10 config for logs and then 8 disks in RAID 10 for Data?b) 12 disks in RAID 10 and have data and logs on the same drives?With regards to TempDB what would you do? Put it on the faster OS disks? or split it amongst the 4/8 or 12 disk configs?Thanks for your help!Simon

SQL Server Express and SQL Server Agent?

Posted: 03 Mar 2013 07:33 AM PST

I just installed SQL Server Express with Advanced Services but can't manage to get SQL Server Agent service started...[url=http://msdn.microsoft.com/en-us/library/cc645993.aspx][u]Features Supported by the Editions of SQL Server 2012[/u][/url] seems to indicate that SQL Server Agent is not supported with the Express EditionHowever, sql server agent seems installed...Is there anyway to get it working?I find slightly silly to install it with SQL Server Express if it's not meant to work??? :angry:
Posted by at No comments:
Labels:

Saturday, March 2, 2013

[SQL 2012] SSAS 2012 Actions not appearing in Excel 2010

[SQL 2012] SSAS 2012 Actions not appearing in Excel 2010

SSAS 2012 Actions not appearing in Excel 2010

Posted: 27 Nov 2012 08:48 PM PST

Hi,I have an SSAS cube that i want to add some actions to. I've had problems adding a reporting action to the cube so decided just to add a URL action instead. Start simple and build on the concepts...So i add a new action, give it a name, set the Target Type to Cells, Taget object to All Cells. I've put no condition on the action since i want it to appear all the time.The action content type is set to URL the action expression is set to "http://www.google.co.uk" I've also set a caption of "Google" under the additional properties and said that the caption is MDX (I'm aware that it isn't but i do intend to expand on this...).See the attached screenshot.I then build and deploy my cube, call up excel (2010) and then create a pivot table off the back of the cube but when i right-click the cells in the pivot table and go to "additional actions" it tells me that there are none specified.Does anyone know where i'm going wrong? I'm obviously missing something rather fundamental here.Thanks,Simon

Alias works with Linked Server but not with Management Studio

Posted: 01 Mar 2013 11:13 PM PST

I have a remote machine running SQL Server 2012 on Windows 2008 and am trying to connect to it from a machine running SQL Server Express 2012 on Windows 8.I have created an alias and am able to attach the remote machine as a linked server and browse the catalogue. When I try to connect to the alias through Management Studio, however, I get the network path was not found, error 53.Definitely not a problem on the remote server as I can connect from a different PC running SQL Server Express 2008R2 on Windows 2003.Any suggestions on what I should be looking at?

SSMS connection to SSIS with packages on remote MSDB server - is that even remotely possible?

Posted: 01 Mar 2013 09:03 AM PST

I've been working in a lab to see what is possible as far as separation of services in SQL Server 2012.I have this setup:Server 1: DB EngineServer 2: SSIS & SSASMy goal is to have the SSIS packages in a database on the main DB server (Server 1) and have the users access Server 2's SSIS via SSMS from their workstation. I'm finding that this is perhaps not possible. [url=http://www.sqlservercentral.com/Forums/Topic1373364-1550-1.aspx#bm1373371]As this forum post states:[/url]Which quotes [url=http://msdn.microsoft.com/en-us/library/aa337083(v=sql.110).aspx]this article from MS[/url]:[quote]Delegation Is Not SupportedSQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.[/quote]So it looks like i finally hit a brick wall. I'm wondering if there is any way to configure such a scenario (mostly for knowledge) or if that is even practical. I'd be curious to hear from experienced DBA's who can perhaps point me in the right direction. Thank you in advance.

Installing SS 2012 Enterprise Edition (Requirements Question)

Posted: 01 Mar 2013 06:44 AM PST

From looking at this link it appears that if i want to install Enterprise Edition on my computer, I need to install Windows Server. http://msdn.microsoft.com/en-us/library/ms143506.aspxCurrently I have Windows 7 Home Premium. I'm sure I don't need Enterprise but I was thinking why not? So i guess I'll do developer.Anyone ever use Windows Server? Is there any reason why I'd want to install that on my laptop? chances are I won't play around with it much anyway. But i guess it would not hurt to get some experience.I get all this software for free from my university.Thanks!

Writing Powershell for BI SSIS 2012 project package deployment

Posted: 01 Mar 2013 02:36 AM PST

As a Powershell newbie, I want to write a PowerShell script that will execute on the production server and create all the necessary objects from dev. for the SSIS 2012 project/packages needed. I need a Powershell script that can 1. Create the SSISDB Catlalog 2. Deploy the SSIS 2012 project/packages to the catalog.3. Create the sql agent job that will execute on a daily schedule one of the parent packages in the catalog.4. Execute 4 of the SSIS packages in the SSISDB catalog that will create the BI staging databases, tables and other stuff. Does anyone have a Powershell script that does a SSIS 2012 deployment. I have found nothing on the web to show me the syntax. The ????? below is an example of where no samples exist for the jobstep command.$jobStep.Subsystem = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::SSIS$jobStep.Command = ?????????????Thanks for any help or guidance to a resource
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)

Search This Blog