June 13, 2011 at 2:25 am
Hello Guys, Recently I was looking for one option as per our requirement. I've read about Mirroring, replication etc. I have one prod server and now need one different sql instance on the same server dedicated for reporting purpose. I am not looking for any data latency.What should be the best option in this scenario ?
June 13, 2011 at 3:05 am
Total Processors ?
Total RAM ?
Total Drives ?
OS version ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 13, 2011 at 4:07 am
There's always some latency. It's unavoidable if you're talking about getting data to another instance in a form that can be queried. How much is another matter.
Is the second server intended primarily for reporting or high availability?
Assuming it's a reporting server, how far behind can the data be?
Will the permissions on the report server be the same as the main database or different?
Will there be any need for data or schema modifications on the report server that aren't made to the main database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2011 at 2:10 pm
sqlnaive (6/13/2011)
Hello Guys, Recently I was looking for one option as per our requirement. I've read about Mirroring, replication etc. I have one prod server and now need one different sql instance on the same server dedicated for reporting purpose. I am not looking for any data latency.What should be the best option in this scenario ?
The only advantage to using another instance on the same server is to reduce the locks associated with the report queries. You will still suffer resource issues on the server that could cause serious performance issues for your production system.
I would not recommend going down that path - and instead build another server to host a copy of the database for reporting.
If you need real-time reporting, the only option you have is replication. All other options are going to lag behind the production system. And, replication will not be instant - there will still be some latency.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 13, 2011 at 3:19 pm
I ws actually looking from same point of view. To reduce lockings. However just this factor does not justify to have different sql instance on same server or does it ?
June 13, 2011 at 4:43 pm
sqlnaive (6/13/2011)
I ws actually looking from same point of view. To reduce lockings. However just this factor does not justify to have different sql instance on same server or does it ?
I have to say it depends. What resources are available. I would be concerned with IO and CPU utulization.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 13, 2011 at 10:51 pm
If you are going to install a second instance of SQL Server you should have enough hardware capability to manage this second instance like Processors Core,RAM and Separate Disk for performance point of view,If you are facing locking issues then you have to optimize your queries and your database phsical Structure, how you can reduce locks
1-Optimize your queries
2-Check Indexes and drop unused indexes
3-Increase the Files in a FileGroups (when you have multiple Core or physical Processors)
4-Change the FileGroup of Non-Clustered Indexes and If clustered index is not a primary key then you can move easily clustered indexes in a separate filegroup
5-Divide large data tables into partition when you partition a table and Esclation is on AUTO mode then SQL Server lock the specific partition not the whole table
before analyze the issues,second instance option is not a solution,Another solution you can create transactional replication for the reports on a separate server but you have to analyze the issues first then got for the solution
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 13, 2011 at 11:49 pm
Syed Jahanzaib Bin hassan (6/13/2011)
3-Increase the Files in a FileGroups (when you have multiple Core or physical Processors)
The only time that is useful is if the DB is under IO pressure and those new files go onto separate physical drives (not related to number of cores or processors). There's little-no gain (usually no) for just creating additional files in a filegroup if they're on the same drive. (That's a tempDB specific optimisation and has to do with the way TempDB is used)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2011 at 2:55 am
When a Core is busy on a file of a filegroup then a second core can access the second file of a filegroup , that is not related only the tempdb ,its apply in the user databases also.
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 14, 2011 at 3:02 am
That's based on the myth that SQL uses one thread per file. It is purely a myth. SQL can use multiple threads to access data regardless of the file layout.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply