Friday, August 30, 2013

[SQL 2012] Date time Query

[SQL 2012] Date time Query

Date time Query

Posted: 30 Aug 2013 03:34 AM PDT

I want to add a condition for date time range of yesterday 3pm . Basically I want to write something like below SELECT * FROM abc WHERE DATETIME between 'Yesterday 3pm' and 'Today 2:59pm'Please help me to add the date time code for the above.

Sql Server 2012 performance

Posted: 29 Aug 2013 07:21 AM PDT

Our Server1 Db is running on SQL server 2008, and the server has 2 processors, 2.67 GHz , 60 GB memory, 64 bit operating System, Windows server 2008 R2, Enterprise.The Server2 server Db is running SQL server 2012, and the server is 2 GHz 2 processors, 128 DB memory, 64 bit Operating System, Windows server 2008 R2, Enterprise.They are both on the same network and use SAN drives with similar performance capacity.The CPU_Count used on Server2 is 32, while it is 24 on production.They both have the same database, as we restored the db on server2 from server1 db.We have very large difference in the time taken to run an ETL on the two servers. It takes 6-7 hrs on server1, while it takes 24-28 hrs on server2Both are running the same code, and processing the same amount of data. We have run several rounds of this ETL on both servers, but the timing is still the same.We were expecting it to take much less on the more robust server2 with latest version SQL. What can we look at to improve performance on server2.

Upgrade SQL Server 2012 from 2008 R2

Posted: 30 Aug 2013 01:50 AM PDT

I have a new Server with Windows Server 2012 installed.I'm going to install SQL Server 2012 Enterprise Edition.I have some SQL Server 2008 R2 Databases which I want upgrade to 2012.Can I upgrade without installing 2008 R2 on the new Server?If I take the backup and perform a restore what else do I need to do to in addition to setting the compatibility mode?Any input would be greatly appreciated.

Unable to see User Databases in SSMS

Posted: 29 Aug 2013 04:46 PM PDT

Hi, need helpI set db_datareader role to the Security Login Group for the User Database in SQL2012 instance on Virtual Machine.This Group cannot see this database in 2008 SSMS under Databases. The only way they can see this database if I would assign this Group a sysadmin role.The same group does not have any problems to see another User database in 2008 SSMS from another SQL2012 Server with the same role.This 2nd one is an actual Server (not Virtual Machine) that is probably not a relevant information.Both Servers have the same version of Windows (Windows 2012) and SQL Server (SQL Server 11.0.3128)Any help would be greatly appreciated.Thank you Alex

SQL 2012 AlwaysOn failover/failback

Posted: 30 Aug 2013 01:04 AM PDT

I'm looking into using SQL 2012 AlwaysOn for DR purposes in our shop and don't find much information on the failback process. Can anyone comment on the failback process or point me to a good link? Is failback exactly the same as failover? I assume there must be some process to run to resync the servers before failing back. This is primarily what I am interested in knowing more about. For example, node 1 fails and it fails over to node 2. it takes a week to repair node 1 (or recover from a true disaster). Now we have a weeks worth of data to mirror back to node 1 before failing back. Is this built in to the failback process? How is this managed and how do you check the state of the mirroring? I would also be interested to hear any general comment, good or bad, tips, tricks, gotchas that anyone has with AlwaysOn. Many thanks!

Close existing connections

Posted: 29 Aug 2013 07:51 AM PDT

While attempting to use SSMS to do a "database" restore I am unable to select "Close existing connections to destination database" on the options page of the restore wizard. In the past I have been able to use this. All of my server except one are now like this. Any ideas what I have done to cause this?[img]http://www.sqlservercentral.com/Forums/Attachment14256.aspx[/img]

Backup / Restore user with minimum permissions

Posted: 29 Aug 2013 07:11 PM PDT

I would like to create a user (SQL Auth. or Windows Auth., doesn't matter) which is only able to backup and restore one or more (but not all) database(s) using SSMS. The user should not be able to do anything else on the SQL server, it should have a minimum set of permissions. Basically i want to create a sort of "backup operator" user which can backup/restore specific databases on the SQL server without being able to do anything else. So far i only found "solutions" for SQL server 2008 or 2005, but unfortunately those "solutions" don't work with SQL server 2012 anymore, apparently because of changes to the permissions/server roles in SQL 2012.The main problem is, that to execute the RESTORE command, the user must have either sysadmin or dbcreator server roles or be the owner (dbo) of the database (the database role db_owner is not enough, it doesn't include the RESTORE permission). I don't want the user to have any of those three permissions/server roles because these permission allow the user to do way more than just backup/restore. sysadmin is out of the question (you can do practically anything with that permission), dbcreator is out of the question (as you can create, alter and drop ANY database with that permission) and the owner of the database should be sa and nobody else.Apparently it is not possible to specifically give a user the RESTORE permission, because there simply is no such permission. The RESTORE permission is only "part" of sysadmin, dbcreator or the dbo and can't be "assigned" to any other server/database role or user.Has anybody implemented such a "back operator user" with SQL 2012 before? Right now i'm completely stuck in implementing this, and i see no way of doing this with SQL 2012.

USERSTORE_TOKENPERM is Huge

Posted: 29 Aug 2013 04:36 AM PDT

I'm having problems with my USERSTORE_TOKENPERM cache becoming growing. When I came in this morning it was 2.4 gb and was actually larger than my buffer pool. I cleared it out and an hour later it is already back up to 1.8 gb. All the information I can find talks about sql server 2005 and it sounds like this was supposed to be fixed long ago. Below is the token class that is taking up the most space. Anyone have any ideas?TokenCount Token Name Class SubClass167214 TokenAccessResult 65535 0
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog