Wednesday, May 22, 2013

[SQL 2012] SQL Server Cumulative Updates broken?

[SQL 2012] SQL Server Cumulative Updates broken?

SQL Server Cumulative Updates broken?

Posted: 22 May 2013 02:46 AM PDT

I've been trying to download a CU for SQL 2012 all day but am hitting a wall. Using SQL 2012 CU5 as an example, I go to the relevent MS page, click on the button to request the hotfix, get taken to the next page to enter my details and get a lovely page of blankness.I've tried several CU's for 2008 and 2012 (and multiple browsers, machines and locations) but keep getting the same blank screen issue.Does anyone have a copy of the CU that they can point me at a download for, I've got a call back due with MS, but this is causing me problems as I can't proceed with the install of SCCM 2012 SP1 until I get CU2 or above working. I wouldn't have a problem if SQL 2012 SP1 worked, but I guess MS decided to delay that working with SCCM for now.ARGH!!!!! and thanks

Managed Service Accounts and Cannot Generate SSPI context error when the password changes

Posted: 21 May 2013 07:44 PM PDT

Hi, I regularly (every month or so) get the error "The target principle name is incorrect. Cannot generate SSPI context" when trying to remotely connect to my SQL 2012 instance. The SQL service is running using a managed service account. I understand this error can occur when the service account cannot authenticate with AD properly. Looking at the properties of the managed service account, the password for the account was automatically changed this morning - just when the error started. I am therefore assuming that this problem is something to do with this, but I am not sure. I can fix the issue by restarting the SQL Service, but that is not ideal. Does anyone have any pointers as to how I can stop this problem from happening again?Thanks.

SSIS Execution Reports Permission

Posted: 22 May 2013 12:42 AM PDT

How do I grant a regular user or group the same rights to view the SSIS execution reports as say the sysadmin role?I need to figure out how to give users the visibility of those reports without actually granting sysadmin.

Estimating Tempdb Size and Log File Sizes

Posted: 21 May 2013 11:01 PM PDT

We have an Oracle Database with a size of about 80 GBs with 8 Redo Logs of 300 MBs each. Just wondering what would be a good starting point in SQL Server 2012 for this same database? I know there are more factors that go into trying to derive the Database Size but this is all I have right now. (I also will try to get the vendor input.) It is a new application (Student Information System for a large school district; about 60,000 students, 4,000 staff.) Is there some kind of guideline, such as set your tempdb to 40-50% of user database size and set log files to 25-30% of user database's data files (primary and secondary) size?Thanks in advance, Kevin

Number of Database in SQL 2012 Instance with AlwaysOn

Posted: 21 May 2013 03:00 PM PDT

I am involved in project of upgrading database servers to SQL 2012 using AlwaysOn functionality. It is still in planning phase and i am currently working on capacity planning.My question are1. Is there any restriction on number of databases in one availability group? 2. Current SQL 2008 R2 instance is hosting 600 databases, has anybody experienced any issues with these number of databases running on 1 SQL instance? I know technically i can have 32k databases on 1 instance but in reality considering transaction log backups, agent jobs running on each database and other DB maintenance tasks, it will be a challenging task for DBA maintaining these databases. Question is, has anyone worked with these number of databases before and what were their experiences.3. Few databases are being replicated to other database servers, what special care do i need to take in order to setup replication with AlwaysOn?

AlwaysON Multi Subnet Cluster, AG Listener

Posted: 03 May 2013 01:00 AM PDT

HelloI am not sure if this is a SQL issue or Windows Cluster issue, but am trying to rule out it being a SQL issue.When I fail an availability group between subnets, I am finding that the DNS entry in DNS is staying. So what happens is the Availablity Group listener has 2 records in DNS, one for each IP. This causes the App to timeout at times, since DNS will return either of the two IP's.Anyone ever run into this before?Qsac

Is there an efficient way to do dynamic sorting within SQL ?

Posted: 21 May 2013 05:05 AM PDT

USE [JobPortal]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO ALTER Procedure [dbo].[GetAllJobsSearched] -- Add the parameters for the stored procedure here @Title varchar(250), @Industry int, @Industry2 int, @Industry3 int, @Date int, @JobTitle int, @JobType int, @Experience int, @Education int, @State int, @City int, @Salary int, @MaxSalary int WITH RECOMPILE AS DECLARE @ActualDate DateTime = cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20));IF (@Title = '') SET @Title = NULL;WITH EMPLOYMENT_OPPORTUNITIES_CTE(rownumber, id,title,contactperson,lastmodified,description,workexperiencetypeid, workexperience,jobtypeid,AcademicExperienceTypeId, industryid,industryid2,industryid3,salaryminid,salarymaxid, jobTitle,city,state,PostalCode,positions,deadline)AS( SELECT top 4000 ROW_NUMBER() OVER (ORDER BY lastmodified DESC) RowNumber, e.id,title,contactperson,lastmodified,e.description,workexperiencetypeid, isnull((select we.[Name] from workexperiencetypes we where we.id=e.workexperiencetypeid),'') as workexperience, isnull((select jot.[Name] from jobtypes jot where jot.id=e.jobtypeid),'') as jobtype, isnull((select edu.[Name] from Degree edu where edu.Id=e.AcademicExperienceTypeId),'') as education, isnull((select ind.[Name] from industries ind where ind.id=e.industryid),'') as industryname, isnull((select ind.[Name] from industries ind where ind.id=e.industryid2),'') as industryname2, isnull((select ind.[Name] from industries ind where ind.id=e.industryid3),'') as industryname3, salaryminid,salarymaxid, isnull((select jt.[Name] from jobTitles jt where jt.id=e.jobtypeid),'') as jobTitle, isnull((select ci.[Name] from cities ci where ci.id=c.cityid),'') as city, isnull((select r.[AbbreviatedName] from regions r where r.id=c.regionid),'') as state, c.PostalCode, positions, substring(cast(e.lastmodified as varchar(20)),1,12) as deadline --ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber from EmploymentOpportunities e With (nolock) inner join companys c on e.officeid=c.id where (@Title IS NULL or title = @Title) and (@Industry = 0 OR e.industryid = @Industry) and (@Industry2 = 0 OR Industryid2 = @Industry2) and (@Industry3 = 0 OR Industryid3 = @Industry3) and (@Date = 0 OR lastmodified >= @Date) and lastmodified is not null and lastmodified > @ActualDate and (@JobTitle = 0 OR title = @JobTitle) and (@JobType = 0 OR jobtypeid = @JobType) and (@Experience = 0 OR workexperiencetypeid = @Experience) and (@Education = 0 OR academicexperiencetypeid = @Education) and (@State = 0 OR c.RegionId = @State) and (@City = 0 OR c.CityId = @City) and (@Salary = 0 OR SalaryMinID >= @Salary) and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary))SELECTid,P1.lastmodified as deaddate,P1.Title,P1.city + ', ' + state + ' ' + PostalCode as Location,contactperson as ContactPerson,description as Description,workexperience,jobtypeid as jobtype,academicexperiencetypeid as education,industryid as industryname,industryid2 as industryname2,industryid3 as industryname3,salaryminid as salary,salarymaxid as maxsalary,jobtitle as jobTitle,positions as Positions,deadline, RowNumber,(select max(RowNumber) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM (SELECT RowNumber,id,lastmodified,title,city,state,PostalCode,contactperson,description,workexperience,jobtypeid,academicexperiencetypeid,industryid,industryid2,industryid3,salaryminid,salarymaxid,jobtitle,positions,deadlineFROM EMPLOYMENT_OPPORTUNITIES_CTE ) p1The above query in its current state searches 600k records in approx. 300 ms.I have tried numerous ways to do dynamic sorting on LastModified, which is a date field, or Title, which is a Job Title field, or City, which is a City Name Field.Regardless of which method I have tried, the dynamically sorted query always takes approx. 8 seconds !At the moment I am doing the sorting in C#, but would like to be able to do the sorting in SQL, if I can get the time under one second, and the data sorted in the correct order. Any suggestions would be greatly appreciated.Thanks The above fields are also sorted ascending or descending.

permission problems with additional drives

Posted: 21 May 2013 06:47 AM PDT

Hello,I have a two questions about permissions when installing SQL 2012.I've created additional drives for Data, Logs and tempdb on my new SQL server. I install SQL 2012 SP1 (on Windows 2012), use the defaults for the service accounts (nt service\mssqlserver), add the local administrators group as server admins and choose mixed mode authentication.The install goes just fine but after, I cannot login to management studio on the sql machine using my domain account when I'm on the sql machine (even though I'm part of a group that is part of the local admins group). I can login when I use management studio on another machine.If I add my teams group as a login, then I am able to use management studio locally. Is this expected? (using sa locally is fine and that's how i was able to add the active directory group)The other thing I don't understand is why I don't have permission to browse the "data" directory on any of my additional drives via windows explorer. From the Windows desktop, if I try to browse to L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\data, I will get an error saying I don't have permission to access "data" but I can click the button to get access to it (which I haven't clicked yet). I have no problems drilling down to that folder, just getting into it is the problem.If I try to browse to the machine via the network, ie. \\10.2....\l$\Pro..... Then I can access the folder no problem and it appears the owner if the local administrators group. If I login to the machine with the Administrator account, I can access the folder. If I use another local account in the local administrators group, I get the same error as my domain account.Is this expected or do I need to set some permissions before installing SQL? (This same issue affected my ability to browser via management studio as well)This is all a brand new setup for it's just a plain vanilla install of Windows 2012 (UAC disabled) and a new install of SQL 2012.If anyone can provide some insight I would really appreciate it.thanks

Re-partitioning a large table

Posted: 21 May 2013 05:54 AM PDT

Current partitioning strategy does not restrict growth within a single partition where we already see 576,114,055 records and growing. Operations on that specific partition are taking unacceptable amount of time. We'd like to re-partition the table with something more manageable but the problem is that the amount of locking that is going to take to merge all partitions into one, switching it out to a non-partition table and then re-partitioning the new table, is unpredictably long (can't simulate production load).Looks like this is the case with just 3-4 partitions, others are 3-5mm range. Any suggestions on how to address those few partitions without re-partitioning the entire table? Or if we have to re-partition, is there a better way than the one proposed here?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog